如何使 sql-mode="NO_ENGINE_SUBSTITUTION" 在 MySQL my.cnf 中永久存在

How to make sql-mode="NO_ENGINE_SUBSTITUTION" permanent in MySQL my.cnf

提问by Chadwick Meyer

UPDATE FIXED 1/18/15

更新已于 15 年 1 月 18 日修复

After we recently updated to MySQL 5.6.27 (from the Ubuntu repo), this option now works. So this appears to have been a problem with the previous version of MySQL.

在我们最近更新到 MySQL 5.6.27（来自 Ubuntu 存储库）之后，这个选项现在可以工作了。所以这似乎是以前版本的 MySQL 的问题。

ORIGINAL QUESTION

原问题

With a new upgrade to MySQL (5.6.20), updates and inserts fail unless I set sql-mode to NO_ENGINE_SUBSTITUTION.

对 MySQL (5.6.20) 进行新升级后，除非我将 sql-mode 设置为 NO_ENGINE_SUBSTITUTION，否则更新和插入都会失败。

Thanks to the documentation, I can run the following from mysql terminal and that fixes the problem (temporarily):

感谢文档，我可以从 mysql 终端运行以下命令并解决问题（暂时）：

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';`

But the next time MySQL restarts, these settings are lost.

但是下次 MySQL 重新启动时，这些设置就会丢失。

So I have tried to make that permanent by editing /etc/mysql/my.cnf (on my standard server running Ubuntu 12.04.5 LTS), and adding the config settings that the documentationsays should be added:

所以我试图通过编辑 /etc/mysql/my.cnf（在我运行 Ubuntu 12.04.5 LTS 的标准服务器上）并添加文档中说应该添加的配置设置来使其永久化：

[mysqld]
sql-mode="NO_ENGINE_SUBSTITUTION"

Alternative Syntaxes for Testing

用于测试的替代语法

Just for testing purposes, I have also tried the following formats (which do not cause errors when restarting MySQL, but they do not affect the setting).

只是为了测试，我还尝试了以下格式（重启MySQL时不会出错，但不影响设置）。

# dash no quotes
sql-mode=NO_ENGINE_SUBSTITUTION
# underscore no quotes
sql_mode=NO_ENGINE_SUBSTITUTION
# underscore and quotes
sql_mode="NO_ENGINE_SUBSTITUTION"

Nothing works. After restart this setting is lost and I have to run the commands manually again from mysql terminal to make saving work again.

什么都行不通。重新启动后，此设置丢失，我必须再次从 mysql 终端手动运行命令才能再次保存。

Alternative Locations

替代地点

• I know /etc/mysql/my.cnf is being referenced because we have replication defined in this file, and that is working.
• There is not another identical setting in this file that is overwriting it.

• 我知道 /etc/mysql/my.cnf 正在被引用，因为我们在此文件中定义了复制，并且正在运行。
• 此文件中没有其他相同的设置会覆盖它。

I get a list of the config files that are being referenced by running this from the command line:

通过从命令行运行它，我得到了被引用的配置文件的列表：

mysqld --help --verbose

I see a line that reads:

我看到一行写着：

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

This is the default location it "looks" for files, it doesn't mean that it actually found a file there, e.g. my server doesn't have /etc/my.cnf, /usr/etc/my.cnfor ~/.my.cnf.

这是它“查找”文件的默认位置，这并不意味着它实际上在那里找到了一个文件，例如我的服务器没有/etc/my.cnf,/usr/etc/my.cnf或~/.my.cnf.

So it looks like my config in /etc/mysql/my.cnf is the only file mysql is referencing, and therefore this setting is not being overwritten.

所以看起来我在 /etc/mysql/my.cnf 中的配置是 mysql 引用的唯一文件，因此这个设置不会被覆盖。

Logical Conclusion of Testing

测试的逻辑结论

Logically then, it seems the syntax is not correct or is being ignored for some other reason. Any other ideas?

从逻辑上讲，语法似乎不正确或由于其他原因被忽略。还有其他想法吗？

回答by Mikepote

