MySQL databases screwed up by power failure

[ericovk]

I hit the bonus this week. Our server was full and had a power failure. Now some databases are somehow not functioning. I tried several things in the last 24 hours, but none of them with good results.

Some of the accounts appear to be ok, some of them not. Creating backups of all databases gives me several of these errors:

[COLOR=#000000][FONT=verdana]mysqldump error output: mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES[/FONT][/COLOR]

and

[COLOR=#000000][FONT=verdana]Error connecting to MySQL: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)[/FONT][/COLOR]

All .frm files seem to be available. But when I try to repair all tables with:

mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair --optimize --all-databases

the check goes well on different accounts, until I get this error:

ectrie_books.exp_online_users
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'OPTIMIZE TABLE ... '

the process stops immediately. Strange enough I also get this message after I deleted the entire account of this user. (I have a backup, so it's safe)

I don't know how to fix this? Do I have any options of repairing or rebuilding?

 

[smtalk]

You may try using force_innodb_recovery (http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html), also, if you'd like to restore user from a backup, make sure his database does not exist anymore in /var/lib/mysql after removal of the user.

 

[ericovk]

Thanks! What do you mean by "does not exist anymore"? Do you mean that the database folder with the .frm files?

 

[smtalk]

Yes, when you remove the user in DA, there should be no database folder left in /var/lib/mysql.

 

[ericovk]

After removing some databases that appeared to be empty (0kb in DA) and removing the folders, the mysqlcheck finally runs until it has checked all the databases. But I still get a lot of these errors on many different databases:

grnprid_dl.webform_last_downloadnote     : Table does not support optimize, doing recreate + analyze instead
error    : Error on rename of './greenpriold_dl/webform_last_download' to './greenpriold_dl/#sql2-4e6f-1' (errno: -1)
status   : Operation failed

and

harkl_mag.checkout_agreement_store
note     : Table does not support optimize, doing recreate + analyze instead
error    : Got error -1 from storage engine
status   : Operation failed

Do I have to increase the number of "innodb_force_recovery = 1" or isn't this going to solve the errors?
edit: apparently not :-/