Description:
I encountered an infinite hang in the involved threads when running the following queries in 3 connections:
500000 inserts: INSERT IGNORE INTO `t1`(`c1`) VALUES ('long string')
followed by:
1 delete: DELETE FROM `t1` WHERE `c1` not in (SELECT 1) limit 400000
The 'long string' is a random string up to 4kb in length.
The table structure for t1 is:
CREATE TABLE `t1`(`c1` TEXT NOT NULL, INDEX(`c1`(512)))ENGINE=MyISAM MAX_ROWS=100000000
The server could still be logged into, but couldn't be shutdown with mysqladmin.
I gathered the following info's before having to kill -9.
mysql> show full processlist\G
*************************** 1. row ***************************
Id: 128
User: root
Host:
db: bug22384
Command: Sleep
Time: 90
State:
Info: NULL
*************************** 2. row ***************************
Id: 179
User: root
Host:
db: bug22384
Command: Sleep
Time: 3
State:
Info: NULL
*************************** 3. row ***************************
Id: 181
User: root
Host:
db: bug22384
Command: Query
Time: 6561
State: Locked
Info: INSERT IGNORE INTO `t1`(`c1`) VALUES ('Z???v?F <cut> is. ')
*************************** 4. row ***************************
Id: 182
User: root
Host:
db: bug22384
Command: Query
Time: 6561
State: Locked
Info: INSERT IGNORE INTO `t1`(`c1`) VALUES ('????? <cut> us.')
*************************** 5. row ***************************
Id: 183
User: root
Host:
db: bug22384
Command: Query
Time: 6561
State: updating
Info: DELETE FROM `t1` WHERE `c1` not in (SELECT 1)
*************************** 6. row ***************************
Id: 185
User: root
Host:
db: bug22384
Command: Query
Time: 0
State: NULL
Info: show full processlist
6 rows in set (0.00 sec)
mysql> exit
Running threads: 6 Stack size: 196608
Current locks:
lock: 0x8c4bc6c:
lock: 0x8bca454: write write_wait
write : 0x62203444 (1648917424:10);
write_wait: 0x8ccd68c (1648716720:7); 0x8b49504 (1645509552:7);
lock: 0x6250c514:
Thread database.table_name Locked/Waiting Lock_type
181 bug22384.t1 Waiting - write Concurrent insert lock
183 bug22384.t1 Locked - write High priority write lock
mysql> show table status like 't1'\G
*************************** 1. row ***************************
Name: t1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1481815
Avg_row_length: 1490
Data_length: 2208619976
Max_data_length: 281474976710655
Index_length: 1369117696
Data_free: 0
Auto_increment: NULL
Create_time: 2006-11-02 18:04:19
Update_time: 2006-11-02 20:35:17
Check_time: 2006-11-02 18:07:44
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=100000000
Comment:
1 row in set (0.11 sec)
25G free on the datadir.
sbester@linux:~> getconf GNU_LIBPTHREAD_VERSION
NPTL 2.3.4
How to repeat:
Will attempt to make a testcase, then will try repeat on 5.0BK.
Suggested fix:
.
Description: I encountered an infinite hang in the involved threads when running the following queries in 3 connections: 500000 inserts: INSERT IGNORE INTO `t1`(`c1`) VALUES ('long string') followed by: 1 delete: DELETE FROM `t1` WHERE `c1` not in (SELECT 1) limit 400000 The 'long string' is a random string up to 4kb in length. The table structure for t1 is: CREATE TABLE `t1`(`c1` TEXT NOT NULL, INDEX(`c1`(512)))ENGINE=MyISAM MAX_ROWS=100000000 The server could still be logged into, but couldn't be shutdown with mysqladmin. I gathered the following info's before having to kill -9. mysql> show full processlist\G *************************** 1. row *************************** Id: 128 User: root Host: db: bug22384 Command: Sleep Time: 90 State: Info: NULL *************************** 2. row *************************** Id: 179 User: root Host: db: bug22384 Command: Sleep Time: 3 State: Info: NULL *************************** 3. row *************************** Id: 181 User: root Host: db: bug22384 Command: Query Time: 6561 State: Locked Info: INSERT IGNORE INTO `t1`(`c1`) VALUES ('Z???v?F <cut> is. ') *************************** 4. row *************************** Id: 182 User: root Host: db: bug22384 Command: Query Time: 6561 State: Locked Info: INSERT IGNORE INTO `t1`(`c1`) VALUES ('????? <cut> us.') *************************** 5. row *************************** Id: 183 User: root Host: db: bug22384 Command: Query Time: 6561 State: updating Info: DELETE FROM `t1` WHERE `c1` not in (SELECT 1) *************************** 6. row *************************** Id: 185 User: root Host: db: bug22384 Command: Query Time: 0 State: NULL Info: show full processlist 6 rows in set (0.00 sec) mysql> exit Running threads: 6 Stack size: 196608 Current locks: lock: 0x8c4bc6c: lock: 0x8bca454: write write_wait write : 0x62203444 (1648917424:10); write_wait: 0x8ccd68c (1648716720:7); 0x8b49504 (1645509552:7); lock: 0x6250c514: Thread database.table_name Locked/Waiting Lock_type 181 bug22384.t1 Waiting - write Concurrent insert lock 183 bug22384.t1 Locked - write High priority write lock mysql> show table status like 't1'\G *************************** 1. row *************************** Name: t1 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 1481815 Avg_row_length: 1490 Data_length: 2208619976 Max_data_length: 281474976710655 Index_length: 1369117696 Data_free: 0 Auto_increment: NULL Create_time: 2006-11-02 18:04:19 Update_time: 2006-11-02 20:35:17 Check_time: 2006-11-02 18:07:44 Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=100000000 Comment: 1 row in set (0.11 sec) 25G free on the datadir. sbester@linux:~> getconf GNU_LIBPTHREAD_VERSION NPTL 2.3.4 How to repeat: Will attempt to make a testcase, then will try repeat on 5.0BK. Suggested fix: .