Just to add my configuration to the mix, I'm using MySQL 5.7.8 which has the same strict sql_mode rules by default.

只是为了将我的配置添加到组合中，我使用的是 MySQL 5.7.8，默认情况下它具有相同的严格 sql_mode 规则。

• I finally figured the following working in my /etc/mysql/my.conf:

  [mysqld]
  sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
  

• 我终于在我的 /etc/mysql/my.conf 中找到了以下工作：

  [mysqld]
  sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
  

i.e. dash, not underscore and quotes around the value.

即破折号，而不是下划线和值周围的引号。

• I have NO other my.conf files other than /etc/mysql/my.conf

• There are some extra config includes being loaded from /etc/mysql/conf.d/ but they are blank.

• 除了 /etc/mysql/my.conf 之外，我没有其他 my.conf 文件

• 有一些额外的配置包括从 /etc/mysql/conf.d/ 加载，但它们是空白的。

And that seems to work for me.

这似乎对我有用。

回答by Riad

It should be:

它应该是：

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

instead of

代替

[mysqld]
sql_mode="NO_ENGINE_SUBSTITUTION"

then restart mysqld service.

然后重启mysqld服务。

回答by fancyPants

Your server may read a different my.cnfthan the one you're editing (unless you specified it when starting mysqld).

您的服务器读取的可能my.cnf与您正在编辑的不同（除非您在启动 mysqld 时指定了它）。

From the MySQL Certification Study Guide:

来自MySQL 认证学习指南：

The search order includes two general option files, /etc/my.cnfand $MYSQL_HOME/my.cnf. The second file is used only if the MYSQL_HOMEenvironment variable is set. Typically, you seet it to the MySQL installation directory. (The mysqld_safe script attempts to set MYSQL_HOMEif it is not set before starting the server.) The option file search order also includes ~/.my.cnf(that is the home directory). This isn't an especially suitable location for server options. (Normally, you invoke the server as mysql, or as rootwith a --user=mysqloption. The user-specific file read by the server would depend on which login account you invoke it from, possibly leading to inconsistent sets of options being used.)

搜索顺序包括两个常规选项文件/etc/my.cnf和 $MYSQL_HOME/my.cnf. 仅当MYSQL_HOME设置了环境变量时才使用第二个文件。通常，您将其设置为 MySQL 安装目录。（MYSQL_HOME如果在启动服务器之前未设置，mysqld_safe 脚本会尝试设置 。）选项文件搜索顺序还包括~/.my.cnf（即主目录）。这不是服务器选项特别合适的位置。（通常情况下，你调用服务器mysql，或者作为root一个--user=mysql选项，用户特定的文件中读取服务器将取决于其登录帐户调用它，可能导致不一致的选项集被使用。）

Another possibility is of course, that your sql-modeoption gets overwritten further down in the same file. Multiple options have to be separated by ,in the same line.

当然，另一种可能性是，您的sql-mode选项在同一文件中被进一步覆盖。多个选项必须,在同一行中用 分隔。

P.S.: And you need the quotes, IIRC. Now that you've tried it without quotes, I'm pretty sure, you're editing the wrong file, since MySQL doesn't start when there's an error in the option file.

PS：你需要引号，IIRC。现在你已经尝试了它没有引号，我很确定，你正在编辑错误的文件，因为在选项文件中没有错误时，MySQL不会启动。

P.P.S.: Had a look at my config files again, there it's

PPS：再次查看我的配置文件，它在那里

