Duplicate entry for key 2

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Scam Warning
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
CentaurAtlas
Registered User
Posts: 50
Joined: Sun Dec 02, 2007 5:39 pm
Contact:

Duplicate entry for key 2

Post by CentaurAtlas »

Support Request Template
What version of phpBB are you using? phpBB 3.0.9
What is your board's URL? http://www.halloween.com/forums/
Who do you host your board with? Rackspace cloud sites
How did you install your board? I used the download package from phpBB.com
Is your board a fresh install or a conversion? Update from a previous version of phpBB3
Do you have any MODs installed? No
Is registration required to reproduce this issue? No
What version of phpBB3 did you update from? phpBB 3.0.8
What styles do you currently have installed? Black pearl
What language(s) is your board currently using? English
Which database type/version are you using? MySQL 5
What is your level of experience? Experienced with PHP and phpBB
When did your problem begin? 5 days ago
Please describe your problem. SQL queries are generating a duplicate key error on posts. For example:
INSERT INTO phpbb_search_wordlist( word_text, word_count ) VALUES ( 'antiquities', 0), ('lame', 0), ('concise', 0)

Gives a duplicate key error. Using the command directly to the DB gives this message:
MySQL said:
#1062 - Duplicate entry 'antiquities' for key 2
Generated by SRT Generator ($Rev$)

It has been running on PHPBB for 4+ years and only am seeing the error in the last week.
Any suggestions on fixing it? Posts and things seem to work, but database replication isn't according to the host.

Thank you for the time.
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52114
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Duplicate entry for key 2

Post by Brf »

CentaurAtlas wrote:
Who do you host your board with? Rackspace cloud sites



database replication isn't according to the host.
So your host is having problems with database replication... That is what is causing your errors -- corrupt database tables.
CentaurAtlas
Registered User
Posts: 50
Joined: Sun Dec 02, 2007 5:39 pm
Contact:

Re: Duplicate entry for key 2

Post by CentaurAtlas »

What they said was that that error was causing problems with them for database replication. I did ask and they said that the error was causing them problems replicating, not that replicating was causing the insert error. I don't know if that makes sense or not.

So assuming it is not an error in the host database, any suggestions on fixing the issue? Things to try?

Thanks for the reply.
CentaurAtlas
Registered User
Posts: 50
Joined: Sun Dec 02, 2007 5:39 pm
Contact:

Re: Duplicate entry for key 2

Post by CentaurAtlas »

This is what they have said:
"We've noticed that some queries for the database xxx_hallowee_phpb3 under the cloud site http://www.halloween.com are causing issues with mysql data replication.

Error 'Duplicate entry 'lame' for key 2' on query. Default database: 'xxx_hallowee_phpb3'. Query: 'INSERT INTO phpbb_search_wordlist (word_text, word_count) VALUES ('antiquities', 0), ('lame', 0), ('concise', 0)'

While you address this error we will skip this table's replication. We recommend correcting this ASAP so that we can restore replication for this table, as without proper replication this table may cause issues in the event of a failover.

The query being made by your application is trying to add a row where a value already exists. The phpbb_words table in this database uses
word_id as a primary key and the insert query made by your application has tried to create a new record where there was a conflict with an existing record. You may need to work with your developer to identify why exactly phpbb3 is creating these queries.

"
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52114
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Duplicate entry for key 2

Post by Brf »

Try doing a repair on that table.
CentaurAtlas
Registered User
Posts: 50
Joined: Sun Dec 02, 2007 5:39 pm
Contact:

Re: Duplicate entry for key 2

Post by CentaurAtlas »

Hi,
I appreciate the suggestion and did a repair, but it was OK. I actually repaired them all and it showed no problems.

I tried switching search settings too to see if that changes it.

[edit: If I figure anything definitive out, I'll follow-up here.]

Thanks again.
Brf wrote:Try doing a repair on that table.
Last edited by CentaurAtlas on Wed Oct 05, 2011 7:35 pm, edited 1 time in total.
User avatar
canonknipser
Registered User
Posts: 2096
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

Re: Duplicate entry for key 2

Post by canonknipser »

It sounds like an error with autoincrement on master-master-replication in mysql: http://tracker.phpbb.com/browse/PHPBB3-7215
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52114
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Duplicate entry for key 2

Post by Brf »

That has nothing at all to do with your problem.
Your problem is that the word_text is not able to be read, so another record is being inserted for the same word.
The bug you are referencing concerns foreign keys being incorrect during the initial board install. It should have no effect at all in your case, since the search tables are not part of the initial install.
StefanL
Registered User
Posts: 2265
Joined: Wed Jul 23, 2003 10:10 pm

Re: Duplicate entry for key 2

Post by StefanL »

SQL Error : 1062 Duplicate entry '-1' for key 2

The problem is that key 2 is not supposed to be a "UNIQUE" key, and somehow, MySQL decided it should be. If you view the table properties in phpmyadmin, it will tell you what MySQL lists for indexs. #2 should be just an "INDEX", not "UNIQUE". You can edit it, if it is, and the problem will go away "forever".
When I suggest that you should alter your files, BACKUP them BEFORE.
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52114
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Duplicate entry for key 2

Post by Brf »

Please start your own topic.
Key-2 is supposed to be unique for the wordlist table.
Locked

Return to “[3.0.x] Support Forum”