MySQL Bugs: #20964: last_insert(id) does not support bigint unsigned

Bug #20964	last_insert(id) does not support bigint unsigned
Submitted:	11 Jul 2006 9:22	Modified:	14 Nov 2012 14:33
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	4.1.20, 5.0.22, 5.1.34	OS:	Linux (Linux, freebsd)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
using last_insert_id on an auto_incremented bigint unsigned does not work for values greater than max-bigint-signed

the same applies for setting max_insert_id(expr)

create table lid (a bigint unsigned not null auto_increment primary key, b int);
insert into lid select 0x7ffffffffffffffe,1;

insert into lid select null,2; select * from lid; select last_insert_id();
insert into lid select null,3; select * from lid; select last_insert_id();

drop table lid;

select last_insert_id(cast(0xffffffffffffffff as unsigned));
select last_insert_id();

How to repeat:
create table lid (a bigint unsigned not null auto_increment primary key, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into lid select 0x7ffffffffffffffe,1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into lid select null,2; select * from lid; select last_insert_id();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
+---------------------+------+
2 rows in set (0.01 sec)

+---------------------+
| last_insert_id()    |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into lid select null,3; select * from lid; select last_insert_id();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
| 9223372036854775808 |    3 |
+---------------------+------+
3 rows in set (0.00 sec)

+----------------------+
| last_insert_id()     |
+----------------------+
| -9223372036854775808 |  ## should be the same as the 3rd row in the select above
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> drop table lid;
Query OK, 0 rows affected (0.00 sec)

#  both of the following should be a positive number
mysql> select last_insert_id(cast(0xffffffffffffffff as unsigned));
+------------------------------------------------------+
| last_insert_id(cast(0xffffffffffffffff as unsigned)) |
+------------------------------------------------------+
|                                                   -1 |
+------------------------------------------------------+
1 row in set (0.02 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               -1 |
+------------------+
1 row in set (0.00 sec)

Suggested fix:
-

Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot 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 1 to server version: 5.0.25

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

mysql> create table lid (a bigint unsigned not null auto_increment primary key,
 b
    -> int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into lid select 0x7ffffffffffffffe,1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into lid select null,2; select * from lid; select last_insert_id(
);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
+---------------------+------+
2 rows in set (0.00 sec)

+---------------------+
| last_insert_id()    |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into lid select null,3; select * from lid; select last_insert_id(
);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
| 9223372036854775808 |    3 |
+---------------------+------+
3 rows in set (0.00 sec)

+----------------------+
| last_insert_id()     |
+----------------------+
| -9223372036854775808 |
+----------------------+
1 row in set (0.00 sec)

mysql> drop table lid;
Query OK, 0 rows affected (0.01 sec)

mysql> select last_insert_id(cast(0xffffffffffffffff as unsigned));
+------------------------------------------------------+
| last_insert_id(cast(0xffffffffffffffff as unsigned)) |
+------------------------------------------------------+
|                                                   -1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               -1 |
+------------------+
1 row in set (0.00 sec)

This bug is related to bug #20924. Same code changes must be implemented in order to fix this one.

Bug #39309 was marked as duplicate of this one.

Same on Windows with 5.0.56sp1-enterprise-gpl-nt-log.

'To be fixed later'??? That's a joke, or? 
That's a no go in our planned professional environment. 

Is it fixed in 5.1.x?

Lars, 
thank you for your interest in MySQL.
If you would like to escalate this bug report, and have a support contract, please use the support channel for that.

Exactly the same results in 5.1.34:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.34-community MySQL Community Server (GPL)

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

mysql> create table lid (a bigint unsigned not null auto_increment primary key,
b int);
Query OK, 0 rows affected (0.39 sec)

mysql> insert into lid select 0x7ffffffffffffffe,1;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> insert into lid select null,2; select * from lid; select last_insert_id()
;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
+---------------------+------+
2 rows in set (0.03 sec)

+---------------------+
| last_insert_id()    |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.06 sec)

mysql> insert into lid select null,3; select * from lid; select last_insert_id()
;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 9223372036854775806 |    1 |
| 9223372036854775807 |    2 |
| 9223372036854775808 |    3 |
+---------------------+------+
3 rows in set (0.00 sec)

+----------------------+
| last_insert_id()     |
+----------------------+
| -9223372036854775808 |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table lid;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> select last_insert_id(cast(0xffffffffffffffff as unsigned));
+------------------------------------------------------+
| last_insert_id(cast(0xffffffffffffffff as unsigned)) |
+------------------------------------------------------+
|                                                   -1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               -1 |
+------------------+
1 row in set (0.00 sec)

Why we expect them to be different?

Bug#50968 was marked as duplicate of this report.

After discussing with Joro setting the lead to Staale.

This could be related to what I am seeing in the binlogs for 'SET INSERT_ID':

  CREATE TABLE test_bigint (
    id   BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)    NOT NULL
  );

  INSERT INTO test_bigint ( id, name ) VALUES ( 18446744073709551611, 'A' );

The binlog will generate:

  SET INSERT_ID=-4

Now, create a table with a SIGNED BIGINT:

  DROP TABLE test_bigint;
  CREATE TABLE test_bigint (
    id   BIGINT SIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)  NOT NULL
  );

  INSERT INTO test_bigint ( id, name ) VALUES ( 9223372036854775700, 'A' );

The binlog will produce:

  SET INSERT_ID=9223372036854775701

This looks to me as if the value holding the INSERT_ID is a signed long long when it should be unsigned long long. I think fix this, and the LAST_INSERT_ID() issue might go away.

Noted in 5.1.67, 5.5.29, 5.6.9, 5.7.0 changelogs.

LAST_INSERT_ID(expr) did not work for expr values greater than the
largest signed BIGINT value.

So, the fix for this bug was a breaking change, and isn't reflected in the latest documentation.

When upgrading from earlier point releases of MySQL, sticking with the same major branch, our code which does a type cast from the expected return type of BIGINT or signed 64bit integer, now throws an exception because the type returned is UNSIGNED BIGINT or unsigned 64bit integer.  I believe a less breaking bug fix would be to make the return value UNSIGNED BIGINT when the primary key of the table is UNSIGNED BIGINT, and otherwise leave the type as BIGINT.

Secondly, the documentation still claims that LAST_INSERT_ID() returns type BIGINT:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

This kind of breaking change to fix what is obviously an edge case, within the same GA branch of MySQL is really disruptive and unexpected.

You're right, the documentation did not reflect this change; here's the revised changelog entry:

LAST_INSERT_ID(expr) did not work for expr values greater than the
largest signed BIGINT value. Such arguments now are accepted, with
some consequences for compatibility with previous versions:
      
* LAST_INSERT_ID() now returns a BIGINT UNSIGNED value, not a BIGINT
  (signed) value.

* For AUTO_INCREMENT columns, negative values are no longer supported.

I'll also revise the description of LAST_INSERT_ID() in the functions chapter
correspondingly.

You're right, the documentation did not reflect this change; here's the revised changelog entry:

LAST_INSERT_ID(expr) did not work for expr values greater than the
largest signed BIGINT value. Such arguments now are accepted, with
some consequences for compatibility with previous versions:
      
* LAST_INSERT_ID() now returns a BIGINT UNSIGNED value, not a BIGINT
  (signed) value.

* For AUTO_INCREMENT columns, negative values are no longer supported.

I'll also revise the description of LAST_INSERT_ID() in the functions chapter
correspondingly.

Addendum to changelog entry:

LAST_INSERT_ID(expr) now returns an unsigned integer value, not a
signed integer value.