Bug #17264 MySQL Server freeze
Submitted: 9 Feb 2006 6:38 Modified: 18 Jun 2010 1:04
Reporter: Mathieu Tremblay Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.18 max, 5.0.19-bk OS:Microsoft Windows (Windows 2003, w2ksp4)
Assigned to: CPU Architecture:Any

[9 Feb 2006 6:38] Mathieu Tremblay
Description:
When execute to "ALTER TABLE table_name AUTO_INCREMENT=X" at the same time ans on the same table, the MySQL server freeze. Even if the number of line in the table is very low.

How to repeat:
Execute simultaneous "ALTER TABLE table_name AUTO_INCREMENT=X" on the same table.
[9 Feb 2006 8:07] Valeriy Kravchuk
Thank you for a problem report. What storage engine is used for that table? Send the SHOW CREATE TABLE table_name results and SHOW TABLE STATUS results, just to be sure.
[9 Feb 2006 14:55] Mathieu Tremblay
My Storage engine is InnoDB.

Here is the table that result in a deadlock when executing simultaneous "ALTER Table Commande AUTO_INCREMENT=X" 

CREATE TABLE `commande` (
  `Numero` int(12) unsigned NOT NULL auto_increment,
  `RefTable` varchar(9) default NULL,
  `RefClient` int(10) unsigned default NULL,
  `RefClientLivraison` varchar(10) default NULL,
  `Reference` varchar(30) default '',
  `RefServeur` varchar(6) NOT NULL default '',
  `Date` datetime default NULL,
  `SousTotal` decimal(12,2) NOT NULL default '0.00',
  `FraisLivraison` decimal(12,2) unsigned NOT NULL default '0.00',
  `Promotion` decimal(12,2) NOT NULL default '0.00',
  `PromotionComptoir` decimal(12,2) NOT NULL default '0.00',
  `TPS` decimal(12,2) NOT NULL default '0.00',
  `TVQ` decimal(12,2) NOT NULL default '0.00',
  `Total` decimal(12,2) NOT NULL default '0.00',
  `Commentaire` varchar(30) default '',
  `Imprimee` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Numero`),
  KEY `IX_Commande_Serveur` (`RefServeur`),
  KEY `IX_Commande_Date` (`Date`),
  KEY `FK_Commande_ClientLivraison` (`RefClientLivraison`),
  KEY `IX_Commande_Table` (`RefTable`),
  KEY `IX_Commande_Client` (`RefClient`),
  CONSTRAINT `FK_Commande_ClientLivraison` FOREIGN KEY (`RefClientLivraison`) REFERENCES `clientlivraison` (`Telephone`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_commande_Serveur` FOREIGN KEY (`RefServeur`) REFERENCES `employe` (`Numero`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 29696 kB'
[10 Feb 2006 14:00] Valeriy Kravchuk
Please, send the SHOW INNODB STATUS results just after the deadlock (they should explain why the deadlock had happened).
[10 Feb 2006 19:22] Mathieu Tremblay
'
=====================================
060210 14:21:06 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 14, signal count 14
Mutex spin waits 94, rounds 131, OS waits 2
RW-shared spins 22, OS waits 11; RW-excl spins 1, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 90071
Purge done for trx's n:o < 0 90070 undo n:o < 0 0
History list length 1
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 1056
MySQL thread id 39, query id 1237 127.0.0.1 root
SHOW INNODB STATUS
---TRANSACTION 0 90067, not started, OS thread id 1788
mysql tables in use 1, locked 2
MySQL thread id 37, query id 1215 127.0.0.1 root rename result table
ALTER Table Commande AUTO_INCREMENT=0
---TRANSACTION 0 90070, not started, OS thread id 3668
mysql tables in use 1, locked 2
MySQL thread id 36, query id 1216 127.0.0.1 root rename result table
ALTER Table Commande AUTO_INCREMENT=0
---TRANSACTION 0 89697, not started, OS thread id 1316
MySQL thread id 33, query id 1226 127.0.0.1 root
---TRANSACTION 0 89684, not started, OS thread id 1120
MySQL thread id 32, query id 1123 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1289 OS file reads, 338 OS file writes, 224 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 332147, used cells 85484, node heap has 98 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 453563803
Log flushed up to   0 453563803
Last checkpoint at  0 453563803
0 pending log writes, 0 pending chkp writes
210 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 93549466; in additional pool allocated 722560
Buffer pool size   5120
Free buffers       3741
Database pages     1281
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1279, created 2, written 124
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 1956, state: waiting for server activity
Number of rows inserted 39, updated 0, deleted 0, read 655531
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
'
[19 Feb 2006 14:13] Valeriy Kravchuk
Please, send also the 

show table status like 'commande'\G

and SHOW PROCESSLIST results for the same situation. I was not able to repeat with 5.0.19-BK on Linux, by the way.
[19 Feb 2006 14:15] Valeriy Kravchuk
Can you, please, try to repeat with a fresh table of the same structure and with the same number of rows? Have you created that table in 4.x.y and then just upgraded?
[20 Feb 2006 14:00] Mathieu Tremblay
show table status like 'commande' 

'commande', 'InnoDB', 10, 'Compact', 0, 0, 16384, 0, 81920, 0, 1, '2006-01-17 11:02:13', '', '', 'latin1_swedish_ci', , '', 'InnoDB free: 29696 kB; InnoDB free: 35840 kB; (`RefClientLivraison`) REFER `bd_r'
[20 Feb 2006 14:19] Mathieu Tremblay
How to reproduce on WINDOWS for sure....

***Note that this procedure works even if my table contain no lines.***
***Note that my table refer a foreign key.***  

1) Start 2 MySQL Command Line Client.
2) Select the Database for each Client.
3) Type the command "ALTER TABLE Commande AUTO_INCREMENT=0;" several times, like 25 times to be sure to get enough time to start the same query on the other client (this is for simulate the simultaneous access).
4) Execute the several queries in the first client.
5) Execute the query "ALTER TABLE Commande AUTO_INCREMENT=0;" in the second client.
6) Its freeze.

