Page 1 of 1

Duplicate entry for key 2

Posted: Wed Oct 05, 2011 1:37 pm
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.

Re: Duplicate entry for key 2

Posted: Wed Oct 05, 2011 1:40 pm
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.

Re: Duplicate entry for key 2

Posted: Wed Oct 05, 2011 5:36 pm
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.

Re: Duplicate entry for key 2

Posted: Wed Oct 05, 2011 5:41 pm
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.

"

Re: Duplicate entry for key 2

Posted: Wed Oct 05, 2011 6:03 pm
by Brf
Try doing a repair on that table.

Re: Duplicate entry for key 2

Posted: Wed Oct 05, 2011 6:39 pm
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.

Re: Duplicate entry for key 2

Posted: Wed Oct 05, 2011 6:51 pm
by canonknipser
It sounds like an error with autoincrement on master-master-replication in mysql: http://tracker.phpbb.com/browse/PHPBB3-7215

Re: Duplicate entry for key 2

Posted: Wed Oct 05, 2011 7:21 pm
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.

Re: Duplicate entry for key 2

Posted: Thu Oct 06, 2011 12:15 am
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".

Re: Duplicate entry for key 2

Posted: Thu Oct 06, 2011 12:45 am
by Brf
Please start your own topic.
Key-2 is supposed to be unique for the wordlist table.