Re: Corruptions like crazy
We'll leave the disk alone then assuming it is fine, rpms installed may be fine too. The tables could be getting further corrupted from poor management, so we'll address fixing in an alternate way. Then I'll guide you to upgrading your database as there are further underlying issues there.
1. It looks like you can still run select statements against your corrupted tables. Since you can, follow this next set of directions to convert yourself away from MyISAM and onto the modern Innodb format instead.
Here we will dump the application database and recreate in a new database the same tables, but as innodb (instead of myisam), then reload. If the new database looks good, drop the old myisam one. Here are the steps.
For these commands, type in the password after using enter to run the command, it will prompt you at that time. You will need to use the mysql 'root' user account for these steps.
a) Unload your database ddl separate from your data
# mysqldump -uroot -p --no-data --databases your-db-name > database-structure.ddl
# mysqldump -uroot -p --no-create-info --databases your-db-name > database-data.sql
b) Then, edit the file database-structure.ddl by changing your database name slightly from the original name. You'll find it in 2 places, on the CREATE DATABASE ....statement, and following that there will be a USE ... statement. Make sure you get both instances changed
c) Continue editing the same file and change all occurrences of Engine=MyISAM to Engine=InnoDB. Every CREATE TABLE statement will have one of these included in it. Upper/lower case for these changes shouldn't matter.
d) load your new database with the adjusted DDL. Then load your data
# mysql -uroot -p < database-structure.ddl
# mysql -uroot -p < database-data.ddl
e) compare the row counts for each table between your old myisam tables and your new innodb tables.
mysql> use my-new-database;
mysql> select count(*) from [table-name];
mysql> use my-old-database;
mysql> select count(*) from [table-name];
f) if you are confident the data is all present in the new database, then repoint your application to use the new databases name and test to see that it works.
g) if your application works, then drop the old database. This will help clear up your database environment for the next step and will get rid of those corrupt tables. If previous steps did not complete successfully....then don't drop your database!
mysql> drop database my-old-database;
2. Also, as the 'root' linux user, upgrade mysql with the following command. It is important that you upgrade your database system.
# service mysql stop
# mysql_upgrade -uroot -p
# service mysql start
- if you've done the upgrade, please provide the output of your mysqld.log from the point when it starts up after the upgrade.
- If both of these things worked though, you should be good to go!
Good luck.
Andrew