I was able to reproducing the deadlock successfully under Windows 2003 Sp1 and Windows XP Sp2.
[20 Feb 2006 17:34] Shane Bester
Repeated according to the above instructions on 5.0.18 on W2KSP4

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: 127.0.0.1:1888
     db: test
Command: Query
   Time: 491
  State: rename result table
   Info: ALTER TABLE Commande AUTO_INCREMENT=0
*************************** 2. row ***************************
     Id: 11
   User: root
   Host: 127.0.0.1:1910
     db: test
Command: Query
   Time: 491
  State: rename result table
   Info: ALTER TABLE Commande AUTO_INCREMENT=0
[20 Feb 2006 17:38] Shane Bester
Although, the server itself doesn't freeze. The two ALTER TABLE statements just remain in deadlock state forever.
[20 Feb 2006 19:47] Mathieu Tremblay
Sorry to have used not appropriate terms, but it is what I wanted to tell when I was talking about deadlock.
[20 Feb 2006 21:02] Shane Bester
Repeated with 5.0.19-bk on w2ksp4.  Cannot repeat on Linux, using 5.0.19-bk.  Attached is the stacks of the hung thread in Windows
[20 Feb 2006 21:07] Shane Bester
processlist, stacks, create table

Attachment: stack_windows.txt (text/plain), 5.58 KiB.

[10 Mar 2006 16:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3722
[1 Jun 2006 18:09] Heikki Tuuri
Approved by Heikki.
[20 Jun 2006 17:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7949
[20 Jun 2006 21:34] Elliot Murphy
Fix has been pushed to the main tree, and will be present in the 5.0.23 release.
[21 Jun 2006 4:25] Paul Dubois
Noted in 5.0.23 changelog.

On Windows, multiple clients simultaneously attempting to perform
ALTER TABLE operations on an InnoDB table could deadlock.
[5 May 2010 15:27] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 1:56] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:52] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:22] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:50] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 21:41] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:53] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:30] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:18] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)