Reprinted -- SQL _mode, reprinted SQL _mode

Reprinted -- SQL _mode, reprinted SQL _mode
1. MySQL is inexplicably changed to Strict SQL Mode

Recently, some database writes failed in the test group. The app layer prompts that the data is successfully inserted, but no data is generated in the table. The other two tables with two write operations have data. Because the insert failure cannot be seen at the database level, ask a php colleague to check the error message:

[Err] 1364 - Field `f_company_id` doesn't have a default value

Obviously, two insert operations, the first one succeeded, and the second one failed. However, because the operation was not controlled in a transaction, the app still prompts that the operation was successful, I thought about how much it would cost if there was a problem online.

Not to mention the development issue, how can a good-end mysql suddenly fail to write some tables? According to the above problem, we can quickly guess that it is a problem with SQL _mode: The NOT NULL column does NOT have a default value but does NOT provide a value in the Code. In non-strict mode, the default value of the int column is 0, the default value of the string column is '', so it is not a problem. However, in strict mode, a failure is returned directly.

Let's take a look:

mysql> show variables like "sql_mode";+---------------+--------------------------------------------+| Variable_name | Value                                      |+---------------+--------------------------------------------+| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |+---------------+--------------------------------------------+

But it was okay all the time, and it suddenly appeared. Who will change the SQL _mode? What is the risk of this problem in the production environment? So it must be pulled out.

Firstset global sql_mode=''To enable them to use the first command (the fundamental solution to the problem will be given later), and open general_log to see which user has the Command similar to setting SQL _mode:

