System Variable Differences Between MariaDB 10.2 and MySQL 5.7

The following is a comparison of variables that either appear only in MariaDB 10.2 or MySQL 5.7, or have different default settings in MariaDB 10.2, and MySQL 5.7. MariaDB 10.2.19 and MySQL 5.7.13, with only default plugins enabled, were used for the comparison.

The most notable differences are that MariaDB includes, by default, the Aria storage engine (resulting in extra memory allocation), Galera Cluster, and has a different thread pool implementation. For this reason, a default implementation of MariaDB 10.2 will use more memory than MySQL 5.7. MariaDB 10.2 and MySQL 5.7 also have different GTID implementations.

MariaDB's extra memory usage can be handled with the following rules of thumb:

Comparison Table

VariableMariaDB 10.2MySQL 5.7Notes
aria_**-The Aria storage engine is only available in MariaDB.
avoid_temporal_upgrade-OFFMySQL-only variable for determining whether ALTER TABLE implicitly upgrades temporal columns.
bind_address-127.0.0.1MySQL has the value of the bind-address option as a variable.
binlog-annotate-row-eventsON-Introduced in MariaDB 5.3 for replicating between MariaDB 5.3 and MySQL/MariaDB 5.1.
binlog_commit_wait_count0-For use in MariaDB's parallel replication.
binlog_commit_wait_usec100000-For use in MariaDB's parallel replication.
binlog_error_actionABORT_SERVERMySQL-only variable for controlling what happens when the server cannot write to the binary log.
binlog_formatMIXEDROWMariaDB and MySQL have differing binary log formats.
binlog_group_commit_sync_delay0MySQL-only variable for controlling the wait time before synchronizing the binary log file to disk.
binlog_group_commit_sync_no_delay_count0MySQL-only variable for setting the maximum number of transactions to wait for before aborting the current binlog_group_commit_sync_delay delay.
binlog_gtid_simple_recovery-ONMySQL-only GTID variable. MariaDB's GTID implementation is different.
binlog_max_flush_queue_time-0MySQL-only variable specifying a timeout for reading transactions from the flush queue before continuing with group commit and syncing log to disk.
binlog_optimize_thread_schedulingON-For optimized kernel thread scheduling.
binlog_order_commits-ONMySQL-only variable for determining whether transactions may be committed in parallel.
binlog_rows_query_log_events-OFFMySQL-only variable for logging extra information in row-based logging.
block_encryption_mode-aes-128-ecbMySQL-only variable for controlling the block encryption mode for block-based algorithms.
check_constraint_checksON-Permits disabling constraint checks, for example when loading a table that violates some constraints that you plan to fix later.
check_proxy_usersOFFMySQL-only variable for controlling whether the server performs proxy user mapping for authentication plugins.
deadlock_search_depth_long15-The Aria storage engine is only available in MariaDB.
deadlock_search_depth_short4-The Aria storage engine is only available in MariaDB.
deadlock_timeout_long50000000-The Aria storage engine is only available in MariaDB.
deadlock_timeout_short10000-The Aria storage engine is only available in MariaDB.
debug_no_thread_alarmOFF-Disable system thread alarm calls, for debugging or testing.
default_authentication_pluginmysql_native_passwordMySQL's default authentication plugin.
default_master_connectionempty-For use with MariaDB's multi-source replication.
default_password_lifetime360MySQL-only variable determining how long passwords are valid for before expiring.
default_regex_flagsempty-For handling incompatibilities between MariaDB's PCRE and the old regex library.
default_tmp_storage_engineemptyInnoDBDefault storage engine used for tables created with CREATE TEMPORARY TABLE.
disabled_storage_enginesemptyMySQL-only variable for disabling specific storage engines.
disconnect_on_expired_password-ONMySQL permits passwords to be expired.
encrypt_binlogOFF-MariaDB enables table and tablespace encryption.
encrypt_tmp_filesOFF-MariaDB enables table and tablespace encryption.
encrypt_tmp_disk_tablesOFF-MariaDB enables table and tablespace encryption.
end_markers_in_json-OFFMySQL-only variable for adding end markers to JSON output.
enforce_gtid_consistency-OFFMariaDB and MySQL have different GTID implementations.
enforce_storage_enginenoneForces the use of a particular storage engine for new tables.
eq_range_index_dive_limit0200For tuning when the optimizer should switch from using index dives to index statistics for qualifying rows estimation.
expensive_subquery_limit100-Used for determining expensive queries for optimization.
extra_max_connections1-Introduced in the MariaDB 5.1 threadpool.
extra_port0-Introduced in the MariaDB 5.1 threadpool.
group_concat_max_len10485761024MariaDB increases the maximum length for a GROUP_CONCAT() result from 1K to 1M.
gtid_binlog_posempty-MariaDB and MySQL have different GTID implementations.
gtid_binlog_stateempty-MariaDB and MySQL have different GTID implementations.
gtid_current_posempty-MariaDB and MySQL have different GTID implementations.
gtid_domain_id0-MariaDB and MySQL have different GTID implementations.
gtid_executed-emptyMariaDB and MySQL have different GTID implementations.
gtid_executed_compression_period-1000MariaDB and MySQL have different GTID implementations.
gtid_ignore_duplicatesOFF-MariaDB and MySQL have different GTID implementations.
gtid_mode-OFFMariaDB and MySQL have different GTID implementations.
gtid_next-AUTOMATICMariaDB and MySQL have different GTID implementations.
gtid_owned-emptyMariaDB and MySQL have different GTID implementations.
gtid_purged-emptyMariaDB and MySQL have different GTID implementations.
gtid_seq_no0-MariaDB and MySQL have different GTID implementations.
gtid_slave_posempty-MariaDB and MySQL have different GTID implementations.
gtid_strict_modeOFF-MariaDB and MySQL have different GTID implementations.
have_opensslMariaDB's version indicates whether YaSSL or openssl was used. MySQL's is a synonym for have_ssl.
have_statement_timeout-MySQL's statement execution timeout feature is available.
histogram_size0-MariaDB introduced Histogram-based Statistics.
histogram_typeSINGLE_PREC_HB-MariaDB introduced Histogram-based Statistics.
in_transaction0-Set to 1 if you are in a transaction, and 0 if not.
innodb_***The list of differences between MariaDB 10.2 and MySQL 5.7 Innodb variables is currently incomplete.
innodb_log_optimize_ddlON-Whether redo logging should be reduced when natively creating indexes or rebuilding tables
innodb_safe_truncateON-Use a backup-safe TRUNCATE TABLE implementation and crash-safe rename operations inside InnoDB.
internal_tmp_disk_storage_engine-INNODBMySQL uses this variable to set the storage engine for on-disk internal temporary tables. In MariaDB, the storage engine for on-disk internal temporary tables is not configurable at run-time. Instead, it is set at compile time, and the two options are Aria or MyISAM. If Aria is used, then the value of the aria_used_for_temp_tables system variable will be ON.
join_buffer_space_limit2097152-Maximum size in bytes of the query buffer. See block-based join algorithms.
join_cache_level2-For determining the join algorithms. See block-based join algorithms
key_buffer_size1342177288388608Size of the buffer for the index blocks used by MyISAM tables and shared for all threads.
key_cache_file_hash_size512-Number of hash buckets for open and changed files.
key_cache_segments0-The number of segments in a key cache. See Segmented Key Cache.
last_gtid-emptyMariaDB and MySQL have different GTID implementations.
lock_wait_timeout8640031536000MariaDB has reduced the timeout for acquiring metadata locks.
log_bin_compressOFF-MariaDB setting for whether or not the binary log can be compressed.
log_bin_compress_min_len256-Minimum length of sql statement (in statement mode) or record (in row mode) that can be compressed. See Compressing Events to Reduce Size of the Binary Log.
log_bin_use_v1_row_events-OFFMySQL-only variable showing whether or not MySQL's version 2 binary logging format is being used.
log_builtin_as_identified_by_password-OFFMySQL variable for use with binary logging of user-management statements,
log_error_verbosity-3MySQL variable for setting verbosity of error, warning, and note messages in the error log.
log_slow_admin_statementsONOFFMariaDB logs slow admin statements to the slow query log by default.
log_slow_filteradmin, filesort, filesort_on_disk, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk-For slow query log filtering.
log_slow_rate_limit1-Limits the number of queries logged to the slow query log.
log_slow_slave_statementsONOFFMariaDB logs slow slave statements to the slow query log by default.
log_slow_verbosityempty-Controls information to be added to the slow query log. See also Slow Query Log Extended Statistics.
log_statements_unsafe_for_binlog-ONMySQL setting for controlling whether binlog warnings are written to the error log.
log_syslog*platform-dependent-MySQL variables with settings for writing to syslog.
log_tc_size24576-Size in bytes of the transaction coordinator log, defined in multiples of 4096.
log_throttle_queries_not_using_indexes-0MySQL-only variable for limiting the number of statements without indexes written to the slow query log.
log_timestamps-UTCMySQL-only variable controlling the timezone for certain logging conditions.
master_info_repository-FILEMySQL-only variable determining whether the slaves log info to file or table.
max_allowed_packet16M4M
max_execution_time-0MySQL renamed the max_statement_time variable.
max_long_data_size16777216-Maximum size for parameter values sent with mysql_stmt_send_long_data(). Removed in MySQL 5.6.
max_points_in_geometry-65536Maximum points_per_circle for MySQL's ST_Buffer_Strategy() function.
max_session_mem_used9223372036854775807-Amount of memory a single user session is allowed to allocate.
max_recursive_iterations4294967295-Maximum number of iterations when executing recursive queries.
max_relay_log_size10737418240Can be set by session in MariaDB.
max_seeks_for_key42949672954294967295 (32-bit) or 18446744073709547520 (64-bit)The most key seeks required when searching with an index, regardless of the actual index cardinality. If this value is set lower than its default and maximum, indexes will tend to be preferred over table scans.
max_statement_time0-Maximum time in seconds that a query can execute before being aborted. MySQL used to have a variable of this name before renaming it max_execution_time.
max_write_lock_count42949672954294967295 (32-bit) or 18446744073709547520 (64-bit)Read lock requests will be permitted for processing after this many write locks.
mrr_buffer_size262144-Size of buffer to use when using multi-range read with range access. See Multi Range Read optimization.
myisam_block_size1024-Block size used for MyISAM index pages.
myisam_recover_optionsBACKUP,QUICKOFFMyISAM recovery mode.
myisam_sort_buffer_size1342167048388608Size in bytes of the buffer allocated when creating or sorting indexes on a MyISAM table.
mysql56_temporal_formatONCauses MariaDB to use the MySQL-5.6 low level formats for TIME, DATETIME and TIMESTAMP instead of the MariaDB 5.3+ version.
mysql_native_password_proxy_users-OFFWhether MySQL's authentication plugin supports proxy users. I
new-OFFUsed for backward-compatibility with MySQL 4.1, not present in MariaDB.
ngram_token_size-2Sets the n-gram token size for MySQL's n-gram full-text parser.
offline_mode-OFFMySQL settting for specifying whether the server should run in offline mode.
old_modeEmpty string-Used for getting MariaDB to emulate behavior from an old version of MySQL or MariaDB. See OLD Mode.
optimizer_selectivity_sampling_limit100-Controls number of record samples to check condition selectivity.
optimizer_switchSee detailsA series of flags for controlling the query optimizer. MariaDB has introduced a number of new settings.
optimizer_trace-Off by defaultMySQL-only variable for optimizer tracing.
optimizer_trace_features-Off by defaultMySQL-only variable for optimizer tracing.
optimizer_trace_limit-1MySQL-only variable for optimizer tracing.
optimizer_trace_max_mem_size-16384MySQL-only variable for optimizer tracing.
optimizer_trace_offset--1MySQL-only variable for optimizer tracing.
optimizer_use_condition_selectivity1-Controls which statistics can be used by the optimizer when looking for the best query execution plan.
parser_max_mem_size-4294967295 (32-bit) or 18446744073709547520 (64-bit)MySQL variable for limiting memory available to the parser.
performance_schemaOFFONThe Performance Schema is off by default in MariaDB.
performance_schema_*Many performance schema variables are autoset in MySQL.
plugin_maturityunknown-Minimum acceptable plugin maturity.
progress_report_time5-Time in seconds between sending progress reports to the client for time-consuming statements.
query_alloc_block_size163848192Size in bytes of the extra blocks allocated during query parsing and execution (after query_prealloc_size is used up).
query_cache_strip_commentsOFF-Whether to strip any comments from the query before searching to see if it exists in the query cache.
query_prealloc_size245768192Size in bytes of the persistent buffer for query parsing and execution, allocated on connect and freed on disconnect.
range_optimizer_max_mem_size-1536000MySQL-only variable setting a limit on the range optimizer's memory usage.
rbr_exec_mode-STRICTMySQL-only variable for determining the handling of certain key errors.
read_binlog_speed_limit0-Permits restricting the speed at which the slave reads the binlog from the master.
relay_log_info_repository-FILEMySQL-only variable determining whether the slave's position in the relay logs is written to a file or table.
replicate_annotate_row_eventsON-Tells the slave to reproduce annotate_rows_events received from the master in its own binary log.
replicate_do_dbempty string-See Dynamic Replication Variables.
replicate_do_tableempty string-See Dynamic Replication Variables.
replicate_events_marked_for_skipreplicate-See Selectively skipping replication of binlog events.
replicate_ignore_dbempty string-See Dynamic Replication Variables.
replicate_ignore_tableempty string-See Dynamic Replication Variables.
replicate_wild_do_tableempty string-See Dynamic Replication Variables.
replicate_wild_ignore_tableempty string-See Dynamic Replication Variables.
require_secure_transport-OFFMySQL-only variable determining whether client to server connections need to be secure.
rowid_merge_buff_size8388608-See Non-semi-join subquery optimizations.
rpl_stop_slave_timeout-31536000MySQL-only variable for controlling the time that STOP SLAVE waits before timing out.
server_id100 is no longer valid in MariaDB.
server_id_bits-server_idMySQL-only variable for use in MySQL Cluster.
server_uuid-UUIDMySQL-only variable containing the UUID.
session_track_gtids-OFFMySQL-only variables for tracking gtid changes. MariaDB and MySQL's gtid implementation is different.
session_track_system_variablesEmpty*MySQL's version tracks a number of system variable by default.
session_track_transaction_infoOFF*For tracking changes to the transaction attributes.
sha256_password_proxy_users-OFFMySQL-only variable determining whether the sha256_password plugin supports proxy users.
show_compatibility_56-OFFMySQL variable for indicating status of certain compatibility traits between MySQL 5.6 and MySQL 5.7.
show_old_temporals-OFFMySQL-only variable for determining whether SHOW CREATE TABLE output should include comments for old format temporal columns.
skip_parallel_replicationOFF-See parallel replication.
skip_replicationOFF-See Selectively skipping replication of binlog events.
slave_allow_batching-OFFMySQL-only replication variable.
slave_checkpoint_group-512MySQL-only replication variable.
slave_checkpoint_period-300MySQL-only replication variable.
slave_ddl_exec_modeIDEMPOTENT-Modes for how replication of DDL events should be executed.
slave_domain_parallel_threads0-For configuring parallel replication.
slave_net_timeout360060MySQL 5.7 has reduced the timeout to 60s.
slave_parallel_max_queued131072-For configuring parallel replication.
slave_parallel_modeconservative-Controls what transactions are applied in parallel when using parallel_replication.
slave_parallel_threads0-For configuring parallel replication.
slave_parallel_type-DATABASEMySQL-only replication variable.
slave_pending_jobs_size_max-16777216MySQL-only replication variable.
slave_preserve_commit_order-OFFMySQL-only replication variable.
slave_rows_search_algorithms-TABLE_SCAN, INDEX_SCANMySQL-only replication variable.
slave_run_triggers_for_rbrNOSee Running triggers on the slave for Row-based events for a description and use-case for this setting.
sort_buffer_size2097152262144The default sort buffer allocated has been reduced in MySQL.
sql_modeSTRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTIONONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTIONSee SQL Mode.
standard_compliant_cteON-See Common Table Expressions.
storage_engineInnoDB-Alias for default_storage_engine, removed in MySQL.
strict_password_validationON-In MariaDB, when password validation plugins are enabled, reject passwords that cannot be validated (passwords specified as a hash).
super_read_only-OFFMySQL variable for prohibiting client updates from users with the SUPER privilege.
sync_binlog01MySQL 5.7 synchronizes all actions to the binary log before they are committed.
table_definition_cache400-1 (autosized)Number of table definitions that can be cached.
table_open_cache_instances816Maximum number of table cache instances.
thread_cache_sizeAutosized-1 (autosized)MariaDB uses an improved thread pool.
thread_concurrency10-Removed in MySQL 5.7.
thread_pool_idle_timeout60-See Using the Thread Pool.
thread_pool_max_threads65536-See Using the Thread Pool.
thread_pool_min_threads1-Windows-only. See Using the Thread Pool.
thread_pool_oversubscribe3-See Using the Thread Pool.
thread_pool_prio_kickupauto-See Using the Thread Pool.
thread_pool_priorityauto-See Using the Thread Pool.
thread_pool_sizeNumber of processors16*See Using the Thread Pool. *Only available in MySQL with a commercial plugin.
thread_pool_stall_limit5006*See Using the Thread Pool. *Only available in MySQL with a commercial plugin.
thread_stack299008196608 or 262144See Using the Thread Pool.
timed_mutexesOFF-Removed in MySQL 5.7.
tls_version-VariesMySQL-only variable showing the permitted tls protocols.
tmp_disk_table_size18446744073709551615-Max size for data for an internal temporary on-disk MyISAM or Aria table.
tmp_memory_table_size16777216-Alias for tmp_table_size.
transaction_allow_batching-OFFMySQL-only variable for enabling batching of statements within the same transaction in MySQL Cluster.
transaction_write_set_extraction-OFFUnused MySQL-only variable.
use_stat_tablesNEVER-Controls the use of engine-independent table statistics.
userstatOFF-Whether to activate MariaDB's User Statistics implementation, not available in MySQL.
version_malloc_library*-Version of the used malloc library.
version_ssl_library*-Version of the used TLS library.
wsrep_**-Galera cluster is only available in MariaDB.
VariableMariaDB 10.2MySQL 5.7Notes

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.