Mac OSX 上的 Mysql 5.6 问题

Mysql 5.6 headaches on Mac OSX

提问by Julie

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:

我和我的几个同事最近使用 Mac 上的自制软件从 MySQL 5.5 升级到 MySQL 5.6，以便在升级我们的服务器之前进行本地测试。自从这次升级以来，我们在运行 Rails 代码时都遇到了间歇性的 MySQL 错误：

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

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

我们尝试在我们的数据库中重新制作我们的用户名和密码，并增加连接超时，但都没有解决问题。错误日志没有提到这个问题。我们遇到问题时找到的唯一解决方法是杀死mysql并重新启动它。我什至最近mysql -u root -p在命令行上使用时注意到了这个错误。似乎一旦我开始收到此错误，无论我使用什么用户名，我都不能超过我当前的连接数。如果我关闭一个连接，那么我可以重新打开一个。

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

• 我们中的一些人：Rails 3.2、Ruby 2、mysql2 0.3.13、MySQL 5.6.12、Mac OSX 10.8.4
• 我们其他人：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

谢谢！朱丽叶

回答by Alex Kovshovik

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

这里的答案都没有帮助我，但最终我让 MySQL 5.6 工作。

THREE options to fix MySQL 5.6:

修复 MySQL 5.6 的三个选项：

1. (confirmed)Edit /etc/my.cnf(create if not exists) and add:

   [mysqld]
   innodb_file_per_table = OFF
   

1. （已确认）编辑/etc/my.cnf（如果不存在则创建）并添加：

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

并重新启动 MySQL。然后要使其工作，您需要将数据库转储到 SQL 文件 (mysqldump)，然后删除并重新创建数据库，然后将数据加载回来。

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/

2. 更改 OSX 的默认 ulimit 值（由 Github 用户sodabrew建议）：https: //superuser.com/questions/261023/how-to-change-default-ulimit-values-in-mac-os-x-10-6

3. 添加下列选项的[mysqld] my.cnf中的部分：table_open_cache = 250。默认情况下，它设置为 2000，这远高于 OSX 的默认 ulimit。也不推荐此解决方案，因为它会损害 MySQL 的性能 - 如果您的表超过 250 个，它会强制 MySQL 经常重新打开表：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 answersuggests 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.

由于 MySQL 5.6 innodb_file_per_table 选项默认为 ON，这意味着每个表的数据都存储在自己的文件中。OSX 默认的打开文件数限制是每个进程 256 个。通常这不是问题，但在我的情况下，我正在并行运行单元测试，这会创建 8 个数据库，每个数据库有 405 个表。OSX 对每个进程的打开文件句柄数量有限制。这个 StackOverflow 答案表明这个限制是 256，这完美地解释了我的问题：在 MySQL 5.6 之前，来自所有这 8 个数据库的所有数据都在一个文件中。

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

感谢我的同事 Thomas L.，他发现了一个暗示这个解决方案的MySQL 错误报告！

回答by Cody Swann

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

回答by Michael Klein

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

这是通过自制软件安装的最新 mysql 版本的问题。

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

5.6.x 产生了问题。降级到 5.5.x 为我解决了这个问题。

You can install old formula versions pretty easily with homebrew:

您可以使用自制软件轻松安装旧的公式版本：

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

brew versions mysql会给你 sha 你必须在 /usr/local 中签出才能安装旧版本

   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 instructionsand reinstall simply by running

这将显示 5.5.29 作为 mysql 版本。然后您可以根据这些说明卸载 mysql并通过运行简单地重新安装

   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后，您可以在/ usr / local中checkout master。brew versions 命令甚至为您提供了仅检出 mysql 公式的命令，但我认为这与仅检出 sha 的整个存储库然后在安装旧的 mysql 版本后返回 master 相比没有任何优势。

回答by John Athayde

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

这是在 OSX 10.8 上的 rbenv 中的 REE (1.8.7) 上的 rails 2.3 上。青年会

回答by Fabio

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.

我在相同的配置（mysql 5.6.12）上遇到了同样的问题。我刚刚用自制软件将 mysql 升级到 5.6.13 版，问题就解决了。

回答by James

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

我用 mysql 5.6.16 解决了这个问题，它是通过 Mavericks 上的 Homebrew 新安装的，还有 rbenv 和 rails 等。

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.

因此，如果您在安装 mysql 等之后还没有重新启动，我建议您在完成这里的答案之前重新启动。

回答by user2965205

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

我发现有效的是将table_open_cache值设置为不高于 1000

table_open_cache=1000

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

我在错误页面上找到了这个，最后一条评论https://bugs.mysql.com/bug.php?id=71960

回答by Scott Willson

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.

升级到 Mavericks 后，我重新安装了 Homebrew。Homebrew 安装了 MySQL 5.6.13 的瓶装版本。