Mysql – Will canceling InnoDB compression that’s already in progress corrupt data

Recovering Disk Space

As far as recovering InnoDB diskspace goes, you only have to migrate data out of ibdata1 and import it back one time if you enable innodb_file_per_table.

Once you do that, any InnoDB table that gets bloated can be shrunk. For example, if you have an InnoDB called mydb.mytable, you can shrink it with:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

or

OPTIMIZE TABLE mydb.mytable;

Restoring Databases

@Craig is totally right on MyISAM.

As for InnoDB, restoring tables individually can be a nightmare. This is the case because tables are either inside ibdata1 (innodb_file_per_table=0) or outside (innodb_file_per_table=1).

• For tables outside ibdata1, this is a 24/7 nightmare because the tablespace id of the .ibd file may change if a table is dropped and recreated throughout the course of a business day. If you backup the .ibd with its tablespace id that day, you cannot restore it to it DB Folder by dropping it in. You also cannot do any tricks ALTER TABLE tblname DISCARD TABLESPACE and ALTER TABLE tblname IMPORT TABLESPACE because the tablespace id is hardcoded. You have to write some custom adhoc scripts to pull off such a miracle in conjunction with ALTER TABLE tblname DISCARD TABLESPACE and ALTER TABLE tblname IMPORT TABLESPACE.
• For tables inside ibdata1, mysqldump is your friend. 'NUFF SAID...

That does not look like a corruption problem. It might be a collation issue, not with the database, but with the IIS 7 or Moodle.

You did not specify if MySQL was running in Linux or Windows, so here it goes:

I ran queries to unveil collations and character sets

For MySQL 5.5.12 running on a Windows 7 machine, I get this:

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+---------------------------------+
| Variable_name            | Value                           |
+--------------------------+---------------------------------+
| character_set_client     | latin1                          |
| character_set_connection | latin1                          |
| character_set_database   | latin1                          |
| character_set_filesystem | binary                          |
| character_set_results    | latin1                          |
| character_set_server     | latin1                          |
| character_set_system     | utf8                            |
| character_sets_dir       | C:\MySQL_5.5.12\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set (0.00 sec)

mysql>

For MySQL 5.5.9 running in Linux, I get this:

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (1.75 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (1.69 sec)

mysql>

You have to make sure your connector software's character set/collation settings match the database it is connecting to. If the REPLACE ran on the DB Server, the REPLACE is a case-sensitive match. I could easily see a collation shift in the eyes of IIS or Moodle messing up a server-side string replace.

You may want to substitute this with doing a client side replace using PHP's str_replace