upgrade from 2.05+ SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date

More
1 month 2 weeks ago #159283 by fdussurget
fdussurget created the topic: upgrade from 2.05+ SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date
Hi there,
I'm stuck when upgrading from 2.05+ to 2.06+ or 2.71 ...

Full log is down here :

Erreur non récupérable durant la mise à jour. Détails de cette erreur :
CDbCommand n'a pas pu exécuter la commande SQL : SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column '391372X194X2855' at row 644. The SQL statement executed was: ALTER TABLE `lime_survey_391372` CHANGE `token` `token` varchar(35) COLLATE 'utf8_bin'

Veuillez corriger cette erreur dans la base de données et recommencer.



LimeSurvey
Version 2.06+ Build 160129
Sommaire du rapport de profilage (Durée: 0.50617s, Memoire: 6,340KB)
Fonction Nb Total (s) Moy. (s) Min. (s) Max. (s)
system.db.CDbCommand.execute(ALTER TABLE `lime_survey_391372` CHANGE `token` `token` varchar(35) COLLATE 'utf8_bin') 1 0.02509 0.02509 0.02509 0.02509
system.db.CDbCommand.execute(ALTER TABLE `lime_tokens_685361` CHANGE `token` `token` varchar(35) COLLATE 'utf8_bin') 1 0.01314 0.01314 0.01314 0.01314
system.db.CDbCommand.execute(ALTER TABLE `lime_tokens_29419` CHANGE `token` `token` varchar(35) COLLATE 'utf8_bin') 1 0.01226 0.01226 0.01226 0.01226
system.db.CDbCommand.execute(ALTER TABLE `lime_survey_137142` CHANGE `token` `token` varchar(35) COLLATE 'utf8_bin') 1 0.00873 0.00873 0.00873 0.00873
system.db.CDbCommand.query(SHOW FULL COLUMNS FROM `lime_survey_611242`) 1 0.00721 0.00721 0.00721 0.00721

etc. etc.

Thank you for your help !

Please Log in to join the conversation.

More
1 month 2 weeks ago #159284 by LouisGac
LouisGac replied the topic: upgrade from 2.05+ SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date
That's a MySQL setting problem: NO_ZERO_DATE

dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Please Log in to join the conversation.

More
1 month 2 weeks ago #159285 by fdussurget
fdussurget replied the topic: upgrade from 2.05+ SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date
Thank you ... So I should disable this NO_ZERO_DATE mode ?

Please Log in to join the conversation.

More
1 month 2 weeks ago #159286 by LouisGac
LouisGac replied the topic: upgrade from 2.05+ SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date
yes, you should.

Please Log in to join the conversation.

More
1 month 2 weeks ago #159309 by fdussurget
fdussurget replied the topic: upgrade from 2.05+ SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date
Thanks a lot, it solved my issue.
I added this to my.cnf :
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

former value was :
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
The following user(s) said Thank You: LouisGac

Please Log in to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now
Join our Newsletter!