如何为 Homebrew 安装的 MySQL 服务器禁用 STRICT_TRANS_TABLES？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20576917/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I disable STRICT_TRANS_TABLES for a Homebrew-installed MySQL server?
提问by Metaphile
It seems that MySQL recently (5.6?) changed the default SQL mode to be more restrictive. The new mode is "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION". I like the change, but at least one website that I maintain does not. INSERT queries are failing because they don't specify values for columns that don't have defaults. Before, MySQL would infer default values by column type.
似乎 MySQL 最近（5.6？）将默认 SQL 模式更改为更具限制性。新模式是“STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION”。我喜欢这种变化，但至少我维护的一个网站不喜欢。INSERT 查询失败，因为它们没有为没有默认值的列指定值。以前，MySQL 会通过列类型推断默认值。
For now, I want to disable STRICT_TRANS_TABLES. I have added sql_mode=NO_ENGINE_SUBSTITUTIONto my.cnfand restarted the server but the strict setting persists. What am I doing wrong?
现在，我想禁用 STRICT_TRANS_TABLES。我已经加入sql_mode=NO_ENGINE_SUBSTITUTION到my.cnf并重新启动服务器，但严格的设置依然存在。我究竟做错了什么？
MySQL version:
MySQL版本：
$ mysqld --version
mysqld Ver 5.6.15 for osx10.9 on x86_64 (Homebrew)
my.cnf:
我的.cnf：
$ cat /etc/my.cnf
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
# not sure if this is needed but it doesn't seem to have an effect either way
[mysqld_safe]
sql_mode=NO_ENGINE_SUBSTITUTION
Confirm that mysqld would use settings in my.cnf:
确认 mysqld 将使用 my.cnf 中的设置：
$ mysqld --print-defaults
mysqld would have been started with the following arguments:
--sql_mode=NO_ENGINE_SUBSTITUTION
Confirm that mysqld is not currently running:
确认 mysqld 当前没有运行：
$ ps aux | grep mysql
metaphile 1022 0.0 0.0 2432784 600 s003 S+ 3:10PM 0:00.00 grep mysql
Property list provided by Homebrew:
Homebrew 提供的属性列表：
$ cat ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>KeepAlive</key>
<true/>
<key>Label</key>
<string>homebrew.mxcl.mysql</string>
<key>ProgramArguments</key>
<array>
<string>/usr/local/opt/mysql/bin/mysqld_safe</string>
<string>--bind-address=127.0.0.1</string>
</array>
<key>RunAtLoad</key>
<true/>
<key>WorkingDirectory</key>
<string>/usr/local/var</string>
</dict>
</plist>
Start MySQL and check SQL mode:
启动 MySQL 并检查 SQL 模式：
$ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
$ mysql -uroot
...
mysql> SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
+--------------------------------------------+--------------------------------------------+
| @@GLOBAL.sql_mode | @@SESSION.sql_mode |
+--------------------------------------------+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)
Argh!
啊！
采纳答案by Metaphile
@ssnobody's answer prompted me to search my entire system for my.cnf files. I had already checked the locations listed by mysqld --help --verbose. It turns out that my server is using /usr/local/Cellar/mysql/5.6.15/my.cnfwhich I had wrongly assumed to be a sample file. The file is not symlinked from any of the standard locations, including /usr/local/mysql.
@ssnobody 的回答促使我在整个系统中搜索 my.cnf 文件。我已经检查了 列出的位置mysqld --help --verbose。事实证明，我的服务器正在使用/usr/local/Cellar/mysql/5.6.15/my.cnf我错误地认为是示例文件的服务器。该文件未从任何标准位置进行符号链接，包括/usr/local/mysql.
Can anybody shed some light on this? Is it a Homebrew thing? How could I have figured this out except by making test modifications to every my.cnf that I could find?
任何人都可以对此有所了解吗？这是自制的东西吗？除了对我能找到的每个 my.cnf 进行测试修改之外，我怎么能想到这一点？
回答by vd1008
On Centos 6.5 i had to edit /usr/my.cnfand set (even though /etc/my.cnfexisted and bindings were successfully set there
在 Centos 6.5 上，我必须编辑 /usr/my.cnf和设置（即使/etc/my.cnf存在并且绑定在那里成功设置）
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION
回答by ssnobody
Please check /usr/local/mysql/my.cnfand comment out the problematic setting.
请检查/usr/local/mysql/my.cnf并注释掉有问题的设置。
Source: MySQL Strict Mode on OS X
回答by Will
To just disable the STRICT_TRANS_TABLES for a specific script,
要为特定脚本禁用 STRICT_TRANS_TABLES，
set session sql_mode = '';
when you initialize your db handle in your script. That will also disable NO_ENGINE_SUBSTITUTION, so just
当您在脚本中初始化数据库句柄时。这也将禁用 NO_ENGINE_SUBSTITUTION，所以只需
set session sql_mode = 'NO_ENGINE_SUBSTITUTION';
will leave that intact.
将保持原样。
回答by Yan Ouellet
You need to edit:
您需要编辑：
/usr/my.cnf
and set
并设置
sql_mode=NO_ENGINE_SUBSTITUTION
