MySQL Bugs: #17264: MySQL Server freeze

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:	Windows (Windows 2003, w2ksp4)
Assigned to:		CPU Architecture:	Any

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.

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.

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'

Please, send the SHOW INNODB STATUS results just after the deadlock (they should explain why the deadlock had happened).

'
=====================================
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
============================
'

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.

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?

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'

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.

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

Although, the server itself doesn't freeze. The two ALTER TABLE statements just remain in deadlock state forever.

Sorry to have used not appropriate terms, but it is what I wanted to tell when I was talking about deadlock.

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

processlist, stacks, create table

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

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

Approved by Heikki.

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

Fix has been pushed to the main tree, and will be present in the 5.0.23 release.

Noted in 5.0.23 changelog.

On Windows, multiple clients simultaneously attempting to perform
ALTER TABLE operations on an InnoDB table could deadlock.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

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)

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)