如何在MySQL的my.cnf文件中永久设置sql-mode="NO_ENGINE_SUBSTITUTION"？

Question

更新已修复 1/18/15

最近我们从Ubuntu存储库升级到MySQL 5.6.27后，此选项现在可用。因此，这似乎是先前版本的MySQL存在的问题。

原问题

在升级到MySQL（5.6.20）后，如果我不将sql-mode设置为NO_ENGINE_SUBSTITUTION，则更新和插入会失败。

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

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

但是下一次MySQL重启时，这些设置将会丢失。

因此，我尝试通过编辑/etc/mysql/my.cnf（在我的标准服务器上运行Ubuntu 12.04.5 LTS），添加文档中建议添加的配置设置，使其永久化。

文档

[mysqld]
sql-mode="NO_ENGINE_SUBSTITUTION"

测试的替代语法

仅供测试目的，我还尝试了以下格式（在重新启动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"

什么都不起作用。重新启动后，此设置将丢失，我必须再次从MySQL终端手动运行命令以使保存工作正常。

备选位置

• 我知道/etc/mysql/my.cnf正在被引用，因为我们在该文件中定义了复制，并且它正在工作。
• 在此文件中没有另一个相同的设置正在覆盖它。

通过从命令行运行以下内容，我可以获取被引用的配置文件列表：

mysqld --help --verbose

我看到一行字，它写着：

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 

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

因此，看起来mysql只引用了/etc/mysql/my.cnf中的配置文件，因此此设置未被覆盖。

测试的逻辑结论

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

- Chadwick Meyer

---

虽然我不是MySQL大师，但客户端在配置文件中也有自己的部分，难道客户端配置会覆盖服务器默认设置吗？ - Matteo Tassinari

谢谢，这个解决了我在Homebrew OSX MySQL中的问题sql_mode="NO_ENGINE_SUBSTITUTION"https://gist.github.com/fhferreira/dd8a2a24000a562c87ab - Flávio H. Ferreira

请注意，上述问题已在我们最近的MySQL 5.6.27更新中修复。 - Chadwick Meyer

Answer 1

仅补充我的设置，我使用的是MySQL 5.7.8，其默认具有相同的严格sql_mode规则。

• 最终我在我的/etc/mysql/my.conf中找到了以下可行的解决方案：

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

i.e. 破折号而不是下划线，值周围加引号。

• 我没有除了 /etc/mysql/my.conf 以外的任何其他 my.conf 文件。

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

对我来说似乎有效。

- Mikepote

---

这里似乎存在很多关于它应该是什么，以及实际有效的变化，正如这些答案所证明的那样 :D 有时使用下划线或破折号，有时使用引号。 （扶额） - Chadwick Meyer

对我来说，当它不在[mysqld]部分下时，我注意到有解析问题。希望这能帮助到某些人。 - Adam Fowler

服务器如果不使用[mysql]，甚至无法启动。 - Joey Pinto

我的“mysql.cnf”中绝对需要 [mysql]！ - Vael Victus

在我的情况下，我将配置写入了/etc/mysql/my.cnf文件中，并使用命令sudo /etc/init.d/mysql restart重新启动了MySQL服务，然后它就起作用了。我正在Ubuntu 18.04中使用MySQL 5.7.26。 - Mahmudul Hasan Shohag

Answer 2

除非您在启动mysqld时指定了它，否则您的服务器可能会读取与您编辑的my.cnf不同的文件。

根据MySQL认证考试指南：

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

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

附言：我想您需要引号。既然您已经尝试过没有引号的选项，请确保您正在编辑正确的文件，因为当选项文件存在错误时，MySQL将无法启动。

附言2：我再次查看了我的配置文件，结果是：

