WL#7764: Make STRICT mode for transactional SEs default in 5.7

Affects: Server-5.7   —   Status: Complete

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