The following is a comparison of variables that either appear only in MariaDB 10.4 or MySQL 8.0, or have different default settings in MariaDB 10.4 and MySQL 8.0. The stable releases MariaDB 10.4.6 and MySQL 8.0.11, with only default plugins enabled, were used for the comparison. Note that MySQL 8 is an 'evergreen' release, so features may be added or removed in later releases.
For a more complete list of differences, see Incompatibilities and Feature Differences Between MariaDB 10.4 and MySQL 8.0 and Function Differences Between MariaDB 10.4 and MySQL 8.0
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.4 will use more memory than MySQL 8.0. MariaDB 10.4 and MySQL 8.0 also have different GTID implementations.
MariaDB's extra memory usage can be handled with the following rules of thumb:
If you are not using MyISAM and don't plan to use Aria:
Set key_buffer_size to something very low (16K) as it's not used.
Set aria_pagecache_buffer_size 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 key_buffer_size (at least 1M).
If you are using MyISAM and not planning to use Aria:
Set aria_pagecache_buffer_size to what you think you need for handling internal tmp tables that didn't fit in memory.
If you are planning to use Aria, you should set aria_pagecache_buffer_size to something that fits a big part of your normal data + overflow temporary tables.
DEFAULT
-
MariaDB 10.3 introduced new ALTER TABLE ALGORITHM clauses to avoid slow copies in certain instances. This variable allows setting this if no ALGORITHM clause is specified.
auto_generate_certs
-
ON
Whether to automatically generate SSL key and certificate files.
avoid_temporal_upgrade
-
OFF
Determines whether ALTER TABLE implicitly upgrades temporal columns.
binlog_error_action
ABORT_SERVER
MySQL-only variable for controlling what happens when the server cannot write to the binary log.
binlog_expire_logs_seconds
-
2592000
Sets the binary log expiration period in seconds
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_max_flush_queue_time
-
0
Specifies a timeout for reading transactions from the flush queue before continuing with group commit and syncing log to disk.
binlog_order_commits
-
ON
Determines whether transactions may be committed in parallel.
binlog_row_metadata
-
MINIMAL
Determines the amount of table metadata added to the binary log with row-based logging.
binlog_row_value_options
-
(empty)
Permits an alternative binlog format for JSON document updates.
binlog_rows_query_log_events
-
OFF
MySQL-only variable for logging extra information in row-based logging.
binlog_transaction_dependency_history_size
-
25000
Maximum number of row hashes kept for looking up transactions that last modified a given row.
binlog_transaction_dependency_tracking
-
COMMIT_ORDER
For determining how to best use the slave's multithreaded applier.
block_encryption_mode
-
aes-128-ecb
MySQL-only variable for controlling the block encryption mode for block-based algorithms.
caching_sha2_password*
-
*
For use with MySQL's SHA-256 authentication with caching.
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.
latin1_swedish_ci or utf8_general_ci
utf8mb4_0900_ai_ci
MySQL 8.0 defaults to the utf8mb4 character set.
cte_max_recursion_depth
-
1000
When MySQL 8.0 introduced common table expressions they used a different name. MariaDB's variable is called max_recursive_iterations.
default_authentication_plugin
-
caching_sha2_password
MySQL 8 introduced a new authentication plugin.
default_collation_for_utf8mb4
-
utf8mb4_0900_ai_ci
For internal use in MySQL 8 replication.
disabled_storage_engines
empty
MySQL-only variable for disabling specific storage engines.
OFF
ON
MariaDB password expiration is off by default, and by default does not disconnect a client when a password has expired.
end_markers_in_json
-
OFF
MySQL-only variable for adding end markers to JSON output.
0
200
Variable for tuning when the optimizer should switch from using index dives to index statistics for qualifying rows estimation.
MariaDB's version indicates whether YaSSL or openssl was used. MySQL's is a synonym for have_ssl.
have_statement_timeout
-
Whether MySQL's statement execution timeout feature is available.
histogram_generation_max_mem_size
-
20000000
Added when MySQL 8 introduced Histogram-based Statistics. MariaDB uses histogram_size
0
-
Time in seconds that the server waits for idle read-only transactions.
information_schema_stats_expiry
-
86400
Time until MySQL Information Schema cached statistics expire.
10.000000
10
1
2
MariaDB has an extra mode, 3, for skipping the rollback of connected transactions. MySQL defaults to row-based replication, so can safely use 2.
innodb_dedicated_server
-
OFF
MySQL option that automatically configures various settings if the server is a dedicated InnoDB database server.
innodb_directories
-
(empty)
Used to search for tablespace files when moving or restoring a new location.
OFF
-
If set to 1 in MariaDB (0 is default) CREATE TABLEs without a primary or unique key where all keyparts are NOT NULL will not be accepted, and will return an error.
100
-
Up to what percentage of dirty pages in MariaDB should be flushed when InnoDB finds it has spare resources to do so.
VATS
-
MariaDB has an improved algorithm for deciding which of the waiting transactions should be granted a lock once it has been released.
OFF
-
Deprecated option in MariaDB for disabling gap locking for searches and index scans. Deprecated in MariaDB, use READ COMMITTED transaction isolation instead.
ON
-
Whether redo logging should be reduced when natively creating indexes or rebuilding tables
innodb_log_spin_*
-
*
MySQL variables for constraining CPU usage while waiting for flushed redo.
innodb_log_wait_for_flush_spin_hwm
-
*
MySQL variable for constraining CPU usage while waiting for flushed redo.
10485760
1073741824
MariaDB 10.2 reduced the limit for when an undo tablespace is marked for truncation.
Autosized (2000)
Autosized (4000)
In most systems, autosized based on the table_open_cache setting, which differs between MariaDB and MySQL.
OFF
-
MariaDB includes the Facebook prefix index queries optimization.
innodb_print_ddl_logs
-
OFF
MySQL option for writing DDL logs to stderr.
innodb_redo_log_encrypt
-
OFF
MySQL 8 has also now introduced redo log encryption, but used a different name. The equivalent option in MariaDB is innodb_encrypt_log.
8
-
Deprecated MariaDB option for control over index distribution statistics.
ON
-
Enabling gives a larger sample of pages for larger tables for the purposes of index statistics calculation.
innodb_undo_log_encrypt
-
OFF
MySQL option for encrypting undo logs residing in separate undo tablespaces.
OFF
ON
MySQL 8 changes the default to ON, marking larger undo logs for truncation.
0
2
Number of tablespace files used for dividing up the undo logs. MySQL 8 has deprecated this setting, and increased the default (and minimum) to 2.
ON
-
Atomic writes are a faster alternative to innodb_doublewrite and MariaDB automatically detects when supporting SSD cards are used.
internal_tmp_disk_storage_engine
-
INNODB
MySQL uses this variable to set the storage engine for on-disk internal temporary tables.
internal_tmp_mem_storage_engine
-
TEMPTABLE
MySQL and MariaDB use different formats for temporary tables. In MariaDB, the aria_used_for_temp_tables performs a similar function.
134217728
8388608
Size of the buffer for the index blocks used by MyISAM tables and shared for all threads.
keyring_operations
-
ON
Whether MySQL 8's keyring operations are enabled.
256
-
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
-
OFF
MySQL-only variable showing whether or not MySQL's version 2 binary logging format is being used.
log_error_services
-
log_filter_internal; log_sink_internal
Components to enable for MySQL error logging.
log_error_verbosity
-
3
MySQL variable for setting verbosity of error, warning, and note messages in the error log.
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.
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_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
-
TABLE
Whether slave logs master status and connection info to a table or a file.
64
1024
Specifies the maximum number of messages stored for display by SHOW ERRORS and SHOW WARNINGS statements.
max_execution_time
-
0
MySQL renamed the max_statement_time variable.
64
1024
Used to decide which algorithm to choose when sorting rows. If the total size of the column data, not including columns that are part of the sort, is less than max_length_for_sort_data, then these are added to the sort key. This can speed up the sort as there's no need to re-read the same row again later. Setting the value too high can slow things down as there will be a higher disk activity for doing the sort.
16777216
-
Maximum size for parameter values sent with mysql_stmt_send_long_data(). Removed in MySQL 5.6.
4294967295
-
Maximum number of failed connections attempts before no more are permitted.
max_points_in_geometry
-
65536
Maximum points_per_circle for MySQL's ST_Buffer_Strategy() function.
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 Multi Range Read optimization.
134216704
8388608
Size in bytes of the buffer allocated when creating or sorting indexes on a MyISAM table.
mysql_native_password_proxy_users
-
OFF
Whether MySQL's authentication plugin supports proxy users. I
ON
Causes MariaDB to use the MySQL-5.6 low level formats for TIME, DATETIME and TIMESTAMP instead of the MariaDB 5.3+ version.
new
-
OFF
Used for backward-compatibility with MySQL 4.1, not present in MariaDB.
mysqlx+*
-
*
MySQL's X plugin related variables.
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 OLD Mode.
OFF
-
MySQL 8 is no longer compatible with the old pre-MySQL 4.1 form of password hashing.
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_*
-
*
MySQL has more settings for optimizer tracing.
4
-
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.
password_*
-
*
Controls reuse of previous passwords in MySQL.
Many performance schema variables are autoset in MySQL, and MySQL has a different version, with additional variables.
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).
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
-
8388608
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.
regexp_*
-
*
Memory and time limits for regular expression matching operations.
relay_log_info_repository
-
TABLE
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.
require_secure_transport
-
OFF
MySQL-only variable determining whether client to server connections need to be secure.
result_metadata
-
FULL
Determine whether the server returns result set metadata for connections where this is optional.
rpl_read_size
-
8192
Minimum data in bytes read from the binary and relay log files.
schema_definition_cache
-
256
Limits the number of schema definition objects kept in the dictionary object cache.
NO
-
MariaDB-only option permitting the restricting of direct setting of a session timestamp..
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.
sha256_password_proxy_users
-
OFF
MySQL-only variable determining whether the sha256_password plugin supports proxy users.
show_create_table_verbosity
-
OFF
Option to cause SHOW CREATE TABLE to display ROW_FORMAT in all cases.
show_old_temporals
-
OFF
MySQL-only variable for determining whether SHOW CREATE TABLE output should include comments for old format temporal columns.
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_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
-
INDEX_SCAN, HASH_SCAN
MySQL-only replication variable.
NO
See Running triggers on the slave for Row-based events 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.
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_ENGINE_SUBSTITUTION
See SQL Mode.
ssl_fips_mode
-
OFF
Whether FIPS mode is enabled on the server side.
ON
-
In MariaDB, when password validation plugins are enabled, reject passwords that cannot be validated (passwords specified as a hash).
stored_program_definition_cache
-
256
Limits the number of stored program definition objects kept in the dictionary object cache.
super_read_only
-
OFF
MySQL variable for prohibiting client updates from users with the SUPER privilege.
tablespace_definition_cache
-
256
Limits the number of tablespace definition objects kept in the dictionary object cache.
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.
1
-
Timeout, in milliseconds, with no activity until the first TCP keep-alive packet is sent.
temptable_max_ram
-
1GB
Limits the RAM used by MySQL's TempTable storage engine.
Number of processors
16*
See Using the Thread Pool. *Only available in MySQL with a commercial plugin.
18446744073709551615
-
Max size for data for an internal temporary on-disk MyISAM or Aria table.
transaction_allow_batching
-
OFF
Variable for enabling batching of statements within the same transaction in MySQL Cluster.
transaction_write_set_extraction
-
OFF
Unused MySQL-only variable.
version_compile_zlib
-
*
Version of the zlib library compiled in.
Varies
-
Permits seeing exactly which version of the source was used for a build.
windowing_high_use_precision
-
*
MySQL option allowing safety to be sacrificed for speed in window function calculations.