[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"

and it's working.

它正在工作。

回答by Pedroe

Woks fine for me on ubuntu 16.04. path: /etc/mysql/mysql.cnf

在 ubuntu 16.04 上对我来说很好。路径：/etc/mysql/mysql.cnf

and paste that

并粘贴

[mysqld]
#
# * Basic Settings
#
sql_mode = "NO_ENGINE_SUBSTITUTION"

回答by user2134041

For me it was a permission problem.

对我来说，这是一个许可问题。

enter:

进入：

mysqld --verbose --help | grep -A 1 "Default options"

[Warning] World-writable config file '/etc/mysql/my.cnf' is ignored.

[警告] 世界可写的配置文件 '/etc/mysql/my.cnf' 被忽略。

So try to execute the following, and then restart the server

于是尝试执行以下，然后重启服务器

chmod 644 '/etc/mysql/my.cnf'

It will give mysql access to read and write to the file.

它将授予 mysql 读取和写入文件的权限。

回答by A G

My problem was that I had spaces in between the options on 5.7.20. Removing them so the line looked like

我的问题是我在 5.7.20 的选项之间有空格。删除它们，使线条看起来像

[mysqld]
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

回答by Michal Przybylowicz

On Linux Mint 18the default config file that has the sql-modeoption set is located here :

在Linux Mint 18上，具有sql-mode选项集的默认配置文件位于此处：

/usr/my.cnf

And relevant line is:

相关行是：

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

So You can set there.

所以你可以在那里设置。

If not sure what config file has such option You can search for it:

如果不确定哪个配置文件有这样的选项您可以搜索它：

$ sudo find / -iname "*my.cnf*"

And get a list:

并得到一个列表：

/var/lib/dpkg/alternatives/my.cnf
/usr/my.cnf
/etc/alternatives/my.cnf
/etc/mysql/my.cnf.fallback
/etc/mysql/my.cnf

回答by Timo Schwarz

For me bothkeys for sql-modeworked. Whether I used

对我来说，这两个键都sql-mode有效。我是否使用过

# dash no quotes
sql-mode=NO_ENGINE_SUBSTITUTION

or

或者

# underscore no quotes
sql_mode=NO_ENGINE_SUBSTITUTION

in the my.inifile made no differenceand both were accepted, as far as I could test it.

在该my.ini文件中所做没有区别，并且都接受了，据我可以测试它。

What actually made a difference was a missing newlineat the end of the my.inifile.

实际上有所不同的是文件末尾缺少换行符my.ini。

So everyone having problems with this or similar problems with my.ini/my.cnf: Make sure there is a blank line at the end of the file!

所以每个人都有这个问题或类似的问题my.ini/ my.cnf: 确保文件末尾有一个空行！

Tested using MySQL 5.7.27.

使用 MySQL 5.7.27 测试。

回答by lvniebelschuetz

The solution is pretty easy... Searched for it for a while and it turns out that you just have to edit 2 config-files:

解决方案很简单......搜索了一段时间，结果你只需要编辑2个配置文件：

• /usr/my.cnf
• /etc/mysql/my.cnf

• /usr/my.cnf
• /etc/mysql/my.cnf

in both files you'll have to add:

在这两个文件中，您必须添加：

[mysqld]
...
sql_mode=NO_ENGINE_SUBSTITUTION

At least, that's what's working for 5.6.24-2+deb.sury.org~precise+2

至少，这适用于 5.6.24-2+deb.sury.org~precise+2

回答by monkey64

I am running WHM 10.2.15-MariaDB. To permanently disable strict mode first find out which configuration file our installation prefers. For that, we need the binary's location:

我正在运行 WHM 10.2.15-MariaDB。要永久禁用严格模式，首先要找出我们的安装更喜欢哪个配置文件。为此，我们需要二进制文件的位置：

$ which mysqld
/usr/sbin/mysqld

Then, we use this path to execute the lookup:

然后，我们使用此路径执行查找：

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

We can see that the first favored configuration file is one in the root of the etc folder but that there is a second .cnf file hidden - ~/.my.cnf. Adding the following to the ~/.my.cnf file permanently disabled strict mode for me (needs to be within the mysqld section):

我们可以看到第一个偏好的配置文件位于 etc 文件夹的根目录中，但隐藏了第二个 .cnf 文件 - ~/.my.cnf。将以下内容添加到 ~/.my.cnf 文件永久禁用了我的严格模式（需要在 mysqld 部分中）：

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

I found that adding the line to /etc/my.cnf had no effect at all apart from sending me crazy.

我发现将这一行添加到 /etc/my.cnf 除了让我发疯之外根本没有任何影响。