-1

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.

  • I think that fact that the "normal" CHECK TABLE doesn't fix it tell us there is no auto-repair going on. The default check for MyISAM is MEDIUM, which is more thorough and checks row checksum. The QUICK check doesn't scan rows, so there it's less likely to find table corruption: dev.mysql.com/doc/refman/5.5/en/check-table.html – socaire Oct 27 '18 at 13:51
  • @socaire Of course the MEDIUM (normal/default) check is more accurate than the QUICK check however that does not explain why i could still insert/read the table before/after the check nor why the "warning" of the QUICK check mystically disappears after a few inserts. – thx4help Oct 27 '18 at 15:15
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.