[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"

而且它正在运作。

- fancyPants

---

我们开启了复制功能，这在my.cnf文件中有定义，所以这肯定是正在使用的配置（但我会检查它是否被其他地方覆盖）。就像我告诉Riad的那样，文档说要使用下划线版本和引号。如果你的建议是正确的，那么文档是错误的。我会试试看的。谢谢。 - Chadwick Meyer

这对我不起作用。我更新了我的问题并进行了更多的故障排除。 - Chadwick Meyer

嗨，我遇到了同样的问题，然后我更新了/etc/my.cnf文件，但是几天后服务器重写了我的文件并创建了旧文件的备份。如何解决这个问题？ - Yogesh Saroya

@YogeshSaroya MySQL服务器从不在那个文件中写入。如果它发生了改变，那么一定是其他东西改变了它，比如你的软件包管理器或其他什么。请在一个单独的问题中询问这个问题。 - fancyPants

Answer 3

应该是：

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

而不是

[mysqld]
sql_mode="NO_ENGINE_SUBSTITUTION"

然后重新启动mysqld服务。

- Riad

---

谢谢。我会尝试一下（虽然我认为我最初就是这样做的）。语法肯定很令人困惑，因为文档说应该是sql-mode="NO_ENGINE_SUBSTITUTION"，带有破折号而不是下划线和引号？“要在服务器启动时设置SQL模式，请在命令行上使用--sql-mode="modes"选项，或在选项文件（如my.cnf（Unix操作系统））中使用sql-mode="modes"。”--文档 - Chadwick Meyer

我更新了我的问题并进行了更多的故障排除。这两个选项都不起作用。 - Chadwick Meyer

Answer 4

在我的Ubuntu 16.04上工作得很好。 路径：/etc/mysql/mysql.cnf

然后粘贴那个文件。

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

- Pedroe

---

它与其他答案有何不同？ - valignatev

它在[mysqld]下面。 - Adam Fowler

Answer 5

对我来说，这是一个权限问题。

输入：

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

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

尝试执行以下步骤，然后重新启动服务器：

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

它将允许MySQL访问该文件的读写权限。

- user2134041

Answer 6

我的问题是在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

- A G

Answer 7

在Linux Mint 18上，设置了sql-mode选项的默认配置文件位于以下位置：

/usr/my.cnf

相关行如下：

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

所以，你可以在那里进行设置。

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

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

并获取一个列表：

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

- Michal Przybylowicz

Answer 8

这个解决方案非常简单... 我查找了一段时间，结果发现你只需要编辑两个配置文件：

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

在这两个文件中，您都需要添加以下内容：

[mysqld]
...
sql_mode=NO_ENGINE_SUBSTITUTION

至少，这对于5.6.24-2+deb.sury.org~precise+2是有效的。

- lvniebelschuetz

---

我们只有一个cnf文件（没有其他文件），并且已经编辑了/etc/mysql/my.cnf。但是问题是，值应该是什么？存在竞争的值（请参见我在问题中提到的语法），对我们来说，它们都不起作用。您采用了哪种语法？ - Chadwick Meyer

谢谢，根据我原来的问题细节，我已经尝试过了，但是当重新启动mysql时它并没有生效。但这证实了这可能是正确的设置，并且一定有其他因素阻止这些设置生效。 - Chadwick Meyer

那么您已经在系统中搜索了其他my.cnf文件，这是唯一的一个吗？ - lvniebelschuetz

@Ivniebelschuetz 是的，这很疯狂。在每个位置、文件名搜索等方面都进行了多次搜索。而且在我的主要 /etc/mysql/my.cnf 文件中，这个定义只出现了一次。虽然有可能我的测试不正确，其中一个实际上确实起作用了，但我不这么认为。 - Chadwick Meyer

Answer 9

对我来说，sql-mode的两个键都可以使用。无论我使用哪个键，它们都可以起作用。

# dash no quotes
sql-mode=NO_ENGINE_SUBSTITUTION

或者

# underscore no quotes
sql_mode=NO_ENGINE_SUBSTITUTION

在my.ini文件中，两种方式都被接受，并没有什么区别，从我测试的结果来看。

实际上，真正有影响的是my.ini文件末尾缺少一个换行符。

因此，所有遇到这个问题或类似问题的人：确保文件末尾有一个空行！

本测试使用的是MySQL 5.7.27。

- Timo Schwarz

Answer 10

这也让我感到疯狂，直到我意识到关键字必须在[mysqld]而不是[mysql]段落中。

因此，对于10.3.22-MariaDB-1ubuntu1版本，我的解决方案是，在/etc/mysql/conf.d/mysql.cnf文件中进行如下设置：

[mysqld]
sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

- Rouletabille