MySQL Bugs: #64469: Deadlock or crash on concurrent TRUNCATE TABLE and SELECT * FROM I

Bug #64469	Deadlock or crash on concurrent TRUNCATE TABLE and SELECT * FROM I_S
Submitted:	27 Feb 2012 14:26	Modified:	3 Feb 2013 19:26
Reporter:	Sergei Glushchenko	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S2 (Serious)
Version:	5.1.54-5.1.61	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	concurrent, crash, deadlock, information_schema, truncate table

Description:
MySQL server suspending (sometimes crashing) when a TRUNCATE TABLE is done together with selecting data over same table from information_schema. Table should be created with InnoDB engine.

How to repeat:
1.
 CREATE DATABASE IF NOT EXISTS testdb;
 use testdb;
 DROP TABLE IF EXISTS test;
 CREATE TABLE test (
         id INTEGER,
         text VARCHAR(60)
 )ENGINE InnoDB;

2.
Run simultaneously several instances of following script:
#!/bin/bash

 for i in {1..100000}; do
        mysql testdb -e 'TRUNCATE TABLE test' & # <---- note the &
        mysql information_schema -e 'SELECT * FROM tables WHERE table_schema="testdb" AND table_name="test"';
 done

Suggested fix:
Locking happens due to incorrect work with dict_table_stats_latches. dict_table_stats_lock chooses the latch to lock using table->id, however table->id is changing during row_truncate_table_for_mysql. Solution is  to grab appropriate dict_table_stats_latche when performing table->id change. The other possible solution is to pick the latch from dict_table_stats_latches not using table->id but using something unchangeable.

See bug #54678 also. No real solution for deadlock in 5.1 was ever implemented, AFAIR...

You have to use the innodb plugin...

Unable to read tablespace 62 page no 3 into the buffer pool after 100 attempts
The most probable cause of this error may be that the table has been corrupted.
You can try to fix this problem by using innodb_force_recovery.
InnoDB: Assertion failure in thread 7364 in file buf\buf0buf.c line 1670
InnoDB: We intentionally generate a memory trap.
ha_innodb_plugin.dll!buf_page_get_gen()[buf0buf.c:1670]
ha_innodb_plugin.dll!btr_block_get_func()[btr0btr.ic:60]
ha_innodb_plugin.dll!btr_root_block_get()[btr0btr.c:696]
ha_innodb_plugin.dll!btr_root_get()[btr0btr.c:721]
ha_innodb_plugin.dll!btr_get_size()[btr0btr.c:1012]
ha_innodb_plugin.dll!dict_update_statistics()[dict0dict.c:4277]
ha_innodb_plugin.dll!ha_innodb::info_low()[ha_innodb.cc:7805]
ha_innodb_plugin.dll!ha_innodb::info()[ha_innodb.cc:8055]
mysqld.exe!get_schema_tables_record()[sql_show.cc:3800]
mysqld.exe!fill_schema_table_by_open()[sql_show.cc:3158]
mysqld.exe!get_all_tables()[sql_show.cc:3574]
mysqld.exe!get_schema_tables_result()[sql_show.cc:6242]
mysqld.exe!JOIN::exec()[sql_select.cc:1863]
mysqld.exe!mysql_select()[sql_select.cc:2555]
mysqld.exe!handle_select()[sql_select.cc:269]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5206]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2336]
mysqld.exe!mysql_parse()[sql_parse.cc:6127]
mysqld.exe!dispatch_command()[sql_parse.cc:1281]
mysqld.exe!do_command()[sql_parse.cc:897]
mysqld.exe!handle_one_connection()[sql_connect.cc:1454]
mysqld.exe!pthread_start()[my_winthread.c:88]
mysqld.exe!_callthreadstart()[thread.c:259]
mysqld.exe!_threadstart()[thread.c:241]

only innodb plugin in 5.1 is affected.  builtin innodb doesn't crash. and 5.5 doesn't crash.

this happens easily in 5.5.16, (run show table status and alter table add/drop index concurrently).

5.5.29 it is not repeatable. thus, it was fixed somewhere.  upgrade is advised.