Bug #10184 auto-increment key not checked for value if in composite key
Submitted: 26 Apr 2005 16:32 Modified: 13 May 2005 12:29
Reporter: SINISA MILIVOJEVIC Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0 and higher OS:Any (any)
Assigned to: Michael Widenius CPU Architecture:Any

[26 Apr 2005 16:32] SINISA MILIVOJEVIC
Description:
If auto-incremented column is present in they segmented key, but not as a first part of it, 
then after each INSERT / UPDATE no checks are made for the integrity of the value.

No checks are made against the highest auto-inc value in the table.

A cause of it is that share->base.auto_key is 0 , instead of 1 !!!

Then in mi_check.c , this branch is skipped:

    if ((uint) share->base.auto_key -1 == key)

and this branch has this important check:

      if (info->s->state.auto_increment > save_auto_value)

This bug is MyISAM specific.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (b(10),a));
INSERT INTO t1 (b) VALUES ('aaaa');
CHECK TABLE t1;
INSERT INTO t1 (b) VALUES ('');
CHECK TABLE t1;
INSERT INTO t1 (b) VALUES ('bbbb');
CHECK TABLE t1;
DROP TABLE IF EXISTS t1;
[13 May 2005 12:29] Michael Widenius
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is correct behaviour as the auto-increment part is not the first part of the key.
In this case the value for the auto-increment key is calculated on insert based on the existing values in the table.