ER_TRUNCATED_WRONG_VALUE: 日期时间值不正确

Question

ER_TRUNCATED_WRONG_VALUE: 日期时间值不正确

20

最近我完成了一个研究项目的申请，一切都很好，现在只剩下将应用程序投入生产中。

我正在使用Node.js和MySQL（我知道，我们不喜欢这个组合，但总得有人试试）。我有一个 socket，它会向 MySQL 的 Message 表中添加聊天消息，其中包含文本、日期时间等。日期时间设置为 new Date()。

现在，当我将应用程序放在生产服务器上（重新安装依赖项、mysql 等）时，写消息时突然出现了以下错误：

Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2017-06-01T09:45:06.253Z' for column 'message_datetime' at row 1

在开发中我没有遇到这个错误，于是我问自己是否下载了不同版本的mysql... 结果我确实下载了：

开发环境:

mysql  Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (i686) using readline 6.3

生产

mysql Ver 14.14 Distrib 5.7.18，用 EditLine 包装器在 Linux (x86_64) 上运行

消息表看起来像这样：

CREATE TABLE message ( message_id INT AUTO_INCREMENT, message_sender_id VARCHAR(80) NOT NULL, message_datetime DATETIME, message_text TEXT, message_chat_id INT NOT NULL, PRIMARY KEY(message_id), FOREIGN KEY(message_chat_id) REFERENCES chat(id) ON DELETE CASCADE ) ENGINE=InnoDB;

那么有什么不同之处呢？为什么 'yyyy-mm-ddThh:mm:ss.%%%Z' 突然不是有效的日期格式了？我该如何解决这个问题？

感谢任何帮助！

- Jesper

MySQL以“YYYY-MM-DD HH:MM:SS”格式检索和显示DATETIME值。支持的范围是“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。 - CBroe

1

我在开发过程中没有遇到那个错误，这可能更多是服务器配置的问题（关于错误容忍度），而不是它实际上可以将“有效”的日期插入数据库。 - CBroe

@CBroe，我甚至不知道有不同的错误容限。我使用Vagrant作为我的开发环境，但是在Digital Ocean上部署了生产服务器。也许生产服务器中的某种更新降低了错误容限。 - Jesper

感谢您发布这个问题。我曾经想过发布一个类似的问题，关于JavaScript / Angular中的“Z”被MySQL拒绝，而且已经做好了说一些像“（我知道，我们不喜欢它，但有人必须尝试它）”这样的话的准备。很高兴看到那里也有这个问题。 :) - reor

2个回答

3

与错误相关的问题，@ Jesper 给出的相同答案同样适用。

ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''

即我的。

select @@GLOBAL.sql_mode; -- and
select @@SESSION.sql_mode;

提供

STRICT_TRANS_TABLES，ERROR_FOR_DIVISION_BY_ZERO，NO_AUTO_CREATE_USER，NO_ENGINE_SUBSTITUTION

当我更新它们时

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

我的MySQL插入操作执行得很顺利

这个错误是由于严格的SQL模式导致的。所以仅仅将sql_mode中的STRICT_TRANS_TABLES移除就足够了。例如：

SET SESSION sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

- Adeel Raza Azeemi

谢谢，这个对我很有用。在从SQLite导出到MySQL时使用Knex，我添加了这个knex.raw()

knex.raw(
          `SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'; SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';`
        );

- jasenmichael

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jesper · Accepted Answer

显然，日期时间值不是一个有效的MySQL Datetime。但有一种方法可以修改服务器SQL模式来解决这个问题。

由于某些原因，在我的开发服务器上，MySQL默认的模式配置被完全删除了。因此，我可以随意插入日期时间值。

mysql> select @@sql_mode;
    +------------+
    | @@sql_mode |
    +------------+
    |            |
    +------------+
    1 row in set (0.00 sec)

另一方面，生产服务器有许多限制，告诉mysql服务器接受哪些日期时间格式。

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

这不是一个安全的方法，但我将MySQL的限制模式更改为no_engine_substitution，然后，哇，一切都像魔术一样工作（几乎）。您需要更改此设置的全局和会话模式才能使其正常工作。

标准SQL模式是“NO_ENGINE_SUBSTITUTION”，因此我们将该模式设为该模式。还有其他一些模式可以添加：

SET GLOBAL sql_mode = '<mode>';
SET SESSION sql_mode = '<mode>';

现在GLOBAL和SESSION模式应该设置为NO_ENGINE_SUBSTITUTION

mysql> SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
mysql> SELECT @@SESSION.sql_mode;
+------------------------+
| @@SESSION.sql_mode     |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
mysql> SELECT @@GLOBAL.sql_mode;
+------------------------+
| @@GLOBAL.sql_mode      |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)