3

System Local PHP 5.6.19 App made with Laravel 5.0, using fzaninotto/Faker as seeder Mysql remote

Remote MySQL Debian 8 with MariaDB 10.1.13-MariaDB-1~jessie

Running seeder 

php artisan migrate:reset; php artisan migrate; php artisan db:seed

After seeder add an indeterminate number of users (different each time the reproduction of the error) get error:

[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))

Well.. date time it's 2008-03-30 02:56:12

A example of latest 3 users created with the seeder before get error:

INSERT INTO `users` (`id`,`type`,`email`,`nick`,`password`,`name`,`vat`,`address`,`zipcode`,`city`,`state`,`country`,`totals`,`remember_token`,`created_at`,`updated_at`) VALUES (15053,'buyer','asauceda@live.com','celia657','$2y$10$P4SOJOAQHsC8W7ctJ5v1F.8loccYzjAW95ILwWZ1RzObNs01hKjr2','Bruno Aguado','71514876X','Paseo Corral, 1, Entre suelo 0º, 63362, La Saiz','58061','Navarrete de Ulla','Cádiz','ES',NULL,NULL,'2012-09-13 06:01:51','2012-09-13 06:01:51');
INSERT INTO `users` (`id`,`type`,`email`,`nick`,`password`,`name`,`vat`,`address`,`zipcode`,`city`,`state`,`country`,`totals`,`remember_token`,`created_at`,`updated_at`) VALUES (15052,'buyer','ana.serna@live.com','cristian126','$2y$10$fe2Hdh4p0bttNfo9uzwSe.L5OOzPwsm5p8g5.YhW5PAoxKcSObDta','Enrique Rivero','06604287K','Ronda Saul, 321, 8º C, 76264, San Álvarez de Arriba','11402','El Roybal Baja','Zamora','ES',NULL,NULL,'2007-11-14 16:39:41','2007-11-14 16:39:41');
INSERT INTO `users` (`id`,`type`,`email`,`nick`,`password`,`name`,`vat`,`address`,`zipcode`,`city`,`state`,`country`,`totals`,`remember_token`,`created_at`,`updated_at`) VALUES (15051,'buyer','iluna@terra.com','aalmanza21','$2y$10$EA.NhWHuT8.nGoZjD18hqOKatHH3MbboY2DjZywlceH8K60spAe5m','Miriam Riojas','24055470T','Calle Soriano, 0, 75º A, 29744, L\' Montenegro','12236','As Martínez','Illes Balears','ES',NULL,NULL,'2013-03-25 23:15:50','2013-03-25 23:15:50');
INSERT INTO `users` (`id`,`type`,`email`,`nick`,`password`,`name`,`vat`,`address`,`zipcode`,`city`,`state`,`country`,`totals`,`remember_token`,`created_at`,`updated_at`) VALUES (15050,'buyer','qsalgado@hotmail.com','mateo26','$2y$10$6.lFRsqEjXb1kdZOhAX7Cu1Xecgu1ZvAkhaADKcfSpEi6ODQM9nRK','Alonso Núñez','50161945A','Camino Ainara, 78, 65º A, 17155, L\' Ayala del Pozo','47226','Román de las Torres','Las Palmas','ES',NULL,NULL,'2016-05-12 11:00:01','2016-05-12 11:00:01');

Read some information, such MYSQL incorrect DATETIME format, How to make sql-mode=“NO_ENGINE_SUBSTITUTION” permanent in MySQL my.cnf and verify mysql variables for this question.

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

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

+------------------------+
| @@SESSION.sql_mode     |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)
Community
  • 1
  • 1
abkrim
  • 3,512
  • 7
  • 43
  • 69

1 Answers1

10

The question is simple. It is a problem concerning the "Daylight Saving Time"

By consulting the table for Daylight Saving Time Spain, and that date is the day indeed 27/03/2011 at 02:00 PM. Therefore, any date is a day that contains a time, between 02:00 and 02:59 will be a failure, because the clock is moved forward from 02:00 to 02:59.

Kirk Beard
  • 9,569
  • 12
  • 43
  • 47
abkrim
  • 3,512
  • 7
  • 43
  • 69
  • Columns (`created_at` and `updated_at`) are of type TIMESTAMP? With the data type DATETIME this does not happen. – wchiquito May 21 '16 at 16:28
  • @wchiquito that's it's not question. Question it's USE DATETIME.Apreciate your comments. Also if read carefully, column has dattime format. Erro show. And yes, happen. Read my solution, and try same insert., for reproduce problem. – abkrim May 21 '16 at 16:49
  • I can't reproduce the problem with the data type `DATETIME`. With `TIMESTAMP` expected error occurs. – wchiquito May 21 '16 at 17:53
  • `TIMESTAMP` is stored as UTC. Converting from "02:56" is impossible for one hour per year, hence `TIMESTAMP` appears to fail. In reality, it is "bad input" because "02:56" does not exist, just as "Feb 29" does not exist in most years. Also, leap seconds may cause trouble. – Rick James May 25 '16 at 23:51
  • 2
    I had trouble fully understanding the provided answer at first, but @abkrim is correct. As a summary, Faker is not taking daylight savings time into account, so it may provide times that may not exist. For example, in 2016, in USA, DST started on March 13, so at 2am, the clocks moved to 3am. Thus, "2016-03-13 02:01:00" through "2016-03-13 02:59:00" are invalid timestamps because the times never existed. – JamieHoward Jan 14 '17 at 13:56