49

Several of my colleagues and I have recently upgraded from MySQL 5.5 to MySQL 5.6 using homebrew on our Macs to test locally before upgrading our servers. Since this upgrade, we all have been experiencing intermittent MySQL errors when running our rails code:

Lost connection to MySQL server at 'sending authentication information', system error: 32

We have tried re-making our usernames and passwords in our database, and upping the connection timeout, but neither have fixed the problem. The error logs do not mention the issue. The only workaround we have found when we run into the problem is to kill mysql and restart it. I have even noticed this error more recently using mysql -u root -p on the command line. It seems that once I start getting this error, I cannot exceed my current number of connections no matter what username I use. If I close a connection, then I can re-open one.

We have the following environments:

  • some of us: Rails 3.2, Ruby 2, mysql2 0.3.13, MySQL 5.6.12, Mac OSX 10.8.4
  • others of us: Rails 3.2, Ruby 1.9, mysql2 0.3.13, MySQL 5.6.10, Mac OSX 10.8.4

Any ideas what might be causing this?

Thanks! Julie

Julie
  • 1,847
  • 3
  • 12
  • 27
  • 1
    The same with Mysql 5.6.14 installed via DMG: `$ mysql` `ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32` – temper Nov 16 '13 at 13:12
  • 1
    simply stopping and restarting the MySQL instance worked for me. 5.6.14 – Eben Geer Mar 19 '14 at 02:42
  • Getting the same error with Percona Server 5.6 on OSX 10.9.2: especially when unit tests in parallel - using 8 connections. Duh. – Alex Kovshovik Mar 31 '14 at 16:24
  • Just found the ultimate solution, posted an answer below. – Alex Kovshovik Mar 31 '14 at 23:07
  • Check this http://stackoverflow.com/questions/38459373/mysql-5-6-is-not-working-in-mac-os/38459414#38459414 its works like a charm – Sk. Irfan Jul 19 '16 at 13:15

8 Answers8

74

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

THREE options to fix MySQL 5.6:

  1. (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.

  1. 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

  2. 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!

Community
  • 1
  • 1
Alex Kovshovik
  • 3,735
  • 4
  • 31
  • 36
  • How its possible to make mysqldump if cannot connect to database? – Imme22009 May 05 '14 at 04:02
  • Unfortunately, you make mysqldump if you can't connect to the database, since mysqldump is just one of the mysql client programs - all of them need to be able to connect to do stuff with mysql. – Alex Kovshovik May 05 '14 at 20:13
  • Good catch @AlexKovshovik, hopefully MySql will have this fixed soon. – Mark Kadlec Oct 29 '14 at 15:45
  • I didn't know that the setting had to be in the [mysqld] section of the config. Useful info. – Paul Chernoch Mar 30 '16 at 16:59
  • 3
    This is best the best answer I've find so far. It solved all my issues on lost connections. This answer is still relevant for OSX Mojave and MySQL 5.7. – andoke Jan 15 '19 at 15:32
  • Add the setting `table_open_cache` is fine for me. And the value should be under 1000 – Rick Luo Oct 09 '20 at 09:52
  • For option 1, you can run `mysql --help | grep -B 1 cnf` to see all the places your mysql loads a `my.cnf` file. In my case, I found it best to edit `usr/local/etc/my.cnf` – Kelsey Hannan May 19 '21 at 23:02
10

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
Cody Swann
  • 518
  • 5
  • 8
4

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.

Community
  • 1
  • 1
Michael Klein
  • 785
  • 6
  • 14
  • Interesting. Have you reported this bug anywhere to Homebrew or tried a non-homebrew install? We are hoping to upgrade to some newer features in 5.6, so would prefer not to have to go back to 5.5. – Julie Jul 25 '13 at 16:24
  • No I haven't reported this. This does not seem to be an homebrew issue though. I googled the problem and I found some people complaining that this was happening with 5.6.x mysql installation. so I downgraded. – Michael Klein Jul 30 '13 at 12:57
  • This worked for us, but this now that we have to upgrade to 5.6 in production due to performance issues, it would be nice to upgrade locally too. Still looking for solution. – Alex Kovshovik Mar 31 '14 at 16:26
  • When I try `brew versions mysql` now I get `Error: Unknown command: versions` – Kelsey Hannan May 19 '21 at 02:45
0

We found that using the following fixes this for us:

brew install mysql --use-llvm

This is on a rails 2.3 ontop of REE (1.8.7) in rbenv on OSX 10.8. YMMV

John Athayde
  • 480
  • 2
  • 12
0

I'm having the same issue on same configuration (mysql 5.6.12). I've just upgraded mysql with homebrew to version 5.6.13 and the problem is gone.

Fabio
  • 17,633
  • 9
  • 76
  • 110
0

I struck this problem with mysql 5.6.16, freshly installed via Homebrew on Mavericks, along with rbenv and rails etc.

Decided to reboot before working through the other solutions here. Problem solved!

So if you haven't rebooted since installing mysql etc, I'd recommend restarting before working through the answers here.

James
  • 265
  • 2
  • 9
0

What I found worked, was setting table_open_cache to a value no higher than 1000

table_open_cache=1000

I found this on the bug page, very last comment https://bugs.mysql.com/bug.php?id=71960

user2965205
  • 63
  • 1
  • 7
-1

On Mavericks, this worked for me:

mysql.server stop
brew install mysql
mysql.server start
gem remove mysql2
gem install mysql2

I reinstalled Homebrew after upgrading to Mavericks. Homebrew installed the bottled version of MySQL 5.6.13.

Scott Willson
  • 11
  • 1