Mysql 5.6 headaches on Mac OSX

None of the answers here helped me, but finally I got MySQL 5.6 to work.

THREE options to fix MySQL 5.6:

(confirmed) Edit /etc/my.cnf (create if not exists) and add:
```
[mysqld]
innodb_file_per_table = OFF
```

and restart MySQL. Then for this to work you'll need to dump your databases into SQL file (mysqldump), then drop and re-create the databases, then load the data back.

Change default ulimit value of OSX (suggested by Github user sodabrew): https://superuser.com/questions/261023/how-to-change-default-ulimit-values-in-mac-os-x-10-6
Add the following option to [mysqld] section of my.cnf: table_open_cache = 250. By default it is set to 2000, which is way above OSX's default ulimit. This solution is also not recommended, because it hurts the performance of your MySQL - it forces MySQL to re-open tables often, if you have more than 250 tables: https://mariadb.com/kb/en/optimizing-table_open_cache/

Why this error is happening?

Since MySQL 5.6 innodb_file_per_table option is ON by default, which means that each table's data is stored in its own file. OSX default limit of the number of the open files is 256 per process. Normally this isn't a problem, but in my case I'm running unit tests in parallel, which creates 8 databases with 405 tables each. OSX has a limit of the number of open file handles per process. This StackOverflow answer suggests that this limit is 256, which explains my problem perfectly: before MySQL 5.6 all data from all these 8 databases was in ONE file.

Thanks to my colleague Thomas L. who found a MySQL bug report which hinted this solution!

We had the same problem. This fixed it for us

project-root$ mysql.server stop
project-root$ gem uninstall mysql2
project-root$ bundle install
project-root$ mysql.server start

That's an issue with the latest mysql version that is installed via homebrew.

5.6.x creates the problem. downgrading to 5.5.x solved the issue for me.

You can install old formula versions pretty easily with homebrew:

brew versions mysql will give you the sha you have to checkout in /usr/local to be able to install an old version

   cd /usr/local
   git checkout 336c976
   brew info mysql

This will show you 5.5.29 as the mysql version. You can then uninstall mysql based on these instructions and reinstall simply by running

   brew install mysql

and running through the normal installation process with homebrew:

  unset TMPDIR
  mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

Hope that helps.

You can checkout master in /usr/local after installing the old version of mysql after that again. The brew versions command even gives you the command to just checkout the formula for mysql but I don't think that has any advantages over just checking out the whole repository for the sha and then going back to master after installing the old mysql version.

Mysql 5.6 headaches on Mac OSX

Related

Recent Posts