MySQL Bugs: #42291: AUTO Increment

Bug #42291	AUTO Increment
Submitted:	23 Jan 2009 4:49	Modified:	1 Feb 2009 16:12
Reporter:	Anthony P	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S1 (Critical)
Version:	5.1.30	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment

Description:
Trying to create a secondary key fails.  This was noticed while trying to set an auto_increment column.  Setting the column as a key destroys the primary key table.  The primary key table can be rebuilt/indexed but a secondary key does not function even though the SQL command executes with an OK response.

How to repeat:
Setup any table with a primary key with several columns. Modify a column with as a primary key with auto_increment (any initial value).  The query will respond with OK but the key/index is never established and any new records do not get auto_incremented.

Thank you for the report.

I can not repeat described behavior. Please provide repeatable test case: output of SHOW CREATE TABLE, ALTER statement, INSERT statement.

ALTER TABLE db.table_name ADD COLUMN col_nameb INTEGER NOT NULL AFTER col_nameb;

ALTER TABLE db.table_name MODIFY COLUMN table_name INTEGER NOT NULL DEFAULT NULL
AUTO_INCREMENT, DROP PRIMARY KEY, ADD PRIMARY KEY USING BTREE (col_namea, col_nameb);
Query OK, 0 rows affected (0.00) sec)
Records: 0 Duplicates: 0 Warnings: 0

These are MyISAM tables.  After trying to conver the table in question to INNODB the results are still the same.

I do have a table with two keys and a single field that is set for AUTO_INCREMENT in another db (same services) using INNODB that was set at time of TABLE CREATE.   I just verified that it is executing the Auto_increment.

Thank you for the feedback.

I still can not create repeatable test case for the problem described. Please send SHOW CREATE TABLE, INSERT statements and data which you get and which you expect to get.

Also please check http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html to be sure you understand correctly how AUTO_INCREMENT works in case if it is second field in the multi-column PRIMARY KEY

The INSERT is just a simple INSERT INTO table_name VALUES ( ,1, 'something', 3);

Where the primary key isn't specified in the first field.  The last column is the field set to ATUO_INCREMENT.  

The value that gets intered into that field is a value of 1.  
After MODIFY then the primary key for field 1 is no longer being maintained missing and of course the secondary key isn't avilable or being set correctly either.  The secondary key and auto_increment values are set but are not working.  I expected to see that after several inserts that the column for the secondary key would increase N+1 values for each INSERT performed.

Thank you for the feedback.

Unfortunately I can not say you experience bug based on information you provided: I created test case and got next results which are correct according to the link I provided in the previous comment.

mysql> drop table t1;
Query OK, 0 rows affected (0.21 sec)

