MySQL Bugs: #71417: Drop temp table cause all threads halt.

Bug #71417	Drop temp table cause all threads halt.
Submitted:	18 Jan 2014 17:34	Modified:	19 Jan 2014 11:02
Reporter:	Eddy Xiao	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S4 (Feature request)
Version:	5.6.15	OS:	Windows (Windows2008SP1)
Assigned to:		CPU Architecture:	Any
Tags:	stored procedure, thread halt, truncate table

Description:
When you concurrently call a stored procedure. And inside the loop of the SP there is a clause like either "TRUNCATE TABLE T1" or "DROP TEMPORARY TABLE T1;CREATE TEMPORARY TABLE T1;". T1 is a tempoary table for each connection.

Use OpenMP to invoke the SP and start the paralell CALL, open the Workbench and click the refresh button and observe the connections. At each new SP invoke, most of the currently running threads will halt and execute the TRUNCATE/DROP CREATE. I have uploaded a screenshot and you can see it here: http://imgsrc.baidu.com/forum/w%3D580/sign=225faee8d2a20cf44690fed746084b0c/bcc0a4c27d1ed2...

How to repeat:
1. Create a table as T1;
2. Create a Stored Procedure and place "TRUNCATE TABLE T1" or "DROP TEMPORARY TABLE T1;CREATE TEMPORARY TABLE T1;" inside a loop. Because Truncate = DROP/Create. So either of them will have the same effect.
3. In VC++, use OpenMP to start paralell invoke the SP. Please NOTE: in the OpenMP for loop, make sure you Initiate/Close connection separately,because temporary table are session safe only.
4. Open workbench -- connection -- observe the activities. And you will find that the Truncate Temporay or Drop/Create temporay clause in any connection will cause other threads to halt as above pic shows.

I firstly found TRUNCATE (temporary)table causes this problem,I doubt it is because the system will check if the table is session level or transaction level and thus cause all threads to halt. But after I changed to "DROP TEMPORARY TABLE T1;CREATE TEMPORARY TABLE T1;" which I added "TEMPORARY ", it still cause other threads to halt, so I thought it must be a BUG!

Suggested fix:
I suggest add TRUNCATE Temporary table to explicitly classify session/transaction level tables, and also all temporay tables should be visible inside each session only and should not effect each other.
Thanks.
Ed

Hi can you upload "show full processlist" output and my.ini?
Also, what is the exact table structure you used in the stored routine?

I couldn't repeat any hang with a small test:

mysql> show processlist;
+----+------+-----------+------+---------+------+----------------+-------------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State          | Info                                                                    |
+----+------+-----------+------+---------+------+----------------+-------------------------------------------------------------------------+
| 10 | root | localhost | test | Sleep   |   10 |                | NULL                                                                    |
| 11 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 12 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 13 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 14 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 15 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 16 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 17 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 18 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 19 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 21 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 22 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 23 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 24 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 25 | root | localhost | test | Query   |    0 | NULL           | drop temporary table if exists t1                                       |
| 26 | root | localhost | test | Query   |    0 | creating table | create temporary table if not exists t1(a int primary key)engine=myisam |
| 67 | root | localhost | test | Query   |    0 | init           | show processlist                                                        |
+----+------+-----------+------+---------+------+----------------+-------------------------------------------------------------------------+
17 rows in set (0.00 sec)

config file for MySQL 5.6.15

Attachment: my.ini (application/octet-stream, text), 14.18 KiB.

screenshot for each connection

Attachment: SQLBUG.jpg (image/jpeg, text), 82.21 KiB.

Hi Shane,
Thank you for your feedback.

1. I have posted the my.ini file just now and you can check it, you will see I put one-thread-per-connection, does it matter?

2. The pic uploaded actually shows all connections. And your showed processlists are SIMILAR to mine. As you can see, ID 11 thru 26 are concurrently executing the create/drop temp! In a massive loop SP, if you put the drop/create temp at the start part of the loop, chances are very small that all connections are performing the same clause.

To further examine this problem, you can increase the loop scale so to make sure the concurrent drop/create temp happens very very rare. And if this still happens, then this is it. Because the SP are invoked in separate connection, and when temporary table name are explicitly specified "Temporary", any drop/create should not interfer other sessions.And I am afraid that table structure is not a problem, coz T1 is only a simple table even withou PK,it works inside the session only, no LOCK/UNLOCK tables. So, we can change the shared table name T1 to concat('T',ThreadID) for each session and further test to see if it still persists.

