Description:
Didn't check older version. Anyway I think only 8.0 needs a fix.
During installation, one of the very first executed SQL scripts is scripts/mysql_system_tables.sql, which starts with:
set sql_mode='';
Given that MySQL nowadays runs with this as default:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
It is odd that this script doesn't use it.
But, worse, this script doesn't restore sql_mode to default when it ends, so it influences all other initialization-time SQL scripts which follow it, e.g. scripts/mysql_sys_schema.sql; so all stored procedures created with the latter script store "sql_mode=0" in their definition and whill thus use that mode every time they run, later.
What to fix:
- mysql_system_tables.sql should preferrably use the default sql_mode, or use a fixed value which is closer to the default (containing at least ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES as these are sane defaults which filter bad queries).
- mysql_system_tables_fix.sql has the same problem, should also be fixed
- mysql_sys_schema.sql sets sql_mode to '' and restores it later (so it doesn't pollute other scripts' execution), but it shouldn't set it at all.
Be careful:
please also verify that "mysqld --initialize" always uses the default value of sql_mode specified in sys_vars.cc and does _not_ take into account any other value configured in a defaults file (e.g. my.cnf) or passed on the command line. Indeed, we do not want DD objects to be created with a sql_mode defined by the user. We want them to be created with the factory default, for stability and predictability.
How to repeat:
see above
