Database Error: Duplicate entry '0-17' for key 'lastMessage'

[cortez]

Code Select Expand


http://xxxxxxxxxxxxx/~xxx/forum/index.php?action=splittopics;sa=execute;topic=577.0

Database Error: Duplicate entry '0-17' for key 'lastMessage'


INSERT INTO xxxxxx_SMFtopics(`id_board`, `id_member_started`, `id_member_updated`, `id_first_msg`, `id_last_msg`, `num_replies`, `unapproved_posts`, `approved`, `is_sticky`)
VALUES
(17, 29919, 29919, 0, 0, 0, 0, 1, 0)

File: /home3/xxxxx/public_html/forum/Sources/SplitTopics.php
Line: 676

- everything is ok both in cpanel and smf, all checks => ok, nothing to repair
- occurs only when trying to split some posts, not everywhere
- changed hoster to bluehost, still occurs
- no mods

I've been having problem with this for months and nobody is being able to help me with this. 

[kat]

For reference, this is a bump of this:

http://www.simplemachines.org/community/index.php?topic=504977.msg3555053#msg3555053

[cortez]

Whoops... I was busy with that other error that I've forgot that I had that thread, too. Sorry for doublepost.

With previous hoster I had so many server changes (especially ones representative didn't mention and forum wasn't in maintenance mode) and tons of server crashes.

I wonder if this will require some payed manual SQL labor. However, I've sent ticket to sql support at bluehost and see if they know.

[cortez]

Bump, I'm getting real tired with this error. Not being able to properly moderate makes my moderators a little bit nervous.

It seems like primary key violation or auto increment fail but I don't know how to repair.

Will subscribing to charter membership provide better help? I'm asking since I cannot get an answer or at least possible solution for more than 2 months...

[kat]

This is not my forté, by any stretch of the imagination...

That having been said, this is what I'd try, if I was getting this error. Naturally, I'd backup the database (Twice, at least), before I even thought about doing it, in case I'm WAAAAY wrong (Which is entirely likely, I'm afraid).

I'd go to phpmyadmin and delete one of those duplicates. If everything is, then, OK, I'd say "WooHoo!" and throw a small party.

If that made something go screwy, I'd restore the backup and try deleting the other duplicate, instead.

Again, if that sorted it, I'd buy drinks all-round. But, if that, too, failed, I'd restore the backup, again, and come back here, a'wailing.

How does that sound?

[Dzonny]

Also, did you tried to use repair table function in phpmyadmin maybe? Just thinking, but that may help. Ofc, you should backup your database firstly as K@ said.
I assume that you're using latest version of SMF, so 2.0.5, right?

[cortez]

Nailed it.

There was issue with one topic, firstMsg and lastMsg were both set to '0' but it had messages. Deleted it manually and now it's ok.

So much about auto repair.  Bothers me that no-one could figure it out here...

Marking as solved.

[Arantor]

It's not that 'no-one could figure it out here'. It's that of the (few) people who would be able to spot that and be able to advise fixing it because they're the truly hardened coders, none of them happened to drop by this thread before you got it fixed.

I'm slightly concerned that a topic is listed has having first and last msg id of 0 and that the repair function didn't deal with it as it should.

[ziycon]

The repair function wouldn't fix this issue if the value was 'correct' in the sense the database was manually updated therefore the repair function would see it as ok, the repair function will only work if the integrity of the data is in question and doesnt corrispond with other files the database uses to validate/check/repair.

[Arantor]

That's the point, the integrity IS in question. There is no circumstance in SMF anywhere where the smf_topics table should ever obtain a value of 0 for any id.

Every major id in SMF is an autoincrement in its own primary key which starts at 1.

More importantly, the repair function SHOULD be fixing it. If the topic has messages in it, it should be fixing the ids to correspond to what it finds in the messages table. If the topic doesn't have messages in it, its orphan record should be deleted.

NB: We're not talking about a REPAIR TABLE command, we're talking about SMF"s find/repair system.

[ziycon]

My mistake, speading to much time with databases gives you a one track mind

To clarify for anyone unsure, I was talking about the REPAIR syntax in the database and not SMF's find/repair function.

[Arantor]

That's what I figured Yeah, the physical integrity is not compromised; the table is physically as it should be. However despite that, the data has become unsynchronised somewhere and the contextual integrity is compromised. I'm not entirely sure how that's possible (not even post deletion should trigger that) but it's evidently happened.

[cortez]

It's not that 'no-one could figure it out here'. It's that of the (few) people who would be able to spot that and be able to advise fixing it because they're the truly hardened coders, none of them happened to drop by this thread before you got it fixed.

I'm slightly concerned that a topic is listed has having first and last msg id of 0 and that the repair function didn't deal with it as it should.

Don't get me wrong, it's wasn't written with arrogance or whatever - it's was just a little bit of "sigh".

I do agree that SMF find/repair system should take care of that and that's where the problem was in first, not in repair table command.

For more debugging concerns:

I was getting messages that one topic had no replies and that SMF should be correcting that - but, here comes the interesting part: it only occured sometimes, maybe one in twenty instances of running "find and repair any errors". It would end in a success but, obviously, with no changes made.

Since it occured in find and repair again after a very long time I decided to manually take a look into database and topic split script and figured out that.