MySQL Bugs: #39309: large unsigned bigints and last_insert

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

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"

example sql script

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

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.

again but now with engine=innodb

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

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