Database crash and then some odd issues

[TheEnemy42]

Hi there,

We've had some odd behaviour recently and while I've tried to search through this support forum I can't really seem to find anything that covers it. So I'm hoping some of you can help me in the right direction
(sorry about the wall of text - I just wanted to include all the details of what I've done so far - scroll to the bottom if you only want to hear about the latest issue)

First some info:
We're running SMF version 1.1.11, have 400,000+ posts with some 300 new each day. PHP version is 5.3.3 and MySQL version is 5.0.51, running on a shared host. We've had the forum from back when it named YaBBSE.
Let me know if you need more.


Last week we had a database crash. The users got an error message about the table log_online. Using phpMyAdmin I realised that also the messages table had errors and I used phpMyAdmin to repair both tables.  I got this from repairing the messages table from phpMyAdmin which I don't really know what to make of:

Table	Op	Msg_type	Msg_text
messages	repair	info	Wrong bytesec: 115- 32-102 at 233197452; Skipped
messages	repair	warning	Number of rows changed from 422705 to 422703
messages	repair	status	OK

The forum seemed ok after this but shortly after more tables began to have errors. After reading for a bit of suggestions I used phpMyAdmin to run analyze on all tables and it was not all good. Some was "OK", some was "Table is already up to date", 3 was corrupt and a few had a wrong number of keys.

I ran the repair on all tables and everything was good except for this one error message (which I didn't think was important):

Table	Op	Msg_type	Msg_text
log_search_results	repair	warning	Number of rows changed from 44001 to 43866

We've read somewhere that it could help by clearing out data from log_errors, so we did. Then I used phpMyAdmin to run optimize on all tables. All green. At this point I was unsure about what to do next as everything seemed fine so I deactivated maintenance mode again.

During the next day new problems came. One user reported a missing topic. Another user had posted to a topic but you couldn't see it except when you replied to the topic then it would be the latest of the existing posts. I realised that the topics table hadn't been updated with numReplies so I changed that manually. I then remembered the maintenance functions on the forum so I went through those.

The "Find and repair any errors." found 30 or so errors and could repair them all. The missing topic resurfaced but without the first post in it (I found an old version in a backup). Elsewhere a few posts was merged into topics they didn't belong and I think the posts were actually a copy of existing ones. Using the error list I managed to go through all of them and clean up. I deactivated maintenance mode again and it seems like it's been running ok for the last few days.


Today a user contacted me and said that he and another user had posted to a topic but their posts ended up in a different topic (the same topic for both users and both of them had earlier posted to this one but didn't do it now). This last one really puzzles me and I've got no clue as to what is going on. I also still don't know what caused the crash in the first place.

Any help and suggestions are very much appreciated

[xenovanis]

This seems to be more a MySQL/hosting issue than a SMF thing though. I'm a bit surprised that with this amount of posts and activity, you're still running on a shared server.

Are there any core files in your webroot? Your host might be able to filter from those what the actual problem is.

[TheEnemy42]

Thank you for replying.

Most of the time the forum runs ok but we do get the "Sorry, SMF was unable to connect to the database" problem every now and then. We (the admins) have talked a bit about moving to a different host but we weren't sure what to go for. Maybe we should start investigating further... any suggestions are most welcome

I can't see any core files but maybe we should try contact the host and see if they can find anything, they must have some kind of logs.

[xenovanis]

Talk to your host first. If you're happy with your host they might be able to offer you a better solution. I'd say with this activity you're almost ready for a dedicated server.

[TheEnemy42]

Yes, I'll try and contact them today. This morning a new issue arised. This error:

"Session verification failed. Please try logging out and in again, and try again"

popped up whenever I tried to post or login into the admin panel. Clearing cookies and the log_online didn't change anything. The sessions table was already empty. However just a short while ago it seemed to work again. This makes me think even more it's an issue with the host. Plus, in the error log I've found these:

2: Unknown: Failed to write session data (files). Please verify that the current setting of session.save [nofollow]_path is correct (/.../tmp)

2: Unknown: open(/.../tmp/sess_cdc0035ddacbbf872a1640416d5a1350, O_RDWR) failed: Read-only file system (30)

2: session_start() [<a href='function.session-start'>function.session-start</a>]: open(/.../tmp/sess_cdc0035ddacbbf872a1640416d5a1350, O_RDWR) failed: Read-only file system (30)
/.../httpd.www/forum/Sources/Load.php

So... is it using files for sessions now?

[xenovanis]

Well, files is a relative definition. In essence, your tables are files as well, stored on the server. However, this error seems to imply there's something wrong with the session save path and/or your temp directory.

Definitely contact your host about this. Something is very wrong at their end.

[TheEnemy42]

Hehe, I guess you're right I know that our host uses one server for the database and another for "normal files" and I thought maybe it had changed from database driven sessions to file based. Just now however I'm beginning to have my doubts about how it was previously. Which is the better thing?

I did contact the host and they could tell from the logs that their database server had crashed on the day our tables crashed so I'm guessing that's pretty much the cause. The tables didn't like the server crashing so they went haywire as well. The issue today was the host moving our domain from one server to another which meant that PHP didn't have write access for the three hours the transfer lasted. I would guess one should get informed about such a thing but I guess not...

According to the host everything should be ok now but I can't help but wonder what about the error report I received from a user about this post being posted in a different topic?


Oh, and thank you very much for your help so far

[xenovanis]

Use database driven sessions.  It's faster.

I'm not sure about the mixup of the topics. This probably is due to your MySQL crashing on the same time. Just keep an eye on it and let us know if it happens again.

[TheEnemy42]

The odd thing is that the topic mixup happened after I had repaired the database. For now I think I'll just call it a lone "freak" and pray it won't happen again but still keep an eye on things.

I'll try and change to database driven sessions shortly.

Thanks again for your help.

[JimM]

If this is solved, please mark it solved by clicking the Mark Topic Solved link at the bottom left.

[RobertMfromLI]

Tip: Add this to your my.cnf


myisam_recover=BACKUP,FORCE

I have yet to find a way to prevent MySQL from crashing on certain tables (and this seems to be a problem affecting various *nix builds, as well as the OS/2 port (from the Linux source), so I too suspect it's something in MySQL (running the latest version). But with the above statement, your server *should* recover from all but the most catastrophic problems, and in the event of something catastrophic, you'll have a backup that you can try to repair manually with the server offline.I'm also forcing a flush after every transaction. I have not noticed any appreciable performance change in doing so (via monitoring pageload time). To do that, if you wish, simply add "flush" (no quotes) to my.cnf