MySQL corrupts after every server restart

Question

I Have a bit of an odd problem. I am running MySQL and Drupal site from a windows environment (not by choice but it is just how things have worked out) and most of the time the site is fine however every time the server is restarted (from updates or for maintenance) The MySQL Database will corrupt.

When I log in to MySQL Work bench I notice that the server status is stopped and when I try to start it I get the error:

ERROR Fatal error: Can't open and lock privilege tables: Can't find file: 'user' (errno: 2 - No such file or directory)

and then MySQL Server fails to restart.

The only way around this problem is to delete data folder in the ProgramData/MySQL and then replace it with a backup from a fresh install of MySQL then import a backup of my tables and recreate my users.

My Question is what is causing MySQL to corrupt at every sever restart and what can I do to prevent this happening moving forward?

Regiment

Answer 1

This occurs when mysql can not find or do not have permissions to user.MYD or user.MYI or user.frm in the data directory. When you start mysql, make sure that these files exist in data directory. You can just copy those files when mysql does not start

Protozoon

That helps but the question is why does this happen in the first place? – Regiment

I am not sure about windows. MySQL won't delete them unless someone executes drop table mysql.user If the files are lost, it means some cron or some process is deleting them – Protozoon

Ok Thanks. Well I can assure you that no one is dropping the table and I would find it weird that some part of the windows shutdown process would delete files from the program data folder but I will check the condition of these files next time MySQL corrupts – Regiment

I have had another error over the weekend and you are correct something has removed User.MYD and User.MYI From the data folder. Any idea what could be deleting these whenever there is a shutdown because this is just really odd behaviour. – Regiment

what is the version? you can also start mysql with --skip-grant-tables and then load the user table from dump file – Protozoon

The MySQL version is 5.6.14. Thanks for the heads up on making it faster to restart but I really need to find a away to stop this from happening more than anything – Regiment

Answer 2

This worked for me: Stick with the 32 bit.

Uninstall and make sure you then DELETE the mysql-data folder and all its subfolders before installing, and prefer to use the 32 bit (sometimes 64 bit versions are the problem).

(Source)

Exfoliation