Advertisement:

Author Topic: Duplicate entry '0' for key 'PRIMARY' error during db move to new host  (Read 546 times)

Offline societyofrobots

  • Jr. Member
  • **
  • Posts: 210
Had to move my site to a different host, and plenty FUBAR happened during the move. I only have one db backup with no ability to get a second one using different export settings (it's already deleted on previous host :'( ).

The forum and db backup is 2.0.15.
PHP v7.2 using DreamHost

The forum and data displays fine, but I get this error whenever I try to make a post on the forum:

Quote
Database Error
Duplicate entry '0' for key 'PRIMARY'
File: /home/dh_2j99xr/societyofrobots.com/robotforum/Sources/Subs-Post.php
Line: 1834

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.15, while your database is at version 2.0.1. The above error might possibly go away if you execute the latest version of upgrade.php.

I then upgraded to 2.0.16 using package manager, and now get that same error but it says "currently at version SMF 2.0.16."

Searching around, one post said I needed to add autoincrement:
https://www.simplemachines.org/community/index.php?topic=80266.0
So I ran this query in phpMyAdmin:
Code: [Select]
ALTER TABLE smf_2sichat AUTO_INCREMENT = 1;
ALTER TABLE smf_2sichat_barlinks AUTO_INCREMENT = 1;
ALTER TABLE smf_2sichat_gadgets AUTO_INCREMENT = 1;
ALTER TABLE smf_admin_info_files AUTO_INCREMENT = 1;
ALTER TABLE smf_approval_queue AUTO_INCREMENT = 1;
ALTER TABLE smf_attachments AUTO_INCREMENT = 1;
ALTER TABLE smf_ban_groups AUTO_INCREMENT = 1;
ALTER TABLE smf_ban_items AUTO_INCREMENT = 1;
ALTER TABLE smf_boards AUTO_INCREMENT = 1;
ALTER TABLE smf_bookmarks AUTO_INCREMENT = 1;
ALTER TABLE smf_calendar AUTO_INCREMENT = 1;
ALTER TABLE smf_calendar_holidays AUTO_INCREMENT = 1;
ALTER TABLE smf_categories AUTO_INCREMENT = 1;
ALTER TABLE smf_custom_fields AUTO_INCREMENT = 1;
ALTER TABLE smf_geoip_blocks AUTO_INCREMENT = 1;
ALTER TABLE smf_geoip_countries AUTO_INCREMENT = 1;
ALTER TABLE smf_geoip_ip AUTO_INCREMENT = 1;
ALTER TABLE smf_geoip_ip_temp AUTO_INCREMENT = 1;
ALTER TABLE smf_geoip_regions AUTO_INCREMENT = 1;
ALTER TABLE smf_group_moderators AUTO_INCREMENT = 1;
ALTER TABLE smf_ip_lookup AUTO_INCREMENT = 1;
ALTER TABLE smf_login_security AUTO_INCREMENT = 1;
ALTER TABLE smf_login_security_log AUTO_INCREMENT = 1;
ALTER TABLE smf_log_actions AUTO_INCREMENT = 1;
ALTER TABLE smf_log_banned AUTO_INCREMENT = 1;
ALTER TABLE smf_log_errors AUTO_INCREMENT = 1;
ALTER TABLE smf_mail_queue AUTO_INCREMENT = 1;
ALTER TABLE smf_membergroups AUTO_INCREMENT = 1;
ALTER TABLE smf_members AUTO_INCREMENT = 1;
ALTER TABLE smf_messages AUTO_INCREMENT = 1;
ALTER TABLE smf_message_icons AUTO_INCREMENT = 1;
ALTER TABLE smf_openid_assoc AUTO_INCREMENT = 1;
ALTER TABLE smf_package_servers AUTO_INCREMENT = 1;
ALTER TABLE smf_permission_profiles AUTO_INCREMENT = 1;
ALTER TABLE smf_personal_messages AUTO_INCREMENT = 1;
ALTER TABLE smf_pm_rules AUTO_INCREMENT = 1;
ALTER TABLE smf_polls AUTO_INCREMENT = 1;
ALTER TABLE smf_related_subjects AUTO_INCREMENT = 1;
ALTER TABLE smf_related_topics AUTO_INCREMENT = 1;
ALTER TABLE smf_scheduled_tasks AUTO_INCREMENT = 1;
ALTER TABLE smf_smileys AUTO_INCREMENT = 1;
ALTER TABLE smf_spiders AUTO_INCREMENT = 1;
ALTER TABLE smf_subscriptions AUTO_INCREMENT = 1;
ALTER TABLE smf_topics AUTO_INCREMENT = 1;

No effect. I do have other tables from mods I didn't run a query on, but only because I don't know if I should or not:
Code: [Select]
smf_sa_ads
smf_sa_positions
smf_log_badbehavior
smf_log_forumfirewall
smf_log_httpBL
smf_ajaxchat_bans
smf_ajaxchat_invitations
smf_ajaxchat_messages
smf_ajaxchat_online
smf_ajaxshout_bans
smf_ajaxshout_invitations
smf_ajaxshout_messages
smf_ajaxshout_online

I'm way over my head here, so any help will need to spell out the solution in noob terms.  ;D

Offline societyofrobots

  • Jr. Member
  • **
  • Posts: 210
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #1 on: January 11, 2020, 01:59:38 PM »
Update, but still not fixed...

Tried to run upgrade.php but it complained about old English language source files. So I used package manager to upgrade from v2.0.16 to v2.0.17.

I then ran the Large upgrade for v2.0.17 (upgrade.php) without problems.

But, when trying to post in my forum, I still get the same error ... although now no mention of my database version so I guess that's progress:

Quote
Database Error
Duplicate entry '0' for key 'PRIMARY'
File: /home/dh_2j99xr/societyofrobots.com/robotforum/Sources/Subs-Post.php
Line: 1834

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,611
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #2 on: January 11, 2020, 05:14:10 PM »
For someone that doesn't know what he's doing, you're doing a lot of things you shouldn't.  There's no need to upgrade the database, and running upgrade.php on an iffy production db wasn't really a good idea.  Running a query on the db was an even worse idea.  Assuming you still have the original db backup, drop all tables in the one you've been tinkering with, reimport the dump, then look at the table structure in _attachments table, id_attach should be the only primary.

Offline societyofrobots

  • Jr. Member
  • **
  • Posts: 210
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #3 on: January 11, 2020, 08:47:43 PM »
I attached the structure for _attachments.

What should I change?

ps - The production db was destroyed pre-maturely before the move was complete, the site is temporarily off-line. Total fubar. All I have is a backup.sql.zip file, so I can just start over each time I break something. Questionable operations isn't a problem, just my hair loss. ;D

Offline societyofrobots

  • Jr. Member
  • **
  • Posts: 210
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #4 on: January 11, 2020, 09:10:23 PM »
I went through every table and found three that didn't have primary in their structure (if there was an index).

Perhaps it's one of these?

Offline Chen Zhen

  • Sophist Member
  • *****
  • Posts: 1,201
  • Gender: Male
  • If you're going through hell, keep going!
    • Underdog-01 on GitHub
    • WebDev.ca
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #5 on: January 11, 2020, 09:41:25 PM »

That id_attach column for the attachments table should be set to AUTO_INCREMENT.
Click on the "Change" link for id_attach from the structure page and then make sure the "A_I" checkbox is checked as enabled.
Save the changes to see if that fixes your problem.
   

My SMF Mods & Plug-Ins

WebDev
Simple Portal Support

Request politely & demand nothing.

Offline societyofrobots

  • Jr. Member
  • **
  • Posts: 210
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #6 on: January 12, 2020, 08:24:54 PM »
That id_attach column for the attachments table should be set to AUTO_INCREMENT.
Click on the "Change" link for id_attach from the structure page and then make sure the "A_I" checkbox is checked as enabled.   

Didn't work, same error.

I noticed there is no A_I checked for any of the smf_ tables. Would you recommend I add that to all tables, or do some tables not get it?

Offline Chen Zhen

  • Sophist Member
  • *****
  • Posts: 1,201
  • Gender: Male
  • If you're going through hell, keep going!
    • Underdog-01 on GitHub
    • WebDev.ca
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #7 on: January 12, 2020, 10:06:57 PM »

I neglected to read the entire thread & only looked at your image prior to posting.

A database consists of multiple tables, each table contains columns.
Normally in some circumstances one of those columns is set to auto increment which allows the parent program (SMF in this case) to add to that table while having its index column auto increment (progressively increase by 1 in number). Some tables do not require it as they are used differently from the parent program (ie. settings table).

Imo you should delete that database, create a new one with the same name & import your backup.mysql.zip anew.
This time do not run any alter table queries.

Just curious but did you blindly run sql queries to alter tables prior to making that backup file or is that db backup from when your forum was functioning properly?
   

My SMF Mods & Plug-Ins

WebDev
Simple Portal Support

Request politely & demand nothing.

Offline societyofrobots

  • Jr. Member
  • **
  • Posts: 210
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #8 on: January 12, 2020, 10:56:44 PM »
Quote
did you blindly run sql queries to alter tables prior to making that backup file or is that db backup from when your forum was functioning properly?

I'd be crazy to do risky things without a backup lol! The forum backup was made days before the forum was prematurely deleted.

I then re-created the forum with a new host, and imported that backup. The primary key error immediately appeared and has nothing to do with any query or other action I've taken.

I'm only doing "risky" things to debug. If fubar happens I can just re-import the backup, no biggie.

So, would you recommend me adding auto-increment as described in the link in my OP?
https://www.simplemachines.org/community/index.php?topic=80266.0

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,611
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #9 on: January 12, 2020, 11:08:11 PM »
Only difference for id_attach between your screenshot and clean database is you're lacking auto increment.  Check that, see if it clears the error.

Offline shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,711
    • sbulen on GitHub
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #10 on: January 13, 2020, 03:25:38 PM »
The code in question, I believe, is posting to the messages table (line ~1834 of subs-post). 

The auto-increment value should be at least as big as the highest ID for each table, in this case, the message # for the messages table. 

Setting them all to 1 across many tables appears to me to be very dangerous.  My first instinct would be to restore and start over.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,611
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #11 on: January 13, 2020, 09:16:05 PM »
 ?

Code: [Select]

// Fix the attachments.
if (!empty($msgOptions['attachments']))
$smcFunc['db_query']('', '
UPDATE {db_prefix}attachments
SET id_msg = {int:id_msg}
WHERE id_attach IN ({array_int:attachment_list})',
array(
'attachment_list' => $msgOptions['attachments'],
'id_msg' => $msgOptions['id'],
)
);


1834 is closing bracket.

Offline societyofrobots

  • Jr. Member
  • **
  • Posts: 210
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #12 on: January 14, 2020, 01:10:35 PM »
I have a backup, so if I fubar my database, I can drop tables and start over. No worries.

Update:
I installed a second unrelated default database so I can compare the db structure of my backup to what the db structure is supposed to look like. I manually went through all 90+ tables and guess what I found?

MISSING AND DUPLICATE PRIMARY KEYS

...exactly what the error was saying lol.

Also found a ton of other mis-matches, such as Default and variables being different, and a ton of missing auto-increment settings. Also, missing index keys:
Code: [Select]
_collapsed_categories, changed id_cat default from none to 0
_log_categories, changed id_topic and id_msg default from 0 to none
_log_errors, changed sessions Type from varchar to char
_smileys, was missing primary key
_sessions, changed Defaults to None, and session_id type from varchar(32) to char(32)
_topics missing primary key, and other grey colored keys (index)
_subscriptions and _spiders, missing primary key

I fixed these.

BUT, here is where I'm stuck. Looking at the structure I do not see any primary keys for these:
Code: [Select]
_themes missing 3 primary keys
_settings missing primary key
_spiders missing primary key and a_i

However, when I go to add the primary keys, I get this error:
#1062 - Duplicate entry 'next_task_time' for key 'PRIMARY'

In the attached image, on the left is my corrupted backup db I'm trying to fix, and on the right is an unrelated SMF forum. You can see the primary key is missing. How do I fix this error?

Lastly, I noticed that the Collation for every single table in my corrupted db is latin1_swedish_ci, while in the unrelated one is utf8_general_ci. Should I change them all to utf8_general_ci, or does it really matter? It's probably an hour of work because there are about 200 of them to manually change.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,611
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #13 on: January 14, 2020, 04:06:16 PM »
    Not to be unkind, but I'd be very surprised if your "fixed" database isn't trashed and unuseable.  Can you get the original database working in a clean forum install?

Offline societyofrobots

  • Jr. Member
  • **
  • Posts: 210
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #14 on: January 14, 2020, 10:35:09 PM »
I'd be very surprised if your "fixed" database isn't trashed and unuseable. Can you get the original database working in a clean forum install?
You misunderstand, this is a clean forum install.

Let me reiterate: the primary key error immediately appeared after the import and has nothing to do with any query or other action I've taken. I suspect the problem is from an improper export, i.e. the backup itself is flawed and has the errors in it.

update:
I resolved those #1062 duplicate entries by searching the data for the duplicates and deleting them. For example this error:
Duplicate entry 'next_task_time' for key 'PRIMARY'
had a duplicate variable called 'next_task_time'.

Some of the duplicates looked like they were in there for a decade, perhaps from a botched upgrade a long time ago that went unnoticed. Some looked new, coming about during this fresh install.

I'm still manually going through each table and have about 30 left to do. I'll post again in a day or two if this resolves the issue.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 9,611
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #15 on: January 15, 2020, 11:28:09 AM »
I vaguely recall fixing a database (or maybe two) couple years back that had been dumped from a very old install, and the format was incompatible with current platform.  Different problem, different fix, but it required cleaning up table structure to get it working.  Good luck.

Offline societyofrobots

  • Jr. Member
  • **
  • Posts: 210
Re: Duplicate entry '0' for key 'PRIMARY' error during db move to new host
« Reply #16 on: January 19, 2020, 05:58:23 PM »
Update: I *think* it's fixed now.

Basically, the backup copy didn't include the primary keys and was missing indexes and a_i. So when I ran the forum, I'm guessing it started inserting the missing keys by itself, causing duplicates?

Not sure. But manually going table by table and naming primary keys, adding auto_increment, and searching for and removing duplicate keys got rid of all those errors.

I still got other errors to deal with but I think they're unrelated to the db.