How to modify a table so that CHECK TABLE fails in mysql 5.1?

Question

I want to simulate an error for a table so that CHECK TABLE will fail. Is there a way to simulate it so that CHECK TABLE on the table will fail ?

Answer 1

WARNING: Before doing this, make sure you have a good backup, as you will lose data. Now is probably a good time to make sure you have binlog enabled and know how to do an point-in-time restore as well.

---

You can induce a corrupt table by shutting down MySQL, opening up the database file in an editor, changing/deleting a bunch of stuff, saving the file, then re-starting MySQL.

If you're using MyISAM tables, then just edit /var/lib/mysql/<db_name>/<table_name>.MYD. If you're using InnoDB and don't have innodb_file_per_table enabled, there's probably no easy way to do this. If you do have innodb_file_per_table enabled, then edit the /var/lib/mysql/<db_name/<table_name>.ibd file.

I just tested this with a MyISAM table:

mysql> check table watchdog;
+-------------------+-------+----------+-------------------------------------------------------+
| Table             | Op    | Msg_type | Msg_text                                              |
+-------------------+-------+----------+-------------------------------------------------------+
| e_d7.watchdog     | check | error    | Size of datafile is: 151154         Should be: 162296 | 
| e_d7.watchdog     | check | error    | Corrupt                                               | 
+-------------------+-------+----------+-------------------------------------------------------+