Variable

MariaDB 10.3 Default

MySQL 5.7 Default

Notes

aria_*

*

-

The Aria storage engine is only available in MariaDB.

avoid_temporal_upgrade

-

OFF

MySQL-only variable for determining whether ALTER TABLE implicitly upgrades temporal columns.

binlog-annotate-row-events

ON

-

Introduced in MariaDB 5.3 for replicating between MariaDB 5.3 and MySQL/MariaDB 5.1.

binlog_commit_wait_count

0

-

For use in MariaDB's parallel replication.

binlog_commit_wait_usec

100000

-

For use in MariaDB's parallel replication.

binlog_error_action

ABORT_SERVER

MySQL-only variable for controlling what happens when the server cannot write to the binary log.

binlog_file_cache_size

16184

-

For setting the size of the file cache for the binary log.

binlog_format

MIXEDhttp://www.cndba.cn/Expect-le/article/2783

ROW

MariaDB and MySQL have differing binary log formats.

binlog_group_commit_sync_delay

0

MySQL-only variable for controlling the wait time before synchronizing the binary log file to disk.

binlog_group_commit_sync_no_delay_count

0

MySQL-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

-

ON

MySQL-only GTID variable. MariaDB's GTID implementation is different.

binlog_max_flush_queue_time

-

0

MySQL-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_scheduling

ON

-

For optimized kernel thread scheduling.

binlog_order_commits

-

ON

MySQL-only variable for determining whether transactions may be committed in parallel.

binlog_rows_query_log_events

-http://www.cndba.cn/Expect-le/article/2783

OFF

MySQL-only variable for logging extra information in row-based logging.

block_encryption_mode

-

aes-128-ecb

MySQL-only variable for controlling the block encryption mode for block-based algorithms.

check_constraint_checks

ON

-

Permits disabling constraint checks, for example when loading a table that violates some constraints that you plan to fix later.

check_proxy_users

OFF

MySQL-only variable for controlling whether the server performs proxy user mapping for authentication plugins.

column_compression_threshold

100

-

MariaDB supports Storage-engine Independent Column Compression.

column_compression_zlib_level

6

-

MariaDB supports Storage-engine Independent Column Compression.

column_compression_zlib_strategy

DEFAULT_STRATEGY

-

MariaDB supports Storage-engine Independent Column Compression.

column_compression_zlib_wrap

OFF

-

MariaDB supports Storage-engine Independent Column Compression.

core_file

-

OFF

MySQL-only variable indicating whether a core file will be written in case of a crash.

deadlock_search_depth_long

15

-

The Aria storage engine is only available in MariaDB.

deadlock_search_depth_short

4

-

The Aria storage engine is only available in MariaDB.

deadlock_timeout_long

50000000

-

The Aria storage engine is only available in MariaDB.

deadlock_timeout_short

10000

-

The Aria storage engine is only available in MariaDB.

debug_no_thread_alarm

OFF

-

Disable system thread alarm calls, for debugging or testing.

default_authentication_plugin

mysql_native_password

MySQL's default authentication plugin.

default_master_connection

empty

-

For use with MariaDB's multi-source replication.

default_password_lifetime

360

MySQL-only variable determining how long passwords are valid for before expiring.

default_regex_flags

empty

-

For handling incompatibilities between MariaDB's PCRE and the old regex library.

default_tmp_storage_engine

empty

InnoDB

Default storage engine used for tables created with CREATE TEMPORARY TABLE.

disabled_storage_engines

empty

MySQL-only variable for disabling specific storage engines.

disconnect_on_expired_password

-

ON

MySQL permits passwords to be expired.

encrypt_binlog

OFF

-

MariaDB enables table and tablespace encryption.

encrypt_tmp_files

OFF

-

MariaDB enables table and tablespace encryption.

encrypt_tmp_disk_tables

OFF

-

MariaDB enables table and tablespace encryption.

end_markers_in_json

