MySQL Bugs: #17328: Incorrect Key_blocks_not_flushed reported after flush tables

Bug #17328	Incorrect Key_blocks_not_flushed reported after flush tables
Submitted:	12 Feb 2006 8:52	Modified:	18 Jan 2012 16:56
Reporter:	Martin Spasov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Linux (Linux x86_64)
Assigned to:		CPU Architecture:	Any

Description:
On the server with delayed key writes turned on, after a table flush I got:
| Key_blocks_not_flushed            | 18446744073709547828 |
After a while (few minutes) everything got fine:
| Key_blocks_not_flushed            | 524         |

The server is binary distributed EMT64, icc compiled.

How to repeat:
This happens on a busy (production) server after one or two days uptime.

Please, explain, why do you think that there is a bug here. Any explicit contradiction with a manual or something like that?

Well, it's an overflow. With enabled delay key writes SHOW GLOBAL STATUS reports about 30,000 key_blocks_not_flushed. I do a flush, then this large number is shown. After a while the counter overflows and continues to count from zero.

ps. I have disabled delay_key_writes, because It's causing table corruption I mentioned in bug 17329.

I had tried to repeat the problem you described with a following test case:

mysql> show global status like 'Key%not%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'Delay%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| delay_key_write        | ALL   |
| delayed_insert_limit   | 100   |
| delayed_insert_timeout | 300   |
| delayed_queue_size     | 1000  |
+------------------------+-------+
4 rows in set (0.00 sec)

mysql> create table tisam(c1 int auto_increment primary key, c2 char(20)) engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tisam(c2) values('abc');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tisam(c2) select c2 from tisam;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
...
mysql> insert into tisam(c2) select c2 from tisam;
Query OK, 4194304 rows affected (52.86 sec)
Records: 4194304  Duplicates: 0  Warnings: 0

mysql> show global status like 'Key%not%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 7248  |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> flush tables with read lock;
Query OK, 0 rows affected (1.30 sec)

mysql> show global status like 'Key%not%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

Is it similar to the situation you have? Can you repeat the problem you described on your server this way?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

also see bug #61130

Closed as duplicate of verified bug #61130