mysql> create table t1(f1 int not null, f2 int, primary key(f1, f2));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 values(1,2), (5, 2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+----+
| f1 | f2 |
+----+----+
|  1 |  2 | 
|  5 |  2 | 
+----+----+
2 rows in set (0.00 sec)

mysql> alter table t1 add column f3 int not null after f2;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table t1 modify column f3 int not null default null auto_increment, drop primary key, add primary key using btree (f2, f3);
Query OK, 2 rows affected (0.20 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL DEFAULT '0',
  `f3` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`f2`,`f3`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----+----+----+
| f1 | f2 | f3 |
+----+----+----+
|  1 |  2 |  1 | 
|  5 |  2 |  2 | 
+----+----+----+
2 rows in set (0.00 sec)

mysql> insert into t1 (f1, f2) values(3,3), (4,4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t1 (f1, f2) values(3,3), (4,4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+----+----+
| f1 | f2 | f3 |
+----+----+----+
|  1 |  2 |  1 | 
|  5 |  2 |  2 | 
|  3 |  3 |  1 | 
|  4 |  4 |  1 | 
|  3 |  3 |  2 | 
|  4 |  4 |  2 | 
+----+----+----+
6 rows in set (0.00 sec)

Here is the output from the mysql console
+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| id_c                | char(36)      | NO   | PRI | NULL    |                |
| policy_number_c     | varchar(15)   | YES  |     | NULL    |                |
| date_of_loss_c      | date          | NO   |     | NULL    |                |
| acutal_cash_value_c | decimal(26,6) | YES  |     | NULL    |                |
| invoice_number_c    | varchar(25)   | NO   |     | NULL    |                |
| file_number_c       | int(11)       | NO   | PRI | NULL    | auto_increment |
+---------------------+---------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Now while is shows the id_c and the file_number_c as being Primary keys, 
I found a secondary issue with the MySQL Database Administrator that does not show either to be a key field.

At any rate when you insert a record then the auto_increment value does not get increased N+1 values.  Every insert generates a value of 1.  Does that help?

Yes..that is the issue you described...you CREATED the TABLE..not ALTER the table.  If you issue and ALTER it does not seem to work as expected.

Thank you for the feedback.

> .you CREATED the TABLE..not ALTER the table.  I

If you look at my example more carefully you notice 2 ALTER TABLE statements.

Please also read at http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html:

"For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix."

So for me it looks like you just don't understand how AUTO_INCREMENT should work in such case.

If you still believe you experience a bug we need example same like I did.

Try a CREATE TABLE t1 (f1 int not null, f2 int, primarky key (f1));
then INSERT into t1 values (1,2), (5,2)
then ALTER TABLE t1 ADD COLUMN f3 INTEGER AFTER f2;
MODIFY the two records to include data for f3.
then ALTER TABLE t1 MODIFY COLUMN f3 INTEGER NOT NULL DEFAULT 0 AUTO_INCREMENT,
DROP PRIMARY KEY, ADD PRIMARY KEY USING BTREE (f1,f3);

I understand completely what is expected with secondary indexes as you provided in the link and for which I read very carefully before submitting the bug and reread at your recommendation.  However when trying to create a additinoal PRIMARY index I cannot get the AUTO_INCREMENT to work.  It is acting each time like it is creating the secondary index (not a second primary index) which is why I documented that at each instance of a UNIQUE PRIMARY KEY VALUE a secondary value would always yeild a 1.  No problem there.  But I am trying to setup a SECONDARY KEY that would auto increment as I have in other tables and for some reason doing an ALTER TABLE (not CREATE TABLE) doesn't seem to make this work.

Thank you for the feedback.

----<Q>----
It is acting each time like it is creating the secondary index
(not a second primary index) which is why I documented that at each instance of a UNIQUE
PRIMARY KEY VALUE a secondary value would always yeild a 1.  No problem there.  But I am
trying to setup a SECONDARY KEY that would auto increment as I have in other tables and
for some reason doing an ALTER TABLE (not CREATE TABLE) doesn't seem to make this work.
----</Q>----

I am sorry, but MySQL only allows single AUTO_INCREMENT field. Having 2 AUTO_INCREMENT fields is not supported. Though you can realize what you need using TRIGGERS or at application level.

I am not trying to create a SECOND AUTO_INCREMENT. I am trying to create a SECOND KEY and that KEY/Field is AUTO_INCREMENT.

f1 KEY NOT NULL
f2 KEY AUTO_INCREMENT NOT NULL

I have this working as a CREATE TABLE but not as a MODIFY TABLE.
That is is the bug.  And it is a sev1 because it is not necessarily trivial to dump data from the table, drop, create and then insert.

Additionally when the MODIFY TABLE is executed Database Administrator does not pick up the changes and in fact does not show ether field as being a KEY value.

Running test case you provided returns correct results in my case:

$mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2129
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (f1 int not null, f2 int, primary key (f1));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT into t1 values (1,2), (5,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD COLUMN f3 INTEGER AFTER f2;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE t1 SET f3=5;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM t1;
+----+------+------+
| f1 | f2   | f3   |
+----+------+------+
|  1 |    2 |    5 | 
|  5 |    2 |    5 | 
+----+------+------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY COLUMN f3 INTEGER NOT NULL DEFAULT NULL AUTO_INCREMENT, DROP PRIMARY KEY, ADD PRIMARY KEY USING BTREE (f1,f3);
Query OK, 2 rows affected (0.28 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+----+------+----+
| f1 | f2   | f3 |
+----+------+----+
|  1 |    2 |  5 | 
|  5 |    2 |  5 | 
+----+------+----+
2 rows in set (0.00 sec)

mysql> 
mysql> INSERT INTO t1 (f1, f2) VALUES (1,3), (5,3), (3,2), (3,3);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+----+------+----+
| f1 | f2   | f3 |
+----+------+----+
|  1 |    2 |  5 | 
|  5 |    2 |  5 | 
|  1 |    3 |  6 | 
|  5 |    3 |  6 | 
|  3 |    2 |  1 | 
|  3 |    3 |  2 | 
+----+------+----+
6 rows in set (0.00 sec)

mysql> \q
Bye

This is how AUTO_INCREMENT is supposed to work. Regarding to bug in MySQL Administrator please open separate one.

Yes that is the type of result that I would have expected.  Thanks for showing me that it is working on your instances.

However I get the following:

mysql> DESCRIBE t1;
+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| f1                  | char(36)     | NO   | PRI | NULL    |                |
| f2                  | float        | YES  |     | NULL    |                |
| f3                  | int(11)      | NO   | PRI | NULL    | auto_increment |
+---------------------+--------------+------+-----+---------+----------------+

CREATE TABLE `t1` (
  `f1` char(36) NOT NULL,
  `f2` varchar(15) DEFAULT NULL,
  `f3` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_c`,`file_number_c`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

| 6d16f5f9-812b-12e1-5fe5-494fbcc05c05 |     86166 |
| 9e9a3f48-8ac8-60b0-4091-494ffcae8bcd |     86167 |
| 804305c3-4c3e-5ec1-5a85-494ffcad0cc1 |     86168 |
| 2ae18e3c-3ad0-31df-2c90-49512924f543 |     86169 |
| ad210749-8013-a2e3-7014-49525a34d0c4 |     86170 |
| 77                                   |     86171 |
+--------------------------------------+-----------+

For each new record the f3 value is increased N+1.  So although I know, understand and appreciate SUB KEYS what I am not seeing here is consistency in the auto_increment between one database and another database on the same machine and same instance.  So why does the same create statement on one table work for N+1 on every record and on another table work as N+1 for every existing KEY value (regardless if primary key is INT or VARCHAR)?  The two tables appear to have been created identical.

Sorry...the cut and paste from the last submission
it should read 
CREATE TABLE `t1` (
  `f1` char(36) NOT NULL,
  `f2` varchar(15) DEFAULT NULL,
  `f3` int(11) NOT NULL DEFAULT '0' NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id_c`,`file_number_c`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

Thank you for the feedback.

This still works correctly for me:

$mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3384
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t1 (f1 char(36) not null, `f2` varchar(15) DEFAULT NULL, primary key (f1));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT into t1 values ('6d16f5f9-812b-12e1-5fe5-494fbcc05c05',2), ('9e9a3f48-8ac8-60b0-4091-494ffcae8bcd',2);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD COLUMN f3 INTEGER AFTER f2;
Query OK, 2 rows affected (0.26 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE t1 SET f3=5;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM t1;
+--------------------------------------+------+------+
| f1                                   | f2   | f3   |
+--------------------------------------+------+------+
| 6d16f5f9-812b-12e1-5fe5-494fbcc05c05 | 2    |    5 | 
| 9e9a3f48-8ac8-60b0-4091-494ffcae8bcd | 2    |    5 | 
+--------------------------------------+------+------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY COLUMN f3 INTEGER NOT NULL DEFAULT NULL AUTO_INCREMENT, DROP PRIMARY KEY, ADD PRIMARY KEY USING BTREE (f1,f3);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+--------------------------------------+------+----+
| f1                                   | f2   | f3 |
+--------------------------------------+------+----+
| 6d16f5f9-812b-12e1-5fe5-494fbcc05c05 | 2    |  5 | 
| 9e9a3f48-8ac8-60b0-4091-494ffcae8bcd | 2    |  5 | 
+--------------------------------------+------+----+
2 rows in set (0.00 sec)

mysql> 
mysql> insert into t1 (f1, f2) values ('804305c3-4c3e-5ec1-5a85-494ffcad0cc1',3), ('2ae18e3c-3ad0-31df-2c90-49512924f543',3), ('ad210749-8013-a2e3-7014-49525a34d0c4',2), ('77',3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+--------------------------------------+------+----+
| f1                                   | f2   | f3 |
+--------------------------------------+------+----+
| 6d16f5f9-812b-12e1-5fe5-494fbcc05c05 | 2    |  5 | 
| 9e9a3f48-8ac8-60b0-4091-494ffcae8bcd | 2    |  5 | 
| 804305c3-4c3e-5ec1-5a85-494ffcad0cc1 | 3    |  1 | 
| 2ae18e3c-3ad0-31df-2c90-49512924f543 | 3    |  1 | 
| ad210749-8013-a2e3-7014-49525a34d0c4 | 2    |  1 | 
| 77                                   | 3    |  1 | 
+--------------------------------------+------+----+
6 rows in set (0.00 sec)

So, please, send complete test case.

OK.. Here are the two tables.
DB1.T1 and DB2.T1

mysql> USE DB1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW CREATE TABLE T1;

| T1 | CREATE TABLE `T1` (                                                                                                                
  `f1` char(36) NOT NULL,                         
  `f2` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`f1`,`f2`)
) ENGINE=InnoDB AUTO_INCREMENT=86172 DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

mysql> INSERT INTO T1 SET id_f1=81;
Query OK, 1 row affected (0.04 sec)

mysql> select * from T1;
+--------------------------------------+-----------+
| f1                                   | f2        |
+--------------------------------------+-----------+
| 6d16f5f9-812b-12e1-5fe5-494fbcc05c05 |     86166 |
| 9e9a3f48-8ac8-60b0-4091-494ffcae8bcd |     86167 |
| 804305c3-4c3e-5ec1-5a85-494ffcad0cc1 |     86168 |
| 2ae18e3c-3ad0-31df-2c90-49512924f543 |     86169 |
| ad210749-8013-a2e3-7014-49525a34d0c4 |     86170 |
| 77                                   |     86171 |
| 81                                   |     86172 |
| 99                                   |     86173 |
+--------------------------------------+-----------+
8 rows in set (0.00 sec)

mysql> USE DB2;

mysql> SHOW CREATE TABLE t1;

| t1    | CREATE TABLE `t1` (
  `f1` varchar(25) NOT NULL,
  `f2` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`f1`,`f2`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

mysql> INSERT INTO t1 SET f1=99;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 SET f1=100;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+-----+----+
| f1  | f2 |
+-----+----+
| 100 |  1 |
| 99  |  1 |
+-----+----+
2 rows in set (0.00 sec)

Notice that in the two create statements that we have MyISAM and INNODB types, however converting types does not affect the outcome.

I just ran this and received the following:
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql>  CREATE TABLE `opportunities_cstm` (
    ->   `f1` char(36) NOT NULL,
    ->   `f2` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`f1`,`f2`)
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
mysql> CREATE TABLE `t1` (
    ->   `f1` char(36) NOT NULL,
    ->   `f2` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`f1`,`f2`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=86174 DEFAULT CHARSET=utf8;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> CREATE TABLE `t1` (   `f1` char(36) NOT NULL,   `f2` int(11) NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`f1`,`f2`) ) ENGINE=MyISAM AUTO_INCREMENT=86174 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql>

NOTE the only change was the DB ENGINE type.  Now when the first instance was created the table was created without error. Hence the following:

SHOW CREATE TABLE T1;

| T1 | CREATE TABLE `T1` (                                                                
                                               
  `f1` char(36) NOT NULL,                         
  `f2` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`f1`,`f2`)
) ENGINE=InnoDB AUTO_INCREMENT=86172 DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

mysql> 

But on occasion I get the following:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key.  How can a PRIMARY KEY of type VARCHAR be an auto column? CHAR(VAL+1)??  

Anyway, I understand this as an error.

So what I am having trouble with is the consistency of the Primary KEY and Secondary keys in which I have working as N+1 in one table with a PRIMARY KEY and a SECONDARY KEY and in a second table the secondary KEY is N+1 based upon KEY value in PRIMARY.  That does not seem to make sense when I have show the CREATE TABLE statements are identical.

Thank you for the feedback.

InnoDB storage engine does not allow AUTO_INCREMENT field to be part of the multi-column key: http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html You probably have table corrupted if SHOW CREATE TABLE shows such definition for InnoDB table.

So this is still "not a bug".

Interesting note about the INNODB multi-column.  We did experience a DB coruption issue a while back (on an earlier 4x version).

However on this new instance it still does not resolve the MyISAM issue.