MySQL/MariaDB 配置警告以抛出错误

2022-01-15 00:00:00 warnings xampp mariadb mysql

我需要 MySQL/MariaDB 来抛出错误消息而不是警告消息.

原因: 在我的开发环境中，SQL/PHP 中的警告被忽略(SQL 和 PHP 都继续处理)，因此我在测试时无法检测到任何问题.然而，在生产中，所有警告都会使查询崩溃，从而终止 API，并返回失败".给用户的消息.

Reason: In my development environment, warnings are ignored in SQL/PHP (and both SQL and PHP continue processing), therefore I can't detect any issues when testing. In production, however, all warnings crash the query, thus terminating the API, and returning a "failure" message to users.

这是一个非常糟糕的结果，在过去引起了很多头痛.

这是一个非常具体的场景，它会导致很多问题:

我有一个表testtable".具有两列pk"和bRequiredBoolean"，其中 pk 是主键(因此自动递增)并且 bRequiredBoolean 没有默认值:

I have a table "testtable" with two columns "pk" and "bRequiredBoolean", where pk is the primary key (thus auto increments) and bRequiredBoolean HAS NO DEFAULT VALUE:

CREATE TABLE `test`.`testtable` ( `pk` INT NOT NULL AUTO_INCREMENT , `bRequiredBoolean` BOOLEAN NOT NULL , PRIMARY KEY (`pk`)) ENGINE = InnoDB;

在我的开发服务器中，我可以运行以下查询:

In my development server, I can run the following query:

INSERT INTO `testtable`() VALUES ()

并收到以下警告:

Warning: #1364 Field 'bRequiredBoolean' doesn't have a default value

在我的开发服务器中，条目已插入，API 继续执行.

In my development server, the entry IS INSERTED and the API continues executing.

在我的生产服务器中，条目 ISN'T INSERTED 并且 API 崩溃.

In my production server, the entry ISN'T INSERTED and the API crashes.

我知道开发/测试服务器应该相同以防止出现此类问题，但我目前没有资金购买额外的许可证，所以我想要一个替代解决方案来抛出错误消息而不是警告捕捉上面提到的场景.

重要提示:我不想在每个查询中插入任何额外的代码，我更愿意编辑一些配置文件.例如，我不想将以下代码添加到每个单独的查询中:

Important note: I don't want to insert any extra code within each query, I'd prefer to edit some configuration file. For instance, I don't want to add the following code TO EVERY SINGLE QUERY:

SHOW COUNT(*) WARNINGS

我宁愿编辑一些配置文件，这样它总是会抛出错误，即使我直接通过 GUI 进行临时查询.

I'd rather edit some configuration file so it will always throw an error, even if I do ad-hoc queries directly through the GUI.

我发现了其他类似的问题，但没有人回复这些帖子:

I found other similar questions, but nobody has responded to those threads:

MariaDB 显示警告而不是错误

在mysql警告pdo上抛出错误

非常感谢任何帮助.

推荐答案

感谢 Barmar 的评论，我找到了解决问题的方法.

Thanks to Barmar's comment, I found the solution to my problem.

您可以通过以下方式检查您的 SQL_Mode:

You can check your SQL_Mode via:

SELECT @@SQL_MODE;

显然，有一个 SQL_Mode 设置专门针对称为 Traditional"的所有警告引发错误

Apparently, there is a SQL_Mode setting that specifically throws errors for all warnings called "Traditional"

您可以查看官方 MySQL 网站以获取有关 SQL_Mode 值的更多信息:https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-full

You can view the official mySQL website for more info regarding SQL_Mode values: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-full

所以我运行了以下查询:

So I ran the following query:

SET GLOBAL sql_mode = 'TRADITIONAL';

运行查询后，我执行了与之前相同的查询:

After running the query, I executed the same query as before:

INSERT INTO `testtable`() VALUES ()

并收到以下错误:

Error
SQL query: Copy


INSERT INTO `testtable`() VALUES ();
MySQL said: Documentation

#1364 - Field 'bRequiredBoolean' doesn't have a default value

这正是我所需要的.非常感谢@Barmar！

相关文章