Bug #61215 FLUSH TABLES which never ends.
Submitted: 18 May 2011 11:21 Modified: 3 Sep 2011 12:18
Reporter: Simon E Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.1.49-1ubuntu OS:Linux (Ubuntu 10.10)
Assigned to: CPU Architecture:Any

[18 May 2011 11:21] Simon E
We have the problem, that much often a FLUSH TABLES tableX, tableY won't finish. It times out or has to be killed.

Look at this.. nothing is doing anything on the MySQL Server but the flush doesn't finish.

xy@localhost:~$ mysqlfullproc
Id   User Host       db   Command Time  State           Info
640  main localhost  NULL Query   227   Flushing tables FLUSH TABLES buffer.bucketcpvector_floating_impression,buffer.recommendations_seen,buffer.bucketcpvector_string_impression,buffer.widgetimpression,buffer.bucketcpvector_cluster_feedback,buffer.widgetintegrations,buffer.itemtagseen,buffer.itemtaghover,buffer.clickfeedback,buffer.bucketcpvector_floating_feedback,buffer.user_demographics,buffer.bucketcpvector_cluster_impression,buffer.bucketcpvector_string_feedback,buffer.activity,buffer.engagement,buffer.implicitratings
687  root localhost  NULL Query   0     NULL            show full processlist

Some "show full processlist" showed that the (connection) "Id" is growing one by one so i know nobody else comes on the server and is doing anything, all processes was stopped and after ~900s i killed the flush table command. "iotop" doesn't show anything doing on the harddrives. It would be really nice if somebody could explain how i can see who has a lock on a table.. thats something i really often wanted to know. Maybe there is a bug but it's hard to repeat (before this command there were many other commands in parallel but then stopped).

Best regards

How to repeat:
Can't repeat because the preconditions aren't clear.
[18 May 2011 11:30] Valeriy Kravchuk
When this will happen again, please 9after hanging for say 600 seconds), try to get the output of:

show innodb status\G
show full processlist;

and the results of:

mysqladmin status

in the error log. Just send the entire error log, compressed.

I'd also recommend to check if the same problem happens with recent version, 5.1.57.
[18 May 2011 12:09] Simon E
Something similar was happening right now and to connections hanging on flush:

Id      User    Host    db      Command Time    State   Info
7       localuser  localhost       ff    Query   206     Waiting for table       SELECT          stats.categoryid,
                        FROM            stats_cross_domain_category stats
                        INNER JOIN      categories USING(categoryid)
622     maintenance     externalhost:41021   NULL    Query   979     Flushing tables FLUSH TABLE ff.stats_intext
626     system user             NULL    Connect 3631    Waiting for master to send event        NULL
627     system user             NULL    Connect 0       Has read all relay log; waiting for the slave I/O thread to update it   NULL
744     maintenance     localhost       NULL    Query   983     Flushing tables FLUSH TABLE ff.stats_cpo
790     root    localhost       NULL    Query   0       NULL    show full processlist

the code which is doing the flush looks like:

LOCK TABLES ff.stats_cpo WRITE
FLUSH TABLE ff.stats_cpo  # this hangs

so i don't understand how this can happen because it has already a lock on the table.

We have no innodb:

(none)> show innodb status\G
ERROR 1286 (42000): Unknown table engine 'InnoDB'

The mysqladmin status:
Uptime: 4161  Threads: 14  Questions: 953905  Slow queries: 30  Opens: 21359  Flush tables: 1  Open tables: 3493  Queries per second avg: 229.248

Error log: there was nothing in there since 1h...

Newer version isn't as easy as it sounds like.. we can't test too much on this server because it is in production..
[18 May 2011 13:27] Valeriy Kravchuk
Sorry, I had to ask for

mysqladmin debug

and its results in the error log when you see the problem, not mysqladmin status. You should see all table locks set as a result.
[19 May 2011 9:25] Simon E
So there it is. You see the "Flushing tables" for ff.stats_widgetimpressions and statistics.by_channelid_and_day. The size of them are:

ff.stats_widgetimpressions (MYD/MYI)      = 80M  / 41M
statistics.by_channelid_and_day (MYD/MYI) = 235K / 136K

I have to say, stats_widgetimpressions is a partinioned table but this shouldn't matter because the other one is a 'normal' MyISAM table.
[22 May 2011 15:24] Valeriy Kravchuk
I see that thread 1888:

| 1888 | maintenance | server8:49997                          | NULL         | Sleep   |  1838 |                                                                       | NULL                                                                                                                                                                                                                                                                                             locked table:

Thread database.table_name          Locked/Waiting        Lock_type

1888    ff.stats_cross_domain       Locked - write        High priority write lock
1888    ff.stats_cross_domain       Locked - write        High priority write lock

and is sleeping for a long time. Do you know what is it doing, and why?
[1 Jun 2011 7:54] Simon E
yes the server8 is syncing the big tables with rsync to itself so it locks and flushes the table and then copy it. The table is quite big so it needs some time. But it shoudn't bother the other ones, should it?
[16 Jun 2011 10:47] Simon E
so is this a bug or not? nobody there with an idea?
[3 Aug 2011 12:18] Sveta Smirnova
Thank you for the report.

What is thhe difference between this bug and bug #45066 and bug #60917?
[3 Sep 2011 23:00] Bugs System
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".