-

OFF

MySQL-only variable for adding end markers to JSON output.

enforce_gtid_consistency

-

OFF

MariaDB and MySQL have different GTID implementations.

enforce_storage_engine

none

Forces the use of a particular storage engine for new tables.

eq_range_index_dive_limit

-

200

MySQL-only variable for tuning when the optimizer should switch from using index dives to index statistics for qualifying rows estimation.

expensive_subquery_limit

100

-

Used for determining expensive queries for optimization.

extra_max_connections

1

-

Introduced in the MariaDB 5.1 threadpool.

extra_port

0

-

Introduced in the MariaDB 5.1 threadpool.

group_concat_max_len

1048576

1024

MariaDB increases the maximum length for a GROUP_CONCAT() result from 1K to 1M.

gtid_binlog_pos

empty

-

MariaDB and MySQL have different GTID implementations.

gtid_binlog_state

empty

-

MariaDB and MySQL have different GTID implementations.

gtid_current_pos

empty

-

MariaDB and MySQL have different GTID implementations.

gtid_domain_id

0

-

MariaDB and MySQL have different GTID implementations.

gtid_executed

-

empty

MariaDB and MySQL have different GTID implementations.

gtid_executed_compression_period

-

1000

MariaDB and MySQL have different GTID implementations.

gtid_ignore_duplicates

OFF

-

MariaDB and MySQL have different GTID implementations.

gtid_mode

-

OFF

MariaDB and MySQL have different GTID implementations.

gtid_next

-

AUTOMATIC

MariaDB and MySQL have different GTID implementations.

gtid_owned

-

empty

MariaDB and MySQL have different GTID implementations.

gtid_pos_auto_engines

empty

-

MariaDB and MySQL have different GTID implementations.

gtid_purged

-

empty

MariaDB and MySQL have different GTID implementations.

gtid_seq_no

0

-

MariaDB and MySQL have different GTID implementations.

gtid_slave_pos

empty

-

MariaDB and MySQL have different GTID implementations.

gtid_strict_mode

OFF

-

MariaDB and MySQL have different GTID implementations.

have_openssl

MariaDB'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_size

0

-

MariaDB introduced Histogram-based Statistics.

histogram_type

SINGLE_PREC_HB

-

MariaDB introduced Histogram-based Statistics.

idle_readonly_transaction_timeout

0

-

Time in seconds that the server waits for idle read-only transactions.

idle_transaction_timeout

0

-

Time in seconds that the server waits for idle transactions.

idle_write_transaction_timeout

0

-

Time in seconds that the server waits for idle write transactions.

in_transaction

0

-

Set to 1 if you are in a transaction, and 0 if not.

innodb_*

*

*

The list of differences between MariaDB 10.3 and MySQL 5.7 Innodb variables is currently incomplete.

innodb_fast_shutdown

1

1

MariaDB has an extra mode, 3, for skipping the rollback of connected transactions.

internal_tmp_disk_storage_engine

-

INNODB

MySQL uses this variable to set the storage engine for on-disk internal temporary tables.

join_buffer_space_limit

2097152

-

Maximum size in bytes of the query buffer. See block-based join algorithms.

join_cache_level

2

-

For determining the join algorithms. See block-based join algorithms

key_buffer_size

134217728

8388608

Size of the buffer for the index blocks used by MyISAM tables and shared for all threads.

key_cache_file_hash_size

512

-

Number of hash buckets for open and changed files.

key_cache_segments

0

-

The number of segments in a key cache. See Segmented Key Cache.

last_gtid

-

empty

MariaDB and MySQL have different GTID implementations.

lock_wait_timeout

86400

31536000http://www.cndba.cn/Expect-le/article/2783

MariaDB has reduced the timeout for acquiring metadata locks.

log_bin_use_v1_row_events

-

OFF

MySQL-only variable showing whether or not MySQL's version 2 binary logging format is being used.

