"Duplicate entry '0' for key 1" after having uploaded the site

By charly on 26 May 2006 at 09:57 UTC

Hi all,

Ive upgraded to drupal 4.7 on my loclhost. All was working fine. I uploaded files and sql dump to he server, and now, i have this warning messages (or similar) quite often:

user warning: Duplicate entry '0' for key 1 query: INSERT INTO accesslog (title, path, url, hostname, uid, sid, timer, timestamp) values('statistiques', 'admin/settings/statistics', 'http://www.les4chenes.be/?q=admin/settings/statistics', '81.243.105.154', 1, 'ff7995ee2aa75ee8503c46b4f4e65ab9', 617, 1148636983) in /home2/l/les4chenes/www/includes/database.mysql.inc on line 120.

These errors regards accesslog or watchdog, and my logs arent working anymore; its quite anoying. I tried some fixes found on another threads about similar problems, but no one works here.

Thanks

Charly

Comments

sorry for commenting to push

charly commented 26 May 2006 at 22:50

sorry for commenting to push the post on top of the list, but i have some more info that can be related: I use mysql Ver 14.12 Distrib 5.0.21, for pc-linux-gnu (i686) using readline 5.1. The server is using mysql 4. I have to dump my db using "--create-options --compatible=mysql40" as switches for mysqldump. Then i send the dump to the server (database is dumped without errors) and warnings described above results. You can see it live at www.les4chenes.be. I can deactivate statistics (and errors about accesslog disapears) but i cant desactivate watchdog and i dont want to.

Thanks

Charly

I managed to get rid of

charly commented 28 May 2006 at 12:10

I managed to get rid of theses errors. Here's what i did: I deleted the concerned tables, recreated them and changed attributes of the primary keys ('x'id) to have auto-increment. I did that with phpmyadmin

I hope it might help others users.

This helped me too!

housetier commented 13 June 2006 at 10:25

I had the same problems. So took mysqladmin and dropped the primary keys and indexes of the problem tables. Then I added the auto_increment to the primary key to be. After this I set the primary key and index again, and things seem to work quite nicely so far.

I probably did one more unneccessary thing: I truncated the tables, since they only contained log information anyway. But thinking back, I believe this was not needed at all. I cannot say for sure, and I don't want to find out; I am glad my site seems to work! :-)

Thanks for hints, charly!

exporting db

abqaria commented 11 July 2006 at 22:22

can anyone type simple steps to solve the problem
i think the main idea is how o export the db

When exporting: "Add auto-increment value"

Leeteq commented 19 January 2007 at 18:43

In PHPmyadmin you can check this option:
"Add AUTO_INCREMENT value".

But it also depends a bit on if the server you export from uses the same version/setup of phpmyadmin as the server you import to. I had a situation where they did not match, and had to go through each table manually on the target server.

Note that the sequences table should also reflect the latest used ID's, but that is a different issue, ref. the comment below here:
http://drupal.org/node/65665#comment-188974

