[MDEV-10068] Invalid datetime format: 1292 Incorrect datetime value: '2011-03-27 02:47:26' Created: 2016-05-14  Updated: 2022-10-04  Resolved: 2016-05-31

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.1.14
Fix Version/s: N/A

Type: Bug Priority: Blocker
Reporter: Abdelakarim Mateos Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: datetime, debian
Environment:

Debian Jessie

Issue Links:
Relates
relates to MDEV-29696 uhelpful error message for timedate i... Open

 Description   

After run a seeder for create 75000 users, after several minutes (almost 7000 users inserts on table with any problem,my app get a exception below.

[Illuminate\Database\QueryException]
 
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2008-03-30 02:56:12' for column 'created_at' at row 1 (S
 
  QL: insert into `users` (`type`, `name`, `email`, `nick`, `password`, `vat`, `address`, `zipcode`, `city`, `state`, `country`, `cr
 
  eated_at`, `updated_at`) values (customer, Adrian Santos, qpuig@live.com, rzavala12, y$Veqd2mkgRHtl6VKtYBV0yey77tVLo34.6THXqhtGRwg
 
  /Ea73/FhlK, 02058805Y, Plaça Abril, 85, 96º D, 44824, Vargas de San Pedro, 57431, San Roig, Huesca, ES, 2008-03-30 02:56:12, 2008-
 
  03-30 02:56:12))

Datetime value it's correct and same for others 7000, 8000 users create with before INSERTS

Try several options such:

MariaDB [(none)]> select @@GLOBAL.sql_mode;
 
+------------------------+
 
| @@GLOBAL.sql_mode      |
 
+------------------------+
 
| NO_ENGINE_SUBSTITUTION |
 
+------------------------+



 Comments   
Comment by Sergei Golubchik [ 2016-05-14 ]

This is probably a DST issue. Depending on your time zone, 2011-03-27 02:47:26 might, indeed, be invalid. If the change to the Summer time happened at 2011-03-27 at 2am, then there was no 02:47:26 at that date, after 2011-03-27 01:59:590 the next second was 2011-03-27 3:00:00.

Comment by Abdelakarim Mateos [ 2016-05-15 ]

A lot of thanks.
I've verified hourly of change datetime on spain at year 2011 and it's correct. 27 March

I have also verified that there is no user or lot within two hours of arc between the change.
Of the 25 records that had, no coincided with the time change.

Comment by Daniel Black [ 2016-05-31 ]

closing as not a bug. If you think this is still a bug please reopen with a description of the expected behaviour.

Comment by MAtteo Valsasna [ 2022-10-04 ]

I just spent 2 hours troubleshooting the same issue.

I was importing data from a mariadb set with timezone 00:00 to one set to local timezone, and the import failed on the DST "non-existing hour".
then this error also meant that all my import was wrong and had to be redone after setting the correct timezone on the target mariadb.

I agree that this is not a bug per se, but the error message is really not helpful, because the date format is indeed correct, and the error is that that specific hour does not exist in the current TZ.

I opened a new bug MDEV-29696 to request a more helpful error message

Generated at Thu Feb 08 07:39:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.