log_builtin_as_identified_by_password

-

OFF

MySQL variable for use with binary logging of user-management statements,

log_disabled_statements

empty

-

Disable logging of certain statements to the general log.

log_error_verbosity

-

3

MySQL variable for setting verbosity of error, warning, and note messages in the error log.

log_slow_admin_statements

ON

OFF

MariaDB logs slow admin statements to the slow query log by default.

log_slow_filter

admin, 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_limit

1

-

Limits the number of queries logged to the slow query log.

log_slow_slave_statements

ON

OFF

MariaDB logs slow slave statements to the slow query log by default.

log_slow_verbosity

empty

-

Controls information to be added to the slow query log. See also Slow Query Log Extended Statistics.

log_statements_unsafe_for_binlog

-

ON

MySQL 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_size

24576

-

Size in bytes of the transaction coordinator log, defined in multiples of 4096.

log_throttle_queries_not_using_indexes

-

0

MySQL-only variable for limiting the number of statements without indexes written to the slow query log.

log_timestamps

-

UTC

MySQL-only variable controlling the timezone for certain logging conditions.

master_info_repository

-

FILE

MySQL-only variable determining whether the slaves log info to file or table.

max_allowed_packet

16M

4M

max_execution_time

-

0

MySQL renamed the max_statement_time variable.

max_long_data_size

16777216

-

Maximum size for parameter values sent with mysql_stmt_send_long_data(). Removed in MySQL 5.6.

max_points_in_geometry

-

65536

Maximum points_per_circle for MySQL's ST_Buffer_Strategy() function.

max_session_mem_used

9223372036854775807

-

Amount of memory a single user session is allowed to allocate.

max_relay_log_size

1073741824

0

Can be set by session in MariaDB.

max_recursive_iterations

4294967295

-

Maximum number of iterations when executing recursive queries.

max_seeks_for_key

4294967295

