Is mysql using some kind of auto repair due to "check table" warnings?

Question

Today i noticed a "strange" behaviour of MySQL when using "CHECK TABLE" on a crashed table. First i got a warning like "Size of datafile is: * ... Should be: *" but i could still "insert into / select from" the table without issues and after a few inserts the warning was gone. I really dont get the point of this behaviour.

• OS: Debian 8.6 (jessie)
• MySQL Server version: 5.5.60-0+deb8u1-log (Debian)
• Debian database checks in "/etc/mysql/debian-start" and myisam-recover options in /etc/mysql/my.cnf are all disabled

My test was done as follows:

1. Created database "testing" and table "persons" for the test

   CREATE DATABASE IF NOT EXISTS testing;
   CREATE TABLE `testing`.`persons` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `lastname` varchar(255) NOT NULL,
     `firstname` varchar(255) DEFAULT NULL,
     `age` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
   

2. Created a simple php script (filltable.php) to continuously fill the table

   <?php
   while(1){
       $cid = mysql_connect('127.0.0.1:3306','root','pass') or die(mysql_error());
       $firstname=md5(uniqid(rand(), TRUE));
       $age=rand (12,127);
       mysql_query("INSERT INTO testing.persons SET 
           firstname='".mysql_real_escape_string($firstname)."',
           age='".$age."'
       ",$cid) or die(mysql_error($cid));
       usleep(5);
   }
   

3. I opened 3 different shells

   • The first for executing my php script to continuously fill my testing.persons table.
   • The second to kill the mysql daemon by intention to fake a table crash.
   • The third to execute other queries on the mysql server.

4. I started my test by executing the php script in the first shell

   php filltable.php
   

5. In the third shell i checked if the table rows counting up properly

   mysql> select count(*) from testing.persons;
   +----------+
   | count(*) |
   +----------+
   |    10647 |
   +----------+
   1 row in set (0.00 sec)
   

6. After about 30 seconds i killed the mysql damoen in the second shell to fake a table crash

   killall -s SIGKILL /usr/sbin/mysqld
   

7. Then i executed the following sql queries in the third shell to check how the table status changes

check the table

    mysql> check table testing.persons quick;
    +-----------------+-------+----------+-------------------------------------------------------+
    | Table           | Op    | Msg_type | Msg_text                                              |
    +-----------------+-------+----------+-------------------------------------------------------+
    | testing.persons | check | warning  | 1 client is using or hasn't closed the table properly |
    | testing.persons | check | warning  | Size of datafile is: 1877424       Should be: 1877376 |
    | testing.persons | check | status   | OK                                                    |
    +-----------------+-------+----------+-------------------------------------------------------+
    3 rows in set (0.01 sec)

again check the table

    mysql> check table testing.persons quick;
    +-----------------+-------+----------+-------------------------------------------------------+
    | Table           | Op    | Msg_type | Msg_text                                              |
    +-----------------+-------+----------+-------------------------------------------------------+
    | testing.persons | check | warning  | Size of datafile is: 1877424       Should be: 1877376 |
    | testing.persons | check | status   | OK                                                    |
    +-----------------+-------+----------+-------------------------------------------------------+
    2 rows in set (0.00 sec)

insert a new row

    mysql> insert into testing.persons set firstname='somename', age='111';
    Query OK, 1 row affected, 1 warning (0.01 sec)

again check the table

    mysql> check table testing.persons quick;
    +-----------------+-------+----------+-------------------------------------------------------+
    | Table           | Op    | Msg_type | Msg_text                                              |
    +-----------------+-------+----------+-------------------------------------------------------+
    | testing.persons | check | warning  | Size of datafile is: 1877424       Should be: 1877400 |
    | testing.persons | check | status   | OK                                                    |
    +-----------------+-------+----------+-------------------------------------------------------+
    2 rows in set (0.00 sec)

insert another new row

    mysql> insert into testing.persons set firstname='somename', age='222';
    Query OK, 1 row affected, 1 warning (0.00 sec)

again check the table

    mysql> check table testing.persons quick;
    +-----------------+-------+----------+----------+
    | Table           | Op    | Msg_type | Msg_text |
    +-----------------+-------+----------+----------+
    | testing.persons | check | status   | OK       |
    +-----------------+-------+----------+----------+
    1 row in set (0.00 sec)

Finally we end with status "OK" and the previous warning "Size of datafile is: 1877424.." is mystically gone away.

• What happens here?
• Why is the "warning" removed after the second insert?
• Does MySQL some kind of auto repair if you insert new data to a flagged table?

The situation is getting even more confusing if you are running the test above with a "normal" table check instead of a "quick" check. In that case the table is immediately detected as corrupted and finally marked as crashed:

check the table

    mysql> check table testing.persons;
    +-----------------+-------+----------+-------------------------------------------------------+
    | Table           | Op    | Msg_type | Msg_text                                              |
    +-----------------+-------+----------+-------------------------------------------------------+
    | testing.persons | check | warning  | 1 client is using or hasn't closed the table properly |
    | testing.persons | check | warning  | Size of datafile is: 1145136       Should be: 1145088 |
    | testing.persons | check | error    | Record-count is not ok; is 23857   Should be: 23856   |
    | testing.persons | check | warning  | Found 23857 key parts. Should be: 23856               |
    | testing.persons | check | error    | Corrupt                                               |
    +-----------------+-------+----------+-------------------------------------------------------+
    5 rows in set (0.01 sec)

again check the table

    mysql> check table testing.persons;
    +-----------------+-------+----------+-------------------------------------------------------+
    | Table           | Op    | Msg_type | Msg_text                                              |
    +-----------------+-------+----------+-------------------------------------------------------+
    | testing.persons | check | warning  | Table is marked as crashed                            |
    | testing.persons | check | warning  | 1 client is using or hasn't closed the table properly |
    | testing.persons | check | warning  | Size of datafile is: 1145136       Should be: 1145088 |
    | testing.persons | check | error    | Record-count is not ok; is 23857   Should be: 23856   |
    | testing.persons | check | warning  | Found 23857 key parts. Should be: 23856               |
    | testing.persons | check | error    | Corrupt                                               |
    +-----------------+-------+----------+-------------------------------------------------------+
    6 rows in set (0.01 sec)

To summarize:

The quick check is warning but after a few inserts the warning is gone and the check says the table is OK - it seems the table was never corrupted or mystically auto repaired. It is absolutely unclear for me why there are no issues inserting to or reading from the table at all - not before and not after the check while on the other hand a "normal" check is marking the table immediately as corrupted/crashed.

Answer 1

Use InnoDB instead of MyISAM. That will avoid the need for CHECK and REPAIR.

--myisam-recover-options=QUICK

https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_myisam-recover-options

myisamchk

https://dev.mysql.com/doc/refman/5.7/en/myisam-crash-recovery.html