#101 Mysql deployment error 1067 (42000) (solved)

[martinbrait]

I got Deployment error of the webcollab 3.46 with mysql
Serveur Version : 8.0.17 - MySQL
ERROR 1067 (42000)

Diagnostic :
troubles caused by default values for timestamp columns ?

The problem is because of sql_modes. Please check your current sql_modes by command:

Solution :
show variables like 'sql_mode' ;

1) remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" to make it work.
This is the default sql_mode in mysql new versions.

2) You can set sql_mode globally as root by command, in SQL console :
set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

3) Simply, before you run any statements put this in the first line:
SET sql_mode = '';

[Andrew Simpson]

Thanks for the bug report.
Are you saying that removing sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" solved the problem?

If that fixes the problem, I guess the question is why there are zeros in the date.

[martinbrait]

Yes Andrew.
It solved the problem.

About de zeros in the date, I didn't do my best to look after the problem,
just try a recommandation, from stackoverflow.
Does the trouble happen with recent versions of mysql only ?
Is there something to do with formatted to local dates (France),
for my installation of mysql server ??

Le jeu. 12 sept. 2019 à 09:34, Andrew Simpson andrewsimpson@users.sourceforge.net a écrit :

Thanks for the bug report.
Are you saying that removing sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE"
solved the problem?

If that fixes the problem, I guess the question is why there are zeros in
the date.

---

• [bugs:#101] https://sourceforge.net/p/webcollab/bugs/101/ Mysql
  deployment error 1067 (42000) (solved)*

Status: open
Group: v1.0_(example)
Created: Wed Sep 11, 2019 07:32 PM UTC by martinbrait
Last Updated: Wed Sep 11, 2019 07:32 PM UTC
Owner: nobody

I got Deployment error of the webcollab 3.46 with mysql
Serveur Version : 8.0.17 - MySQL
ERROR 1067 (42000)

Diagnostic :
troubles caused by default values for timestamp columns ?

The problem is because of sql_modes. Please check your current sql_modes
by command:

Solution :
show variables like 'sql_mode' ;

1) remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" to make it work.
This is the default sql_mode in mysql new versions.

2) You can set sql_mode globally as root by command, in SQL console :
set global sql_mode =
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

3) Simply, before you run any statements put this in the first line:
SET sql_mode = '';

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/webcollab/bugs/101/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[Andrew Simpson]

The real question is why zero dates are being passed to the database.

This code in time.php is intended to stop zero dates being passed to the database.

  //check for valid calendar date
  if( ! checkdate($month, $day, $year ) ) {
    warning($lang['invalid_date'], sprintf($lang['invalid_date_sprt'], safe_data($year.'-'.$month_array[$month ].'-'.$day ) ) );
  }

If the date is valid (non zero) the value is then formatted for the database:

 //format is 2004-08-02 00:00:00
    // The hour is set to 2.00am (instead of 12.00am) to accommodate config changes of +/-1 hour with daylight saving
  return sprintf('%04d-%02d-%02d 02:00:00', $year, $month, $day );