Thursday, August 06, 2015

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.

VARIABLE 5.6.26 5.7.8
binlog_error_action IGNORE_ERROR ABORT_SERVER
binlog_format STATEMENT ROW
binlog_gtid_simple_recovery OFF ON
eq_range_index_dive_limit 10 200
innodb_buffer_pool_dump_at_shutdown OFF ON
innodb_buffer_pool_instances 8 1
innodb_buffer_pool_load_at_startup OFF ON
innodb_checksum_algorithm innodb crc32
innodb_file_format Antelope Barracuda
innodb_large_prefix OFF ON
innodb_log_buffer_size 8388608 16777216
innodb_purge_threads 1 4
innodb_strict_mode OFF ON
log_warnings 1 2
performance_schema_accounts_size 100 –1
performance_schema_hosts_size 100 –1
performance_schema_max_cond_instances 1382 –1
performance_schema_max_file_instances 2557 –1
performance_schema_max_mutex_instances 5755 –1
performance_schema_max_rwlock_instances 3138 –1
performance_schema_max_socket_instances 230 –1
performance_schema_max_statement_classes 168 191
performance_schema_max_table_handles 616 –1
performance_schema_max_table_instances 684 –1
performance_schema_max_thread_instances 288 –1
performance_schema_setup_actors_size 100 –1
performance_schema_setup_objects_size 100 –1
performance_schema_users_size 100 –1
pseudo_thread_id 5 7
slave_net_timeout 3600 60
sync_binlog 0 1
table_open_cache_instances 1 16
warning_count 0 1

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.

No comments: