Introduction to the

In the course of normal project development, if MySQL is upgraded from 5.6 to 5.7. When considering the impact of a database version upgrade as a DBA, there are a few things to keep in mind:

  1. sql_modeDefault value changes
  2. optimizer_switchThe change of the value
  3. The upgrade of the standby database affects the active/standby replication

In this article, we will focus on the error caused by the default SQL_mode value after MySQL upgrade to version 5.7 and the corresponding solution.

Case 1: ONLY_FULL_GROUP_BY

Problem description

After the MySQL version is upgraded from 5.6 to 5.7, some SQL execution errors are reported as follows:

ERROR 1055 (42000): Expression #3 of XXXXXX list is not in GROUP BY clause and contains nonaggregated column ‘XXXXX.XXXXXX’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Copy the code

The default value of SQL_mode is ONLY_FULL_GROUP_BY. The default value of SQL_mode is ONLY_FULL_GROUP_BY. For SQL queries that use GROUP BY, do not allow SELECT columns that do not appear in GROUP BY. That is, SELECT columns must appear in GROUP BY, use aggregate functions, or have unique attributes.

The solution

  • Solution 1 (not recommended) : Change version 5.7sql_modeValue,ONLY_FULL_GROUP_BYTo get rid ofONLY_FULL_GROUP_BYIs to strengthen the SQL specification, its purpose is to make SQL query results more consistent with the specification, more accurate.

If ONLY_FULL_GROUP_BY is not restricted, the following SQL can be executed: SELECT a,b,c FROM t GROUP BY a. SQL groups groups by field A. If a field A corresponds to multiple B or C values, the B and C values in the query result are uncertain.

  • Scheme two: for non-conformityONLY_FULL_GROUP_BYRestricted field, add unique index
  • Plan 3: rewrite SQL, write SQL according to the specification
  • Solution 4: Use ANY_VALUE()ONLY_FULL_GROUP_BYThe ANY_VALUE() function is used for the fields that MySQL skipsONLY_FULL_GROUP_BYdetection
mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by
mysql>SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
Copy the code

Case 2: NO_ZERO_DATE & NO_ZERO_IN_DATE & time_zone

Problem description

Misalignment stage one

SQL > alter table create (‘ table ‘);

mysql> CREATE TABLE `t_manager` ( ..... -> 'CREATE_DATETIME' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time ', -> 'MODIFIER' varchar(32) DEFAULT NULL COMMENT 'update ', -> 'MODIFY_DATETIME' TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT 'change time ', -> 'IS_DELETED' bit(1) DEFAULT b'0' COMMENT ' IS_ENABLE 'bit(1) DEFAULT b'1' COMMENT' enable '0: enable ', ->' IS_ENABLE 'bit(1) DEFAULT b'1' COMMENT' enable '0: enable ', -> PRIMARY KEY (`CACHE_ID`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'Copy the code

Error: The default value of MODIFY_DATETIME is invalid. I checked the default SQL_mode value in 5.7, considering I just upgraded from 5.6 to 5.7. It found two options that might have an impact:

  • NO_ZERO_DATE: Time field inserted into MySQL. Date zero is not allowed
  • NO_ZERO_IN_DATE: Time field inserted into MySQL. The date and month cannot be zero

Phase two

Mysql > create table ‘NO_ZERO_DATE’; mysql > create table ‘NO_ZERO_DATE’; mysql > create table ‘NO_ZERO_DATE’;

mysql>CREATE TABLE `t_manager` ( ..... -> 'CREATE_DATETIME' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time ', -> 'MODIFIER' varchar(32) DEFAULT NULL COMMENT 'update ', -> 'MODIFY_DATETIME' TIMESTAMP NOT NULL DEFAULT '1001-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'UPDATE time ', -> 'IS_DELETED' bit(1) DEFAULT b'0' COMMENT ' IS_ENABLE 'bit(1) DEFAULT b'1' COMMENT' enable '0: enable ', ->' IS_ENABLE 'bit(1) DEFAULT b'1' COMMENT' enable '0: enable ', -> PRIMARY KEY (`CACHE_ID`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'Copy the code

All sql_mode values are checked, and all conform to the specification, but the table is still not created successfully. Have to go to the official manual to find timestamp introduction:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

Misline stage three

It can be seen that the value range of timestamp field in the official definition is ‘1970-01-01 00:00:01’ to ‘2001-19 03:14:07’. The original default value we set is not within the timestamp range. Change the default value again:

mysql>CREATE TABLE `t_manager` ( ..... -> 'CREATE_DATETIME' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time ', -> 'MODIFIER' varchar(32) DEFAULT NULL COMMENT 'update ', -> 'MODIFY_DATETIME' TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'change time ', -> 'IS_DELETED' bit(1) DEFAULT b'0' COMMENT ' IS_ENABLE 'bit(1) DEFAULT b'1' COMMENT' enable '0: enable ', ->' IS_ENABLE 'bit(1) DEFAULT b'1' COMMENT' enable '0: enable ', -> PRIMARY KEY (`CACHE_ID`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'Copy the code

The table cannot be created successfully. The same statement was executed successfully on his MySQL server, which is also version 5.7.23. Puzzled. In a fit of anger, I took out the parameter values on both sides and compared them. As expected, I found the difference.

The test environment Colleagues environment
system_time_zone=CST system_time_zone UTC
time_zone=’+08:00′ time_zone=SYSTEM

Looking back at the scope of the TIMESTAMP field definition:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

The time range refers to the UTC time zone. If the test environment has set the TIME zone of CST EAST 8, an additional 8 hours must be added to the corresponding time range. So change the default value of timestamp field to ‘1970-01-01 08:00:01’ and the table is created successfully.

mysql>CREATE TABLE `mn_cache_refresh_manager` ( ...... -> 'CREATE_DATETIME' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time ', -> 'MODIFIER' varchar(32) DEFAULT NULL COMMENT 'update ', -> 'MODIFY_DATETIME' TIMESTAMP NOT NULL DEFAULT '1970-01-01 08:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'change time ', -> 'IS_DELETED' bit(1) DEFAULT b'0' COMMENT ' IS_ENABLE 'bit(1) DEFAULT b'1' COMMENT' enable '0: enable ', ->' IS_ENABLE 'bit(1) DEFAULT b'1' COMMENT' enable '0: enable ', -> PRIMARY KEY (`CACHE_ID`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.02sec)Copy the code

The solution

  • Change the default value of timestamp field to the minimum value of the corresponding CST time zone ‘1970-01-01 08:00:01’

conclusion

The default value of the sql_mode parameter in MySQL 5.7

  • ONLY_FULL_GROUP_BY is usedGROUP BYSQL to perform the query is not allowedSELECTPart of aGROUP BYFields that do not appear in theSELECTThe query field must beGROUP BYThat either uses aggregate functions or has a unique attribute.
  • STRICT_TRANS_TABLES This option takes effect for transactional storage engines but not for non-transactional storage engines. STRICT_TRANS_TABLES indicates that strict SQL mode is enabled. In strict SQL mode, if an invalid field value is inserted or updated in an INSERT or UPDATE statement, the operation is interrupted
  • NO_ZERO_IN_DATE Time field inserted into MySQL. The date and month cannot be zero
  • NO_ZERO_DATE Time field inserted into MySQL. The date cannot be zero
  • ERROR_FOR_DIVISION_BY_ZERO If data is divided by 0 in the ERROR_FOR_DIVISION_BY_ZERO INSERT or UPDATE statement, a warning (in strict SQL mode) or error (in strict SQL mode) occurs.
  • When this option is turned off, the number is divided by 0, resulting in NULL and no warning
  • When this option is on and not in strict SQL mode, the number is divided by 0, resulting in NULL but a warning
  • When this option is enabled and in strict SQL mode, the number is divided by 0, generating an error and interrupting the operation
  • This option limits the GRANT syntax used in previous versions of NO_AUTO_CREATE_USER, which automatically creates a user if the user does not already exist
  • NO_ENGINE_SUBSTITUTION generates an error when executing storage engines using the CREATE TABLE or ALTER TABLE syntax if the specified storage engine is disabled or not compiled.