Score:-1

How can I stop my MySQL server from going down?

id flag

I have a website based on a MySQL server on an Ubuntu machine. Every several weeks, one of my users complains that the site does not work, and I find out (using service mysql status) that it is because the MySQL server is down. I restart it (using service mysql restart) and the site starts working fine again. In /var/log/mysql/error.log, the only error messages I see are of the form [ERROR] [MY-013134] [Server] Table 'XXX' is marked as crashed and should be repaired. But, after I restart the server, I do not see any issue with the tables (I am not sure if this is the reason for the server going down).

These frequent failures are quite a nuisance both for me and for my users. How can I solve this problem?

tsc_chazz avatar
vn flag
My immediate thought is disk drive hardware. Have you tried moving your tables to a different drive? Have you looked for drive errors?
Erel Segal-Halevi avatar
id flag
@tsc_chazz I am on shared hosting (DigitalOcean) so I do not have direct access to the drive.
HBruijn avatar
in flag
**1)** install & configure proper monitoring and alerting so that you can respond to issues before your users start complaining. **2)** When a service fails : do a proper root cause analysis what circumstances caused that failure because many things are possible : running out of resources (disk, memory, or something else) , failure (of hardware, network connectivity), caused by badly constructed queries, spikes in visitors, increased data sets etc etc —- At the moment you don’t show that you did much looking for that cause, so we can only guess. Check your logs, system and MySQL logs first
Erel Segal-Halevi avatar
id flag
@HBruijn I looked at the logs, and all I saw was a note on a table crash.
Score:2
es flag

If you running out of disk space or a similar unexpected event, some of the database tables may have become corrupt.

To solve this you need to run this from MYSQL.

REPAIR TABLE db_name.table_name;

Score:1
ua flag

It sounds like the table is declared ENGINE=MyISAM. This old engine has the problem that abrupt shutdowns cause that error message. As already noted REPAIR TABLE (for each table involved) is the manual fix.

The long-term solution is to switch to ENGINE=InnoDB. That engine avoids that problem and self-repairs most other problems.

The engine change will eventually be mandatory, as MyISAM is being phased out.

Nikita Kipriyanov avatar
za flag
This is not a bad advice, but it absolutely doesn't address the depicted problem, nor the real cause. Also it would be good to see an instructions *how* to do that.
ua flag
@NikitaKipriyanov - MyISAM's flaws + a crash are the cause; `REPAIR TABLE` is the quick cure. InnoDB is a better cure.
Nikita Kipriyanov avatar
za flag
You can't change engine without fixing table first. Also, "flaws", well, not, bad design maybe, but it served us well for 25+ years, there could be no deadly "flaws" that cause random table breaks, so it couldn't be the "cause". Crash yes, and I am sure it is broken once and wasn't repaired and now it crashes each time it tries to access the table; simple "repair" will fix it and it won't crash anymore. So it's not a "cure" but "stronger prevention from happening again". REPAIR is mandatory in this case, InnoDB is optional.
ua flag
@NikitaKipriyanov - Here are the steps to reproduce it: 1) open a MyISAM table; 2) kill mysqld or reboot the server; 3) restart mysqld. It is OK to call it a bug in MyISAM, and point out that it has been there for 25+ years.
ua flag
Tips on converting to InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb
Francisco avatar
ca flag
I had the same problem on a MyISAM table due to power failures. The solution, I changed to InnoDB and replacement of the backup sources.
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.