1062 Duplicate entry '0' for key 1 after export/import of database

[Vixay]

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!

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...

[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

[Vixay]

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)

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

[DrByte]

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....

[miss60]

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.

[DrByte]

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...

[DrByte]

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:

# 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

[Vixay]

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?

[cshart]

I am experiencing this problem in my admin area when I try to go anywhere.

What can be done to correct this?

cshart

[Mark_Kimsal]

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