Re: 1062 Duplicate entry '0' for key 1
SOLUTION:
Exectute the following SQL code in PHPMyAdmin... (Copy & paste)
This will change the log_id column to auto_increment... you get this error when it doesn't auto_increment!
Code:
ALTER TABLE admin_activity_log CHANGE COLUMN log_id log_id int(15) NOT NULL auto_increment;
Found in thread http://www.zen-cart.com/forum/showpo...5&postcount=13
Worked for me!
Hope it helps everyone else out there...
Re: 1062 Duplicate entry '0' for key 1
As you can probably tell my issue with savannahspasecrets.com has been resolved. For the duplicate entry error message that I received I simply compared the original sql file that built the original tables for the database to the errored database. I found that for every "key" ID I was missing the auto-increment setting. In this case upon every new data entry to what ever table it was trying to start from "0" and then the following new row written would try to write over this same row key value with "0"... kicking back to the script a <b>1062 Duplicate entry '0' for key 1</b> error! Bingo, this solution has been working for my zen-cart for 2 weeks now.
Good luck, I will try to help anyone else if their situation is a bit different.
Aaron
Re: 1062 Duplicate entry '0' for key 1
Quote:
Originally Posted by adanker
As you can probably tell my issue with savannahspasecrets.com has been resolved. For the duplicate entry error message that I received I simply compared the original sql file that built the original tables for the database to the errored database. I found that for every "key" ID I was missing the auto-increment setting. In this case upon every new data entry to what ever table it was trying to start from "0" and then the following new row written would try to write over this same row key value with "0"... kicking back to the script a <b>1062 Duplicate entry '0' for key 1</b> error! Bingo, this solution has been working for my zen-cart for 2 weeks now.
Good luck, I will try to help anyone else if their situation is a bit different.
Aaron
would be cool if someone posted an SQL patch to fix alll auto_increment collumns that are not auto_increment now...
or is there a better way to do it, without screwing up your migrated data?
I fixed the one in the admin activity log, but i have to fix it in the other tables too... the banner history log i guess... (when the error shows up on the bottom of the page on the main site)
Code:
ALTER TABLE admin_activity_log CHANGE COLUMN log_id log_id int(15) NOT NULL auto_increment;
ALTER TABLE banners_history CHANGE COLUMN banners_history_id banners_history_id int(11) NOT NULL auto_increment;
probably need to edit other tables with history in it...
e.g. orders_status_history, paypal_payment_status_history, project_version_history
Re: 1062 Duplicate entry '0' for key 1
If the tables which are "supposed to have" auto-increment on them ... do not... then you've done something odd with your database. When Zen Cart installs them, they are already set to auto-increment.
My point is that there's not so much a need for a script to set them to auto-increment, but rather the more important key is to find out WHY they have lost the auto-increment setting in the first place....
Re: 1062 Duplicate entry '0' for key 1
I've just moved my site by doing a database export & import and ran into the same problem. i think I may have resolved it by reinstating the autoincrement in the Extra column of log_id in the admin_activity_log table.
Re: 1062 Duplicate entry '0' for key 1
I suppose one potential cause of losing auto-increment settings is if you do a backup via phpMyAdmin and uncheck the box related to retaining auto-increment values.
Technically this shouldn't leave the auto-increment out of the structure/schema, just not include the current value in it.
Perhaps something's busted there...
Re: 1062 Duplicate entry '0' for key 1
Okay, having found more cases of problems identical to this, it appears that the cause is related to the way in which various hosts will export backups of data to transfer from one server to another. I'm guessing that many of them don't include the auto-increment property by default ... which is very unfortunate. I'm rather surprised to have not seen this reported earlier. Nevertheless, here's a script to reinstate auto-increment properties on tables in v1.3.x:
Code:
# This script simply rebuilds the auto-increment settings on tables which should have them.
ALTER TABLE upgrade_exceptions CHANGE COLUMN upgrade_exception_id upgrade_exception_id smallint(5) NOT NULL auto_increment;
ALTER TABLE address_book CHANGE COLUMN address_book_id address_book_id int(11) NOT NULL auto_increment;
ALTER TABLE address_format CHANGE COLUMN address_format_id address_format_id int(11) NOT NULL auto_increment;
ALTER TABLE admin CHANGE COLUMN admin_id admin_id int(11) NOT NULL auto_increment;
ALTER TABLE admin_activity_log CHANGE COLUMN log_id log_id int(15) NOT NULL auto_increment;
ALTER TABLE authorizenet CHANGE COLUMN id id int(11) unsigned NOT NULL auto_increment;
ALTER TABLE banners CHANGE COLUMN banners_id banners_id int(11) NOT NULL auto_increment;
ALTER TABLE banners_history CHANGE COLUMN banners_history_id banners_history_id int(11) NOT NULL auto_increment;
ALTER TABLE categories CHANGE COLUMN categories_id categories_id int(11) NOT NULL auto_increment;
ALTER TABLE configuration CHANGE COLUMN configuration_id configuration_id int(11) NOT NULL auto_increment;
ALTER TABLE configuration_group CHANGE COLUMN configuration_group_id configuration_group_id int(11) NOT NULL auto_increment;
ALTER TABLE countries CHANGE COLUMN countries_id countries_id int(11) NOT NULL auto_increment;
ALTER TABLE coupon_email_track CHANGE COLUMN unique_id unique_id int(11) NOT NULL auto_increment;
ALTER TABLE coupon_gv_queue CHANGE COLUMN unique_id unique_id int(5) NOT NULL auto_increment;
ALTER TABLE coupon_redeem_track CHANGE COLUMN unique_id unique_id int(11) NOT NULL auto_increment;
ALTER TABLE coupon_restrict CHANGE COLUMN restrict_id restrict_id int(11) NOT NULL auto_increment;
ALTER TABLE coupons CHANGE COLUMN coupon_id coupon_id int(11) NOT NULL auto_increment;
ALTER TABLE currencies CHANGE COLUMN currencies_id currencies_id int(11) NOT NULL auto_increment;
ALTER TABLE customers CHANGE COLUMN customers_id customers_id int(11) NOT NULL auto_increment;
ALTER TABLE customers_basket CHANGE COLUMN customers_basket_id customers_basket_id int(11) NOT NULL auto_increment;
ALTER TABLE customers_basket_attributes CHANGE COLUMN customers_basket_attributes_id customers_basket_attributes_id int(11) NOT NULL auto_increment;
ALTER TABLE email_archive CHANGE COLUMN archive_id archive_id int(11) NOT NULL auto_increment;
ALTER TABLE featured CHANGE COLUMN featured_id featured_id int(11) NOT NULL auto_increment;
ALTER TABLE files_uploaded CHANGE COLUMN files_uploaded_id files_uploaded_id int(11) NOT NULL auto_increment;
ALTER TABLE geo_zones CHANGE COLUMN geo_zone_id geo_zone_id int(11) NOT NULL auto_increment;
ALTER TABLE group_pricing CHANGE COLUMN group_id group_id int(11) NOT NULL auto_increment;
ALTER TABLE ezpages CHANGE COLUMN pages_id pages_id int(11) NOT NULL auto_increment;
ALTER TABLE languages CHANGE COLUMN languages_id languages_id int(11) NOT NULL auto_increment;
ALTER TABLE layout_boxes CHANGE COLUMN layout_id layout_id int(11) NOT NULL auto_increment;
ALTER TABLE manufacturers CHANGE COLUMN manufacturers_id manufacturers_id int(11) NOT NULL auto_increment;
ALTER TABLE media_clips CHANGE COLUMN clip_id clip_id int(11) NOT NULL auto_increment;
ALTER TABLE media_manager CHANGE COLUMN media_id media_id int(11) NOT NULL auto_increment;
ALTER TABLE media_types CHANGE COLUMN type_id type_id int(11) NOT NULL auto_increment;
ALTER TABLE meta_tags_categories_description CHANGE COLUMN categories_id categories_id int(11) NOT NULL auto_increment;
ALTER TABLE meta_tags_products_description CHANGE COLUMN products_id products_id int(11) NOT NULL auto_increment;
ALTER TABLE music_genre CHANGE COLUMN music_genre_id music_genre_id int(11) NOT NULL auto_increment;
ALTER TABLE newsletters CHANGE COLUMN newsletters_id newsletters_id int(11) NOT NULL auto_increment;
ALTER TABLE orders CHANGE COLUMN orders_id orders_id int(11) NOT NULL auto_increment;
ALTER TABLE orders_products CHANGE COLUMN orders_products_id orders_products_id int(11) NOT NULL auto_increment;
ALTER TABLE orders_products_attributes CHANGE COLUMN orders_products_attributes_id orders_products_attributes_id int(11) NOT NULL auto_increment;
ALTER TABLE orders_products_download CHANGE COLUMN orders_products_download_id orders_products_download_id int(11) NOT NULL auto_increment;
ALTER TABLE orders_status_history CHANGE COLUMN orders_status_history_id orders_status_history_id int(11) NOT NULL auto_increment;
ALTER TABLE orders_total CHANGE COLUMN orders_total_id orders_total_id int(10) unsigned NOT NULL auto_increment;
ALTER TABLE paypal_session CHANGE COLUMN unique_id unique_id int(11) NOT NULL auto_increment;
ALTER TABLE paypal CHANGE COLUMN paypal_ipn_id paypal_ipn_id int(11) unsigned NOT NULL auto_increment;
ALTER TABLE paypal_testing CHANGE COLUMN paypal_ipn_id paypal_ipn_id int(11) unsigned NOT NULL auto_increment;
ALTER TABLE paypal_payment_status CHANGE COLUMN payment_status_id payment_status_id int(11) NOT NULL auto_increment;
ALTER TABLE paypal_payment_status_history CHANGE COLUMN payment_status_history_id payment_status_history_id int(11) NOT NULL auto_increment;
ALTER TABLE product_type_layout CHANGE COLUMN configuration_id configuration_id int(11) NOT NULL auto_increment;
ALTER TABLE product_types CHANGE COLUMN type_id type_id int(11) NOT NULL auto_increment;
ALTER TABLE products CHANGE COLUMN products_id products_id int(11) NOT NULL auto_increment;
ALTER TABLE products_attributes CHANGE COLUMN products_attributes_id products_attributes_id int(11) NOT NULL auto_increment;
ALTER TABLE products_description CHANGE COLUMN products_id products_id int(11) NOT NULL auto_increment;
ALTER TABLE products_options_values_to_products_options CHANGE COLUMN products_options_values_to_products_options_id products_options_values_to_products_options_id int(11) NOT NULL auto_increment;
ALTER TABLE project_version CHANGE COLUMN project_version_id project_version_id tinyint(3) NOT NULL auto_increment;
ALTER TABLE project_version_history CHANGE COLUMN project_version_id project_version_id tinyint(3) NOT NULL auto_increment;
ALTER TABLE query_builder CHANGE COLUMN query_id query_id int(11) NOT NULL auto_increment;
ALTER TABLE record_artists CHANGE COLUMN artists_id artists_id int(11) NOT NULL auto_increment;
ALTER TABLE record_company CHANGE COLUMN record_company_id record_company_id int(11) NOT NULL auto_increment;
ALTER TABLE reviews CHANGE COLUMN reviews_id reviews_id int(11) NOT NULL auto_increment;
ALTER TABLE salemaker_sales CHANGE COLUMN sale_id sale_id int(11) NOT NULL auto_increment;
ALTER TABLE specials CHANGE COLUMN specials_id specials_id int(11) NOT NULL auto_increment;
ALTER TABLE tax_class CHANGE COLUMN tax_class_id tax_class_id int(11) NOT NULL auto_increment;
ALTER TABLE tax_rates CHANGE COLUMN tax_rates_id tax_rates_id int(11) NOT NULL auto_increment;
ALTER TABLE template_select CHANGE COLUMN template_id template_id int(11) NOT NULL auto_increment;
ALTER TABLE zones CHANGE COLUMN zone_id zone_id int(11) NOT NULL auto_increment;
ALTER TABLE zones_to_geo_zones CHANGE COLUMN association_id association_id int(11) NOT NULL auto_increment;
NOTE: The up-to-date version of this script is ALSO available in the Zen Cart master fileset ... look for: /zc_install/sql/db_rebuild_autoincrement.sql
Re: 1062 Duplicate entry '0' for key 1
Thanks a lot for this Dr. Byte. I think you are correct in that the export import doesn't work out correctly.
Can a moderator merge all the topics that have the same symptom? as that will surely get people to the answer faster...
Maybe this should be added to the FAQ?
Re: 1062 Duplicate entry '0' for key 1
I am experiencing this problem in my admin area when I try to go anywhere.
What can be done to correct this?
cshart
Re: 1062 Duplicate entry '0' for key 1
When exporting from mysql4.1 and using the --compat=mysql40 or --compaty-mysql3 flags, mysqldump export command will not give you proper auto-increment flags. This is considered proper behavior by MySQL AB and will not be changed. When going from 4.1 to 4.0 or 3.23 the most common issue is the syntax at the end of the table definition: ENGINE= and DEFAULT_CHARSET=.
Here is a general fix for dumping from 4.1 -> 4.0 or 3.23 when not using the --compat flag. This only fixes the most general problem with the table definitions, I don't think it works on enums, and it is only safe to use on a file that has only table definitions, not table definitions and data.
sed -e 's/\ ENGINE=\([a-zA-Z]*\)\ DEFAULT\ CHARSET=[a-zA-Z0-9]*/\ TYPE=\1/' foobar.mysql > foobar.fixed.mysql
or, for a directory of files
for x in `ls mydumpdir/*.sql`
do
sed -e 's/\ ENGINE=\([a-zA-Z]*\)\ DEFAULT\ CHARSET=[a-zA-Z0-9]*/\ TYPE=\1/' $x > fixed.$x
done
