Friday, 30 September 2011

Error Code : 1062 Duplicate entry '' for key 1 auto_increment [SOLVED]

I am using mySQL and Fabrik and Joomla for my web app and out of the blue it started giving errors:
Duplicate entry '' for key 1 SQL=INSERT INTO `main_table` (`field1`,`field2`,`field3`) VALUES ( 'Value1','Value2','','Value3') 
This meant that no data was being successfully entered into that database table :(


After Googling a bit (no, a lot) I stumbled into this blog which helped me a lot (thanks Vinu Thomas):

The important sentences:  “Mysql throws this error because the field type of the auto increment field is not large enough to hold the next value, so it tries to wraps the count back to ’0′ where the is already a record with that value.
We changed the field from int to unsigned bigint which fixed the problem for us. So if you face the same problem, check if your auto-increment field has maxed the datatype for that field.

That’s exactly what I did and saw that my table definition was this – where field1 was the PK and of type varchar(255):
table_name CREATE TABLE `table_name` (
`field1` varchar(255) NOT NULL auto_increment,
`field2` varchar(15) default NULL,
`field3` varchar(255) default NULL,

I altered the table to this and the problem was solved!:
table_name CREATE TABLE `table_name` (
`field1` bigint(255) unsigned NOT NULL auto_increment,                      
`field2` varchar(15) default NULL,                                             
`field3` varchar(255) default NULL,                                          

Using this sql statement:
alter table `dbname`.`table_name` change `field1` `field1` bigint(255) UNSIGNED NOT NULL AUTO_INCREMENT

No comments:

Post a Comment

Live Traffic Feed


Be notified of
page updates
it's private
powered by

Copyright © 2008 HandyTechTipper. All articles are released under the Creative Commons Attribution 2.5 South Africa license, unless where otherwise stated.