The pitfalls and solutions caused by the default value of sql_mode in MySQL 5.7

During normal project development, if the MySQL version is upgraded from 5.6 to 5.7. When a DBA considers the impact of a database version upgrade, there are generally several points to note:

sql_mode
optimizer_switch

The main content of this article is the pitfalls caused by the default sql_mode value after MySQL is upgraded to version 5.7 and the corresponding solutions.

Case 1: ONLY_FULL_GROUP_BY

Problem Description

After upgrading MySQL version from 5.6 to 5.7, some SQL execution errors are reported. The error information is 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

The reason for this problem is that the default value of sql_mode has changed after upgrading from version 5.6 to version 5.7. In the default value of sql_mode in version 5.7, ONLY_FULL_GROUP_BY is intended. The meaning of this option means that for SQL that uses GROUP BY for query, fields that do not appear in GROUP BY are not allowed to appear in the SELECT part, that is, the fields queried by SELECT must appear in GROUP BY or use aggregate functions.

Solution

Solution 1 (not recommended): Modify the sql_mode value of version 5.7 and remove ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY is used to strengthen SQL specifications. Its purpose is to make the results of SQL queries more standardized and accurate.

Without the ONLY_FULL_GROUP_BY specification restriction, the following SQL is allowed to execute: SELECT a,b,c FROM t GROUP BY a . SQL groups by the value of field a. When the same field value a corresponds to multiple b or c values, the b and c values ​​in the query results are uncertain.

Solution 2: Rewrite SQL

Case 2: NO_ZERO_DATE & NO_ZERO_IN_DATE & time_zone

Problem Description

Troubleshooting Phase 1

After upgrading MySQL version from 5.6 to 5.7, the table creation process failed:

mysql> CREATE TABLE `t_manager` (
  .....
  -> `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  -> `MODIFIER` varchar(32) DEFAULT NULL COMMENT 'Updater',
  -> `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
  -> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT 'Delete status 1: Deleted 0: Not deleted',
  -> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT 'Enable status 1: Enable 0: Disable',
  -> PRIMARY KEY (`CACHE_ID`)
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'

The error message says that the default value of the MODIFY_DATETIME field is invalid. Considering that I just upgraded from version 5.6 to 5.7, I checked the default sql_mode value in 5.7. It turns out that there are two options that may have an impact:

NO_ZERO_DATE
NO_ZERO_IN_DATE

Troubleshooting Phase 2

So the solution is to set the default value according to the requirements of NO_ZERO_DATE and NO_ZERO_IN_DATE, and set the default value of the MODIFY_DATETIME field to '1001-01-01 01:01:01'. As a result, it is found that the table cannot be created successfully:

mysql>CREATE TABLE `t_manager` (
  .....
  -> `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  -> `MODIFIER` varchar(32) DEFAULT NULL COMMENT 'Updater',
  -> `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '1001-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
  -> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT 'Delete status 1: Deleted 0: Not deleted',
  -> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT 'Enable status 1: Enable 0: Disable',
  -> PRIMARY KEY (`CACHE_ID`)
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'

I checked all the sql_mode values ​​and found that they all met the specifications, but the table still could not be created successfully. I had to go to the official manual to find the 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.

Troubleshooting Phase 3

It can be seen that the official definition of the timestamp field value range is '1970-01-01 00:00:01' to '2038-01-19 03:14:07'. It turns out that the default value we set is not within the timestamp range. So modify the default value again:

mysql>CREATE TABLE `t_manager` (
  .....
  -> `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  -> `MODIFIER` varchar(32) DEFAULT NULL COMMENT 'Updater',
  -> `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
  -> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT 'Delete status 1: Deleted 0: Not deleted',
  -> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT 'Enable status 1: Enable 0: Disable',
  -> PRIMARY KEY (`CACHE_ID`)
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'

What a pity! I still can't create the table successfully. I was at my wit's end, so I asked a colleague for help. He said he would try it on his own machine, and the same statement worked successfully on his MySQL, also version 5.7.23.

I just can't figure it out.

In a fit of anger, I compared the parameter values ​​on both sides and indeed found the root of the difference.

Let's look back at the range defined by the timestamp field:

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.

This time range refers to the time range of the UTC time zone. If the test environment is set to the CST East 8 time zone, then the corresponding time range also needs to be increased by 8 hours. So the default value of the timestamp field is changed to '1970-01-01 08:00:01', and the table is finally created successfully.

mysql>CREATE TABLE `mn_cache_refresh_manager` (
  ......
  -> `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  -> `MODIFIER` varchar(32) DEFAULT NULL COMMENT 'Updater',
  -> `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
  -> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT 'Delete status 1: Deleted 0: Not deleted',
  -> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT 'Enable status 1: Enable 0: Disable',
  -> PRIMARY KEY (`CACHE_ID`)
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

Summarize

The above is the pitfalls and solutions brought by the default value of sql_mode in MySQL 5.7 introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!