3. WORKAROUND:I myself actually moveed the drop/create temp table to the head of the SP, instead of the head of the LOOP, and changed inside the loop from "Insert into T1 select F1,F2 from ..." to "Replace into T1 select 1 as ID,F1,F2 from ..." and perfectly ducked from this problem! I kept refreshing the connection activities for an hour, and this kind of problem never happen again(an hour long makes sure that many old connections are replaced by new ones). So the problem is some cranky when system handling TRUNCATE / DROP/Create inside the loop of SP instead of outside of it, weird.

4. Suggestion: Why can't we use TRUNCATE TEMPORARY TABLE while can use DROP TEMPORARY TABLE AND CREATE TEMPORARY TABLE? In Oracle,TRUNCATE TEMPORARY TABLE explicitly separates temp table from physical tables, so that when you TRUNCATE a temp, MYSQL does not have to check if it is a physical table. Maybe this BUG is due to such mixing?

Please let me know if you can not repeat the hang.

And anyway, this bug only cause a short time system halt, that you will find the Server machine fan noise / CPU load decrease for a little while and resume computation.

Okay, I think I understand what this bug report is about.   
The problem here is that there is a contention on the mutex THR_LOCK_myisam 
when creating/opening/closing TEMPORARY TABLEs.   
The bug report is similar to another old one:
http://bugs.mysql.com/bug.php?id=65077

I checked a per stack traces, they indeed wait here for example:

ntdll!NtWaitForSingleObject+0xa
ntdll!RtlpWaitOnCriticalSection+0xe8
ntdll!RtlEnterCriticalSection+0xd1
mysqld!mi_close+0x99 [d:\build\mysql-5.6\storage\myisam\mi_close.c @ 36]
mysqld!closefrm+0x38 [d:\build\mysql-5.6\sql\table.cc @ 2436]
mysqld!close_temporary+0x40 [d:\build\mysql-5.6\sql\sql_base.cc @ 2139]
mysqld!recreate_temporary_table+0x7f [d:\build\mysql-5.6\sql\sql_truncate.cc @ 274]

-------

ntdll!NtWaitForSingleObject+0xa
ntdll!RtlpWaitOnCriticalSection+0xe8
ntdll!RtlEnterCriticalSection+0xd1
mysqld!mi_create+0xd1d [d:\build\mysql-5.6\storage\myisam\mi_create.c @ 573]
mysqld!ha_myisam::create+0x232 [d:\build\mysql-5.6\storage\myisam\ha_myisam.cc @ 2019]
mysqld!ha_create_table+0x1d2 [d:\build\mysql-5.6\sql\handler.cc @ 4755]
mysqld!recreate_temporary_table+0xb0 [d:\build\mysql-5.6\sql\sql_truncate.cc @ 277]

----------
ntdll!NtWaitForSingleObject+0xa
ntdll!RtlpWaitOnCriticalSection+0xe8
ntdll!RtlEnterCriticalSection+0xd1
mysqld!mi_open+0x164 [d:\build\mysql-5.6\storage\myisam\mi_open.c @ 114]
mysqld!ha_myisam::open+0x43 [d:\build\mysql-5.6\storage\myisam\ha_myisam.cc @ 748]
mysqld!handler::ha_open+0x36 [d:\build\mysql-5.6\sql\handler.cc @ 2504]
mysqld!open_table_from_share+0x64d [d:\build\mysql-5.6\sql\table.cc @ 2341]
mysqld!open_table_uncached+0x146 [d:\build\mysql-5.6\sql\sql_base.cc @ 6116]
mysqld!recreate_temporary_table+0xd7 [d:\build\mysql-5.6\sql\sql_truncate.cc @ 277]

So I verify this as a feature request to try to reduce this locking for the case of TEMPORARY TABLEs.

Yes,pretty similar. Mutex contention hardly happens after we drag the temp table initialization out of loops.

Glad that you understood.I think this problem can be easily solved as long as we truely ensure that same name temp tables have nothing to do with each other.
So this post can be closed. Thank you,Shane.