1062 - Duplicate entry '2147483647' for key 1

[resoman]

Hello,

 

I was handed a cart to update/maintain from a new client. When trying to add new option values to option attributes, I get an error!

 

1062 - Duplicate entry '2147483647' for key 1

 

insert into products_options_values_to_products_options (products_options_id, products_options_values_id) values ('21', '26236')

 

I have no idea where to start here, any ideas for an OSC newbie?

 

I have deleted the option over and over again, and it gets entered, but as '2147483647'

 

I understand that is the max number allowed for an INT, why would it go to the max when it should be assigning the above ID of '26236'

 

Thanks for any help you may have!

[resoman]

Ok, from previous posts I jimmy-rigged a solution, yet I am not sure of its stability.

 

I changed the column 'products_options_values_to_products_options_id' from INT(11) to BIGINT(11)

 

After that I ran into another duplicate entry error in the table products attributes

 

I also changed the column 'products_attributes_id' to BIGINT(11)

 

I am now able to assign attributes, but they are all going above the id '2147483647'

 

 

Will this cause a problem for me in the future?

 

Any input is highly recommended!

 

Thanks in advance,

 

Jesse

[Guest]

If I remember correctly 2147483647 is the max number of rows you can have in a Mysql table. So if you are starting there then there is nowhere higher you can go. Using PHPMyadmin reset the row number to 1. You might have to download the table and then upload again to change the first entry number.

[resoman]

You are right about the max for INT, BIGINT seems to be functional.

 

I just want to be sure this wont interrupt the ordering / updating process.

[resoman]

You are right about the max for INT, BIGINT seems to be functional.

 

I just want to be sure this wont interrupt the ordering / updating process.

 

 

bigint(11) worked, yet now it seems to have interfered with how I add attributes to products. I can assign attributes, but I cannot change or delete them via the administration panel (I can in phpMyAdmin)

 

So, its a fix, but not complete.

 

Any suggestions?

[Guest]

Change it back to INT and then using PHPMyadmin export the table. When you open it in notepad at the bottom you will see;

) TYPE=MyISAM=2147483647; (or something similar to that)

 

Change that to;

 

) TYPE=MyISAM;

 

and then upload the file again.

[resoman]

Change it back to INT and then using PHPMyadmin export the table. When you open it in notepad at the bottom you will see;

) TYPE=MyISAM=2147483647; (or something similar to that)

 

Change that to;

 

) TYPE=MyISAM;

 

and then upload the file again.

 

Thanks a bunch!

 

I will give it a shot

 

jesse rooney

[resoman]

Thanks a bunch!

 

I will give it a shot

 

jesse rooney

 

 

I have had this problem a few days now, and THIS FIXED IT!

 

THOSE WHO ARE SEARCHING for duplicate errors fixes, PLEASE TRY THIS STEP!

 

I changed my Auto_Increment to the last id in the column so it continued where it left off.

 

Be sure you do not delete auto_increment, otherwise it will continue to produce the error.

 

Thanks again for your post!

[Guest]

Your file didn't work because it already thought there were 2147483647 rows in the table and since that is the max number of rows in a Mysql table it couldn't produce another row, hence you get the error. It is an error in the SQL file that you uploaded. You can make the change to the contribution that caused this and up reupload the sql file for that contribution to help others.

 

Thank wizardandwars blog for that "tip". :thumbsup:

[scooterfactory]

this is fairly close to the same problems im experiencing. Can I please get a walkthru from myphpadmin on the steps to rectify my problem.. Thank you.

[Guest]

PHPMyadmin is hard to explain, but very easy to do. You just need to export the offending table, save it to your desktop. Open in Notepad, edit as needed. Then upload it again.

 

Remembering to backup your whole database first. If you don't know your way around PHPMyadmin Google for tutorials on it, there are lots out there that might help you find your way around.

[scooterfactory]

will exporting a table delete or change anything from the db?

[Guest]

Exporting wont change anything, Importing will change everything. make a backup of your whole database first and include the drop tables (just a checkmark) as then it makes importing it easier. Also make sure your db is REALLY in your backup file before doing your work.

[cmhorna]

There is a much faster fix that does not involve exporting or importing any tables.

 

In phpMyAdmin, go to where you can type in an SQL command and put in the following :

 

ALTER TABLE `table_name` auto_increment = number;

 

where table_name is the name of the table with the problem and number is the next number that should be in the auto_increment sequence.

 

Example :

 

ALTER TABLE `products_attributes` auto_increment = 357768;

 

 

Then click on "go".

 

Problem solved.

[j2ee.freelancer]

There is another way to do. No queries required !! Go to PHPMySQLAdmin-> Select the table-> Click Operations. Now update the auto_increment field value with the last value in your table.