The following is a comparison of variables that either appear only in MariaDB 10.3 or MySQL 5.7, or have different default settings in MariaDB 10.3, and MySQL 5.7. MariaDB 10.3.10 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 storage engine (resulting in extra memory allocation), Galera Cluster, and has a different . For this reason, a default implementation of MariaDB 10.3 will use more memory than MySQL 5.7. MariaDB 10.3 and MySQL 5.7 also have different .
MariaDB's extra memory usage can be handled with the following rules of thumb:
If you are not using and don't plan to use :
Set to something very low (16K) as it's not used.
Set to what you think you need for handling internal tmp tables that didn't fit in memory.
Normally this is what before you had set for (at least 1M).
If you are using and not planning to use :
Set to what you think you need for handling internal tmp tables that didn't fit in memory.
If you are planning to use , you should set to something that fits a big part of your normal data + overflow temporary tables.
ON
-
Introduced in for between and MySQL/.
0
-
For use in MariaDB's .
100000
-
For use in MariaDB's .
binlog_error_action
ABORT_SERVER
MySQL-only variable for controlling what happens when the server cannot write to the binary log.
16184
-
For setting the size of the file cache for the .
MIXED
ROW
MariaDB and MySQL have differing .
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 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.
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
-
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.
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.
100
-
MariaDB supports .
6
-
MariaDB supports .
DEFAULT_STRATEGY
-
MariaDB supports .
OFF
-
MariaDB supports .
15
-
The Aria storage engine is only available in MariaDB.
4
-
The Aria storage engine is only available in MariaDB.
50000000
-
The Aria storage engine is only available in MariaDB.
10000
-
The Aria storage engine is only available in MariaDB.
OFF
-
Disable system thread alarm calls, for debugging or testing.
default_authentication_plugin
mysql_native_password
MySQL's default authentication plugin.
empty
-
For use with MariaDB's .
default_password_lifetime
0
MySQL-only variable determining how long passwords are valid for before expiring.
empty
-
For handling incompatibilities between MariaDB's and the old regex library.
empty
InnoDB
Default storage engine used for tables created with .
disabled_storage_engines
empty
MySQL-only variable for disabling specific storage engines.
disconnect_on_expired_password
-
ON
MySQL permits passwords to be expired.
OFF
-
MariaDB enables .
OFF
-
MariaDB enables .
OFF
-
MariaDB enables .
end_markers_in_json
-
OFF
MySQL-only variable for adding end markers to JSON output.
enforce_gtid_consistency
-
OFF
MariaDB and MySQL have different .
none
Forces the use of a particular storage engine for new tables.
0
200
Variable for tuning when the optimizer should switch from using index dives to index statistics for qualifying rows estimation.
100
-
Used for determining expensive queries for optimization.
1
-
Introduced in the .
0
-
Introduced in the .
1048576
1024
MariaDB increases the maximum length for a result from 1K to 1M.
empty
-
MariaDB and MySQL have different .
empty
-
MariaDB and MySQL have different .
empty
-
MariaDB and MySQL have different .
0
-
MariaDB and MySQL have different .
gtid_executed
-
empty
MariaDB and MySQL have different .
gtid_executed_compression_period
-
1000
MariaDB and MySQL have different .
OFF
-
MariaDB and MySQL have different .
gtid_mode
-
OFF
MariaDB and MySQL have different .
gtid_next
-
AUTOMATIC
MariaDB and MySQL have different .
gtid_owned
-
empty
MariaDB and MySQL have different .
empty
-
MariaDB and MySQL have different .
gtid_purged
-
empty
MariaDB and MySQL have different .
0
-
MariaDB and MySQL have different .
empty
-
MariaDB and MySQL have different .
OFF
-
MariaDB and MySQL have different .
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.
0
-
MariaDB introduced .
SINGLE_PREC_HB
-
MariaDB introduced .
0
-
Time in seconds that the server waits for idle read-only transactions.
0
-
Time in seconds that the server waits for idle transactions.
0
-
Time in seconds that the server waits for idle write transactions.
0
-
Set to 1 if you are in a transaction, and 0 if not.
*
*
The list of differences between and MySQL 5.7 Innodb variables is currently incomplete.
1
1
MariaDB has an extra mode, 3, for skipping the rollback of connected transactions.
ON
-
Whether redo logging should be reduced when natively creating indexes or rebuilding tables
internal_tmp_disk_storage_engine
-
INNODB
MySQL uses this variable to set the storage engine for on-disk internal temporary tables.
2097152
-
Maximum size in bytes of the query buffer. See ..
2
-
For determining the join algorithms. See .
134217728
8388608
Size of the buffer for the index blocks used by MyISAM tables and shared for all threads.
512
-
Number of hash buckets for open and changed files.
0
-
The number of segments in a key cache. See .
-
empty
MariaDB and MySQL have different .
86400
31536000
MariaDB has reduced the timeout for acquiring metadata locks.
OFF
-
MariaDB setting for whether or not the can be compressed.
256
-
Minimum length of sql statement (in statement mode) or record (in row mode) that can be compressed. See .
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,
sp
-
Disable logging of certain statements to the .
log_error_verbosity
-
3
MySQL variable for setting verbosity of error, warning, and note messages in the error log.
ON
OFF
MariaDB logs slow admin statements to the by default.
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.
1
-
Limits the number of queries logged to the .
ON
OFF
MariaDB logs slow slave statements to the by default.
empty
-
Controls information to be added to the . See also .
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.
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.
16M
4M
max_execution_time
-
0
MySQL renamed the max_statement_time variable.
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.
4294967295
-
Maximum number of iterations when executing recursive queries.
1073741824
0
Can be set by session in MariaDB.
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.
9223372036854775807
-
Amount of memory a single user session is allowed to allocate.
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.
4294967295
4294967295 (32-bit) or 18446744073709547520 (64-bit)
Read lock requests will be permitted for processing after this many write locks.
262144
-
Size of buffer to use when using multi-range read with range access. See .
1024
-
Block size used for index pages.
BACKUP,QUICK
OFF
MyISAM recovery mode.
134216704
8388608
Size in bytes of the buffer allocated when creating or sorting indexes on a MyISAM table.
ON
Causes MariaDB to use the MySQL-5.6 low level formats for , and instead of the + 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.
Empty string
-
Used for getting MariaDB to emulate behavior from an old version of MySQL or MariaDB. See .
100
-
Controls number of record samples to check condition selectivity.
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.
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.
OFF
ON
The is off by default in MariaDB.
Many performance schema variables are autoset in MySQL.
One less than the server maturity
-
Minimum acceptable plugin maturity.
5
-
Time in seconds between sending progress reports to the client for time-consuming statements.
16384
8192
Size in bytes of the extra blocks allocated during query parsing and execution (after query_prealloc_size is used up).
OFF
-
Whether to strip any comments from the query before searching to see if it exists in the .
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
-
STRICT
MySQL-only variable for determining the handling of certain key errors.
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.
ON
-
Tells the slave to reproduce annotate_rows_events received from the master in its own binary log.
empty string
-
See .
empty string
-
See .
replicate
-
See .
empty string
-
See .
empty string
-
See .
empty string
-
See .
empty string
-
See .
require_secure_transport
-
OFF
MySQL-only variable determining whether client to server connections need to be secure.
8388608
-
See .
-
-
includes 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.
NO
-
MariaDB-only option permitting the restricting of direct setting of a session timestamp..
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.
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.
OFF
-
See .
OFF
-
See .
slave_allow_batching
-
OFF
MySQL-only replication variable.
slave_checkpoint_group
-
512
MySQL-only replication variable.
slave_checkpoint_period
-
300
MySQL-only replication variable.
IDEMPOTENT
-
Modes for how replication of DDL events should be executed.
0
-
For configuring .
3600
60
MySQL 5.7 has reduced the timeout to 60s.
131072
-
For configuring .
conservative
-
Controls what transactions are applied in parallel when using .
0
-
For configuring .
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.
NO
See for a description and use-case for this setting.
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 list of errors that should be retried by adding a comma-separated list of error numbers to this variable.
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.
2097152
262144
The default sort buffer allocated has been reduced in MySQL.
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 .
ON
-
See .
InnoDB
-
Alias for , removed in MySQL.
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.
0
1
MySQL 5.7 synchronizes all actions to the binary log before they are committed.
ERROR
-
MariaDB has
DEFAULT
-
MariaDB has
400
-1 (autosized)
Number of table definitions that can be cached.
8
16
Maximum number of table cache instances.
0
-
Interval, in seconds, between when successive keep-alive packets are sent if no acknowledgement is received.
0
-
Number of unacknowledged probes to send before considering the connection dead and notifying the application layer.
0
-
Timeout, in milliseconds, with no activity until the first TCP keep-alive packet is sent.
Autosized
-1 (autosized)
MariaDB uses an .
10
-
Removed in MySQL 5.7.
60
-
See .
65536
-
See .
1
-
Windows-only. See .
3
-
See .
auto
-
See .
auto
-
See .
Number of processors
16*
See . *Only available in MySQL with a commercial plugin.
500
6*
See . *Only available in MySQL with a commercial plugin.
299008
196608 or 262144
See .
OFF
-
Removed in MySQL 5.7.
tls_version
-
Varies
MySQL-only variable showing the permitted tls protocols.
18446744073709551615
-
Max size for data for an internal temporary on-disk MyISAM or Aria table.
16777216
-
Alias for .
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.
NEVER
-
Controls the use of .
OFF
-
Whether to activate MariaDB's implementation, not available in MySQL.
*
-
Version of the used malloc library.
Varies
-
Permits seeing exactly which version of the source was used for a build.
*
-
Version of the used TLS library.
*
-
is only available in MariaDB.
Variable
MySQL 5.7
Notes
Variable
MariaDB 10.3 Default
MySQL 5.7 Default
Notes
DEFAULT
-
MariaDB 10.3 introduces new clauses to avoid slow copies in certain instances. This variable allows setting this if no ALGORITHM clause is specified. .
*
-
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.