-1

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?

Erel Segal-Halevi
  • 141
  • 6
  • My immediate thought is disk drive hardware. Have you tried moving your tables to a different drive? Have you looked for drive errors? – tsc_chazz May 29 '23 at 07:00
  • @tsc_chazz I am on shared hosting (DigitalOcean) so I do not have direct access to the drive. – Erel Segal-Halevi May 29 '23 at 07:12
  • 2
    **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 – HBruijn May 29 '23 at 07:14
  • @HBruijn I looked at the logs, and all I saw was a note on a table crash. – Erel Segal-Halevi May 30 '23 at 08:06

2 Answers2

2

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;

user1024991
  • 21
  • 1
1

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.

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • 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. – Nikita Kipriyanov May 30 '23 at 03:25
  • @NikitaKipriyanov - MyISAM's flaws + a crash are the cause; `REPAIR TABLE` is the quick cure. InnoDB is a better cure. – Rick James May 30 '23 at 03:40
  • 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. – Nikita Kipriyanov May 30 '23 at 03:43
  • @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. – Rick James May 30 '23 at 04:12
  • Tips on converting to InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb – Rick James May 30 '23 at 04:14
  • 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. – Francisco Jun 01 '23 at 02:26