MySQL Bugs: #37142: auto_increment problem with type bigint

Bug #37142	auto_increment problem with type bigint
Submitted:	2 Jun 2008 17:04	Modified:	4 Jul 2008 20:06
Reporter:	d a	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	4.0.14	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, BIGINT

Description:

Hi,

I'm experiencing a probelm with auto_increment; it works fine with new table and first insert, but the second insert gives 72,057,594,037,927,937 as the table id and this happens only when the column type is set to bigint - any idea why this happens!!!!? 

thanks in advance.

How to repeat:
donno what you mean by repeated - sorry!

Thank you for the report.

Please provide output of SHOW CREATE TABLE problem_table and indicate accurate version of MySQL server you are using.

thank you for the response.

here is the requested information:

CREATE TABLE `player` (
 `id` bigint(20) NOT NULL auto_increment,
 `username` varchar(150) NOT NULL default '',
 `signupDate` datetime NOT NULL default '0000-00-00 00:00:00',
 `lastLogin` datetime default NULL,
 PRIMARY KEY  (`id`),
 UNIQUE KEY `username` (`username`)
) TYPE=MyISAM

MySQL server version: 4.0.14

Thank you for the report.

But version 4.0.14 is very old. Please upgrade to current version 4.0.30, try with it and informa us if problem still exists.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm having the same problem with MySQL version 5.0.51a (Client API version).

I only have this problem if the Storage engine of the table is set to MyISAM.
A list of the rows I get:
1
72057594037927937
72057594037927938
144115188075855874
144115188075855875
216172782113783811
216172782113783812
288230376151711748
288230376151711749
360287970189639685
360287970189639686
432345564227567622
432345564227567623
504403158265495559
504403158265495560
etc.

However, if I set the Storage engine to InnoDB then this are the rows I get (table got 2929 rows):
128
384
640
896
1152
1408
1664
1920
2176
2432
2688
129
385
641
897
1153
1409
1665
1921
2177
2433
2689
130
386
etc.

So with InnoDB the auto_increment goes well, but not the ordering on a column of type bigint.

I didn't test other Storage Engines.

I've got the feeling that the problem started when I used the InnoDB Engine for the first time.