1

If you try to access a table in a database of type INNODB and MariaDB tells you it isn't here, you have a corrupted database.

(I am posting this question because all of the existing questions have been "closed" by various moderators and NONE of them have a real answer. They discuss the complexities of INNODB, and ultimately that you have to rebuild the entire database server through a torturous process.)

2

This may not work for everyone, but it did work for us on several occasions:

  1. Stop the database (service mysql stop)
  2. edit mysql.cnf so that under [mysqld] section it has the:

    [mysqld] 
    innodb_force_recovery = 1 
    
  3. Start the database (service mysql start) and in the logfile you will see it force loading the bad database/table

  4. log into the database server as root: mysql -u root

  5. run the command "show databases[ENTER]" to get a list of database

  6. run he command "drop [dbname][ENTER]" where [thedb] is the database you need to drop. It will drop it but will complain that it cannot delete the directory ./[thedb].

  7. Open a separate shell and go into /var/lib/mysql/[thedb]/ and remove the file(s) there.

  8. Stop the database (service mysql stop)

  9. Turn off the REPAIR mode by commenting out the line in mysql.cnf

    [mysqld]
    #innodb_force_recovery = 1 
    
  10. restart the database with the command:

    service mysql start[ENTER]
    

Voila.

Now recreate the database and restore from your backup.

Thanks, David

  • I'm a bit confused by step 7. Did you mean to say something about deleting a file or directory? – dbdemon Dec 6 '17 at 9:52
  • I just re-re-read your comment. I am sorry for the confusion. I have cleaned up the text so it reads better. – TekOps May 3 at 20:21
  • That looks better, thanks for clarifying! – dbdemon May 3 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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