日期时间值不正确数据库错误编号：1292 [英] Incorrect datetime value Database Error Number: 1292

问题描述

日期时间值错误0000-00-00 00:00:00 +0000数据库错误号码：1292

有一个问题a与我的托管公司完成的服务器升级，我想知道发生了什么，所以我可以解决这个问题

我的服务器最近升级到服务器版本：5.6.17和我得到错误，说我的日期时间值不正确的地方？

似乎是添加+0000到结束datetime但我不知道为什么。这在5.5版本以前工作正常，但最近的升级影响了我的时间戳的工作

 错误号码：1292 
 
日期时间值不正确：'2014-04-02 08:49:43 +0000'for row'created'in row 1 
 
 INSERT INTO`activitylog`（`tablename`，`row `，`user_id`，`description`，`action`，`private`，`created`）VALUES（'user'，'1'，'1'，'People'，'Updated'，0，'2014-04 -02 08:49:43 +0000'）
  

如果我修改这个sql查询，工作？

它会影响我表格上任何类型的DATETIME。

类似的问题和现在什么解决方案是让这个工作。现在我是一个事情，我将不得不改变我所有的PHP函数来回应日期/时间，而不是我在查询字符串上调用NOW（）

解决方案

我发现在升级到MySQL 5.7后，即使我没有在查询中提供日期，这个错误在随机情况下开始。

这似乎是因为<$> 以前的版本的MySQL支持的日期，例如 0000-00-00 00:00:00 .4对 NO_ZERO_DATE 设置进行了一些更改。如果在使用较新的MySQL版本时仍然有旧数据，则会出现随机错误。

我需要执行这样的查询以重置所有零日期到另一个日期。

 ＃如果列支持NULL，请使用
 UPDATE表SET date_column = NULL WHERE date_column< ; '1000-01-01'; 
 
＃否则提供另一个默认日期
 UPDATE表SET date_column ='1970-01-01'WHERE date_column< '1000-01-01'; 
  

或者，您可以调整 NO_ZERO_DATE 设置，虽然注意文档说的是什么：

NO_ZERO_DATE 模式影响服务器是否允许0000-00-00作为有效日期。

• 如果未启用此模式，0000-00-00




• 如果启用此模式，则允许0000-00-00，插入将产生警告。 p>




• 如果启用此模式和严格模式，则不允许使用0000-00-00，并且插入会产生错误，除非IGNORE也一样。对于允许INSERT IGNORE 和 UPDATE IGNORE ，'0000-00-00'，插入将产生警告。 p>

自MySQL 5.7.4起， NO_ZERO_DATE 已弃用。在MySQL 5.7.4到5.7.7中， NO_ZERO_DATE 在显式命名时不执行任何操作。相反，其效果包含在严格SQL模式的影响中。在MySQL 5.7.8和更高版本中， NO_ZERO_DATE 在显式命名时不会有影响，并且不是严格模式的一部分，如MySQL 5.7.4之前。但是，它应该与严格模式结合使用，并且默认情况下启用。如果启用 NO_ZERO_DATE 而不启用严格模式，则会发生警告，反之亦然。有关更多的讨论，请参阅MySQL 5.7中的SQL模式更改。

由于 NO_ZERO_DATE 已弃用，未来的MySQL版本作为单独的模式名称，其效果包括在严格SQL模式的效果中。

从 http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html #sqlmode_no_zero_date

Incorrect datetime value 0000-00-00 00:00:00 +0000 Database Error Number: 1292

Hi Everyone I'm having a problem a with a server upgrade done by my hosting company and I'm trying to understand what is occurring so i can fix the problem

My sever has recently been upgraded to Server version: 5.6.17 and I'm getting errors all over the place saying my datetime value is incorrect?

It seem to be add +0000 to the end of the datetime but I'm not sure why. This used to work perfectly fine on 5.5 but a recent upgrade has affected how my timestamps work

Error Number: 1292

Incorrect datetime value: '2014-04-02 08:49:43 +0000' for column 'created' at row 1

INSERT INTO `activitylog` (`tablename`, `row`, `user_id`, `description`, `action`, `private`,`created`) VALUES ('user', '1', '1', 'People', 'Updated', 0, '2014-04-02 08:49:43 +0000')

If I modify this sql query without +0000 it works?

It affects anything that is a type of DATETIME on my table.

Has anyone else had a similar problem and now what the solution is to get this to work. At the moment I'm thing I will have to change all my PHP functions to echo the Date/Time rather than me calling NOW() on the query string

解决方案

I discovered after upgrading to MySQL 5.7 that this error started occurring in random situations, even when I wasn't supplying a date in the query.

This appears to be because previous versions of MySQL supported dates like 0000-00-00 00:00:00 (by default) however 5.7.4 introduced some changes to the NO_ZERO_DATE setting. If you still have old data present when using a newer MySQL version, then random errors may crop up.

I needed to perform a query like this to reset all the zero dates to another date.

# If the columns supports NULL, use that
UPDATE table SET date_column = NULL WHERE date_column < '1000-01-01';

# Otherwise supply another default date
UPDATE table SET date_column = '1970-01-01' WHERE date_column < '1000-01-01';

Alternatively, you may be able to adjust the NO_ZERO_DATE setting, although note what the docs say about it:

The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

• If this mode is not enabled, '0000-00-00' is permitted and inserts produce no warning.

• If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.

• If this mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

As of MySQL 5.7.4, NO_ZERO_DATE is deprecated. In MySQL 5.7.4 through 5.7.7, NO_ZERO_DATE does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, NO_ZERO_DATE does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_DATE is enabled without also enabling strict mode or vice versa. For additional discussion, see SQL Mode Changes in MySQL 5.7.

Because NO_ZERO_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

From http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

这篇关于日期时间值不正确数据库错误编号：1292的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！