Solution to mysql error when modifying sql_mode

A murder caused by ERR 1067

Today, when I ran the SQL statement in Navicat to create a data table, an error Err 1067 occurred. This statement is correct on some colleagues' MySQL, but it gives an error on some others. Isn’t it infuriating?

The reason turned out to be that the default value of timestamp was incorrect.

According to the information, there is a STRICT mode in MySQL 5.7. In this mode, the date value is not allowed to be set to all 0 values ​​by default.

To solve this problem, you need to modify the value of sql_mode.

The source of the second problem is sql_mode

We can go into mysql to find out what sql_mode is. First, enter the bin directory under the MySQL installation directory and log in to the MySQL database as an administrator user.

Use the command mysql –h localhost –u root–p, where -h is the specified host name or IP address, -u is the specified user, and -p is to log in using the password.

Use the command select @@sql_mode; to view the value of sql_mode. If I enter a command but nothing happens, and only -> appears, then I think

You probably did not enter the ";" like me.

From the results in the figure above, we can see that sql_mode has NO_ZERO_IN_DATE and NO_ZERO_DATE. Enter in the command line

set sql_mode=(select replace(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE','')); You can modify sql_mode.

You can then check the value of sql_mode. It can be found that NO_ZERO_IN_DATE and NO_ZERO_DATE have been successfully removed.

I re-ran the SQL statement for creating the table, but it didn't work, and Err 1067 still occurred. Don't think about any wrong posture problem, it's just that the global sql_mode is not set, and the sql_mode set here has no effect on the overall situation.

Use the command select @@global.sql_mode; to view the value of the global sql_mode.

The remaining operations are the same as the previous sql_mode settings, except that the previous sql_mode is replaced with @@global.sql_mode, as shown in the figure.

After completing the settings, you can re-run the SQL statement in Navicat, but before that you must reconnect to the database, otherwise it will still fail.

Don’t ask me why, just call me Lei Feng. The results are as follows:

OK, this time the table was created successfully, and an error Err 1055 was thrown, which means "Cannot create a table containing a non-aggregate column information_schema.

PROFILING.SEQ is grouped, this feature no longer relies on grouping and is incompatible with the new rule sql_mode=only_full_group_by". It also says that this is due to

This is caused by "ONLY_FULL_GROUP_BY" in sql_mode. The sql_mode can be modified again.

Delete the previously created table, reconnect to the database, run the SQL statement, and then everything will be fine.

Of course, this solution is just a temporary solution. Once you restart the MySQL database, all the values ​​you set with great effort will be restored to the state before liberation.

There is a solution to this, which I will introduce to you below.

3. Set sql_mode

You can set sql_mode by modifying the configuration file so that the value of sql_mode will not change after the database is restarted.

First of all, we need to know the order in which MySQL configuration files are loaded. Go to the bin directory of the database installation directory and use the command

You can see it by running mysqld --verbose --help, but the output of this command is too long and I haven’t found a more suitable command to view it yet.

The loading order is as follows:

When these configuration files are loaded, the later loaded ones will overwrite the same values ​​in the previously loaded configuration files. But I only found it in the mysql installation directory

One config file with a very similar name, the rest were not found.

After backing up this file, change the name to my.ini to correspond to the file in the given order of loading configuration files. Then open the file, my configuration here

There are only two values ​​for sql_mode in the file.

After restarting the database, use the command to check the value of sql_mode and find that it is exactly the same as in the configuration file. Done!

Additional introduction to several common sql_mode values:

Introduction to several common modes

• ONLY_FULL_GROUP_BY : Columns that appear in the select statement, HAVING condition, and ORDER BY statement must be GROUP BY columns or function columns that depend on GROUP BY columns.
• NO_AUTO_VALUE_ON_ZERO : This value affects the insertion of auto-increment columns. By default, inserting 0 or NULL will generate the next auto-increment value. This option is useful if the user wants to insert a value of 0 and the column is auto-incrementing.
• STRICT_TRANS_TABLES : In this mode, if a value cannot be inserted into a transactional table, the current operation is interrupted, and no restrictions are imposed on non-transactional tables.
• NO_ZERO_IN_DATE : This mode affects whether the month and day of a date are allowed to contain zero. If this mode is turned on, 2016-01-00 is not allowed, but 0000-02-01 is allowed. Its actual behavior is affected by whether strict mode is enabled.
• NO_ZERO_DATE : Set this value, MySQL database does not allow the insertion of zero date. Its actual behavior is affected by whether strictmode is enabled.
• ERROR_FOR_DIVISION_BY_ZERO : During an INSERT or UPDATE, if data is divided by zero, an error is generated instead of a warning. If this mode is not given, MySQL returns NULL if the value is divided by zero.
• NO_AUTO_CREATE_USER : Prevent GRANT from creating users with empty passwords
• NO_ENGINE_SUBSTITUTION : Throw an error if the required storage engine is disabled or not compiled in. If this value is not set, the default storage engine is used instead and an exception is thrown.
• PIPES_AS_CONCAT : Treat "||" as a string concatenation operator instead of an OR operator, which is the same as Oracle database and similar to the string concatenation function Concat
• ANSI_QUOTES : When ANSI_QUOTES is enabled, you cannot use double quotes to quote strings, because they are interpreted as identifiers.

This is the end of this article about how to solve the error when modifying mysql sql_mode. For more relevant mysql sql_mode content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!