1

I needed to reinstall the WAMP server on Windows and in the process it reinstalled MYSQL as well. Now when I login to phpmyadmin, I can see my databases with the tables listed on the left side navigation panel. But when I click on the individual tables, it throws an error saying that the table doesn't exist. Browsing to this location C:\wamp\bin\mysql\mysql5.6.12\data, I see the folders with the database names. I also see files ending with *.ibd. The filenames are the same as the table names. With different databases, the folder contents are different. Some have files ending with *.ibd only. Some have a mix of *.ibd and *.frm. There is also a *.opt file in some folders. I do not know much about the admin side of MySQL. I only know enough to use PHP to interact with it for simple SQL statements. I have gone through some posts on how to possibly recovering the databases. But most of what was discussed there doesn't seem to either directly relate to my specific issue or are beyond my understanding.

Is there a simple enough way to restore the databases that is easy for anyone to follow? Or does it require previous experience with recovering databases?
Please suggest a few options.

Improve this question
2
  • 1
    hello, can you include any errors you see in the C:\wamp\bin\mysql\mysql5.6.12\mysql.err our mysql.log or similar?
    – Craig Efrein
    Commented Dec 12, 2013 at 8:37
  • @Craig Efrein This is what I see in the logs - 2013-12-12 15:08:09 6768 [Warning] InnoDB: Cannot open table photo_gallery/photographs from the internal data dictionary of InnoDB though the .frm file for the table exists. See dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
    – user1720897
    Commented Dec 12, 2013 at 9:39

1 Answer 1

Reset to default
2

A couple of possibilities, not sure if any of them will help you

Force recovery

[mysqld]
innodb_force_recovery = 4

If you run Force Recovery, your database will still be in an incoherent state. This will however allow you to make a backup of the data that you do have access to.

Usually, most of the data obtained in this way is intact. Serious corruption might cause SELECT * FROM tbl_name statements or InnoDB background operations to crash or assert, or even cause InnoDB roll-forward recovery to crash. In such cases, use the innodb_force_recovery option to force the InnoDB storage engine to start up while preventing background operations from running, so that you can dump your tables

Force InnoDB recovery

Permissions

Make sure the MySQL service user has full permissions on C:\wamp\bin\mysql\mysql5.6.12\data

enter image description here

The service that runs MySQL needs full control for the directory where you are storing your data files

Update

FS error

Problems reading from the C:\wamp\bin\mysql\mysql5.6.12\data directory ?

If you go to Control Panels-> Administrative Tools-> Events Viewer, then Windows Logs -> System, you might find more information about disk issues

One possible solution, would be to copy your data directory to another drive and/or directory.

Stop MySQL

Copy the data directory contents to the new drive and/or directory.

Open the C:\wamp\bin\mysql\mysql5.6.12\my.cnf file and change the datadir option under mysqld

[mysqld]

datadir=D:\some\other\directory

Start MySQL

Improve this answer
5
  • I have enabled innodb_force_recovery = 4 and restarted the MySQL service. After that I have tried to dump the database with mysqldump. However, I get the following error - mysqldump: Got error: 1146: Table 'test.subjects' doesn't exist when using LOCK TABLES
    – user1720897
    Commented Dec 12, 2013 at 14:22
  • Did you check permissions on the data folder? You can find out which user is being used to start MySQL by going to Control Panels -> Administrative Tools -> Services
    – Craig Efrein
    Commented Dec 12, 2013 at 14:24
  • The permissions on the data folder is set to allow full control. The mysql service logs on as the Local System account.
    – user1720897
    Commented Dec 12, 2013 at 14:30
  • And you do have enough disk space on the C:\ drive? My only other thought, would be to copy those files to another directory and preferably to another disk, just in case there is a problem with C:\. Then you would have to point mysql to that directory. I'll inlcude that in my answer
    – Craig Efrein
    Commented Dec 12, 2013 at 15:04
  • I had to go up to innodb_force_recovery = 6 to finally get mysql back running (wamp goes green) so I could do a mysqldump.
    – David Graham
    Commented Mar 30, 2015 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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