Description:
Perform 'Flush table' command right after 'Lock table' as well as performing a concurrent 'Delete from' and 'Unlock table' afterwards will cause 'Show table status' output's 'Rows' column value to be incorrect.
How to repeat:
You would need 2 separate MySQL clients for this test.
1. Populate table 'product' with 4 rows as shown:
mysql> select * from product;
+------+--------+
| id | amount |
+------+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
+------+--------+
4 rows in set (0.00 sec)
2. From MySQL client #1, execute "LOCK TABLE product READ;" command.
3. From MySQL client #2, execute "DELETE FROM product WHERE id = 4;" command.
This client is now hung because client #1 has put a READ lock on 'product' table.
4. From MySQL client #1, execute "UNLOCK TABLES;" command.
Client #2's "DELETE FROM" statement will run after this one and the last row will be deleted.
5. From MySQL client #1, execute "SHOW TABLE STATUS LIKE 'product';" command.
The value on the 'Rows' column shows 3 which is correct (client #2's delete went successfully).
6. From MySQL client #1, execute "FLUSH TABLE;" command.
7. From MySQL client #1, execute "SHOW TABLE STATUS LIKE 'product';" command again.
ERROR: The value on the 'Rows' column now shows 4 which is incorrect.
The "SELECT * FROM product;" output only shows 3 rows and not 4.
Here's the snip from client #1:
mysql>
mysql>
mysql> lock table product read;
Query OK, 0 rows affected (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show table status like 'product';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| product | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 0 | NULL | 2013-08-21 20:29:03 | NULL | NULL | latin1_swedish_ci | NULL | | |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
mysql> flush table;
Query OK, 0 rows affected (0.00 sec)
mysql> show table status like 'product';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| product | InnoDB | 10 | Compact | 4 | 4096 | 16384 | 0 | 0 | 0 | NULL | 2013-08-21 20:29:03 | NULL | NULL | latin1_swedish_ci | NULL | | |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> select * from product;
+------+--------+
| id | amount |
+------+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+------+--------+
3 rows in set (0.00 sec)
mysql>
Here's the snip from client #2:
mysql> select * from product;
+------+--------+
| id | amount |
+------+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
+------+--------+
4 rows in set (0.00 sec)
mysql> delete from product where id = 4;
Query OK, 1 row affected (5.79 sec)
mysql> select * from product;
+------+--------+
| id | amount |
+------+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+------+--------+
3 rows in set (0.00 sec)
mysql>
