Laravel SQLSTATE [22007]:无效的日期时间格式:1292错误的日期时间值:'updated_at'列的'2019-03-10 02:00:39'(夏令时?) [英] Laravel SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2019-03-10 02:00:39' for column 'updated_at' (daylight savings?)

问题描述

我正在使用一个队列工作器来处理Laravel应用程序，它刚开始失败并向日志中抛出了恒定的异常流.这是其中之一:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2019-03-10 02:00:39' for column 'updated_at' at row 1 (SQL: update `videos` set `updated_at` = 2019-03-10 02:00:39 where `id` = 30860) 

一些注意事项:

• 时间戳出现为正确的格式
• 该列是使用InnoDB的MySQL TIMESTAMP类型(可空)
• updated_at字段由Laravel默认值管理，日期格式未自定义
• 我没有使用任何日期转换器，也没有使用任何会在保存之前更改值的东西
• 此代码已运行了数周，没有任何问题
• 最近没有代码更改
• 我做了git checkout .和composer install以确保没有任何库文件已被更改或损坏(未更改)
• 该错误首次出现在本地时间(中部时间)晚上8点(UTC)

明尼苏达州的夏令时2019将在凌晨2:00开始 3月10日，星期日

-Google

我在Google上能找到的所有东西都与某人使用明显错误的时间格式有关，所以这并没有帮助我弄清楚.

我觉得应该从夏令时开始，在世界标准时间02:00开始的错误之间有太多的重合，但是我不知道为什么在时间戳记时会引发此错误由框架管理并转换为UTC(没有夏令时).

MySQL 5.5.45
Laravel 5.7.28
nesbot/carbon 1.36.2
PHP 7.2.7

config/database.php:

'mysql' => [

    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

什么可能导致日期值被识别为无效?

我已经确定这绝对是夏令时错误，因为时钟向前跳1小时，02:00至03:00之间的时间无效.我不明白的是为什么设置为UTC的应用程序上的TIMESTAMP字段会引发错误?夏时制如何影响UTC?当时间戳由框架(Laravel + Carbon)管理时，我该怎么办?

解决方案

我发现问题出在我的MySQL服务器的time_zone设置被设置为SYSTEM(并且我的系统是美国中央). Laravel提供了已经转换为UTC的时间戳，但是由于time_zone设置，我的数据库将其解释为美国中部.实际上，MySQL内部再次将时间 转换为 real UTC Unix时间戳表示，即使在每个查询中它们似乎已经是UTC(我知道是正确的). /p>

因此，在当地时间20:00:39(8PM)，我的"UTC"时间戳是02:00:39. MySQL将这些时间解释为美国中部时间，并且由于该时间在02:00到03:00之间(这是美国中部的时钟向前跳的时间)，因此该时间无效.

Laravel应用程序的最佳解决方案是强制每个连接使用+00:00时区(或您在config/app.php中设置为应用程序时区的任何值)，这样就不会发生二次转换.这个可以 在config/database.php中完成:

'mysql' => [
    // ...

    'timezone'  => '+00:00'
],

如果数据库服务器的配置时区与Laravel应用程序不同，则不必担心.另一个选择是更改数据库的time_zone设置，但是如果您更改主机或由于任何原因需要重建服务器(并且又没有正确地配置时区)，仍然会冒重复出现该错误的风险.

I'm working on a Laravel application with a queue worker, and it just started failing and throwing a constant stream of exceptions to the log. Here is one of them:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2019-03-10 02:00:39' for column 'updated_at' at row 1 (SQL: update `videos` set `updated_at` = 2019-03-10 02:00:39 where `id` = 30860) 

A few things to note:

• The timestamp appears to be the correct format
• The column is a MySQL TIMESTAMP type (nullable), using InnoDB
• The updated_at field is managed by Laravel defaults, with nothing custom happening for the date formatting
• I'm not using any date mutators, or anything that would be changing the value before save
• This code has been running for weeks without any issue
• No code has changed recently
• I did a git checkout . and composer install to make sure none of the library files had changed or been corrupted somehow (no change)
• The error first appeared at 2019-03-10 02:00:39 (UTC), which is 8pm local (Central) time

Daylight saving time 2019 in Minnesota will begin at 2:00 AM on Sunday, March 10

-- Google

Everything I can find on google is related to somebody using a blatantly wrong time format, so that hasn't helped me figure it out.

I feel like there is too much of a coincidence between the errors starting at 02:00 UTC on the day when daylight savings is supposed to begin, but I don't have a clue why it would be throwing this error when timestamps are managed by the framework and converted to UTC (which doesn't have daylight savings).

MySQL 5.5.45
Laravel 5.7.28
nesbot/carbon 1.36.2
PHP 7.2.7

config/database.php:

'mysql' => [

    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

What could be causing the date value to be recognized as invalid?

Edit: I've determined this is definitely a daylight savings error, as clocks skip forward 1 hour and times between 02:00 and 03:00 are invalid. What I don't understand is why a TIMESTAMP field on an application set to UTC would be throwing an error? How can UTC be affected by daylight savings, and what can I do to prevent this when the timestamp is managed by the framework (Laravel+Carbon)?

解决方案

I figured out that the problem was due to my MySQL server's time_zone setting being set to SYSTEM (and my system is US Central). Laravel is providing timestamps that were already converted to UTC, but my database is interpreting them as US Central due to the time_zone setting. The times are actually being converted again internally by MySQL to a real UTC unix timestamp representation, even though they appear to be UTC already in every query (I know right).

Because of this, at 20:00:39 (8PM) local time my "UTC" timestamps are 02:00:39. MySQL interprets these times as US Central time, and because the time is between 02:00 and 03:00 (which is when clocks skip forward for US Central), the time is invalid.

The best solution for a Laravel application is to force every connection to use a +00:00 timezone (or whatever you have set as the application timezone in config/app.php) so there will not be a secondary conversion happening. This can be done in config/database.php:

'mysql' => [
    // ...

    'timezone'  => '+00:00'
],

This way you are not at the mercy of your database server if it has a configured timezone that is different from your Laravel application. The other option is to change the database's time_zone setting, but then you still risk the bug recurring if you ever change hosts or need to rebuild the server for any reason (and don't configure the timezone correctly again).

这篇关于Laravel SQLSTATE [22007]:无效的日期时间格式:1292错误的日期时间值:'updated_at'列的'2019-03-10 02:00:39'(夏令时?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！