MySQL Bugs: #19604: CHECK TABLE with concurrent INSERT can reset auto

Bug #19604	CHECK TABLE with concurrent INSERT can reset auto_increment
Submitted:	8 May 2006 10:18	Modified:	10 Jun 2006 15:49
Reporter:	Ingo Strüwing	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.0.19	OS:	Linux (Linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
When running one or more threads doing CHECK TABLE t1 and one or more threads doing (concurrent) INSERTS on t1, it can happen that the auto_increment value is reset (at least for one INSERT statement). With an unique auto_increment column this gives an error.

This is one of the problems I detected while working on Bug#17332 (changing key_buffer_size on a running server can crash under load). I report it as a new bug since it is independent from the key cache and can be fixed quickly.

How to repeat:
I will add a shell script later.

Suggested fix:
In the check table code, do not let update_auto_increment() work on the real auto_increment value. It must not be modified without an exclusively (write) locked table. CHECK TABLE does just use a shared (read) lock.

My test script.

Attachment: bug19604-1.sh (application/x-sh, text), 14.43 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/6239

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/7076

CHECK TABLE did temporarily clear the auto_increment value.
  It runs with a read lock, allowing other readers and
  conurrent INSERTs. The latter could grab the wrong value
  in this moment.
  
  CHECK TABLE does no longer modify the auto_increment value.
  Not even for a short moment.

Pushed to 5.0.23 and 5.1.12.

Noted in 5.0.23, 5.1.12 changelogs.