Mac OSX上的Mysql 5.6令人头疼 [英] Mysql 5.6 headaches on Mac OSX

问题描述

最近，我和我的几个同事已经在Mac上使用自制软件从MySQL 5.5升级到MySQL 5.6，以便在升级服务器之前在本地进行测试.自升级以来，我们所有人在运行我们的rails代码时都遇到间歇性的MySQL错误:

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

我们尝试在数据库中重新创建用户名和密码，并增加连接超时，但均未解决问题.错误日志未提及该问题.遇到问题时，我们发现的唯一解决方法是杀死mysql并重新启动它.我什至最近在命令行上使用mysql -u root -p注意到了此错误.似乎一旦开始出现此错误，无论使用什么用户名，我都无法超过当前的连接数.如果我关闭了连接，则可以重新打开一个连接.

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.

我们具有以下环境:

• 我们中的一些人: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?

谢谢！ 朱莉

推荐答案

这里的答案都没有帮助我，但最终我得到了MySQL 5.6的支持.

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

三个用于修复MySQL 5.6的选项:

1. (已确认)编辑/etc/my.cnf(如果不存在则创建)并添加:

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

[mysqld]
innodb_file_per_table = OFF

并重新启动MySQL.然后，要执行此操作，您需要将数据库转储到SQL文件(mysqldump)中，然后删除并重新创建数据库，然后将数据加载回去.

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. 更改OSX的默认ulimit值(由Github用户建议 sodabrew ):https://superuser.com/questions/261023/how -to-change-default-ulimit-values-in-mac-os-x-10-6

将以下选项添加到my.cnf的[mysqld]部分:table_open_cache = 250.默认情况下，它设置为2000，这比OSX的默认ulimit高得多.不建议使用此解决方案，因为它会损害MySQL的性能-如果您有超过250个表，它将迫使MySQL经常重新打开表:

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/

为什么会发生此错误?

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

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.

感谢我的同事Thomas L.发现了一个 MySQL错误报告，其中提示了此解决方案！

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

这篇关于Mac OSX上的Mysql 5.6令人头疼的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！