MariaDB versus MySQL - Compatibility
Upgrading from MySQL to MariaDB? Let us help you!
Contact UsMariaDB Corporation has a MariaDB vs MySQL whitepaper available for download.
See also MariaDB vs MySQL - Features
Contents
- MariaDB is a binary drop in replacement for MySQL
- Incompatibilities between MariaDB 5.1 and MySQL 5.1
- Incompatibilities between MariaDB 5.2 and MySQL 5.1
- Incompatibilities between MariaDB 5.3 and MySQL 5.1 and MariaDB 5.2
- Incompatibilities between MariaDB 5.5 and MariaDB 5.3
- Incompatibilities between MariaDB 5.5 and MariaDB 5.3 and MySQL 5.5
- Incompatibilities between MariaDB 10.0 and MariaDB 5.5 / MySQL 5.5
- Incompatibilities between MariaDB 10.0 and MySQL 5.6
- Old, unsupported configuration options
- Replacing a MySQL RPM
- Incompatibilities between MariaDB and MySQL-Proxy
- Related Links
MariaDB is a binary drop in replacement for MySQL
For all practical purposes, MariaDB is a binary drop in replacement of the same MySQL version (for example MySQL 5.1 -> MariaDB 5.1, MariaDB 5.2 & MariaDB 5.3 are compatible. MySQL 5.5 is compatible with MariaDB 5.5 and also in practice with MariaDB 10.0). What this means is that:
- Data and table definition files (.frm) files are binary compatible.
- See note below for an incompatibility with views!
- All client APIs, protocols and structs are identical.
- All filenames, binaries, paths, ports, sockets, and etc... should be the same.
- All MySQL connectors (PHP, Perl, Python, Java, .NET, MyODBC, Ruby, MySQL C
connector etc) work unchanged with MariaDB.
- There are some installation issues with PHP5 that you should be aware of (a bug in how the old PHP5 client checks library compatibility).
- The
mysql-client
package also works with MariaDB server. - The shared client library is binary compatible with MySQL's client library.
This means that for most cases, you can just uninstall MySQL and
install MariaDB and you are good
to go. (No need to convert any datafiles if you use same main version, like
5.1). You must however still run mysql_upgrade
to finish the upgrade. This is needed to ensure that your mysql privilege and event tables are updated with the new fields MariaDB uses.
We do monthly merges with the MySQL code base to ensure we keep up our compatibility and get any and all features and bug fixes Oracle adds.
We have also done a lot of work on the upgrade scripts to the point where it is now easier to upgrade from MySQL 5.0 to MariaDB 5.1 than from MySQL 5.0 to MySQL 5.1.
That said, MariaDB has a lot of new options, extension, storage engines and bug fixes that are not in MySQL. You can find the feature set for the different MariaDB versions on the What is in the different MariaDB Releases page.
Incompatibilities between MariaDB 5.1 and MySQL 5.1
In some few cases MariaDB has to be incompatible to allow MariaDB to provide more and better information than MySQL.
Here is the list of all known user level incompatibilities you may see when using MariaDB 5.1 instead of MySQL 5.1.
- The installation package names start with MariaDB instead of MySQL.
- Timings may be different as MariaDB is in many cases faster than MySQL.
- mysqld in MariaDB also reads the
[mariadb]
sections of your my.cnf files. - You can't use a binary only storage engine library with MariaDB if it's not compiled for exactly the same MariaDB version. (This is because the server internal structure THD is different between MySQL and MariaDB. This is common also between different MySQL versions). This should not be a problem as most people don't load new storage engines and MariaDB comes with more storage engines than MySQL.
CHECKSUM TABLE
may give different result as MariaDB doesn't ignore NULL's in the columns as MySQL 5.1 does (Future MySQL versions should calculate checksums the same way as MariaDB). You can get the 'old style' checksum in MariaDB by starting mysqld with the--old
option. Note however that that the MyISAM and Aria storage engines in MariaDB are using the new checksum internally, so if you are using--old
, theCHECKSUM
command will be slower as it needs to calculate the checksum row by row.- The slow query log has more information about the query, which may be a problem if you have a script which parses the slow query log.
- MariaDB by default takes a bit more memory than MySQL because we have by
default enabled the Aria storage engine for
handling internal temporary tables. If you need MariaDB to take very little
memory (at the expense of performance), you can set the value
of
aria_pagecache_buffer_size
to1M
(the default is128M
). - If you are using new command options, new features of MariaDB or new storage engines, you can't move easily back and forth between MySQL and MariaDB anymore.
Incompatibilities between MariaDB 5.2 and MySQL 5.1
The list is the same as between MariaDB 5.1 and MySQL 5.1, with one addition:
- A new SQL_MODE value was added:
IGNORE_BAD_TABLE_OPTIONS
. If it is not set, using a table, field, or index attribute (option) that is not supported by the chosen storage engine will cause an error. This change might cause warnings in the error log about incorrectly defined tables from themysql
database, fix that with mysql_upgrade.
For all practical purposes, MariaDB 5.2 is a drop in replacement for MariaDB 5.1 and MySQL 5.1.
Incompatibilities between MariaDB 5.3 and MySQL 5.1 and MariaDB 5.2
- Views with definition ALGORITHM=MERGE or ALGORITHM=TEMPTABLE got accidentally swapped between MariaDB 5.2 and MariaDB 5.3! You have to re-create views created with either of these definitions!
- A few error messages related to wrong conversions are different as MariaDB provides more information in the message about what went wrong.
- Error numbers for MariaDB-specific errors have been moved to start from 1900 so as not to conflict with MySQL errors.
- Microseconds now work in all contexts; MySQL, in some contexts, lost the microsecond part from datetime and time.
- UNIX_TIMESTAMP(constant-date-string) returns a timestamp with 6 decimals in MariaDB while MySQL returns it without a decimal. This can cause a problem if you are using UNIX_TIMESTAMP() as a partitioning function. You can fix this by using FLOOR(UNIX_TIMESTAMP(..)) or changing the date string to a date number, like 20080101000000.
- MariaDB performs stricter checking of date, datetime and timestamp values. For example UNIX_TIMESTAMP('x') now returns NULL instead of 0.
- The old
--maria-
startup options are removed. You should use the--aria-
prefix instead. (MariaDB 5.2 supports both--maria-
and--aria-
) SHOW PROCESSLIST
has an extraProgress
column which shows progress for some commands. You can disable it by startingmysqld
with either--old-mode=NO_PROGRESS_INFO
or with the--old
flag (see OLD_MODE).INFORMATION_SCHEMA.PROCESSLIST
has three new columns for progress reporting:STAGE
,MAX_STAGE
, andPROGRESS
.- Long comments which start with
/*M!
or/*M!#####
are executed. - If you use max_user_connections=0 (which means any number of connections) when starting mysqld, you can't change the global variable
anymore while mysqld remains running. This is because when mysqld is started
with
max_user_connections=0
it does not allocate counting structures (which also involve a mutex for each connection). This would lead to wrong counters if you later changed the variable. If you want to be able to change this variable at runtime, set it to a high value at startup. - You can set
max_user_connections
(both the global variable and theGRANT
option) to-1
to stop users from connecting to the server. The globalmax_user_connections
variable does not affect users with theSUPER
privilege. - The IGNORE directive does not ignore all errors (like fatal errors), only things that are safe to ignore.
Incompatibilities between MariaDB 5.5 and MariaDB 5.3
XtraDB
Percona, the provider of XtraDB, does not provide all earlier XtraDB features in the 5.5 code base. Because of that, MariaDB 5.5 can't provide them either.
XtraDB options missing in 5.5
The following options are not supported by XtraDB 5.5. If you are using them in any of your my.cnf files, you should remove them before upgrading to 5.5.
- innodb_adaptive_checkpoint; Use innodb_adaptive_flushing_method instead.
- innodb_auto_lru_dump; Use innodb_buffer_pool_restore_at_startup instead (and innodb_buffer_pool_load_at_startup in MariaDB 10.0).
- innodb_blocking_lru_restore; Use innodb_blocking_buffer_pool_restore instead.
- innodb_enable_unsafe_group_commit
- innodb_expand_import; Use innodb_import_table_from_xtrabackup instead.
- innodb_extra_rsegments; Use innodb_rollback_segments instead.
- innodb_extra_undoslots
- innodb_fast_recovery
- innodb_flush_log_at_trx_commit_session
- innodb_overwrite_relay_log_info
- innodb_pass_corrupt_table; Use innodb_corrupt_table_action instead.
- innodb_use_purge_thread
- xtradb_enhancements
XtraDB options that have changed default value
Option | Old value | New value |
---|---|---|
innodb_change_buffering | inserts | all |
innodb_flush_neighbor_pages | 1 | area |
New options in XtraDB 5.5
The following new options have been added to XtraDB / InnoDB in 5.5. (Listed here just to have all XtraDB information in the same place)
- innodb_adaptive_flushing_method
- innodb_adaptive_hash_index_partitions
- innodb_blocking_buffer_pool_restore
- innodb_buffer_pool_instances
- nnodb_buffer_pool_restore_at_startup
- innodb_change_buffering_debug
- innodb_corrupt_table_action
- innodb_flush_checkpoint_debug
- innodb_force_load_corrupted
- innodb_import_table_from_xtrabackup
- innodb_large_prefix
- innodb_purge_batch_size
- innodb_purge_threads
- innodb_recovery_update_relay_log
- innodb_rollback_segments
- innodb_sys_columns
- innodb_sys_fields
- innodb_sys_foreign
- innodb_sys_foreign_cols
- innodb_sys_tablestats
- innodb_use_global_flush_log_at_trx_commit
- innodb_use_native_aio
See also Perconas guide of how to upgrade to 5.5
Incompatibilities between MariaDB 5.5 and MariaDB 5.3 and MySQL 5.5
- Views with definition ALGORITHM=MERGE or ALGORITHM=TEMPTABLE got accidentally swapped between MariaDB and MySQL! You have to re-create views created with either of these definitions!
- INSERT IGNORE also gives warnings for duplicate key errors. You can turn this off by setting
OLD_MODE=NO_DUP_KEY_WARNINGS_WITH_IGNORE
(see OLD_MODE). - Before MariaDB 5.5.31,
X'HHHH'
, the standard SQL syntax for binary string literals, erroneously worked in the same way as0xHHHH
, which could work as a number or string depending on the context. In 5.5.31 this was fixed to behave as a string in all contexts (and never as a number), introducing an incompatibility with previous versions of MariaDB, and all versions of MySQL. See CAST for more details and an example. - See also a detailed breakdown of System variable differences between MariaDB 5.5 and MySQL 5.5.
Incompatibilities between MariaDB 10.0 and MariaDB 5.5 / MySQL 5.5
SET OPTION
syntax is deprecated in MariaDB 10.0 and MySQL 5.6. Use justSET
.
Incompatibilities between MariaDB 10.0 and MySQL 5.6
- All MySQL binaries (
mysqld
, myisamchk etc.) give a warning if one uses a prefix of an option (such as--big-table
instead of--big-tables
). MariaDB binaries work in the same way as most other Unix commands and don't give warnings when using unique prefixes. - MariaDB GTID is not compatible with MySQL 5.6. This means that one can't have MySQL 5.6 as a slave for MariaDB 10.0. However MariaDB 10.0 can be a slave of MySQL 5.6 or any earlier MySQL/MariaDB version.
- To make CREATE TABLE ... SELECT work the same way in statement based and row based replication it's by default executed as CREATE OR REPLACE TABLE on the slave. One benefit of this is that if the slave dies in the middle of CREATE ... SELECT it will be able to continue.
- One can use the slave-ddl-exec-mode variable to specify how
CREATE TABLE
andDROP TABLE
is replicated.
- One can use the slave-ddl-exec-mode variable to specify how
- See also a detailed breakdown of System variable differences between MariaDB 10.0 and MySQL 5.6.
- MySQL 5.6 has performance schema enabled by default. For performance reasons MariaDB 10.0 has it disabled by default. You can enable it by starting
mysqld
with the option--performance-schema
.
Old, unsupported configuration options
If you are using any of the following options in your /etc/my.cnf
or other
my.cnf
file you should remove them. This is also true for MySQL 5.1 or
newer:
skip-bdb
Replacing a MySQL RPM
If you uninstalled a MySQL RPM to install MariaDB, note that the MySQL RPM on
uninstall renames /etc/my.cnf
to /etc/my.cnf.rpmsave
.
After installing MariaDB you should do the following to restore your old configuration options:
mv -vi /etc/my.cnf.rpmsave /etc/my.cnf
Incompatibilities between MariaDB and MySQL-Proxy
A MySQL client API is able to connect to MariaDB using MySQL-Proxy but a MariaDB client API will receive progress reporting informations that MySQL-Proxy does not implement, to get full compatibility in all case just disable progress reporting on the client or server side.