Bug #39309 large unsigned bigints and last_insert_id() failure
Submitted: 8 Sep 2008 11:40 Modified: 9 Sep 2008 20:29
Reporter: Lars Monecke Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.51a-community-log OS:Linux
Assigned to: CPU Architecture:Any

[8 Sep 2008 11:40] Lars Monecke
Description:
we have autoincrement unsigned bigints for our id columns. We use last_insert_id() to get the id back to our program. last_insert_id() returns as data type bigint (signed), so id larger than 9223372036854775807 will be not handled correctly.

BTW: is there a builtin function to get the data_type of a result column?

How to repeat:
drop table if exists test.foobar;
create table test.foobar (
	foobar_id bigint unsigned not null auto_increment, 
	modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	primary key (foobar_id)
);
drop table if exists test.foobar2;
create table test.foobar2 (
	foobar2_id bigint unsigned not null auto_increment, 
	foobar_id bigint unsigned not null , 
	modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	primary key (foobar2_id),
	CONSTRAINT `fk_foobar`  FOREIGN KEY (`foobar_id` )    REFERENCES test.foobar (foobar_id )  ON DELETE CASCADE ON UPDATE CASCADE
);
insert into test.foobar (foobar_id) VALUES (2);
insert into test.foobar2 (foobar_id) VALUES ( last_insert_id());

insert into test.foobar (foobar_id) VALUES (18446744073709551610);
insert into test.foobar2 (foobar_id) VALUES (last_insert_id());

result is:
"foobar2_id","foobar_id","modified"
1,2,"2008-09-08 13:34:16"
2,1,"2008-09-08 13:34:16"

expected result is:
"foobar2_id","foobar_id","modified"
1,2,"2008-09-08 13:34:16"
2,18446744073709551610),"2008-09-08 13:34:16"
[8 Sep 2008 11:41] Lars Monecke
example sql script

Attachment: cast_example.sql (text/plain), 869 bytes.

[8 Sep 2008 11:43] Lars Monecke
sorry i'm missed to set engine=innodb. If i do a fk error occurred:
Error while execeuting query: insert into test.foobar2 (foobar_id) VALUES (last_insert_id()):
Cannot add or update a child row: a foreign key constraint fails (`test/foobar2`, CONSTRAINT `fk_foobar` FOREIGN KEY (`foobar_id`) REFERENCES `foobar` (`foobar_id`) ON DELETE CASCADE ON UPDATE CASCADE) (errno: 1452)
Click 'Ignore' if you'd like to have this error ignored until the end of the script.
[8 Sep 2008 11:43] Lars Monecke
again but now with engine=innodb

Attachment: cast_example.sql (text/plain), 897 bytes.

[9 Sep 2008 20:29] Sveta Smirnova
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #20964