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
• Status: Not a Bug
• Category: Shell Upgrade Checker
• Severity: S3 (Non-critical)
• Version: 5.7.23
• OS: Any
• Assigned to: MySQL Verification Team
• 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] MySQL Verification Team

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