This is a read-only copy of the MariaDB Knowledgebase generated on 2024-12-18. For the latest, interactive version please visit https://mariadb.com/kb/.

Created: 3 years, 4 months ago
Modified: 11 months, 3 weeks ago
Type: article
Status: active
License: CC BY-SA / Gnu FDL

Attachments

No attachments exist

System Variable Differences Between MariaDB 10.6 and MySQL 8.0

Comparison Table
See Also

The following is a comparison of variables that either appear only in MariaDB 10.6 or MySQL 8.0, or have different default settings in MariaDB 10.6, and MySQL 8.0. The stable releases MariaDB 10.6.4 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.6 and MySQL 8.0 and Function Differences Between MariaDB 10.6 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.6 will use more memory than MySQL 8.0. MariaDB 10.6 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.

Comparison Table

Variable	MariaDB 10.6 Default	MySQL 8.0 Default	Notes
activate_all_roles_on_login	-	OFF	Determines whether to automatically activate roles on login.
alter_algorithm	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.
analyze_sample_percentage	100.0000	-	Percentage of rows from the table ANALYZE TABLE will sample to collect table statistics.
aria_*	*	-	The Aria storage engine is only available in MariaDB.
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.
back_log	Autosized	Autosized	MariaDB and MySQL have different autosizing algorithms.
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_expire_logs_seconds	0	2592000	Sets the binary log expiration period in seconds
binlog_file_cache_size	16184	-	For setting the size of the file cache for the binary log.
binlog_format	MIXED	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	Specifies 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	Determines whether transactions may be committed in parallel.
binlog_row_metadata	NO_LOG	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.
character_set_*	latin1 or utf8	utf8mb4	MySQL 8.0 defaults to the utf8mb4 character set.
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.
collation_*	latin1_swedish_ci or utf8_general_ci	utf8mb4_0900_ai_ci	MySQL 8.0 defaults to the utf8mb4 character set.
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.
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.
date_format	%Y-%m-%d	-	Unused variable removed in MySQL 8.0
datetime_format	%Y-%m-%d	-	Unused variable removed in MySQL 8.0
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	-	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.
default_master_connection	empty	-	For use with MariaDB's multi-source replication.
default_password_lifetime	0	360	MariaDB defaults to password expiration off.
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	OFF	ON	MariaDB password expiration is off by default, and by default does not disconnect a client when a password has 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	0	200	Variable for tuning when the optimizer should switch from using index dives to index statistics for qualifying rows estimation.
event_scheduler	OFF	ON	MySQL enables the event scheduler by default.
expensive_subquery_limit	100	-	Used for determining expensive queries for optimization.
explicit_defaults_for_timestamp	OFF	ON	MySQL 8 disables the old timestamp behavior.
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_cleanup_batch_size	64	-	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_crypt	YES	-	MySQL has removed the ENCRYPT function.
have_openssl			MariaDB's version indicates whether YaSSL or openssl was used. MySQL's is a synonym for `have_ssl`.
have_query_cache	YES	-	MySQL has removed the query cache.
have_statement_timeout	-		Whether MySQL's statement execution timeout feature is available.
have_symlink	YES	DISABLED	MySQL has removed symlink support.
histogram_generation_max_mem_size	-	20000000	Added when MySQL 8 introduced Histogram-based Statistics. MariaDB uses histogram_size
histogram_size	0	-	MariaDB introduced Histogram-based Statistics.
histogram_type	DOUBLE_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.
ignore_builtin_innodb	OFF	-	Ignored and removed in MySQL 8.
in_predicate_conversion_threshold	1000	-	Controls the Conversion of Big IN Predicates Into Subqueries optimization.
in_transaction	0	-	Set to `1` if you are in a transaction, and `0` if not.
information_schema_stats_expiry	-	86400	Time until MySQL Information Schema cached statistics expire.
innodb_adaptive_flushing_lwm	10.000000	10	Adaptive flushing is enabled when this this low water mark percentage of the redo log capacity is reached. MariaDB's variable is a double, MySQL's an integer.
innodb_adaptive_hash_index	OFF	ON	Defaulting to OFF is a performance improvement especially for DROP TABLE, TRUNCATE TABLE, ALTER TABLE, or DROP INDEX operations
innodb_adaptive_max_sleep_delay	-	150000	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6.
innodb_api_*	-	*	Specific to MySQL's memcached, removed in MariaDB 10.2.
innodb_autoinc_lock_mode	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_buffer_pool_instances	-	1	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6 since the original reasons for introducing no longer apply.
innodb_checksum_algorithm	full_crc32	crc32	`fullcrc32` permits encryption to be supported over a SPATIAL INDEX, which crc32 does not support.
innodb_commit_concurrency	-	0	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6.
innodb_compression_*	*	-	Introduced with MariaDB's InnoDB compression.
innodb_concurrency_tickets	-	5000	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6.
innodb_deadlock_report	Full	-	How to report deadlocks.
innodb_dedicated_server	-	OFF	MySQL option that automatically configures various settings if the server is a dedicated InnoDB database server.
innodb_default_encryption_key_id	1	-	Default encryption key id used for table encryption. See Data at Rest Encryption.
innodb_defragment	*	-	MariaDB can defragment InnoDB tablespaces.
innodb_directories	-	(empty)	Used to search for tablespace files when moving or restoring a new location.
innodb_disallow_writes	OFF	-	Tell InnoDB to stop any writes to disk.
innodb_encrypt_*	1	-	See MariaDB's Data at Rest Encryption.
innodb_fatal_semaphore_wait_threshold	600	-	MariaDB's fatal semaphore timeout is configurable.
innodb_flush_method	O_DIRECT	fsync	MariaDB 10.6 InnoDB flushing method by default on Unix systems bypasses the file system cache for improved performance in most cases.
innodb_flush_neighbors	1	0	MySQL 8 by default now assumes the use of an SSD device.
innodb_force_primary_key	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.
innodb_immediate_scrub_data_uncompressed	OFF	-	MariaDB has support for data scrubbing.
innodb_instant_alter_column_allowed	add_drop_reorder	-	See Instant ADD COLUMN for InnoDB.
innodb_log_checksums	-	ON	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6, as there is no reason to allow checksums to be disabled on the redo log.
innodb_log_compressed_pages	-	ON	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6 as part of the InnoDB redo log performance improvements.
innodb_log_files_in_group	-	2	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6 as part of the InnoDB redo log performance improvements.
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.
innodb_max_dirty_pages_pct	75	90	MySQL 8 increased the default to 90.
innodb_max_dirty_pages_pct_lwm	0	10	MySQL 8 increased the default to 10.
innodb_max_undo_log_size	10485760	1073741824	MariaDB 10.2 reduced the limit for when an undo tablespace is marked for truncation.
innodb_open_files	Autosized (2000)	Autosized (4000)	In most systems, autosized based on the table_open_cache setting, which differs between MariaDB and MySQL.
innodb_page_cleaners	-	1	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6 as the original reasons for for splitting the buffer pool have mostly gone away.
innodb_prefix_index_cluster_optimization	OFF	-	MariaDB includes the Facebook prefix index queries optimization.
innodb_print_ddl_logs	-	OFF	MySQL option for writing DDL logs to stderr.
innodb_read_only_compressed	ON	-	Whether to set ROW_FORMAT=COMPRESSED tables to read-only.
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.
innodb_replication_delay	-	0	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6.
innodb_rollback_segments	-	128	Removed in MariaDB as part of the InnoDB cleanup. It makes sense to always create and use the maximum number of rollback segments.
innodb_spin_wait_delay	4	6	MariaDB changed the default from 6 to 4 based on extensive benchmarking.
innodb_stats_modified_counter	0	-	MariaDB option to control the calculation of new statistics.
innodb_stats_traditional	ON	-	Enabling gives a larger sample of pages for larger tables for the purposes of index statistics calculation.
innodb_sync_array_size	-	1	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6.
innodb_thread_concurrency	-	0	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6.
innodb_thread_sleep_delay	-	10000	Deprecated and ignored in MariaDB 10.5 and removed in MariaDB 10.6.
innodb_undo_log_encrypt	-	OFF	MySQL option for encrypting undo logs residing in separate undo tablespaces.
innodb_undo_log_truncate	OFF	ON	MySQL 8 changes the default to ON, marking larger undo logs for truncation.
innodb_undo_tablespaces	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.
innodb_use_atomic_writes	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.
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.
keyring_operations	-	ON	Whether MySQL 8's keyring operations are enabled.
last_gtid	-	empty	MariaDB and MySQL have different GTID implementations.
local_infile	ON	OFF	MySQL no longer supports LOAD DATA LOCAL by default.
lock_wait_timeout	86400	31536000	MariaDB has reduced the timeout for acquiring metadata locks.
log_bin	OFF	ON	MySQL 8 enables the binary log by default.
log_bin_compress	OFF	-	MariaDB setting for whether or not the binary log can be compressed.
log_bin_compress_min_len	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_disabled_statements	sp	-	Disable logging of certain statements to the general log.
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.
log_slave_updates	OFF	ON	MySQL 8 has by default enabled binary logging of updates a slave receives from a master.
log_slow_admin_statements	ON	OFF	MariaDB logs slow admin statements to the slow query log by default.
log_slow_disabled_statements	admin,call,slave,sp	-	Disable logging of certain statements to the slow query log.
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.
log_warnings	2	-	MySQL 8 has replaced with `log_error_verbosity`.
mandatory_roles	-	(empty)	MySQL variable for assigning roles to all users.
master_info_repository	-	TABLE	Whether slave logs master status and connection info to a table or a file.
max_allowed_packet	16M	64M
max_error_count	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.
max_length_for_sort_data	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.
max_password_errors	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.
max_recursive_iterations	4294967295	-	Maximum number of iterations when executing recursive queries.
max_relay_log_size	1073741824	0	Can be set by session in MariaDB.
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_session_mem_used	9223372036854775807	-	Amount of memory a single user session is allowed to allocate.
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_tmp_tables	32	-	Unused variable removed in MySQL.
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.
mysql_native_password_proxy_users	-	OFF	Whether MySQL's authentication plugin supports proxy users. I
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.
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.
old_alter_table	DEFAULT	OFF	An alias for alter_algorithm.
old_mode	Empty string	-	Used for getting MariaDB to emulate behavior from an old version of MySQL or MariaDB. See OLD Mode.
old_passwords	OFF	-	MySQL 8 is no longer compatible with the old pre-MySQL 4.1 form of password hashing.
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_*	-	*	MySQL has more settings for optimizer tracing.
optimizer_use_condition_selectivity	4	-	Controls which statistics can be used by the optimizer when looking for the best query execution plan.
original_commit_timestamp	-	*	Used by MySQL 8 for delaying replication.
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.
performance_schema	OFF	ON	The Performance Schema is off by default in MariaDB.
performance_schema_*			Many performance schema variables are autoset in MySQL, and MySQL has a different version, with additional variables.
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.
proxy_protocol_networks	(empty)	-	Enable proxy protocol for these source networks.
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_*	*	-	MySQL has removed 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	-	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.
read_binlog_speed_limit	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.
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.
result_metadata	-	FULL	Determine whether the server returns result set metadata for connections where this is optional.
rowid_merge_buff_size	8388608	-	See Non-semi-join subquery optimizations.
rpl_read_size	-	8192	Minimum data in bytes read from the binary and relay log files.
rpl_semi_sync_*	-	-	MariaDB includes semisynchronous replication without the need to install a plugin.
rpl_stop_slave_timeout	-	31536000	Controls the time that STOP SLAVE waits before timing out.
s3_*	*	-	The S3 storage engine is only available in MariaDB.
schema_definition_cache	-	256	Limits the number of schema definition objects kept in the dictionary object cache.
secure_auth	ON	-	Removed in MySQL.
secure_timestamp	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.
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 reduced the timeout to 60s.
slave_parallel_max_queued	131072	-	For configuring parallel replication.
slave_parallel_mode	optimistic	-	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	-	INDEX_SCAN, HASH_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_if_exists	OFF	-	Adds an implicit IF EXISTS to ALTER, RENAME and DROP of TABLES, VIEWS, FUNCTIONS and PACKAGES
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_ENGINE_SUBSTITUTION	See SQL Mode.
ssl_fips_mode	-	OFF	Whether FIPS mode is enabled on the server side.
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).
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.
sync_binlog	0	1	MySQL synchronizes all actions to the binary log before they are committed.
sync_frm	1	-	.frm files have been removed in MySQL.
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.
tablespace_definition_cache	-	256	Limits the number of tablespace definition objects kept in the dictionary object cache.
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	-	Set the TCP_NODELAY option (disable Nagle's algorithm) on socket.
tcp_nodelay	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.
thread_cache_size	Autosized	-1 (autosized)	MariaDB uses an improved thread pool.
thread_pool_dedicated_listener	0	-	Better precision for the data in the Information Schema THREADPOOL_QUEUES Table.
thread_pool_exact_stats	0	-	Better precision for the data in the Information Schema THREADPOOL_QUEUES Table.
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	Varies	See Using the Thread Pool.
time_format	%H:%i:%s	-	Removed in MySQL.
tmp_disk_table_size	18446744073709551615	-	Max size for data for an internal temporary on-disk MyISAM or Aria table.
tmp_memory_table_size	16777216	-	Alias for tmp_table_size.
transaction_allow_batching	-	OFF	Variable for enabling batching of statements within the same transaction in MySQL Cluster.
transaction_isolation	-	REPEATABLE-READ	The MariaDB equivalent is tx_isolation.
transaction_read_only	-	OFF	The MariaDB equivalent is tx_read_only.
transaction_write_set_extraction	-	OFF	Unused MySQL-only variable.
tx_isolation	REPEATABLE-READ	-	The MySQL equivalent is transaction_isolation.
tx_read_only	OFF	-	The MySQL equivalent is transaction_read_only.
use_stat_tables	preferably_for_queries	-	Controls the use of engine-independent table statistics.
userstat	OFF	-	Whether to activate MariaDB's User Statistics implementation, not available in MySQL.
version_compile_zlib	-	*	Version of the zlib library compiled in.
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.
windowing_high_use_precision	-	*	MySQL option allowing safety to be sacrificed for speed in window function calculations.
wsrep_*	*	-	Galera cluster is only available in MariaDB.
Variable	MariaDB 10.6	MySQL 8.0	Notes

System Variable Differences Between MariaDB 10.6 and MySQL 8.0

Contents

Comparison Table

See Also

Products

Services

Pricing

Resources

About Us

Download MariaDB