Shulou(Shulou.com)05/31 Report--

This article mainly introduces what problems the default value of sql_mode in MySQL5.7 will bring, which is very detailed and has certain reference value. Friends who are interested must finish it!

During normal project development, if the MySQL version is upgraded from 5.6 to 5.7. As a DBA, when considering the impact of database version upgrades, there are a few points to pay attention to:

Sql_modeoptimizer_switch

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

Case 1: ONLY_FULL_GROUP_BY

Problem description

After upgrading the MySQL version from 5.6 to 5.7, some SQL executes the error message 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 5.6 to 5.7. there is an intention to ONLY_FULL_GROUP_BY in the default value of sql_mode in version 5.7. this option means: for SQL querying with GROUP BY, fields that do not appear in GROUP BY are not allowed in SELECT, that is, the fields queried by SELECT must appear in GROUP BY or use aggregate functions.

Solution

Option 1 (not recommended): modify the sql_mode value of version 5.7 to remove ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY strengthens the SQL specification, and its purpose is to make the results of SQL query more in line with the specification and more accurate.

If there are no restrictions on the ONLY_FULL_GROUP_BY specification, then the execution of the following SQL can be allowed: SELECT a dint bje c FROM t GROUP BY a. SQL is grouped according to a field value. When the same a field value corresponds to multiple b or c values, the b and c values in the query results are uncertain.

Plan 2: rewrite SQL

Case 2: NO_ZERO_DATE & NO_ZERO_IN_DATE & time_zone

Problem description

Error stage one

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

Mysql > CREATE TABLE `t _ manager`. -> `MODIFIER` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT',-> `MODIFIER` varchar (32) DEFAULT NULL COMMENT 'UPDAT',-> `MODIFIER` timestamp NOT NULL DEFAULT '0000-00-0000: 00MODIFY_ 00' ON UPDATE CURRENT_TIMESTAMP COMMENT' modification time',-> `MODIFIER` bit (1) DEFAULT bounded 0' COMMENT 'deletion status 1: delete 0: not deleted' -> `ENABLE` bit (1) DEFAULT bounded 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 indicates that the default value set for the MODIFY_DATETIME field is invalid, considering that it has just been upgraded from version 5.6 to version 5.7, so it goes back to the default value of sql_mode in version 5.7. As a result, two options that may have an impact were found:

NO_ZERO_DATE

NO_ZERO_IN_DATE

Stage 2 of troubleshooting

Therefore, 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 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01, and it is found that the table still cannot be created successfully:

Mysql > CREATE TABLE `t _ manager`. -> `MODIFIER` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',-> `MODIFIER` varchar (32) DEFAULT NULL COMMENT 'updater',-> `MODIFIER` varchar 'timestamp NOT NULL DEFAULT' 1001-01-01 01varchar 01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',-> `MODIFIER` bit (1) DEFAULT bounded 0' COMMENT 'deletion status 1: deleted 0: not deleted' -> `ENABLE` bit (1) DEFAULT bounded 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 conformed to the specification, but the table creation was still unsuccessful. I had to look for the introduction of timestamp in the official manual:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00 UTC 01' UTC to' 2038-01-1903 UTC.

Error stage 3

You can see that the range of the timestamp field values in the official definition is' 1970-01-01 00 01' to '2038-01-19 03 1415 07'. It turns out that the default value we set is not within the range of timestamp. So change the default value again:

Mysql > CREATE TABLE `t _ manager`. -> `MODIFIER` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',-> `MODIFIER` varchar (32) DEFAULT NULL COMMENT 'updater',-> `MODIFIER` varchar 'timestamp NOT NULL DEFAULT' 1970-01-01 0000LV 01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',-> `IS_ DELETED` bit (1) DEFAULT bounded 0' COMMENT 'deletion status 1: delete 0: not deleted' -> `ENABLE` bit (1) DEFAULT bounded 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'

Evil door, unexpectedly still can not successfully create the table. There was nothing I could do. I asked my colleague for help. the colleague said he tried it on the machine and the same sentence was successfully executed on his MySQL, the same version 5.7.23.

I don't understand.

Angrily, I compared the parameter values of the two sides, and sure enough, I found the different roots.

Test environment colleague environment system_time_zone=CSTsystem_time_zone UTCtime_zone='+08:00'time_zone=SYSTEM

Looking back at the scope 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 UTC 01' UTC to' 2038-01-1903 UTC.

This time range refers to the time range of the UTC time zone. If the test environment sets the time zone of CST East Zone 8, the corresponding time range also needs to be added 8 hours. So the default value of the timestamp field is changed to '1970-01-01 08 01mm, and the table is finally created successfully.

Mysql > CREATE TABLE `mn_cache_refresh_ manager` (. -> `MODIFIER` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',-> `MODIFIER` varchar (32) DEFAULT NULL COMMENT 'updater',-> `MODIFIER` varchar 'timestamp NOT NULL DEFAULT' 1970-01-01 08LV 01' ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',-> `IS_ DELETED` bit (1) DEFAULT bounded 0' COMMENT 'deletion status 1: delete 0: not deleted' -> `ENABLE` bit (1) DEFAULT bounded 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) above are all the contents of this article entitled "what are the problems caused by the default value of sql_mode in MySQL5.7"? thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.