Database inconsistencies

[gkawa]

Hi there!

I found a couple of inconsistencies in our forum database that the maintenance scripts couldn't detect or fix.

One of them could have been a consequence of a migration procedure from PHPBB. It was many years ago and I have no idea how it was done, but the dates seem to point that way. The issue, in this case, is that the message indicated as the first message for a topic doesn't exist. So, the forum doesn't show the topic at all and if it's invoked by putting the topic number in the URL it shows a message saying the same. Other than that, all the elements seem to be consistent, the topic record is complete, the messages are there and pointing to the right topic. I'm fixing them by hand, they're just a few. But I think this is a check that has to be included in the maintenance script, probably with a check of the last message pointer too.

The other issue is more recent and I'm sure it was caused while the forum was on 2.0.17 or 2.0.16. Many messages showed up with id_topic = 0. The odd thing is that they all seem to be duplicates of existing messages, and all from the same user. Three messages, between 6 and 10 occurrences of each one. Exactly the same content, consecutive message numbers, about 10 seconds apart. It's a fluke for sure, happened only once in 10 years, only for one user. Maybe I can reproduce something like that with a slow connection and clicking the post button many times. However, it doesn't explain why the id_topic was zero. I don't think it's worth checking on this one, but I'm letting you know just in case someone else finds this same issue in the future.

That's all. Thank you all for the work you put on SMF.

[Oldiesmann]

If you go to Admin -> Maintenance -> Forum Maintenance and click "Run Task" under "Find and repair any errors", does that fix the first issue at all? I haven't looked at the code for that in a while but I think topics with an incorrect first message ID is something it attempts to fix. It is something that can be fixed, but a lot would depend on the size of your forum as well since the fix involves finding all topics with that problem then looping through the list and updating each one individually.

[gkawa]

No, the Find and repair routine doesn't detect wrong first messages. At least it didn't work on mine. The forum is not big, it's about half a million messages. I found the error because of my own sitemap script, which lists the URLs of all pages of all topics. Without the first message pointer, the URL ?topic:XXXX.0 goes nowhere.
I checked with

select id_topic, id_first_msg from smf_topics where id_first_msg not in (select id_msg from smf_messages)

I'm writing by memory, sorry if I mistype a field name. I don't have a lot of experience with SMF. Anyway, with that, I found the offending topics and they matched my list of failing URLs. I rechecked by hand, one by one. They weren't so many, so it wasn't a big deal, and I found out that those messages were gone. Based on the titles with Re:, I assumed that the original post was missing on each. And, as I said, the dates seem to match a previous migration from PHPBB. But that's based on comments from users, I wasn't there at the time.

I was pretty sure that some last message pointers were wrong too because the problem seems to be a whole day or more of messages that went South. It's ancient history anyway, I kept working on it so you can have more information about the problem. I fixed the first message pointers manually and carefully. With that, the sitemap script got fixed.

I emptied the cache, just to be on the safe side, I have no idea if it was necessary, and ran the Find and repair routine again. This time, it detected all the last message pointer errors. I took note, did the same manual check but for id_last_msg, and the lists matched. I answered yes and the routine reported the base fixed. It didn't. I checked again with the routine and manually on the base, it doesn't fix the problem. I didn't have a lock on the database or interfered in any way. When I ran the repair routine I was disconnected from it.

So, I did the fix manually. This time, I checked the forum lists, the positions where those topics should have been, and they were not there. I did that to have a reference to verify that my fix was working. As I fixed the last message pointers, the topics showed up. After all the pointers were fixed, the repair routine gave me a .?? error. I did the stupid thing and told it to repair it. But it looks like it did, whatever it was. Now it's not reporting any error and all pointers are fixed.

In conclusion, it looks like the first message pointer (FMP) errors are not detected by the routine. And somehow they prevent the routine from detecting last message pointer (LMP) errors. Without FMPs, the routine detects LMPs correctly, but it seems to be unable to fix them.

I hope it makes sense to you. If you have any questions or want me to do some tests, just let me know.

Thanks