MySQL Bugs: #60917: DEADLOCK ending in FLUSH TABLES which never ends.

Bug #60917	DEADLOCK ending in FLUSH TABLES which never ends.
Submitted:	19 Apr 2011 8:46	Modified:	29 Apr 2011 19:55
Reporter:	Simon E	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.1.49-1ubuntu	OS:	Linux (Ubuntu 10.10)
Assigned to:		CPU Architecture:	Any

Description:
Each night when many operations on the DB are executed (because of a dayswitch and a full backup) the FLUSH TABLES hangs (more precisly each FLUSH TABLES and some LOCKS so maybe all thinks going on TABLE INFORMATION.. SELECT/INSERT seems to be ok). Nothing seems to run in parallel but nothing is going on.

Id	User	       Host	       db	    Command	Time	State	                                                                Info
1	system user		       NULL	    Connect	1289854	Waiting for master to send event	                                NULL
38179	system user		       NULL	    Connect	1	Has read all relay log; waiting for the slave I/O thread to update it	NULL
38913	master	       localhost       information  Query	23057	Waiting for table	                                                TRUNCATE stats_implicitratings_today
39387	maintenance    localhost       NULL	    Query	22862	Flushing tables	                                                        FLUSH TABLE information.stats_cross_domain_widget
39427	root	       localhost       information  Query	22367	Waiting for table	                                                show table status like 'stats\\_cross\\_domain\\_widget'
39432	maintenance    localhost       NULL	    Query	22920	Flushing tables	                                                        FLUSH TABLES buffer.bucketcampaignvector_floating_impression,buffer.recommendations_seen,bu.. (more buffer.* tables)
39453	maintenance    master8:59478   NULL	    Query	20894	Flushing tables	                                                        FLUSH TABLE statistics.by_campaignid_and_day
39460	maintenance    localhost       NULL	    Query	20415	Flushing tables	                                                        FLUSH TABLE information.ads_top_n
39474	maintenance    master8:33249   NULL	    Query	19095	Flushing tables	                                                        FLUSH TABLE information.stats_implicitratings
39568	maintenance    master8:39144   NULL	    Query	10094	Waiting for table	                                                LOCK TABLES statistics.by_campaignid_and_day READ
39589	maintenance    master8:39457   NULL	    Query	8295	Waiting for table	                                                LOCK TABLES information.stats_implicitratings READ

/s

How to repeat:
I'm not sure how to repeat it because it happens in the live environment with big tables (some are  3-8 GB). It's also over the day when a "FLUSH TABLES db.table" got a timeout but then only this one is dying and no global deadlock appear for all the things

Argh.. to see the output better:

http://dpaste.org/pia9/

I see that this statement 

TRUNCATE stats_implicitratings_today

is executed for a long time, and probably it blocks others.

So, what storage engine is used for this table and how big is it?

Also, please, check if the same problem still happens with a newer version, 5.1.56.

Ok i can think it's related to http://bugs.mysql.com/bug.php?id=45066

When you do something like this (whereas client1 is a long running mysqldump command):

client1: LOCK TABLE xyz READ LOCAL; //.. also other tables
.... time went by
client2: LOCK TABLE xyz READ;
client2: FLUSH TABLE xyz; // will block
... now client1 (mysqldump) wants to dump the db and begins with SHOW TABLE STATUS
client1: SHOW TABLE STATUS LIKE 'xyz'; // will block

so client1 waits for client2 and client2 for client1..

I'm not sure if this is wanted but its confusing.. if client1 would also FLUSH TABLE xyz then ok its a little bit weird (but could be resolved to do something like: close xyz, reopen for both clients and readd lock table read).. but that "SHOW TABLE STATUS" locks but any SELECT wouldn't thats not logical or did i forget something?

Thank you for the feedback.

You are right this is duplicate of bug #45066. Please upgrade to version 5.5 where this problem does not exist.