1

This is the second time within the last 4 days that one of the tables in my database got corrupted. The is the error that I have seen in my apache log:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 144 Table [TABLE_NAME] is marked as crashed and last (automatic?) repair failed'

I managed to repair it manually using:

myisamchk -r -f $TABLE_NAME

but of course this is not a long term solution.

I need to understand why is this happening, so I can prevent it from happening again in the future.

  • Total database size is 2Gb, 100 tables, 7.5 million rows.
  • The table where it happened is the largest in the database: 1.2 million rows, 650Mb.
  • Server version: 5.5.50
  • ubuntu 14.04.1
  • PHP 5.5.9
  • I checked and there was enough free disk space. About ram, it was not an issue so far.
  • The host where the db reside is c3.xlarge (8Gb ram)

The database sits in the server harddisk. I am thinking about moving it to RDS, I wonder if that would help.

What do you suggest I should do? Any way to analyze mysql and understand where is the problem?

UPDATE

/var/log/mysql/error.log has this in it:

200213 14:57:21 [Warning] /usr/sbin/mysqld: Forcing close of thread 200315105  user: '[user1]'
200213 14:57:47 [Warning] Warning: Optimize table got errno 28 on [TABLENAME], retrying
200213 14:57:47  InnoDB: Starting shutdown...
200213 14:57:49  InnoDB: Shutdown completed; log sequence number 1983956605556

UPDATE - It is out of memory

found this in my syslog

Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744017.270767] Out of memory: Kill process 14232 (mysqld) score 32 or sacrifice child
Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744017.285148] Killed process 14232 (mysqld) total-vm:2447868kB, anon-rss:241432kB, file-rss:0kB
Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744020.934420] init: mysql main process (14232) killed by KILL signal

Like I said before, my server is c3.xlarge with 8Gb RAM. Normal utilization is pretty low. I think there was a backup process that was running, maybe it could have caused higher resource utilization.

Any suggestions how to check further?

Improve this question
6
  • I managed to repair it manually using: Are you sure that the problematic table's engine is MyISAM?
    – Akina
    Feb 16, 2020 at 14:36
  • I am checking the database in phpMyAdmin. Some of the tables are InnoDB, some are MyISAM. The specific table with the error is MyISAM.
    – justadev
    Feb 16, 2020 at 14:41
  • This is likely caused by an abnormal termination of the MySQL daemon. Review the MySQL error log and the server syslog to identify why it happened.
    – mustaccio
    Feb 16, 2020 at 17:25
  • I checked the mysql log. something about error 28 (updated in the question). From what I read it is disk space, which is weird because I have at least 2.5Gb free
    – justadev
    Feb 16, 2020 at 18:13
  • 1
    MyISAM tables are easily get corrupted. Migrate to InnoDB.
    – akuzminsky
    Feb 16, 2020 at 18:23

1 Answer 1

Reset to default
1

"marked as crashed" -- Longterm solution: Switch from MyISAM to InnoDB.

"errno 28" -- Check the setting for tmpdir and consider changing it to a bigger dis partition.

"Out of memory" -- What is running? Is this server hosting MySQL only? Or does it have other things, such as a web server and PHP? If hosting multiple apps, let's see my.cnf so we can advise on what settings to shrink.

References:

Improve this answer
1
  • The server was running other php files, some of them were not quitting cleanly, consuming ram. Took a while to find, thanks.
    – justadev
    Feb 23, 2020 at 11:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.