4294967295 (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_time

0

-

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_count

4294967295

4294967295 (32-bit) or 18446744073709547520 (64-bit)

Read lock requests will be permitted for processing after this many write locks.

mrr_buffer_size

262144

-

Size of buffer to use when using multi-range read with range access. See Multi Range Read optimization.

myisam_block_size

1024

-

Block size used for MyISAM index pages.

myisam_recover_options

BACKUP,QUICK

OFF

MyISAM recovery mode.

myisam_sort_buffer_size

134216704

8388608

Size in bytes of the buffer allocated when creating or sorting indexes on a MyISAM table.

mysql56_temporal_format

ON

Causes 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

-

OFF

Whether MySQL's authentication plugin supports proxy users. I

new

-

OFF

Used for backward-compatibility with MySQL 4.1, not present in MariaDB.

ngram_token_size

-

2

Sets the n-gram token size for MySQL's n-gram full-text parser.

offline_mode

-

OFF

MySQL settting for specifying whether the server should run in offline mode.

old_mode

Empty string

-

Used for getting MariaDB to emulate behavior from an old version of MySQL or MariaDB. See OLD Mode.

optimizer_selectivity_sampling_limit

100

-

Controls number of record samples to check condition selectivity.

optimizer_switch

See details

A series of flags for controlling the query optimizer. MariaDB has introduced a number of new settings.

optimizer_trace

-

Off by default

MySQL-only variable for optimizer tracing.

optimizer_trace_features

-

Off by default

MySQL-only variable for optimizer tracing.

optimizer_trace_limit

-

1

MySQL-only variable for optimizer tracing.

optimizer_trace_max_mem_size

-

16384

MySQL-only variable for optimizer tracing.

optimizer_trace_offset

-

-1

MySQL-only variable for optimizer tracing.

optimizer_use_condition_selectivity

1

-

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_schema

OFF

ON

The Performance Schema is off by default in MariaDB.

performance_schema_*

Many performance schema variables are autoset in MySQL.

plugin_maturity

One less than the server maturity

-

Minimum acceptable plugin maturity.

progress_report_time

5

-

Time in seconds between sending progress reports to the client for time-consuming statements.

query_alloc_block_size

16384

8192

Size in bytes of the extra blocks allocated during query parsing and execution (after query_prealloc_size is used up).

query_cache_strip_comments

OFF

-

Whether to strip any comments from the query before searching to see if it exists in the query cache.

query_prealloc_size

24576

8192

Size in bytes of the persistent buffer for query parsing and execution, allocated on connect and freed on disconnect.

range_optimizer_max_mem_size

-

1536000

MySQL-only variable setting a limit on the range optimizer's memory usage.

rbr_exec_mode

-

STRICThttp://www.cndba.cn/Expect-le/article/2783

MySQL-only variable for determining the handling of certain key errors.

read_binlog_speed_limit

0

-

Permits restricting the speed at which the slave reads the binlog from the master.

relay_log_info_repository

-

FILE

MySQL-only variable determining whether the slave's position in the relay logs is written to a file or table.

replicate_annotate_row_events

ON

-

Tells the slave to reproduce annotate_rows_events received from the master in its own binary log.

replicate_do_db

empty string

-

See Dynamic Replication Variables.

replicate_do_table

empty string

-

See Dynamic Replication Variables.

replicate_events_marked_for_skip

replicate

-

See Selectively skipping replication of binlog events.

replicate_ignore_db

empty string

-

See Dynamic Replication Variables.

replicate_ignore_table

empty string

-

See Dynamic Replication Variables.

replicate_wild_do_table

empty string

-

See Dynamic Replication Variables.

replicate_wild_ignore_table

empty string

-

See Dynamic Replication Variables.

require_secure_transport

-

OFF

MySQL-only variable determining whether client to server connections need to be secure.

rowid_merge_buff_size

8388608

-

See Non-semi-join subquery optimizations.

rpl_semi_sync_*

-

-

MariaDB 10.3 includes semisynchronous replication without the need to install a plugin.

rpl_stop_slave_timeout

-

31536000

MySQL-only variable for controlling the time that STOP SLAVE waits before timing out.

server_id

1

0

0 is no longer valid in MariaDB.

server_id_bits

-

server_id

MySQL-only variable for use in MySQL Cluster.

server_uuid

-

UUID

MySQL-only variable containing the UUID.

session_track_gtids

-

OFF

MySQL-only variables for tracking gtid changes. MariaDB and MySQL's gtid implementation is different.

session_track_transaction_info

OFF

*

For tracking changes to the transaction attributes.

sha256_password_proxy_users

-

OFF

MySQL-only variable determining whether the sha256_password plugin supports proxy users.

show_compatibility_56

-

OFF

MySQL variable for indicating status of certain compatibility traits between MySQL 5.6 and MySQL 5.7.

show_old_temporals

-

OFF

MySQL-only variable for determining whether SHOW CREATE TABLE output should include comments for old format temporal columns.

skip_parallel_replication

OFF

-

See parallel replication.

skip_replication

OFF

-

See Selectively skipping replication of binlog events.

slave_allow_batching

-

OFF

MySQL-only replication variable.

slave_checkpoint_group

-

512

MySQL-only replication variable.

slave_checkpoint_period

-

300

MySQL-only replication variable.

slave_ddl_exec_mode

IDEMPOTENT

-

Modes for how replication of DDL events should be executed.

slave_domain_parallel_threads

0

-

For configuring parallel replication.

slave_net_timeout

3600

60

MySQL 5.7 has reduced the timeout to 60s.

slave_parallel_max_queued

131072

-

For configuring parallel replication.

slave_parallel_mode

conservative

-

Controls what transactions are applied in parallel when using parallel_replication.

slave_parallel_threads

0

-

For configuring parallel replication.

slave_parallel_type

-

DATABASE

MySQL-only replication variable.

slave_pending_jobs_size_max

-

16777216

MySQL-only replication variable.

slave_preserve_commit_order

-

OFF

MySQL-only replication variable.

slave_rows_search_algorithms

-

TABLE_SCAN, INDEX_SCAN

MySQL-only replication variable.

slave_run_triggers_for_rbr

NO

See Running triggers on the slave for Row-based events for a description and use-case for this setting.

slave_transaction_retry_errors

1213,1205

-

When an error occurs during a transaction on the slave, replication usually halts. By default, transactions that caused a deadlock or elapsed lock wait timeout will be retried. One can add other errors to the the list of errors that should be retried by adding a comma-separated list of error numbers to this variable.

slave_transaction_retry_interval

0

-

Interval in seconds for the slave SQL thread to retry a failed transaction due to a deadlock, elapsed lock wait timeout or an error listed in slave_transaction_retry_errors.

sort_buffer_size

2097152

262144

The default sort buffer allocated has been reduced in MySQL.

sql_mode

STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

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

See SQL Mode.

standard_compliant_cte

ON

-

See Common Table Expressions.

storage_engine

InnoDB

-

Alias for default_storage_engine, removed in MySQL.

strict_password_validation

ON

-

In MariaDB, when password validation plugins are enabled, reject passwords that cannot be validated (passwords specified as a hash).

super_read_only

-

OFF

MySQL variable for prohibiting client updates from users with the SUPER privilege.

sync_binlog

0

1

MySQL 5.7 synchronizes all actions to the binary log before they are committed.

system_versioning_alter_history

ERROR

-

MariaDB has System-Versioned Tables

system_versioning_asof

DEFAULT

-

MariaDB has System-Versioned Tables

table_definition_cache

400

-1 (autosized)

Number of table definitions that can be cached.

table_open_cache_instances

8

16

Maximum number of table cache instances.

tcp_keepalive_interval

0

-

Interval, in seconds, between when successive keep-alive packets are sent if no acknowledgement is received.

tcp_keepalive_probes

0

-

Number of unacknowledged probes to send before considering the connection dead and notifying the application layer.

tcp_keepalive_time

0

-

Timeout, in milliseconds, with no activity until the first TCP keep-alive packet is sent.

thread_cache_size

Autosized

-1 (autosized)

MariaDB uses an improved thread pool.

thread_concurrency

10

-

Removed in MySQL 5.7.

thread_pool_idle_timeout

60

-

See Using the Thread Pool.

thread_pool_max_threads

65536

-

See Using the Thread Pool.

thread_pool_min_threads

1

-

Windows-only. See Using the Thread Pool.

thread_pool_oversubscribe

3

-

See Using the Thread Pool.

thread_pool_prio_kickup

auto

-

See Using the Thread Pool.

thread_pool_priority

auto

-

See Using the Thread Pool.

thread_pool_size

Number of processors

16*

See Using the Thread Pool. *Only available in MySQL with a commercial plugin.

thread_pool_stall_limit

500

6*

See Using the Thread Pool. *Only available in MySQL with a commercial plugin.

thread_stack

299008

196608 or 262144

See Using the Thread Pool.

timed_mutexes

OFF

-

Removed in MySQL 5.7.

tls_version

-

OFF

MySQL-only variable showing the permitted tls protocols.

transaction_allow_batching

-

OFF

MySQL-only variable for enabling batching of statements within the same transaction in MySQL Cluster.

transaction_write_set_extraction

-

OFF

Unused MySQL-only variable.

use_stat_tables

NEVER

-

Controls the use of engine-independent table statistics.

userstat

OFF

-

Whether to activate MariaDB's User Statistics implementation, not available in MySQL.

version_malloc_library

*

-

Version of the used malloc library.

version_source_revision

Varies

-

Permits seeing exactly which version of the source was used for a build.

version_ssl_library

*

-

Version of the used TLS library.

wsrep_*

*

-

Galera cluster is only available in MariaDB.