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
Triage: Triaged: D2 (Serious)

[11 Jul 2006 9:22] Martin Friebe
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:
-
[11 Jul 2006 18:46] Valeriy Kravchuk
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)
[14 Sep 2006 9:32] Alexey Kopytov
This bug is related to bug #20924. Same code changes must be implemented in order to fix this one.
[9 Sep 2008 20:29] Sveta Smirnova
Bug #39309 was marked as duplicate of this one.
[10 Sep 2008 7:06] Lars Monecke
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?
[30 Sep 2008 15:11] Konstantin Osipov
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.
[20 May 2009 6:48] Valeriy Kravchuk
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?
[9 Feb 2010 9:50] Tonci Grgin
Bug#50968 was marked as duplicate of this report.
[19 Feb 2010 9:19] Tonci Grgin
After discussing with Joro setting the lead to Staale.
[7 Feb 2011 23:17] Alfie John
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.
[14 Nov 2012 14:33] Paul Dubois
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.
[13 Aug 2013 19:05] Jeremiah Gowdy
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.
[28 Aug 2013 3:04] Paul Dubois
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.
[28 Aug 2013 3:04] Paul Dubois
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.
[28 Aug 2013 13:16] Paul Dubois
Addendum to changelog entry:

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