mysql error 145, 'Table is marked as crashed and

chris (2005-07-26 21:44:17)
14297 views
3 replies
This is just a quick note for those stuck with mysql error 145. Usually in these circumstances, MySQL will start just fine, the mysql> prompt will work, but attempts to read a particular table fail with error 145. This is usually a sign of a corrupted table. One common cause of this is the disk hitting 100%, so use df and du -sk / to try and figure out where your clogs are.

Once you're happy that the disk is okay, return to the mysql> prompt and run the following: check table table_name; where table_name is the name of your corrupted table. This may or may not fix the problem. This is basically the equivalent to running myisamchk, but without the range of options available with the full application.

Once again, you might find that you are still unable to access your table.MYI (that's the file which actually stores the information for the table). Your last resort is to exit mysql and run myisamchk from your shell prompt, passing in the 'recover' option. This can fix just about any MyISAM corruption and is done as follows:

/usr/bin/myisamchk -r /var/lib/mysql/database_name/table_name.MYI

Note that you may need to change those paths to point to your copy of myisamchk and your data directory. use 'find' and 'locate' to determin where they are on your system.

myisamchk -r is the most useful of all tools to the MySQL DBA in disaster recovery situations and has saved my skin on numerous occasions in the past..

christo
comment
RAJA
2009-02-07 10:35:08

excellent

This is just a quick note for those stuck with mysql error 145. Usually in these circumstances, MySQL will start just fine, the mysql> prompt will work, but attempts to read a particular table fail with error 145. This is usually a sign of a corrupted table. One common cause of this is the disk hitting 100%, so use df and du -sk / to try and figure out where your clogs are.

Once you're happy that the disk is okay, return to the mysql> prompt and run the following: check table table_name; where table_name is the name of your corrupted table. This may or may not fix the problem. This is basically the equivalent to running myisamchk, but without the range of options available with the full application.

Once again, you might find that you are still unable to access your table.MYI (that's the file which actually stores the information for the table). Your last resort is to exit mysql and run myisamchk from your shell prompt, passing in the 'recover' option. This can fix just about any MyISAM corruption and is done as follows:

/usr/bin/myisamchk -r /var/lib/mysql/database_name/table_name.MYI

Note that you may need to change those paths to point to your copy of myisamchk and your data directory. use 'find' and 'locate' to determin where they are on your system.

myisamchk -r is the most useful of all tools to the MySQL DBA in disaster recovery situations and has saved my skin on numerous occasions in the past..

christo
reply iconedit reply
Richard
2009-06-29 10:09:44

yes, agreed, excellent!

This is just a quick note for those stuck with mysql error 145. Usually in these circumstances, MySQL will start just fine, the mysql> prompt will work, but attempts to read a particular table fail with error 145. This is usually a sign of a corrupted table. One common cause of this is the disk hitting 100%, so use df and du -sk / to try and figure out where your clogs are.

Once you're happy that the disk is okay, return to the mysql> prompt and run the following: check table table_name; where table_name is the name of your corrupted table. This may or may not fix the problem. This is basically the equivalent to running myisamchk, but without the range of options available with the full application.

Once again, you might find that you are still unable to access your table.MYI (that's the file which actually stores the information for the table). Your last resort is to exit mysql and run myisamchk from your shell prompt, passing in the 'recover' option. This can fix just about any MyISAM corruption and is done as follows:

/usr/bin/myisamchk -r /var/lib/mysql/database_name/table_name.MYI

Note that you may need to change those paths to point to your copy of myisamchk and your data directory. use 'find' and 'locate' to determin where they are on your system.

myisamchk -r is the most useful of all tools to the MySQL DBA in disaster recovery situations and has saved my skin on numerous occasions in the past..

christo
reply iconedit reply
Shripad
2009-07-18 09:55:37

Very nice solution

This is just a quick note for those stuck with mysql error 145. Usually in these circumstances, MySQL will start just fine, the mysql> prompt will work, but attempts to read a particular table fail with error 145. This is usually a sign of a corrupted table. One common cause of this is the disk hitting 100%, so use df and du -sk / to try and figure out where your clogs are.

Once you're happy that the disk is okay, return to the mysql> prompt and run the following: check table table_name; where table_name is the name of your corrupted table. This may or may not fix the problem. This is basically the equivalent to running myisamchk, but without the range of options available with the full application.

Once again, you might find that you are still unable to access your table.MYI (that's the file which actually stores the information for the table). Your last resort is to exit mysql and run myisamchk from your shell prompt, passing in the 'recover' option. This can fix just about any MyISAM corruption and is done as follows:

/usr/bin/myisamchk -r /var/lib/mysql/database_name/table_name.MYI

Note that you may need to change those paths to point to your copy of myisamchk and your data directory. use 'find' and 'locate' to determin where they are on your system.

myisamchk -r is the most useful of all tools to the MySQL DBA in disaster recovery situations and has saved my skin on numerous occasions in the past..

christo
reply iconedit reply