Mysql – How to prevent ‘table is mark as and last (automatic) repair failed’

MySQL

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?

Best Answer

"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:

Related Solutions

Mysql – Replication and REPAIR TABLE

I think this advice is more a safety suggestion than a requirement. Further, if a table is corrupted/damaged such than it needs repair ran on it that means something that wasn't supposed to happen happened. The slave very well may be okay and not need to go through the expensive table rebuild.

While it is true a slave will execute anything written to the binlogs after it was stopped once restarted, you can simply prevent certain statements from even being written to the binlog in the first place.

If you run

set sql_log_bin=0;

then anything you execute in that session thereafter will not be written to the binlog; will never be executed on the slave.

Edit

After repairing a table on the master you'll want to checksum it. If you can afford to have your master table offline for a period beyond the repair you can just run

checksum table ;

If you need to get back in action quickly you should look to using pt-table-checksum from the Percona Toolkit. This might also be something to consider over a straight checksum even if the table isn't being actively used.

Here's why. pt-table-checksum will check your table in chunks. You can read the details on exactly how it operates in the docs. In short it will check parts of the table, for example, IDS 1-1000, 1001-2000, etc. This is meant to server as an anti-lock braking system if you will for live tables. A portion is checksumed then the lock on the table or rows being checked is released, allowing other live traffic waiting to make some progress in between chunks.

Further, even if extended locking is not a concern it gives you insight to what parts of the tables differ. Maybe it turns out you only need to synch up a small segment of rows instead of copying for gigabytes of data.

The percona toolkit also includes pt-table-sync to resolve such conflicts.

Mysql – How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

Ouch you have innodb_file_per_table off

I wrote articles on this before. You will have to convert the InnoDB infrastructure.

Oct 29, 2010 : My Original Post in StackOverflow
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

Once you do this, anytime you do OPTIMIZE TABLE to an InnoDB, it will actually shrink.

If you do not perform the InnoDB infrastructure cleanup, the ibdata1 file will actually grow in size. To make matter worse, I think mysqltuner.pl is using a weird algorithm for determining InnoDB fragmentation. I tend to ignore it.

I check for fragmentation differently.

Back on April 19, 2012 I answered a question similar to this: How do you remove fragmentation from InnoDB tables?.

Here is what I said in that earlier post:

After running the above steps, how can you determine what tables need to be defragmentated? It is possible to find out, but you will have script it.

Here is an example: Suppose you have the table mydb.mytable. With innodb_file_per_table enabled, you have the file /var/lib/mysql/mydb/mytable.ibd

You will have to retrieve two numbers

FILESIZE FROM OS : You can ascertain the filesize from the OS like this
ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'
FILESIZE FROM INFORMATION_SCHEMA : You can ascertain the filesize from information_schema.tables like this:
SELECT (data_length+index_length) tblsize FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';
Just subtract INFORMATION_SCHEMA value from OS value and divide the difference by the INFORMATION_SCHEMA value.

From there you would decide what percentage deems it necessary to defrag that table. In other words, if there is a big difference between the filesize of the .ibd file and the number from INFORMATION_SCHEMA.TABLES, go ahead with OPTIMIZE TABLE. Regardless of the filesize, please don't worry if the fragmentation is less than 1%.