Changed defaults between MySQL 5.6 and 5.7
MySQL 5.7 comes with many changes. Some of them are better explained than others.
I wanted to see how many changes I could get by comparing SHOW VARIABLES in MySQL 5.6 and 5.7.
The most notable ones are:
- binlog_format: the default is now ROW. This variable affects the format of the binary log, whether you use it as a backup complement or for replication, the change means bigger binary logs and possibly side effects.
- binlog_error_action now defaults to ABORT_SERVER. If the server cannot write to the binary log, rather than continuing its work without logging, it shuts down. This could be a desirable course of action, but better be prepared for the eventuality.
- innodb_strict_mode is enabled by default, which is probably a good thing, but it means that previously accepted events will now generate an error instead than a warning.
- sql_mode is now STRICT by default. While many well prepared users will be pleased with this change, which was advocated as best practice by some DBAs, the practical outcome is that several exiting applications may break because of unclean input.
- sync_binlog, which affects data safety but also server performance is enabled.
The table below shows the full list.
I don’t know if the other changes can have the effect of breaking existing apps. Please comment on this post if you know of possible side effects related to the above variables.