MySQL Bugs: #43988: AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31

Bug #43988	AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
Submitted:	31 Mar 2009 16:30	Modified:	4 Sep 2009 8:02
Reporter:	Victor Kirkebo	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1/6.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, autoinc, error 1022, error 1467

Description:
Inserting negative values into auto_increment field of partitioned InnoDB tables 
can give the following errors:
ERROR 1022 (23000): Can't write; duplicate key in table and ERROR 1467 (HY000): Failed to read auto-increment value from storage engine.

How to repeat:
Scenario 1:  With Partitions>=4
ERROR 1022 (23000): Can't write; duplicate key in table 't'
-----------------------------------------------------------
drop database if exists tst;
create database tst;
use tst;
create table t (i int not null auto_increment, primary key(i), f int) engine=innodb partition by hash(i) partitions 4;
insert into t ( f ) values ( 10 );
insert into t ( f ) values ( 20 );
insert into t ( i, f ) values ( -1, -10 );
insert into t ( f ) values ( 30 );
insert into t ( f ) values ( 40 );
select * from t;

Output from last two lines:
mysql> insert into t ( f ) values ( 40 );
ERROR 1022 (23000): Can't write; duplicate key in table 't'
mysql> select * from t;
+----+------+
| i  | f    |
+----+------+
|  1 |   30 |
| -1 |  -10 |
|  1 |   10 |
|  2 |   20 |
+----+------+
4 rows in set (0.00 sec)

---------------------------------------------------------------------------
Scenario 2:  With Partitions>=2
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
---------------------------------------------------------------------------
drop database if exists tst;
create database tst;
use tst;
create table t (i int not null auto_increment, primary key(i), f int) engine=innodb partition by hash(i) partitions 2;
insert into t ( i, f ) values ( -2, -20 );
insert into t ( f ) values ( 10 );
select * from t;

Output from last two lines:
mysql> insert into t ( f ) values ( 10 );
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
mysql> select * from t;
+----+------+
| i  | f    |
+----+------+
| -2 |  -20 |
+----+------+
1 row in set (0.00 sec)

---------------------------------------------------------------------------
Scenario 3:  With Partitions>=2
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
---------------------------------------------------------------------------
drop database if exists tst;
create database tst;
use tst;
create table t (i int not null auto_increment, primary key(i), f int) engine=innodb partition by hash(i) partitions 2;
insert into t ( i, f ) values ( -3, -30 );
insert into t ( f ) values ( 10 );
insert into t ( f ) values ( 20 );
select * from t;

Output from last two lines:
mysql> insert into t ( f ) values ( 20 );
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
mysql> select * from t;
+------------+------+
| i          | f    |
+------------+------+
|         -3 |  -30 |
| 2147483647 |   10 |
+------------+------+
2 rows in set (0.00 sec)

------------------------------------------------------------
Scenario 4:  With Partitions>=2
ERROR 1022 (23000): Can't write; duplicate key in table 't'
------------------------------------------------------------
drop database if exists tst;
create database tst;
use tst;
create table t (i int not null auto_increment, primary key(i), f int) engine=innodb partition by hash(i) partitions 2;
insert into t ( i, f ) values ( -4, -40 );
insert into t ( f ) values ( 10 );
insert into t ( f ) values ( 20 );
select * from t;

Output from last two lines:
mysql> insert into t ( f ) values ( 20 );
ERROR 1022 (23000): Can't write; duplicate key in table 't'
mysql> select * from t;
+------------+------+
| i          | f    |
+------------+------+
|         -4 |  -40 |
| 2147483647 |   10 |
+------------+------+
2 rows in set (0.00 sec)

Queries result

Attachment: bug43988-output.txt (text/plain), 3.35 KiB.

Thank you for the bug report. Same result with MyISAM engine so changing Category to Partitioning.

The bug is that the partitioning engine only uses unsigned values for handling auto_increment values and have no special handling for signed (negative) values. This should both be fixed and documented.

There is also no test for handling float/double auto_increment columns on partitioned tables. And what should happen when the floating point resolution gets greater than 2.0, so that x + 1.0 still yields x, as in 10^30 + 1 ?

Found this in the manual:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

An integer or floating-point column can have the additional attribute AUTO_INCREMENT. 

...

Note

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.
=========

I would still say that this is a bug, since it is not consistent with how MyISAM and InnoDB works.

Also see bug #45823 where the testcase inserts some negative values into the auto_increment field of a partitioned table followed by a delete statement. The delete statement causes different errors with MyISAM and InnoDB tables. The server crashes when using InnoDB tables.
InnoDB: # ERROR 2013 (HY000): Lost connection to MySQL server during query
MyISAM: # ERROR 1032 (HY000): Can't find record in '<tablename>'

The following scenario causes a server crash. I think this is same as
Bug#45823. I feel Bug#43988 can be extended to include the use case for
a server crash and Bug#45823 can be marked as duplicate.

drop database if exists tst;
create database tst;
use tst;
create table t (i int not null auto_increment, primary key(i), f int) engine=innodb
partition by hash(i) partitions 4;
insert into t ( f ) values ( 10 );
insert into t ( f ) values ( 20 );
insert into t ( i, f ) values ( -1, -10 );
select * from t;
+----+------+
| i  | f    |
+----+------+
| -1 |  -10 |
|  1 |   10 |
|  2 |   20 |
+----+------+
3 rows in set (0.00 sec)
insert into t ( f ) values ( 30 );
select * from t; (causes a server crash)
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
ERROR: 
Can't connect to the server

Both MyIsam and Innodb in the above case allow the insert
of the negative value, but continue incrementing from the
previous positive value

mysql> insert into t ( i, f ) values ( -1, -10 );
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+------+
| i  | f    |
+----+------+
|  1 |   10 |
|  2 |   20 |
| -1 |  -10 |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into t ( f ) values ( 30 );
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+------+
| i  | f    |
+----+------+
|  1 |   10 |
|  2 |   20 |
| -1 |  -10 |
|  3 |   30 |
+----+------+
4 rows in set (0.00 sec)

mysql>

This bug is a duplicate of Bug#45823.