I'm trying to move our development environment onto a vagrant box (virtual machine using Oracle virtualbox). Currently I'm stuck on one problem:
The environment needs a MySQL server and some databases. I've got all the scripts set up that install MySQL, create and populate the databases, etc. That works.
Since the developers are likely to set up further data of their own, my idea was to put the MySQL data folder in a shared virtualbox folder. This way when the vagrant machine is destroyed/recreated, the data folder remains and can be reused without loss of data.
However sometimes (but not always, I don't know what triggers it) when I simply suspend and then resume the virtual machine, MySQL starts complaining that a lot of its tables (but not all) are corrupted. Fixing the tables results in all data in them being lost. Obviously this is unacceptable.
Does anyone have any idea why this happens and how to prevent it?
A few possible clues or red herrings - since I read that virtualbox's shared folders are slow, I've increased the InnoDB buffer pool size to 1GB, so that it has a lot of cache space. We do use InnoDB and MyISAM in proportions of about 50/50. I haven't yet checked if the failed tables all belong to the same engine - I'll pay attention to it when it next crashes.
Ha, I managed to catch it red handed! It's actually... suspense... BOTH InnoDB and MyISAM.
Selecting from a MyISAM table gives the error
Table 'xxx' is marked as crashed and should be repaired
Trying to run
mysqlcheck -A -a (analyze all tables) actually CRASHES MySQL. The log files tell the story (edited for clarity):
[Warning] InnoDB: Retry attempts for writing partial data failed. [ERROR] InnoDB: Write to file ./ib_logfile0failed at offset 29885952, 1024 bytes should have been written, only 0 were written. Operating system error number 71. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded. [ERROR] InnoDB: Error number 71 means 'Protocol error' [ERROR] mysqld got signal 6 Query (0x7fbc12501b50): ANALYZE TABLE `yyy`
And then, in a moment, systemctl restarts the service and all runs perfectly again.
My educated guess is that suspending down the virtual machine releases the locks that virtualbox was holding on opened files, and when virtualbox starts up again, it doesn't re-acquire them. So from MySQL's point of view, it's keeping a file opened, but virtualbox has lost that grip, so when MySQL tries to access the file again, it gets an error.