Bug #92804 mysql_upgrade not validate sql_mode
Submitted: 16 Oct 2018 19:48 Modified: 19 Oct 2018 15:09
Reporter: José Demerval Zechel Junior Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Shell Upgrade Checker Severity:S3 (Non-critical)
Version:5.7.23 OS:Any
Assigned to: Bogdan Kecman CPU Architecture:Any

[16 Oct 2018 19:48] José Demerval Zechel Junior
Description:
mysql_upgrade is not respecting the current sql_mode for the instance.
The sql_mode NO_ZERO_IN_DATE and NO_ZERO_DATE are not being respected during the mysql_upgrade execution with tables using the datetime datatype and default value to '0000-00-00 00:00:00'.

$ mysql_upgrade --user=root --password=*** --verbose
...
test.test_datetime                                 OK
Upgrade process completed successfully.
Checking if update is needed.

mysql> alter table test_datetime force;
ERROR 1067 (42000): Invalid default value for 't'

mysql > insert into test_datetime (i) values (null);
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 't' at row 1

How to repeat:
- Create a table on MySQL 5.6.27 with a column datetime and default value as '0000-00-00 00:00:00'
 
CREATE TABLE `test_datetime` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `t` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`i`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

insert into test_datetime VALUES (NULL, time(now()));
insert into test_datetime (i) values (null);

- Do an in-place upgrade to MySQL 5.7.23, then:

$ mysql_upgrade --user=root --password=*** --verbose

mysql> alter table test_datetime force;

mysql> insert into test_datetime (i) values (null);

Suggested fix:
mysql_upgrade needs to validate the current sql_mode configuration and raise an error about the divergences during the execution
[19 Oct 2018 15:09] Bogdan Kecman
Hi,

I don't agree this is a bug. mysql_upgrade checks table versions and that's it, the compatibility of the configuration, your queries and other stuff are not in the scope of mysql_upgrade. I personally would rather we ditch mysql_upgrade completely and add this functionality directly into mysqld but.. 

kind regards
Bogdan