.
--
( Evaluating the long-term route for Drupal 7.x via BackdropCMS at https://www.CMX.zone )

AUTO_INCREMENT missing from MySQL dump

jkirkwood commented 11 February 2007 at 21:44

I had this same problem with my webhost server (MySQL version: 4.0.27-standard; phpMyAdmin - 2.9.0.2).

If I choose my database in the dropdown in the left pane of the phpMyAdmin interface and then export the database (tables and data, with 'Add AUTO_INCREMENT' option checked), the dump is missing all AUTO_INCREMENT information.

However, if I go to the phpMyAdmin home page and click on the 'Export' option (in the list of links below the server version) and select my database in the listbox on the page that is displayed (with 'Add AUTO_INCREMENT' checked), then the dump contains all the AUTO_INCREMENT information expected. Just remove the 'CREATE' and 'USE' database statements from the header of the .sql file.

There have been/are bugs in this area in MySQL and your server settings can get in the way too (see other solutions in this thread - my server only allows MyISAM databases, and not Innodb databases), but this is a great workaround for me, at least!

If you are importing a MySQL 4.x dump into MySQL 5.x, one thing that is still missing is the field options for the phplist_list.modified field (installed by CiviCRM, for example). You may need to modify it to read " `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, "

All the best,
John Kirkwood

'Working to make a difference' (work in progress - http://www.epnetwork.org)

MySQL Bug & Script for Fixing

colan

he/him

English

Toronto 🇨🇦

commented 25 July 2006 at 21:33

I've written a little script to be run after an import:

#############################################################################
# The purpose of this script is to fix an import of a Drupal 4.7 database
# dumped from "mysqldump --compatible=mysql40" with the auto_increment
# bug, http://bugs.mysql.com/bug.php?id=14515.
#
# Run this script after doing the import.
#
# If you've added any modules that have an auto_increment field, you'll
# probably need to add "alter table" commands for those modules as well.
#############################################################################
alter table access modify aid tinyint(10) not null auto_increment;
alter table accesslog modify aid int(10) not null auto_increment;
alter table aggregator_category modify cid int(10) not null auto_increment;
alter table aggregator_feed modify fid int(10) not null auto_increment;
alter table aggregator_item modify iid int(10) not null auto_increment;
alter table authmap modify aid int(10) unsigned not null auto_increment;
alter table boxes modify bid tinyint(4) not null auto_increment;
alter table comments modify cid int(10) not null auto_increment;
alter table contact modify cid int(10) unsigned not null auto_increment;
alter table node_comment_statistics modify nid int(10) unsigned not null auto_increment;
alter table client modify cid int(10) unsigned not null auto_increment;
alter table filter_formats modify format int(4) not null auto_increment;
alter table locales_source modify lid int(11) not null auto_increment;
alter table node modify nid int(10) unsigned not null auto_increment;
alter table profile_fields modify fid int(10) not null auto_increment;
alter table url_alias modify pid int(10) unsigned not null auto_increment;
alter table poll_choices modify chid int(10) unsigned not null auto_increment;
alter table role modify rid int(10) unsigned not null auto_increment;
alter table term_data modify tid int(10) unsigned not null auto_increment;
alter table vocabulary modify vid int(10) unsigned not null auto_increment;
alter table watchdog modify wid int(5) not null auto_increment;

Thanks for this script. It

soupp commented 31 July 2006 at 16:41

Thanks for this script. It worked fine!

_{Drupal Top Sites | Drupal Sites Directory}

Add this line if using the audio module

Ben Finklea commented 8 September 2006 at 22:29

Awesome piece of work, colan. You saved my bacon 10 times over.

Add this line if you're using the audio/podcasting module:


alter table playlist_relate modify rid int(11) not null auto_increment;

SpryDev Online Marketing

--Ben Finklea, CEO
Volacci

Just Building On This List for ECommerce Modules

mgifford

he/him

English

commented 9 September 2006 at 03:20

Just had to downgrade from 4.1 to 4.0, so thought I'd document the changes here:

alter table ec_address modify aid int(11) not null auto_increment;
alter table ec_attribute modify aid int(10) not null auto_increment;
alter table ec_flexicharge modify chid int(10) not null auto_increment;
alter table ec_shipping_product_attribute modify pid int(10) not null auto_increment;
alter table ec_shipping_shipfrom modify sid int(11) not null auto_increment;
alter table ec_variation modify vid int(10) not null auto_increment;

--
OpenConcept | WLP | FVC | OX | OO | Octopus

+ table ec_authorize_net

BeatnikDude commented 6 December 2007 at 00:56

ALTER TABLE `ec_authorize_net` CHANGE `txnid` `txnid` INT( 11 ) NOT NULL AUTO_INCREMENT

'(Òvó)

Will this approach help my

sarvi commented 12 September 2006 at 07:14

Will this approach help my case. I am getting the error below.
My website was working great until I installed the location module.
The installation went through fine.
I was then able to enabled it in administer->modules and configuring it at administer->settings->location.

I see the error below when I try to enabled the country and state fields on a specific content-type(the content type is custom, using CCK but I am thinking that may be irrelevent.) I suspec the problem may be related to the location module related tables.

The specific content-type is "content_incident_experience_report".

Please also find the list of tables in setup below. I don't understand which of these tables are causing this problem.

Thanks,
Sarvi

Error messages
------------------
Warning: Duplicate entry 'location_collapsible_content_incident_experience' for key 1 query: variable_set INSERT INTO variable (name, value) VALUES ('location_collapsible_content_incident_experience_repo', 'i:1;') in /home/content/s/a/r/sarvilive/html/includes/database.mysql.inc on line 120

Warning: Duplicate entry 'location_collapsed_content_incident_experience_r' for key 1 query: variable_set INSERT INTO variable (name, value) VALUES ('location_collapsed_content_incident_experience_repo', 'i:1;') in /home/content/s/a/r/sarvilive/html/includes/database.mysql.inc on line 120

Warning: Duplicate entry 'location_province_content_incident_experience_re' for key 1 query: variable_set INSERT INTO variable (name, value) VALUES ('location_province_content_incident_experience_repo', 's:1:\"1\";') in /home/content/s/a/r/sarvilive/html/includes/database.mysql.inc on line 120

Warning: Duplicate entry 'location_postal_code_content_incident_experience' for key 1 query: variable_set INSERT INTO variable (name, value) VALUES ('location_postal_code_content_incident_experience_repo', 's:1:\"1\";') in /home/content/s/a/r/sarvilive/html/includes/database.mysql.inc on line 120

Warning: Duplicate entry 'location_country_content_incident_experience_rep' for key 1 query: variable_set INSERT INTO variable (name, value) VALUES ('location_country_content_incident_experience_repo', 's:1:\"1\";') in /home/content/s/a/r/sarvilive/html/includes/database.mysql.inc on line 120

Warning: Cannot modify header information - headers already sent by (output started at /home/content/s/a/r/sarvilive/html/includes/database.mysql.inc:120) in /home/content/s/a/r/sarvilive/html/includes/common.inc on line 266

My Tables List:
access Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
accesslog Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
adsense_clicks Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
aggregator_category Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
aggregator_category_feed Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
aggregator_category_item Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
aggregator_feed Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
aggregator_item Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
authmap Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
blocks Browse Structure Search Insert Empty Drop 132 MyISAM 6.1 KB -
book Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
boxes Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
cache Browse Structure Search Insert Empty Drop 7 MyISAM 292.8 KB 28,208 Bytes
client Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
client_system Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
comments Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
contact Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
contemplate Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
devel_queries Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
devel_times Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
ejournal_articles Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
ejournal_articles_log Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
ejournal_authors Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
ejournal_email_templates Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
ejournal_issues Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
ejournal_journals Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
ejournal_shortly Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
ejournal_store Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
feedback_pages Browse Structure Search Insert Empty Drop 1 MyISAM 2.3 KB -
files Browse Structure Search Insert Empty Drop 11 MyISAM 2.8 KB -
file_revisions Browse Structure Search Insert Empty Drop 12 MyISAM 2.2 KB -
filters Browse Structure Search Insert Empty Drop 6 MyISAM 2.1 KB -
filter_formats Browse Structure Search Insert Empty Drop 3 MyISAM 4.2 KB -
flood Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
forum Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
gsitemap Browse Structure Search Insert Empty Drop 21 MyISAM 2.5 KB -
history Browse Structure Search Insert Empty Drop 40 MyISAM 2.5 KB -
i18n_node Browse Structure Search Insert Empty Drop 15 MyISAM 2.3 KB -
i18n_variable Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
image_attach Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
img_assist_map Browse Structure Search Insert Empty Drop 6 MyISAM 2.1 KB -
invite Browse Structure Search Insert Empty Drop 8 MyISAM 2.4 KB -
locales_meta Browse Structure Search Insert Empty Drop 5 MyISAM 2.1 KB -
locales_source Browse Structure Search Insert Empty Drop 2,071 MyISAM 203.7 KB -
locales_target Browse Structure Search Insert Empty Drop 3,394 MyISAM 203.2 KB -
menu Browse Structure Search Insert Empty Drop 118 MyISAM 7.7 KB -
node Browse Structure Search Insert Empty Drop 21 MyISAM 14.7 KB -
node_access Browse Structure Search Insert Empty Drop 1 MyISAM 3.0 KB -
node_comment_statistics Browse Structure Search Insert Empty Drop 21 MyISAM 3.4 KB -
node_content_incident_experience_repo Browse Structure Search Insert Empty Drop 2 MyISAM 2.7 KB -
node_counter Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
node_data_field_person_providing_service Browse Structure Search Insert Empty Drop 2 MyISAM 2.0 KB -
node_data_field_person_receiving_service Browse Structure Search Insert Empty Drop 2 MyISAM 2.0 KB -
node_field Browse Structure Search Insert Empty Drop 9 MyISAM 5.7 KB 2,044 Bytes
node_field_instance Browse Structure Search Insert Empty Drop 9 MyISAM 9.2 KB 1,992 Bytes
node_group Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
node_group_fields Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
node_revisions Browse Structure Search Insert Empty Drop 27 MyISAM 57.0 KB -
node_type_content Browse Structure Search Insert Empty Drop 1 MyISAM 3.7 KB 28 Bytes
permission Browse Structure Search Insert Empty Drop 6 MyISAM 6.6 KB 116 Bytes
poll Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
poll_choices Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
poll_votes Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
profile_fields Browse Structure Search Insert Empty Drop 7 MyISAM 5.7 KB -
profile_values Browse Structure Search Insert Empty Drop 7 MyISAM 3.2 KB -
role Browse Structure Search Insert Empty Drop 7 MyISAM 3.2 KB -
search_dataset Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
search_index Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
search_total Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
sequences Browse Structure Search Insert Empty Drop 9 MyISAM 3.2 KB -
sessions Browse Structure Search Insert Empty Drop 17 MyISAM 33.0 KB 13,804 Bytes
system Browse Structure Search Insert Empty Drop 131 MyISAM 30.2 KB 28 Bytes
tasks Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
term_access Browse Structure Search Insert Empty Drop 91 MyISAM 5.2 KB -
term_access_defaults Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
term_data Browse Structure Search Insert Empty Drop 13 MyISAM 3.5 KB -
term_hierarchy Browse Structure Search Insert Empty Drop 13 MyISAM 4.1 KB -
term_node Browse Structure Search Insert Empty Drop 1 MyISAM 4.0 KB -
term_relation Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
term_synonym Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
tinymce_role Browse Structure Search Insert Empty Drop 4 MyISAM 2.1 KB -
tinymce_settings Browse Structure Search Insert Empty Drop 1 MyISAM 4.6 KB -
url_alias Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
userreview Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
users Browse Structure Search Insert Empty Drop 9 MyISAM 6.6 KB -
users_roles Browse Structure Search Insert Empty Drop 27 MyISAM 2.2 KB -
variable Browse Structure Search Insert Empty Drop 349 MyISAM 48.1 KB -
video Browse Structure Search Insert Empty Drop 1 MyISAM 2.3 KB 176 Bytes
view_argument Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
view_exposed_filter Browse Structure Search Insert Empty Drop 5 MyISAM 3.7 KB 996 Bytes
view_filter Browse Structure Search Insert Empty Drop 6 MyISAM 3.7 KB 980 Bytes
view_sort Browse Structure Search Insert Empty Drop 2 MyISAM 2.2 KB -
view_tablefield Browse Structure Search Insert Empty Drop 0 MyISAM 2.5 KB 504 Bytes
view_view Browse Structure Search Insert Empty Drop 1 MyISAM 4.3 KB -
vocabulary Browse Structure Search Insert Empty Drop 3 MyISAM 2.2 KB -
vocabulary_node_types Browse Structure Search Insert Empty Drop 7 MyISAM 2.1 KB -
votingapi_action Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
votingapi_action_condition Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
votingapi_action_set Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
votingapi_cache Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
votingapi_vote Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
watchdog Browse Structure Search Insert Empty Drop 376 MyISAM 81.2 KB 12,020 Bytes
webform Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
webform_component Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
webform_submissions Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -
webform_submitted_data Browse Structure Search Insert Empty Drop 0 MyISAM 1.0 KB -

An even simpler solution

TBarregren commented 12 October 2006 at 12:04

An even simpler solution is this PHP script.

Thomas (www.webbredaktoren.se)

Very good!

funana commented 28 January 2007 at 20:10

Timesaver. Thank you!

Thank you, this worked for

xamox commented 15 October 2006 at 20:57

Thank you, this worked for me as well.

---------------------------------------------------------------
http://xamox.NET

How do I know which tables to include?

craigdurling commented 2 December 2007 at 14:23

I would like to try running this script (http://drupal.org/node/65665#comment-139967) but I'm not sure what other tables I should include. In other words, how do I know which modules (and tables) have an auto_increment field? I'm not a frequent user of phpmyadmin so forgive me if this seems like a simple and obvious question.

Really, A Simple Fix

ravinggenius commented 18 December 2006 at 23:23

The problem I found when I got this error was simply that a table for holding the highest nid/vid. I started a new thread (http://drupal.org/node/103899) to provide more information that what can be found at http://drupal.org/node/80195

The short of it is simply updating the sequences table with the max() values for nid/vid from the node table. For instance:

SELECT MAX(nid)
FROM node;

SELECT MAX(vid)
FROM node;

UPDATE sequences
SET id = # use the value from MAX(nid)
WHERE name LIKE 'node_id';

UPDATE sequences
SET id = # use the value from MAX(vid)
WHERE name LIKE 'node_revisions_id';

Different Issue

Tresler commented 15 January 2007 at 15:14

It should be noted that these are TWO seperate issues with different fixes.

The first is a result of transferring from one database to another and the 'Auto-Increment' settings are removed on the tables. The sript(s) above fix that.

The second is when your 'sequences' table doesn't coincide with the rest of your tables. and can be fixed with the script immediately above.

You'll almost certainly notice the first with a duplicate entry error on 'watchdog' The second could be a duplicate entry on a number of tables, but *not* watchdog. ( Not a certain test - but good indications)

Sam Tresler
http://www.treslerdesigns.com
-------------------------------------
"A list of common problems and their solutions can be found in the Troubleshooting FAQ."

I had the same problem after

preludedan commented 12 April 2007 at 14:19

I had the same problem after moving from Mysql 5.0.21 with phpMyAdmin - 2.8.1 (on my development environment) to MySQL 4.1.12 with phpMyAdmin - 2.9.0.2 (on my web server)

I had to export using compatibility for Mysql40 and the import went smoothly. But after logging in, I removed an old user from the user list and had the same error as charly, but relating to the watchdog table. To fix I simply went into phpmyadmin on my web server and edited the watchdog table so 'wid' had 'auto_increment' set (as it wasn't set) and I was back in business.

Strange thing I'd like to know is why does this happen? The other tables seem to have auto_increment set, why didn't watchdog when I moved the tables across?

how to edit watchdog table in phpmyadmin

lisa commented 22 June 2007 at 15:01

When I moved test site to server, site looks ok, but when I try logging in I get this error:
Warning: Duplicate entry '0' for key 1 query: INSERT INTO watchdog

SQL file appears to set auto-increment for wid

DROP TABLE IF EXISTS `watchdog`;
CREATE TABLE `watchdog` (
`wid` int(11) NOT NULL auto_increment,
`uid` int(11) NOT NULL default '0',
`type` varchar(16) NOT NULL default '',
`message` longtext NOT NULL,
`severity` tinyint(3) unsigned NOT NULL default '0',
`link` varchar(255) NOT NULL default '',
`location` text NOT NULL,
`referer` varchar(128) NOT NULL default '',
`hostname` varchar(128) NOT NULL default '',
`timestamp` int(11) NOT NULL default '0',
PRIMARY KEY (`wid`),
KEY `type` (`type`)
)

BUT current dB has

field wid
Type int(11)
Collation
Attributes
Null	Yes
Default 0
Extra 

Collation, Attributes and Extra are blank

Which column of the table should show auto_increment?
How do I add auto_increment to the wid field in the watchdog table using phpmyadmin?

I am running Drupal 5.1. And both servers and running MySQL 5

wid should show

danmurf commented 16 August 2007 at 09:03

wid should show auto_increment. To set this in phpmyadmin just click the database name on the left panel (so the list of tables appears in the main window) and click the second button in on the watchdog table (structure). Use the change button on wid, and set extra to auto_increment. I hope this helps