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

问题描述

我正在使用队列工作器开发 Laravel 应用程序，它刚刚开始失败并向日志抛出持续的异常流.这是其中之一:

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) 

注意事项:

• 时间戳似乎是正确的格式
• 该列是 MySQL TIMESTAMP 类型(可为空)，使用 InnoDB
• updated_at 字段由 Laravel 默认管理，日期格式没有任何自定义
• 我没有使用任何日期修改器，也没有使用任何会在保存前更改值的东西
• 此代码已运行数周没有任何问题
• 最近没有更改代码
• 我做了一个 git checkout . 和 composer install 以确保没有任何库文件被更改或以某种方式损坏(没有更改)
• 该错误首次出现在 2019-03-10 02:00:39 (UTC)，即当地时间晚上 8 点

• 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

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

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

-- 谷歌

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

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.

我觉得在夏令时应该在美国中部时间 02:00 开始的同一天，从 02:00 UTC 开始的错误之间有太多的巧合，但我不知道为什么当时间戳由框架管理并在保存之前转换为 UTC 时会抛出此错误(并且 UTC 不会因夏令时而改变).

I feel like there is too much of a coincidence between the errors starting at 02:00 UTC on the same day when daylight savings is supposed to begin at 02:00 US Central Time, 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 before saving (and UTC doesn't change for daylight savings).

MySQL 5.5.45
Laravel 5.7.28
nesbot/碳 1.36.2
PHP 7.2.7

MySQL 5.5.45
Laravel 5.7.28
nesbot/carbon 1.36.2
PHP 7.2.7

config/database.php:

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?

我已经确定这绝对是夏令时错误，因为时钟向前跳过 1 小时，并且在美国中部时间 02:00 和 03:00 之间的时间无效.我不明白的是为什么设置为 UTC 的应用程序上的 TIMESTAMP 字段会抛出错误?UTC 如何受到夏令时的影响，当时间戳由框架 (Laravel+Carbon) 管理时，我可以做些什么来防止这种情况发生?

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 in US Central Time. 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)?

推荐答案

我发现问题是由于我的 MySQL 服务器的 time_zone 设置被设置为 SYSTEM (我的系统是美国中央).Laravel 提供的时间戳已转换为 UTC，但由于 time_zone 设置，我的数据库将它们解释为 US Central.时间实际上在 MySQL 内部再次转换为真实的"时间.UTC unix 时间戳表示(这将是不正确的，因为它被时区偏移)，即使它们在每个查询中似乎已经是 UTC，因为它们也会再次转换回 US Central 以进行读取(我知道是对的).

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 (which will be incorrect because it's offset by the time zone), even though they appear to be UTC already in every query because they also get converted back to US Central again for reading (I know right).

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

Because of this, at 20:00:39 (8:00 PM) local time my Laravel 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.

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

The best solution for a Laravel application is to force every database 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'
],

这样，如果您的数据库服务器配置的时区与您的 Laravel 应用程序不同，那么您就不会受到数据库服务器的支配.另一种选择是更改数据库的 time_zone 设置，但是如果您更改主机或出于任何原因需要重建服务器(并且不要再次正确配置时区)，您仍然面临再次出现错误的风险)，或影响服务器上的其他数据库.

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), or affecting other databases on the server.

重要说明:由于所有以前的时间戳都由 MySQL 在内部从配置的时区偏移到 UTC unix 时间戳(这也是错误的，因为记录已经是 UTC)，因此可能需要运行数据迁移更正旧的时间戳.我没有进一步调查，因为对于我的应用程序，旧时间戳是否错误几个小时并不重要.

Important note: Since all of the previous timestamps were being offset internally by MySQL from the configured time zone to UTC unix timestamps (which, again, were wrong because the records were already UTC) it might be necessary to run a data migration to correct the old timestamps. I haven't investigated further because for my application it doesn't matter if the old timestamps were wrong by a few hours.

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