1

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.

Update

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.

  • 1
    I would say that this is indeed the correct place. I would also suggest going back to the SO post and posting a link there to hgere to avoid duplication of effort! – Vérace Sep 12 '17 at 13:53
  • Are the corrupted tables MyISAM or InnoDB? A suspension is like pulling the plug - AFAIK, InnoDB can cope with this and MyISAM can't. Why can' t you change all your tables to InnoDB? – Vérace Sep 12 '17 at 13:59
  • History, if I remember correctly. The tables in production have tens of gigabytes of data in them - transaction records over a decade. Unless we want dev environment to be different than prod (and one of the points with the vagrant machine is to achieve exactly the opposite), we'd need to convert the production tables, and that would be... hard. Probably not justified for the effort. – Vilx- Sep 12 '17 at 15:52
  • @Vérace - suspend no more! Read the update. :) – Vilx- Sep 13 '17 at 7:43
1

Maybe you can stabilize your system by putting the database files on a dedicated virtual hard disk. Steps: set up a good size VHD, give it a mount point, format it, add its mount point to /etc/fstab, copy all database files to the new VHD. Then, edit the (database server's) configuration files so that they point to the files on the new virtual hard drive.

Yes, we can set permissions etc in order to protect the files on a shared folder, but a virtual hard drive could make a better "container" for the database, maybe more suitable for your requirements. M Hashimoto has run some tests, comparing the performance of shared folders/virtual environments/NFS (see the report: http://mitchellh.com/comparing-filesystem-performance-in-virtual-machines). Not that you were complaining about the performance as such, but shared folders with VirtualBox seem to have some "difficulties" (or: seem to have had them in the past).

If you really have to "suspend" the VM, there are some hints here (https://askubuntu.com/questions/63524/whats-the-best-way-to-pause-my-work-in-virtualbox-with-ubuntu-as-a-guest). I can imagine that this works smoother when all files (associated with the db server) are located on virtual hard drives that are attached to the VM.

You can also detach (and keep) the virtual disk that contains the database files, create a new VM, and attach the disk to a new VM if need be.

  • Hmm, actually, this is a pretty good idea! I'll see if I can coerce vagrant to do this. – Vilx- Sep 13 '17 at 7:18
  • Bah, roadblock. Virtualbox is being difficult. If the image is deleted, I need to remove it from the storage registry before I can add a new one in the same place. But I cannot remove it blindly either, because it complains if no such storage is registered. And I cannot query the registry from Vagrant to see if the file is registered or not. – Vilx- Sep 18 '17 at 11:59
  • Can you use VBoxManage (command line)? See a list of all commands in section 8.2. - virtualbox.org/manual/ch08.html - Maybe you can get the information you need via VBoxManage list (8.4.) – stefan Sep 19 '17 at 5:20
  • I could use vboxmanage - if I knew where the executable resided. I don't know about Linux, but on Windows it's not on PATH by default. I can invoke it from Vagrant with a special command (after all, Vagrant needs to know where it is to do its stuff), but I must specify all the commands in advance, I cannot get their results (they are executed only after my script is done), and none of them may fail or Vagrant aborts. – Vilx- Sep 19 '17 at 7:43
  • Maybe someone in the Vagrant Google group can help... groups.google.com/forum/#!forum/vagrant-up – stefan Sep 20 '17 at 5:35
1

When the production box crashes, and you find that the MyISAM tables are corrupted there, and you find how how many hours (or days) it takes to REPAIR them, you may rethink whether moving to InnoDB is justified.

At some point your 'suspend' + corruption may make the MyISAM tables so messed up that dev is impacted.

OK, lecture over; back to your questions.

How big is the VM? If you are 50/50 InnoDB/MyISAM on that box, then use these settings if the VM is at least 4GB:

key_buffer_size = 10% of the VM size
innodb_buffer_pool_size = 35%

If less than 4GB, lower those percentages some. The goal is to avoid ever swapping.

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.