1134456 Query   SET autocommit=11134456 Query   Set sql_mode='NO_ENGINE_SUBSITUTION,STRICT_TRANS_TABLES'1134457 Connect ecuser@10.0.200.173 on1134457 Query   /* mysql-connector-java-5.1.35 ...

It can be seen that SQL _mode is set when a framework in the java group establishes a connection, but this is a session level and does not affect the connection of php users.

<! -- More -->

Why does it change back to the strict mode after set global? So I thought that mysqld_safe is actually a protection process and will be pulled up after the mysqld exception stops, will there be exceptions in the middle cause mysqld to restart and cause global to fail? After reading the mysql error log, I thought that the power was cut off the previous day, so I decided to change it directly./etc/my.cnfConfiguration:

[mysqld]sql_mode=NO_ENGINE_SUBSTITUTION

After myqld is restartedSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONIn the my. cnf configuration is rarely invalid. Coincidentally, I found the same problem on stackoverflow: how-to-make-SQL-mode-no-engine-substitution-permanent-in-mysql-my-cnf. The reason is very simple, SQL _mode is overwritten by configurations elsewhere.

Let's take a look at the loading sequence of mysql configuration files:

$ mysqld --help --verbose|grep -A1 -B1 cnfDefault options are read from the following files in the given order:/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Mysql loads the configuration file in the above order, and the subsequent configuration items will overwrite the previous one. Finally/usr/my.cnfFind onesql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESDelete the file, and the file in/etc/my. cnf takes effect.

However, what I cannot understand at present is how mysql suddenly runs for so long/usr(MYSQL_BASE. Other instances have no such problems.

There is also another example: the strict mode is not allowed to pass ''To the int type in the stored procedure, but not the strict mode is only a warning. (After the command line executes the statement,show warningsVisible)

The ultimate (recommended) solution to this type of problem is that, considering the compatibility and accuracy of data, MySQL should run in strict mode! Whether in the development or production environment, porting code to the production environment may cause hidden problems.

The SQL _mode problem can be very simple or complicated. Someone once saw in a communication group that the inconsistent settings of the master and slave SQL _mode caused a replication exception. Here, I will take a full look at several common values to facilitate troubleshooting in multiple directions.

2. Description of common SQL _mode values

The official manual has a special section about https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html. The SQL Mode defines two aspects: the SQL syntax that MySQL should support and the validation check on the data.

1. SQL syntax support

• ONLY_FULL_GROUP_BYFor group by aggregation, if the columns in SELECT, HAVING, or order by clauses are not in group by, this SQL statement is invalid. It is understandable, because there is a conflict in the display that is not found in the group by column.
  It is enabled by default in 5.7. Therefore, you must note the following during the upgrade from 5.6 to 5.7:

   Expression #1 of SELECT list is not in GROUP BYclause and contains nonaggregated column'1066export.ebay_order_items.TransactionID' whichis not functionally dependent on columns in GROUP BYclause; this is incompatible with sql_mode=only_full_group_by

• ANSI_QUOTESWhen ANSI_QUOTES is enabled, strings cannot be referenced using double quotation marks because it is interpreted as an identifier and serves the same purpose. After setting it,update t set f1="" ...The Unknown column ''in 'field list syntax error is reported.

• PIPES_AS_CONCATSet||It is treated as a string join operator rather than an OR operator, which is the same as the Oracle database and is similar to the String concatenation function CONCAT.

• NO_TABLE_OPTIONSUseSHOW CREATE TABLEMySQL-specific syntax is not output, as shown in figureENGINEThis should be considered when mysqldump is used for cross-database migration.

• NO_AUTO_CREATE_USERUsers are not automatically created. We are used to using MySQL user authorizationGRANT ... ON ... TO dbuserCreate a user together. This option is similar to the oracle operation. Users must be created before authorization. 5.7.7 is also default.

2. Data check

• NO_ZERO_DATEThe date '2017-00-00 'is considered invalid because it depends on whether the strict mode is set later.

• NO_ENGINE_SUBSTITUTIONUseALTER TABLEOrCREATE TABLEWhat should I do if the storage ENGINE is disabled or not compiled when the ENGINE is specified. EnableNO_ENGINE_SUBSTITUTIONIf this parameter is not set, the system directly throws an error. If this parameter is not set, the default storage engine is used for CREATE. The ATLER does not change the value and throws a warning.

• STRICT_TRANS_TABLESSet it to enable strict mode.
  Note:STRICT_TRANS_TABLESNot a combination of several policies.INSERT,UPDATEWhat should I do if there are few or invalid values:

The preceding section does not cover all the SQL modes. You have selected several representative modes. For more information, see the manual.

SQL _mode generally pays little attention to it and does not start or stop the above entry before it encounters any actual problem. The SQL _mode we usually set isANSI,STRICT_TRANS_TABLES,TRADITIONAL, Ansi and traditional are the preceding combinations.

• ANSI: Change the syntax and behavior to make it more compliant with standard SQL
  Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE

• TRADITIONAL: More like a traditional SQL database system, the simple description of this mode is to "give an error rather than warning" when an incorrect value is inserted in the column ".
  Equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

• ORACLE: Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER

Regardless of the mode, after an error occurs, the execution of a single SQL statement fails. For tables that support transactions, the current transaction is rolled back. However, if it is not executed in the transaction, or the storage engine table that does not support transactions may cause data inconsistency. MySQL believes that data inconsistency is more serious than direct error termination. ThereforeSTRICT_TRANS_TABLESFor non-transaction tables, try to continue writing as much as possible, such as giving the "most reasonable" default value or truncation. ForSTRICT_ALL_TABLESIf a single update is performed, it will not be affected. If multiple updates are performed, the first update is successful, and some updates will occur if the next update fails.

5.6.6 and later versionsNO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES, 5.5 is ''by default ''.

3. Set SQL _mode

View

View the SQL mode of the current connection session: mysql> select @ session. SQL _mode; or take mysql> show variables like "SQL _mode" from the environment variable; view the global SQL _mode settings: mysql> select @ global. SQL _mode; only global is set. It takes effect only after a new connection is established.

Set

 

The format is mysql> set SQL _mode = ''; mysql> set global SQL _mode = 'no _ ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES ';