WL#7764: Make STRICT mode for transactional SEs default in 5.7
Affects: Server-5.7 — Status: Complete — Priority: Medium
In MySQL Server 5.6 we introduced the my.cnf/my.ini files, which specified the the STRICT_TRANS_TABLES setting, which sets STRICT mode for transactional storage engines. In 5.7 we want to set this SQL mode as default. This change does not affect non-transactional storage engines. Proper documentation must be provided to help upgrade scenarios and investigate effects on cross version dump/restore. Proper documentation must be provided to help upgrade scenarios, for users who do cross-version replication. Some notes from the replication team: 1. STRICT_TRANS_TABLES is one of the bits in in sql_mode. Since sql_mode is replicated, and slave sql thread is restarted with the latest sql_mode replicated from master at any time, so changing the default will not affect replication. 2. sql_mode is changed to MODE_NO_AUTO_VALUE_ON_ZERO temporarily and then restored when applying Rows_log_event in RBR, so RBR works fine with the changed default of sql_mode. 3. sql_mode is changed to zero temporarily and then restored when slave sql thread is executing a PROCEDURE or a ROUTINE, which also work fine with the changed default of sql_mode. Effect on Live Upgrade and Dump Upgrade: ======================================= 1. Live Upgrade: It will not be affected with changed sql_mode default. In live upgrade, user will start 5.7 server on the old data directory and run mysql_upgrade tool which will fix the system table structure. So with upgraded server, it will get new sql_mode defaults and further queries which it will run may start producing error which is expected. So there is no issue here. 2. Dump Upgrade: There is no effect on dump upgrade as well. This is because mysqldump tool always executes the SQL statements in ".sql" with NO_AUTO_VALUE_ON_ZERO mode enabled. So changed strict sql_mode default will have no effect using dump upgrade as well. It has also been verified manually by taking dump of invalid date/time values present in a table in 5.6 and this dump successfully executed in WL branch. Changes in mysqlslap tool / mysqlslap.c ======================================= It was observed that some autogenerated DML queries by mysqlslap tool in mysqlslap.test started failing in STRICT MODE due to changed default value of sql_mode. Therefore, a new option --sql_mode is introduced for the user to specify sql_mode explicitly while running the mysqlslap tool. The testcases in mysqlslap.test are modified accordingly. User Documentation ================== http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html (there are separate entries for the SQL mode change and the --sql-mode option) http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html http://dev.mysql.com/doc/refman/5.7/en/mysqlslap.html#option_mysqlslap_sql-mode
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.