I wasn't 100% sure, but I'm guessing its a server config topic, so I'm posting it here...
Situation:
I have a Xen HVM VPS running 3 sites, all forum scripts. One site runs SMF and is pretty big, the other two are somewhat smaller and run vB 3. OS is Centos, ram is 2GB, server setup is nginx+php-fpm+apc.
What happened:
My SMF dB has a couple InnoDB tables, one of them being smf_members. Somehow it started crashing and taking down MySQL with it. This first happened during an automated backup process (mentioned below), mysqld would restart whenever smf_members got touched, which in turn resulted in all 3 sites losing mysql connections.
What I did:
Being wise and all :P I have a daily backup routine which sends the entire gzipped sql to Dropbox, for all three sites, every morning. I dropped the members table and loaded a recent backup (previous day). Problem solved.
However because the dB backups are done in sequence, and the SMF backup is done first, the backup process of the day the crash happened was interrupted by, once again, mysql connection drops and none of the other backups completed successfully.
So questions?
1. should I be worried about some sort of attack via member data? I have a few mods, tapatalk being one which I hate because it breaks my registration system (where I handcoded some hacks a while ago).
2. In order to make sure these sort of crashes only effect one site rather than all, is the only option I have running multiple instances of mysql?
3. Should I just convert smf_members back to myisam as then I'd have checking and repairing routines? (which are done before each backup)
btw I'm not an expert on hosting or anything, these sites are just ones I like and am active in, I just have some previous linux knowledge so I offered to host them for no profit...
Have you looked at the mySQL logs to see why it's crashing?
How big is the members table? What is your buffer pool size? What is the max transaction size?
Well mysql logs say:
QuoteInnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 8479.
InnoDB: You may have to recover from a backup.
140309 10:13:12 InnoDB: Page dump in ascii and hex (16384 bytes):
members table is currently 13MB with around 21 thousand members,
buffer pool size = 128MB
max transaction also 128MB, but it was 64 before the crashes.
4LP3RUZ1,
You stated that you make frequent backups (automated or otherwise), so do you have a backup previous to the initial issues? One that can be applied now since the settings were recently increased? I understand that due to the possible time that has passed since the initial onset of issues, some data will be lost but at least it will possibly be stable. phpmyadmin also has some features/commands to repair a database due to corruption, have you tried using it?
Regards.
Quote from: 4LP3RUZ1
1. should I be worried about some sort of attack via member data? I have a few mods, tapatalk being one which I hate because it breaks my registration system (where I handcoded some hacks a while ago).
Btw this sounds related to your issues since registration obviously deals with the members table. Perhaps you or Taptalk somehow corrupted the members table.
AFAIK, InnoDB tables don't get repaired or checked like MyISAM tables. I already fixed the crash by reverting, I just don't want something similar to happen again :)
As for the registration system,
its basically using custom profile fields to get a users name and surname, then what I do is create the display name with "name+surname+(member id)" I have been using it for years like that. Tapatalk however is not compatible with custom profile fields so people registering from there get an empty display name :( :(
Actually, you *can* repair InnoDB. It's just a whole different process, you just move up one level at a time. (1 -> 6)
http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html
On a table that's only 21MB and those settings, I am assuming it ain't telling lies: there is corruption, somewhere.
I had another crash this morning, starting once again while the upgrade script (mysqldumper) was running.
This time it crashed while trying the optimize log_boards which is another InnoDB board. :(
This in turn corrupted another table for one of my vB sites, which could be repaired. But my log was filled with "autorepair failed" lines :(
If it keeps crashing, it calls for further investigation on the server-side to be honest... Needless to say: it's abnormal.
I don't cut access to the forum while taking a backup, bad? not so bad?
edit: I'm now putting SMF on maintenance mode 2 while taking a backup, lets see how this goes...
edit2: I'm also switching the vB boards off too now and things seem to have calmed down, marking as solved.
do what now? :o
Could you be more specific? I thought you said the issue was resolved.
I'm thinking there was a post that was deleted here :)
Yes, safe moding all the forums before taking backups worked. Havent had a crash since (like 3 months now?)