Auto increment field hit maximum integer value

Eng Goh · #1 (**permalink**) 01-12-06, 05:12

What happens when MySQL auto increment field hit the maximum integer value that the data type can contain/handle ?

My search around the net yield the following :

"The behavior of the auto-increment mechanism is not defined … if the value becomes bigger than the maximum integer that can be stored in the specified integer type."
http://dev.mysql.com/doc/refman/5.0/...nt-column.html

"The behaviour of auto-increment is not defined … if the value becomes bigger than the maximum integer that can be stored in the specified integer type."
http://sunsite.mff.cuni.cz/MIRRORS/f...nt_column.html (documents MySQL up to Version 4.1.0-alpha)

If there is no proper defined elegant behaviour when auto increment hits max. I think it will be hard for anyone to make use of it as it is not reliable.

Can anyone give me a definite answer or point me to a definite answer ?

Thanks.

anjanesh · #2 (**permalink**) 01-12-06, 05:40

PHP Code:


			
CREATE TABLE `test` (

  `id` int(11) NOT NULL auto_increment,

  `data` varchar(100) NOT NULL default '',

  PRIMARY KEY  (`id`)

) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=1 ;



ALTER TABLE `test` PACK_KEYS=0 CHECKSUM=0 DELAY_KEY_WRITE=0 AUTO_INCREMENT=2147483647

INSERT INTO `test` VALUES('', 'data1'); // Works

INSERT INTO `test` VALUES('', 'data2'); // At this point I get an error :

Code:

#1062 - Duplicate entry '2147483647' for key 1

Internet Services

Web Development

Digital Marketing

Consumer Tech