Following compatibility report was done on 10.2.4 and may get some fixing in next minor releases

• MySQL unix socket plugin can be different. MariaDB can get similar usage via INSTALL PLUGIN unix_socket SONAME 'auth_socket.so'; you may have to enable this plugin in config files via load plugin.

• When using data type JSON , one should convert type to TEXT, virtual generated column works the same after.

• When using InnoDB FULLTEXT index one should not use innodb_defragment

• MySQL re-implemented partitioning in 5.7, thus you cannot perform in-place upgrades for partitioned tables. They will require mysqldump/import to work correctly in MariaDB.

_{This page is licensed: CC BY-SA / Gnu FDL}

There is a screencast for upgrading from MySQL 5.1.55 to MariaDB. Watch this example to see how easy this process is. It really is just a "drop in replacement" to MySQL.

_{This page is licensed: CC BY-SA / Gnu FDL}

This page lists general differences between MariaDB and other DBMSs (database management systems). For specific differences (for instance, between MariaDB and PostgreSQL), refer to the respective pages in this section.

Database vs. Schema

In MariaDB, the terms "schema" and "database" are synonymous and used interchangeably. The CREATE SCHEMA statement is a direct synonym for CREATE DATABASE, meaning they create the same object—a container for database objects like tables and views.

If you upgrade to from MySQL 5.1 you don't have to do anything with your data or MySQL clients. Things should "just work".

When upgrading between different major versions of MariaDB or MySQL you need to run the mysql_upgrade program to convert data that are incompatible between versions. This will also update your privilege tables in the mysql database to the latest format.

In almost all cases mysql_upgrade should be able to convert your tables, without you having to dump and restore your data.

After installing MariaDB, just do:

If you want to run with a specific TCP/IP port do:

If you want to connect with a socket do:

To see other options, use --help.

"mysql_upgrade" reads the my.cnf sections [mysql_upgrade] and [client] for default values.

There are a variety of reasons tables need to be converted; they could be any of the following:

• The collation (sorting order) for an index column has changed

• A field type has changed storage format

  • and changed format between MySQL 4.1 and MySQL 5.0

• An engine has a new storage format

in SHOW TABLES until you convert them.

If you don't convert the tables, one of the following things may happen:

• You will get warnings in the error log every time you access a table with an invalid (old) file name.

• When searching on key values you may not find all rows

• You will get an error "ERROR 1459 (HY000): Table upgrade required" when accessing the table.

• You may get crashes

"mysql_upgrade" works by calling mysqlcheck with different options and running the "mysql_fix_privileges" script. If you have trouble with "mysql_upgrade", you can run these commands separately to get more information of what is going on.

Most of the things in the section also applies to MariaDB.

The following differences exists between "mysql_upgrade" in MariaDB and MySQL (as of ):

• MariaDB will convert long table names properly.

• MariaDB will convert tables (no need to do a dump/restore or ).

• MariaDB will convert old archive tables to the new 5.1 format (note: new feature in testing).

• "mysql_upgrade --verbose" will run "mysqlcheck --verbose" so that you get more information of what is happening.

_{This page is licensed: CC BY-SA / Gnu FDL}

This page contains links and hints to setup MariaDB for testing. The page is designed for SQL Server users, assuming that they are mostly familiar with Windows and they are not familiar with MariaDB.

Choosing a MariaDB Version

As a general rule, for new installations it's better to choose the .

If you need a feature that is only present in a version that is not yet production-ready, and the project will surely not go to production before that version is GA, it could make sense to use a non-GA version. In this case however, keep in mind that you are using a version that is only suitable for testing.

If you need to work with an existing production instance, you should of course use the same version in testing. However, deprecated versions should not be used in production, because they could be exposed to vulnerabilities that will never be fixed. See if you are not sure about the version you are using.

Setting up MariaDB on Windows

There are two different ways to use MariaDB on Windows natively: using Zip packages or MSI packages.

In both cases, 32-bit platforms are still supported.

Check the page to verify if current versions of MariaDB have troubles on Windows. More generally, it is a good idea to check the category.

ZIP Packages

Windows users don't necessarily need to install MariaDB to use it. They can download ready-to-use ZIP packages to avoid any change in the system (except for downloading MariaDB and writing databases on the disk). This is very useful for testing without risking some undesired side effect on the machine in use. And it avoids the hassle of installing Docker or virtual machines.

It is necessary to run to install the data directory.

The drawback is that MariaDB will need to be started and stopped from the command line.

See .

MSI Packages

MSI packages provide a friendly graphical interface to install MariaDB. The installation process is easy but flexible. For example, the user can decide which components to install, whether to install it as a service or not, and if networking should be enabled. An interface to uninstall MariaDB is also provided.

See .

Installing MariaDB on Docker

Docker is a container platform that runs natively on Linux. A Docker image is a representation of a basic Linux system, which usually runs a single process - in our case, that process is MariaDB. A container is an instance of an image, which can be created or destroyed instantaneously. Once a container is started, it can be used just like a normal system.

Docker runs on all major operating systems. On Windows and MacOS it runs on a Linux virtual machine, but this additional complexity is transparent for the end user.

Docker's characteristics makes it optimal to test MariaDB functionalities without wasting time on installation and without making changes to the host system. However, it is not ideal to test MariaDB performance.

See .

Reinitializing MariaDB Data Directory

While experimenting with MariaDB, you could end up with an unusable installation. This occurs for example if you deliberately delete files that you shouldn't delete. If it happens, there is no need to uninstall and reinstall MariaDB. Instead, you can simply delete the contents of the data directory and run . The program will recreate your system tables and the essential files.

To know where your data directory is, check the system variable.

_{This page is licensed: CC BY-SA / Gnu FDL}

This guide details migrating a live database from a MySQL Galera Cluster to a . The migration strategy requires setting up a new MariaDB Cluster and using to sync it with the existing MySQL Cluster. The method includes a reliable failback route.

Prerequisites

• MySQL Server: A running MySQL Galera Cluster (version 8.0 or 8.4) to migrate.

• MariaDB Server: The target . This guide assumes the latest stable version.

• mysqldump: A command-line utility by MySQL to create initial database backup.

• Root or Sudo Access: Administrative are required on all servers involved in the migration.

Migration Steps

When planning a migration between different DBMSs, one of the most important aspects to consider is that the new database system will probably miss some features supported by the old one. This is not relevant for all users. The most widely used features are supported by most DBMSs. However, it is important to make a list of unsupported features and check which of them are currently used by applications. In most cases it is possible to implement such features on the application side, or simply stop using them.

This page has a list of SQL Server features that are not supported in MariaDB. The list is not exhaustive.

Introduced in SQL Server versions older than 2016

Modern DBMSs implement several advanced features. While an SQL standard exists, the complete feature list is different for every database system. Sometimes different features allow achieving the same purpose, but with a different logic and different limitations. This is something to take into account when planning a migration.

Some features are implemented by different DBMSs, with a similar logic and similar syntax. But there could be important differences that users should be aware of.

This page has a list of SQL Server features that MariaDB implements in a different way, and SQL Server features for which MariaDB has an alternative feature. Minor differences are not taken into account here. The list is not exhaustive.

SQL

• The list of supported is different.

• There are relevant .

• SNAPSHOT isolation level is not supported. Instead, you can use START TRANSACTION WITH CONSISTENT SNAPSHOT to acquire a snapshot at the beginning of the transaction. This is compatible with all isolation levels. See .

• JSON support is .

Indexes and Performance

• Clustered indexes. In MariaDB, the physical order of rows is delegated to the storage engine. InnoDB uses the primary key as a clustered index.

• Hash indexes. Only some storage engines support HASH indexes.

  • The storage engine has a feature called adaptive hash index, enabled by default. It means that in InnoDB all indexes are created as BTREE, and depending on how they are used, InnoDB could convert them from BTree to hash indexes, or the other way around. This happens in the background.

Tables

• Computed columns are called in MariaDB and are created with a different syntax. See also .

• use a different (more standard) syntax on MariaDB. In MariaDB, the history is stored in the same table as current data (but optionally in different partitions). MariaDB supports both and .

• Hidden columns are in MariaDB.

• are implemented and used differently.

High Availability

• NOT FOR REPLICATION

  • MariaDB supports to exclude some tables or databases from replication

  • It is possible to keep a table empty in a slave (or in the master) by using the .

  • The master can have columns that are not present in a slave (the other way around is also supported). Before using this feature, carefully read the

Security

• The list of is different.

• Security policies. MariaDB allows one to achieve the same results by assigning permissions on views and stored procedures. However, this is not a common practice and it's more complicated than defining security policies. See .

• MariaDB does not support an OUTPUT clause. Instead, we can use and, since , and .

Other Features

• Linked servers. MariaDB supports storage engines to read from, and write to, remote tables. When using the engine, those tables could be in different DBMSs, including SQL Server.

• Job scheduler: MariaDB uses an to schedule events instead.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB has the following types of backups:

• Logical backups (dumps).

• Hot backups with mariadb-backup.

• Snapshots.

There are many different ways to migrate from to MariaDB. This article will discuss some of those options.

MariaDB's CONNECT Storage Engine

MariaDB's storage engine can be used to migrate from PostgreSQL to MariaDB. There are two primary ways that this can be done.

See for information on how to install the CONNECT storage engine.

Some MariaDB features are not available in SQL Server.

At first glance, it is not important to know about those features to migrate from SQL Server to MariaDB. However, this is not the case. Using MariaDB features that are not in SQL Server allows one to obtain more advantages from the migration, getting the most from MariaDB.

This page has a list of MariaDB features that are not supported in SQL Server. The list is not exhaustive.

Plugin Architecture

• .

• .

• .

• is a columnar storage engine designed to scale horizontally. It runs on a specific edition of MariaDB, so currently it cannot be used in combination with other engines.

SQL

• The variable determines in which cases an SQL statement should fail with an error, and in which cases it should succeed with a warning even if it is not entirely correct. For example, when a statement tries to insert a string in a column which is not big enough to contain it, it could fail, or it could insert a truncated string and emit a warning. It is a tradeoff between reliability and flexibility.

  • allows one to use a small subset of SQL Server proprietary syntax.

• The options are supported for most .

See also .

Types

• don't depend on column type. They can be set globally, or at database, table or column level.

• Columns may use non-constant expressions as the value. columns may have a DEFAULT value.

• numeric types.

• (note that JSON is usually preferred to this feature).

See also .

JSON

For compatibility with some other database systems, MariaDB supports the pseudo-type. However, it is just an alias for:

LONGTEXT CHECK (JSON_VALID(column_name))

is the MariaDB equivalent of SQL Server's ISJSON().

Features

• functionality allows one to "undo" the changes that happened after a certain point in time.

• support the following features:

  • Tables can be partitioned based on .

  • Several are available.

_{This page is licensed: CC BY-SA / Gnu FDL}

There are several ways to move data between SQL Server and MariaDB. Here we will discuss them and we will highlight some caveats.

Moving Data Definition from SQL Server to MariaDB

To copy SQL Server data structures to MariaDB, one has to:

1. Generate a CSV file from SQL Server data.

2. Modify the syntax so that it works in MariaDB.

3. Run the file in MariaDB.

Variables That Affect DDL Statements

DDL statements are affected by some server system variables.

determines the behavior of some SQL statements and expressions, including how strict error checking is, and some details regarding the syntax. Objects like , and , are always executed with the sql_mode that was in effect during their creation. can be used to have MariaDB behaving as close to SQL Server as possible.

enables the so-called InnoDB strict mode. Normally some errors in the options are ignored. When InnoDB strict mode is enabled, the creation of InnoDB tables will fail with an error when certain mistakes are made.

determines whether view updates can be made with an or statement with a LIMIT clause if the view does not contain all primary or not null unique key columns from the underlying table.

Dumps and sys.sql_modules

SQL Server Management Studio allows one to create a working SQL script to recreate a database - something that MariaDB users refer to as a dump. Several options allow fine-tuning the generated syntax. It could be necessary to adjust some of these options to make the output compatible with MariaDB. It is possible to export schemas, data or both. One can create a single global file, or one file for each exported object. Normally, producing a single file is more practical.

Alternatively, the procedure returns information about how to recreate a certain object. Similar information is also present in the table (definition column), in the sys schema. Such information, however, is not a ready-to-use set of SQL statements.

Remember however that . An SQL Server schema is approximately a MariaDB database.

To execute a dump, we can pass the file to , the MariaDB command-line client.

Provided that a dump file contains syntax that is valid in MariaDB, it can be executed in this way:

--show-warnings tells MariaDB to output any warnings produced by the statements contained in the dump. Without this option, warnings will not appear on screen. Warnings don't stop the dump execution.

Errors will appear on screen. Errors will stop the dump execution, unless the --force option (or just -f) is specified.

For other mariadb options, see .

Another way to achieve the same purpose is to start the mariadb client in interactive mode first, and then run the source command. For example:

In this case, to show warnings we used the \W command, where "w" is uppercase. To hide warnings (which is the default), we can use \w (lowercase).

For other mariadb commands, see .

CSV Data

If the table structures are already in MariaDB, we need only to import table data. While this can still be done as explained above, it may be more practical to export CSV files from SQL Server and import them into MariaDB.

SQL Server Management Studio and several other Microsoft tools allow one to export CSV files.

MariaDB allows importing CSV files with the statement, which is essentially the MariaDB equivalent of BULK INSERT.

It can happen that we don't want to import the whole data, but some filtered or transformed version of it. In that case, we may prefer to use the storage engine to access CSV files and query them. The results of a query can be inserted into a table using .

Moving Data from MariaDB to SQL Server

There are several ways to move data from MariaDB to SQL Server:

• If the tables don't exist at all in SQL Server, we need to generate a dump first. The dump can include data or not.

• If the tables are already in SQL Server, we can use CSV files instead of dumps to move the rows. CSV files are the most concise format to move data between different technologies.

• With the tables already in SQL Server, another way to move data is to insert the rows into tables that "point" to remote SQL Server tables.

Using a Dump (Structure)

can be used to generate dumps of all databases, a specified database, or a set of tables. It is even possible to only dump a set of rows by specifying the WHERE clause.

By specifying the --no-data option we can dump the table structures without data.

--compatible=mssql will produce an output that should be usable in SQL Server.

Using a Dump (Data)

mariadb-dump by default produces an output with both data and structure.

--no-create-info can be used to skip the statements.

--compatible=mssql will produce an output that should be usable in SQL Server.

--single-transaction should be specified to select the source data in a single transaction, so that a consistent dump is produced.

--quick speeds up the dump process when dumping big tables.

Using a CSV File

CSV files can also be used to export data to SQL Server. There are several ways to produce CSV files from MariaDB:

• The statement.

• The storage engine, with the .

• The storage engine (note that it doesn't support NULL and indexes).

Using CONNECT Tables

The storage engine allows one to access external data, in many forms:

• (, , , HTML and more).

• Remote databases, using the or standards, or .

• Some .

CONNECT was mentioned previously because it could allow one to read a CSV file and query it in SQL, filtering and transforming the data that we want to move into regular MariaDB tables.

However, CONNECT can also access remote SQL Server tables. We can read data from it, or even write data.

To enable CONNECT to work with SQL Server, we need to fulfill these requirements:

• Install the ODBC driver, downloadable form website. The driver is also available for Linux and MacOS.

• Install .

• (unless it is already installed).

Here is an example of a CONNECT table that points to a SQL Server table:

The key points here are:

• ENGINE=CONNECT tells MariaDB that we want to create a CONNECT table.

• TABLE_TYPE must be 'ODBC', so CONNECT knows what type of data source it has to use.

• CONNECTION is the connection string to use, including server address, username and password.

CONNECT is able to query SQL Server to find out the remote table structure. We can use this feature to avoid specifying the column names and types:

However, we may prefer to manually specify the MariaDB types, sizes and character sets to use.

Linked Server

Instead of using MariaDB CONNECT, it is possible to use SQL Server Linked Server functionality. This will allow one to read data from a remote MariaDB database and copy it into local SQL Server tables. However, note that CONNECT allows more control on mapping.

Refer to section in Microsoft documentation.

_{This page is licensed: CC BY-SA / Gnu FDL}

Part 1: Installing Oracle Database Express Edition (XE)

This section covers the installation of Java and the Oracle XE database on a Debian-based system like Ubuntu.

Step 1: Download Oracle Database XE

1. Sign up for an Oracle account (if you don't have one) and log in.

2. Navigate to the download page for legacy versions of the database. A good starting point is the .

3. Accept the license agreement.

4. Download Oracle Database Express Edition 11g Release 2 for Linux x64. The file will be named similarly to oracle-xe-11.2.0-1.0.x86_64.rpm.zip.

Step 2: Install and Configure Java 8

Oracle XE 11gR2 requires a Java environment.

A) Add the Java PPA and Install

B) Verify Java Installation

After the installation completes, verify the version.

The output should be similar to:

C) Set JAVA_HOME Environment Variable

Edit the system-wide bash configuration file.

Scroll to the bottom of the file and add the following lines:

Save the file and exit. Then, source the file to apply the changes and verify the variable is set.

The output should be:

Step 3: Install Prerequisite Packages

Additional packages are required to convert and install the Oracle RPM.

Step 4: Convert the Oracle RPM to a DEB Package

The downloaded file is an RPM, which must be converted to a DEB package for Debian-based systems.

Step 5: Create chkconfig and Kernel Configuration

A) Create a chkconfig Compatibility Script

The Oracle installer expects the chkconfig utility, which doesn't exist on Debian-based systems. Create a compatibility script.

Add the following content to the new file:

Save the file, then make it executable:

B) Configure Kernel Parameters

Create a configuration file for Oracle-specific kernel parameters.

Copy and paste the following into the file. The value for kernel.shmmax should be approximately half of your system's physical RAM, specified in bytes (e.g., 512MB = 536870912 bytes).

Apply the new kernel parameters and verify the fs.file-max setting.

The expected output is 6815744.

Step 6: Final System Adjustments

A) Create Compatibility Links and Directories

B) Configure Shared Memory (/dev/shm)

To avoid a potential ORA-00845: MEMORY_TARGET error, re-mount the /dev/shm directory with a sufficient size. Replace 4096m with the amount of RAM on your machine.

To make this change persistent across reboots, create a startup script.

Add the following content, again replacing 4096m with your machine's RAM size.

Save the file and make it executable:

Step 7: Install and Configure Oracle XE

A) Install the Converted Package

By now, the .deb package should have been created. Install it using \.

B) Run the Oracle Configuration Script

Configure the database by running the script and answering the prompts. The default answers are usually acceptable.

You will be prompted for:

• HTTP port for Oracle Application Express (default: 8080)

• Database listener port (default: 1521)

• A password for the SYS and SYSTEM accounts.

C) Set Oracle Environment Variables

Edit /etc/bash.bashrc again to add the Oracle-specific variables.

Add the following to the end of the file:

Apply and verify the changes:

D) Start Oracle

Part 2: Installing SQL Developer

Step 1: Download and Install SQL Developer

1. Go to the Oracle site and download the Linux RPM package for Oracle SQL Developer.

2. Use alien to convert the package and dpkg to install it.

1. Create a configuration directory.

Step 2: Run and Connect to Oracle XE

1. Launch SQL Developer.

   Bash

2. If it asks for the Java path, provide it: /usr/lib/jvm/java-8-oracle.

3. Inside SQL Developer, create a new connection with the following details:

Part 3: Installing MariaDB and Connecting to Oracle

Step 1: Install MariaDB

Follow the official instructions on the MariaDB website for your specific OS version.

Step 2: Install MariaDB ODBC Connector

1. Download the "MariaDB Connector/ODBC" for Linux from the MariaDB website.

2. Extract the archive and copy the library file.

Step 3: Install UnixODBC and CONNECT Engine

Step 4: Configure ODBC Drivers

A) Configure the Oracle Driver

Edit /etc/odbcinst.ini and add the following section:

B) Configure the Oracle DSN

Edit /etc/odbc.ini and add a Data Source Name (DSN) for your Oracle XE database.

Step 5: Test the Oracle ODBC Connection

Use isql to test the connection and create a sample table in Oracle.

You should see the rows 1, 3, 5, 8 returned.

Step 6: Configure and Test the MariaDB CONNECT Engine

A) Set Environment Variables for MariaDB

For the CONNECT engine to find the Oracle libraries, you must add the Oracle environment variables to MariaDB's startup script. Edit /etc/init.d/mysql.

Immediately after ### END INIT INFO, add the following block:

B) Restart MariaDB and Test the Connection

Once logged into the MariaDB client, run the following SQL commands:

You should see the values 1, 3, 5, 8 retrieved from the Oracle table.

Part 4: Connecting to MariaDB via JDBC

Step 1: Install the MariaDB JDBC Driver

1. Download the "MySQL Connector/J" from the MySQL or MariaDB website.

2. Extract the archive and copy the .jar file to a known location.

Step 2: Configure SQL Developer

1. Launch SQL Developer: sudo /opt/sqldeveloper/sqldeveloper.sh

2. Go to Tools -> Preferences.

3. Expand Database and click on Third Party JDBC Drivers.

4. Click Add Entry... and navigate to the .jar file you copied: /usr/lib/jvm/java-8-oracle/lib/mariadb/mysql-connector-java-5.0.8-bin.jar

Step 3: Connect to MariaDB

1. In SQL Developer, create a new connection.

2. Switch to the MySQL tab.

3. Enter the connection details:

   • Connection Name: MariaDB via MySQL Conn

This page is licensed: CC BY-SA / Gnu FDL

This page explains how transactions work in MariaDB, and highlights the main differences between MariaDB and SQL Server transactions.

Note that XA transactions are handled in a completely different way and are not covered in this page. See XA Transactions.

Missing Features

These SQL Server features are not available in MariaDB:

• Autonomous transactions;

• Distributed transactions.

Transactions, Storage Engines and the Binary Log

In MariaDB, transactions are optionally implemented by . The default storage engine, , fully supports transactions. Other transactional storage engines include and . Most storage engines are not transactional, therefore they should not considered general purpose engines.

Most of the information in this page refers to generic MariaDB server behaviors or InnoDB. For and please check the proper KnowledgeBase sections.

Writing into a non-transactional table in a transaction can still be useful. The reason is that a is acquired on the table for the duration of the transaction, so that are queued.

It is possible to write into transactional and non-transactional tables within a single transaction. It is important to remember that non-transactional engines will have the following limitations:

• In case of rollback, changes to non-transactional engines won't be undone. We will receive a warning 1196 which reminds us of this.

• Data in transactional tables cannot be changed by other connections in the middle of a transaction, but data in non-transactional tables can.

• In case of a crash, committed data written into a transactional table can always be recovered, but this is not necessarily true for non-transactional tables.

If the is enabled, writing into different transactional storage engines in a single transaction, or writing into transactional and non-transactional engines inside the same transaction, implies some extra work for MariaDB. It needs to perform a two-phase commit to be sure that changes to different tables are logged in the correct order. This affects the performance.

Transaction Syntax

The first read or write to an InnoDB table starts a transaction. No data access is possible outside a transaction.

By default is on, which means that the transaction is committed automatically after each SQL statement. We can disable it, and manually commit transactions:

Whether autocommit is enabled or not, we can start transactions explicitly, and they will not be automatically committed:

BEGIN can also be used to start a transaction, but does not work in stored procedures.

Read-only transactions are also available using START TRANSACTION READ ONLY. This is a small performance optimization. MariaDB will issue an error when trying to write data in the middle of a read-only transaction.

Only DML statements are transactional and can be rolled back. This may change in a future version, see - Atomic DDL and - transactional DDL.

Changing autocommit and explicitly starting a transaction will implicitly commit the active transaction, if any. DDL statements, and several other statements, implicitly commit the active transaction. See for the complete list of these statements.

A rollback can also be triggered implicitly, when certain errors occur.

You can experiment with transactions to check in which cases they implicitly commit or rollback. The system variable can help: it is set to 1 when a transaction is in progress, or 0 when no transaction is in progress.

This section only covers the basic syntax for transactions. Much more options are available. For more information, see .

Constraint Checking

MariaDB supports the following :

In some databases, constraints can temporarily be violated during a transaction, and their enforcement can be deferred to the commit time. SQL Server does not support this, and always validates data against constraints at the end of each statement.

MariaDB does something different: it always checks constraints after each row change. There are cases this policy makes some statements fail with an error, even if those statements would work on SQL Server.

For example, suppose you have an id column that is the primary key, and you need to increase its value for some reason:

The reason why this happens is that, as the first thing, MariaDB tries to change 1 to 2, but a value of 2 is already present in the primary key.

A solution is to use this non-standard syntax:

Changing the ids in reversed order won't duplicate any value.

Similar problems can happen with CHECK constraints and foreign keys. To solve them, we can use a different approach:

The last solutions temporarily disable CHECK constraints and foreign keys. Note that, while this may solve practical problems, it is dangerous because:

• This doesn't disable a single CHECK or foreign key, but also others, that you don't expect to violate.

• This doesn't defer the constraint checks, but it simply disables them for a while. This means that, if you insert some invalid values, they will not be detected.

See and system variables.

Isolation Levels and Locks

For more information about MariaDB isolation levels see .

Locking Reads

In MariaDB, the locks acquired by a read do not depend on the isolation level (with one exception noted below).

As a general rule:

• Plain are not locking, they acquire snapshots instead.

• To force a read to acquire a shared lock, use .

• To force a read to acquire an exclusive lock, use .

Changing the Isolation Level

The default, the isolation level in MariaDB is REPEATABLE READ. This can be changed with the system variable.

Applications developed for SQL Server and later ported to MariaDB may run with READ COMMITTED without problems. Using a stricter level would reduce scalability. To use READ COMMITTED by default, add the following line to the MariaDB configuration file:

It is also possible to change the default isolation level for the current session:

Or just for one transaction, by issuing the following statement before starting a transaction:

How Isolation Levels are Implemented in MariaDB

MariaDB supports the following isolation levels:

• READ UNCOMMITTED

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE

MariaDB isolation levels differ from SQL Server in the following ways:

• REPEATABLE READ does not acquire share locks on all read rows, nor a range lock on the missing values that match a WHERE clause.

• It is not possible to change the isolation level in the middle of a transaction.

• SNAPSHOT isolation level is not supported. Instead, you can use START TRANSACTION WITH CONSISTENT SNAPSHOT to acquire a snapshot at the beginning of the transaction. This is compatible with all isolation levels.

Here is an example of WITH CONSISTENT SNAPSHOT usage:

As you can see, session 1 uses WITH CONSISTENT SNAPSHOT, thus it sees all tables as they were when the transaction begun.

Avoiding Lock Waits

When we try to read or modify a row that is exclusive-locked by another transaction, our transaction is queued until that lock is released. There could be more queued transactions waiting to acquire the same lock, in which case we will wait even more.

There is a timeout for such waits, defined by the variable. If it is set to 0, statements that encounter a row lock will fail immediately. When the timeout is exceeded, MariaDB produces the following error:

It is important to note that this variable has two limitations (by design):

• It only affects transactional statements, not statements like ALTER TABLE or TRUNCATE TABLE.

• It only concerns row locks. It does not put a timeout on metadata locks, or table locks acquired - for example - with the statement.

Note however that can be used for metadata locks.

There is a special syntax that can be used with SELECT and some non-transactional statements including ALTER TABLE: the clauses. This syntax puts a timeout in seconds for all lock types, including row locks, table locks, and metadata locks. For example:

InnoDB Transactions

InnoDB Lock Types

InnoDB locks are classified based on what exactly they lock, and which operations they lock.

The first classification is the following:

• Record Locks lock a row or, more precisely, an index entry.

• Gap Locks lock an interval between two index entries. Note that indexes have virtual values of -Infinum and Infinum, so a gap lock can cover the gap before the first or after the last index entry.

• Next-Key Locks lock an index entry and the gap between it and the next entry. They're a combination of record locks and gap locks.

• Insert Intention Locks are gap locks acquired before inserting a new row.

Lock modes are the following:

• Exclusive Locks (X) are generally acquired on writes, e.g. immediately before deleting a row. Only one exclusive lock can be acquired on a resource simultaneously.

• Shared Locks (S) can be acquired on reads. Multiple shared locks can be acquired at the same time (because the rows are not supposed to change when shared-locked) but are incompatible with exclusive locks.

• Intention locks (IS, XS) are acquired when it is not possible to acquire an exclusive lock or a shared lock. When a lock on a row or gap is released, the oldest intention lock on that resource (if any) is converted to an X or S lock.

For more information see .

Information Schema

Querying the is the best way to see which transactions have acquired some locks and which transactions are waiting for some locks to be released.

In particular, check the following tables:

• : requests for locks not yet fulfilled, or that are blocking another transaction.

• : queued requests to acquire a lock.

• : information about all currently executing InnoDB transactions, including SQL queries that are running.

Here is an example of their usage.

Deadlocks

InnoDB detects deadlocks automatically. Since this consumes CPU time, some users prefer to disable this feature by setting the variable to 0. If this is done, locked transactions will wait until the they exceed the . Therefore it is important to set innodb_lock_wait_timeout to a very low value, like 1.

When InnoDB detects a deadlock, it kills the transaction that modified the least amount of data. The client will receive the following error:

The latest detected deadlock, and the killed transaction, can be viewed in the output of . Here's an example:

The latest detected deadlock never disappears from the output of SHOW ENGINE InnoDB STATUS. If you cannot see any, MariaDB hasn't detected any InnoDB deadlocks since the last restart.

Another way to monitor deadlocks is to set to 1 (0 is the default). InnoDB will log all detected deadlocks into the .

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB supports the following types of replication:

• Asynchronous replication.

• Semi-synchronous replication.

• Galera Cluster.

MariaDB starting with

Repairing tables in MariaDB is not similar to repairing tables in SQL Server.

The first thing to understand is that every MariaDB table is handled by a . Storage engines are plugins that know how to physically read and write a table, so each storage engine allows one to repair tables in different ways. The default storage engine is .

MariaDB provides specific SQL statements to deal with corrupted tables:

• checks if a table is corrupted;

• repairs a table if it is corrupted.

This page helps to map each SQL Server type to the matching MariaDB type.

Numbers

In MariaDB, numeric types can be declared as SIGNED or UNSIGNED. By default, numeric columns are SIGNED, so not specifying either will not break compatibility with SQL Server.

When using UNSIGNED

MariaDB architecture is partly different from the architecture of traditional DBMSs, like SQL Server. Here we will examine the main components that a new MariaDB DBA needs to know. We will also discuss a bit of history, because this may help understand MariaDB philosophy and certain design choices.

This section is an overview of the most important components. More information is included in specific sections of this migration guide, or in other pages of the MariaDB documentation (see the links scattered over the text).

Storage Engines

MariaDB was born from the source code of MySQL, in 2008. Therefore, its history begins with MySQL.

MySQL was born at the beginning of the 90s. Back in the days, if compared to its existing competitors, MySQL was lightweight, simple to install, easy to learn. While it had a very limited set of features, it was also fast in certain common operations. And it was open source. These characteristics made it suitable to back the simple websites that existed at that time.

The web evolved rapidly, and the same happened to MySQL. Being open source helped a lot in this respect, because the community needed functionalities that weren’t supported at that time.

MySQL was probably the first database system to support a . Basically, this means that MySQL knows very little about creating or populating a table, reading from it, building proper indexes and caches. It just delegated all these operations to a special plugin type called a storage engine.

One of the first plugins developed by third parties was . It is very fast, and it adds two important features that are not otherwise supported: transactions and .

Note that when MariaDB asks a storage engine to write or read a row, the storage engine could theoretically do anything. This led to the creation of very interesting alternative engines, like (which doesn’t write or read any data, acting like the /dev/null file in Linux), or (which can read and write to files written in many different formats, or remote DBMSs, or some other special data sources).

Nowadays InnoDB is the default MariaDB storage engine, and it is the best choice for most use cases. But for particular needs, sometimes using a different storage engine is desirable. In case of doubts about the best storage engine to use for a specific case, check the page.

When we create a table, we specify its storage engine or use the default one. It is possible to convert an existing table to another storage engine, though this is a blocking operation which requires a complete table copy. Third-party storage engines can also be installed while MariaDB is running.

Note that it is perfectly possible to use tables with different storage engines in the same transaction (even if some engines are not transactional). It is even possible to use different engines in the same query, for example with JOINs and subqueries.

The default storage engine can be changed by changing the variable. A different default can be specified for temporary tables by setting . MariaDB uses for system tables and temporary tables created internally to store the intermediate results of a query.

InnoDB

It is worth spending some more words here about , the default storage engine.

Primary Key and Indexes

InnoDB primary keys are always the equivalent of SQL Server clustered indexes. In other words, an InnoDB table is always ordered by the primary key.

If an InnoDB table doesn't have a user-defined primary key, the first UNIQUE index whose columns are all NOT NULL is used as a primary key. If there is no such index, the table will have a clustered index. The terminology here can be a bit confusing for SQL Server and other DBMS users. A clustered index in InnoDB is a 6 bytes value that is added to the table. This index and its values are completely invisible to the users. It's important to note that clustered indexes are governed by a global mutex that greatly reduces their scalability.

Secondary indexes are ordered by the columns that are part of the index, and contain a reference to each entry's corresponding primary key value.

Some consequences of these design choices are the following:

• For performance reasons, a primary key value should be inserted in order. In other words, the last inserted value should be the highest. This order is normally followed when inserting values into an AUTO_INCREMENT primary key. The reason is that inserting values in the middle of an ordered data structure is slower, unless they fit into existing holes. If we insert primary key values randomly, InnoDB often has to rearrange pages to make some room for the new data.

• A big primary keys means that all secondary indexes are also big.

• A query by primary key will require a single search. A query on a secondary index that also reads columns not contained in the index will require one search on the index, plus one more search for each row that satisfies the index condition.

Tablespaces

For InnoDB, a tablespace is a file containing data (not a file group as in SQL Server). The types of tablespaces are:

• .

• .

• .

The system tablespace is stored in the file ibdata. It contains information used by InnoDB internally, like rollback segments, as well as some system tables. Historically, the system tablespace also contained all tables created by the user. In modern MariaDB versions, a table is created in the system tablespace only if the system variable is set to 0 at the moment of the table creation. By default, innodb_file_per_table is 1.

Tables created while innodb_file_per_table=1 are written into their own tablespace. These are .ibd files.

Starting from , temporary tables are written into temporary tablespaces, which means ibtmp* files. Previously, they were created in the system tablespace or in file-per-table tablespaces according to the value of innodb_file_per_table, just like regular tables. Temporary tablespaces, if present, are deleted when MariaDB starts.

It is important to remember that tablespaces can never shrink. If a file-per-table tablespace grows too much, deleting data won't recover space. Instead, a new table must be created and data needs to be copied. Finally, the old table will be deleted. If the system tablespace grows too much, the only solution is to move data into a new MariaDB installation.

Transaction Logs

In SQL Server, the transaction log contains both the undo log and the redo log. Usually we have only one transaction log.

In MariaDB the undo log and the redo log are stored separately. By default, the is written to two files, called ib_logfile0 and ib_logfile1. The by default is written to the system tablespace, which is in the ibdata1 file. However, it is possible to write it in separate files in a specified directory.

MariaDB provides no way to inspect the contents of the transaction logs. However, it is possible to inspect the .

InnoDB transaction logs are written in a circular fashion: their size is normally fixed, and when the end is reached, InnoDB continues to write from the beginning. However, if very long transactions are running, InnoDB cannot overwrite the oldest data, so it has to expand the log size instead.

InnoDB Buffer Pool

MariaDB doesn't have a central buffer pool. Each storage engine may or may not have a buffer pool. The is typically assigned a big amount of memory. See .

MariaDB has no extension like the SQL Server buffer pool extension.

A part of the buffer pool is called the . It contains dirty pages that have been modified in memory and not yet flushed.

InnoDB Background Threads

InnoDB has background threads that take care of flushing dirty pages from the change buffer to the tablespaces. They don't directly affect the latency of queries, but they are very important for performance.

shows information about them in the BACKGROUND THREAD section. They can also be seen using the table, in the .

InnoDB flushing is similar to lazy writes and checkpoints in SQL Server. It has no equivalent for eager writing.

For more information, see and .

Checksums and Doublewrite Buffer

InnoDB pages have checksums. After writing pages to disk, InnoDB verifies that the checksums match. The checksum algorithm is determined by . Check the variable documentation for its consequences on performance, backward compatibility and encryption.

In case of a system crash, hardware failure or power outage, a page could be half-written on disk. For some pages, this causes a disaster. Therefore, InnoDB writes essential pages to disk twice. A backup copy of the new page version is written first. Then, the old page is overwritten. The backup copies are written into a file called the doublewrite buffer.

• If an event prevents the first page from being written, the old version of the page will still be available.

• If an event prevents the old page from being completely overwritten by its new version, the page can still be recovered using the doublewrite buffer.

The doublewrite buffer can disabled using the variable, but this usually doesn't bring big performance benefits. The doublewrite buffer location can be changed with .

Aria

Even if we only create InnoDB tables, we use Aria indirectly, in two ways:

• For system tables.

• For internal temporary tables.

Aria is a non-transactional storage engine. By default it is crash-safe, meaning that all changes to data are written and fsynced to a write-ahead log and can always be recovered in case of a crash.

Aria caches indexes into the pagecache. Data are not directly cached by Aria, so it's important that the underlying filesystem caches reads and writes.

The pagecache size is determined by the system variable. To know if it is big enough we can check the proportion of free pages (the ratio between and ) and the proportion of cache misses (the ratio between and .

The proportion of dirty pages is the ratio between and tells us if the log file is big enough.

The size of Aria log is determined by .

Databases

MariaDB does not support the concept of schema. In MariaDB SQL, schema and schemas are synonyms for database and databases.

When a user connects to MariaDB, they don't connect to a specific database. Instead, they can access any table they have permissions for. There is however a concept of default database, see below.

A database is a container for database objects like tables and views. A database serves the following purposes:

• A database is a namespace.

• A database is a logical container to separate objects.

• A database has a default and collation, which are inherited by their tables.

• Permissions can be assigned on a whole database, to make permission maintenance simpler.

System Databases

MariaDB has the following system databases:

• is for internal use only, and should not be read or written directly.

• contains all information that can be found in SQL Server's information_schema and more. However, while SQL Server's information_schema is a schema containing information about the local database, MariaDB's information_schema is a database that contains information about all databases.

• contains information about MariaDB runtime. It is disabled by default. Enabling it requires setting the system variable to 1 and restarting MariaDB.

Default Database

When a user connects to MariaDB, they can optionally specify a default database. A default database can also be specified or changed later, with the command.

Having a default database specified allows one to specify tables without specifying the name of the database where they are located. If no default database is specified, all table names must be fully qualified.

For example, the two following snippets are equivalent:

Even if a default database is specified, tables from other databases can be accessed by specifying their fully qualified names:

MariaDB has the function to determine the current database:

Stored procedures and triggers don't inherit a default database from the session, nor by a caller procedure. In that context, the default database is the database which contains the procedure. USE can be used to change it. The default database will only be valid for the rest of the procedure.

The Binary Log

Different tables can be built using different storage engines. It is important to note that not all engines are transactional, and that different engines implement the transaction logs in different ways. For this reason, MariaDB cannot replicate data from a primary to a replica using an equivalent of SQL Server transactional replication.

Instead, it needs a global mechanism to log the changes that are applied to data. This mechanism is the , often abbreviated to binlog.

The binary log can be written in the following formats:

• STATEMENT logs SQL statements that modify data;

• ROW logs a reference to the rows that have been modified, if any (usually it’s the primary key), and the new values that have been added or modified, in a binary format.

• MIXED is a combination of the above formats. It means that ROW is used for statements that can safely be logged in this way (see below), and STATEMENT is used in other cases. This is the default format from .

In most cases, STATEMENT is slower because the SQL statement needs to be re-executed by the replica, and because certain statements may produce a different result in the replica (think about queries that use LIMIT without ORDER BY, or the CURRENT_TIMESTAMP() function). But there are exceptions, and besides, DDL statements are always logged as STATEMENT to avoid flooding the binary log. Therefore, the binary log may well contain both ROW and STATEMENT entries.

See .

The binary log allows:

• replication, if enabled on the primary;

• promoting a replica to a primary, if enabled on that replica;

• incremental backups;

• seeing data as they were in a point of time in the past ();

If you don't plan to use any of these features on a server, it is possible to the binary log to slightly improve the performance.

The binary log can be inspected using the utility, which comes with MariaDB. Enabling or disabling the binary log requires restarting MariaDB.

See also and for a better understanding of how the binary log is used.

Plugins

Storage engines are a special type of . But others exist. For example, plugins can add authentication methods, new features, SQL syntax, functions, informative tables, and more.

A plugin may add some server variables and some status variables. Server variables can be used to configure the plugin, and status variables can be used to monitor its activities and status. These variables generally use the plugin's name as a prefix. For example InnoDB has a server variable called innodb_buffer_pool_size to configure the size of its buffer pool, and a status variable called Innodb_pages_read which indicates the number of memory pages read from the buffer pool. The category of the MariaDB documentation has specific pages for system and status variables associated with various plugins.

Many plugins are installed by default, or available but not installed by default. They can be installed or uninstalled at runtime with SQL statements, like INSTALL PLUGIN, UNINSTALL PLUGIN and others; see . 3rd party plugins can be made available for installation by simply copying them to the .

It is important to note that different plugins may have different maturity levels. It is possible to prevent the installation of plugins we don’t consider production-ready by setting the system variable. For plugins that are distributed with MariaDB, the maturity level is determined by the MariaDB team based on the bugs reported and fixed.

Some plugins are developed by 3rd parties. Even some 3rd party plugins are included in MariaDB official distributions - the ones available on mariadb.org.

In MariaDB every authorization method (including the default one) is provided by an . A user can be required to use a certain authentication plugin. This gives us much flexibility and control. Windows users may be interested in (which supports Windows authentication, Kerberos and NTLM) and (which uses named pipe impersonation).

Other plugins that can be very useful include , which includes statistics about resources and table usage, and , which provides information about metadata locks.

Thread Pool

MariaDB supports . It works differently on UNIX and on Windows. On Windows, it is enabled by default and its implementation is quite similar to SQL Server. It uses the Windows native CreateThreadpool API.

If we don't use the thread pool, MariaDB will use its traditional method to handle connections. It consists of using a dedicated thread for each client connection. Creating a new thread has a cost in terms of CPU time. To mitigate this cost, after a client disconnects, the thread may be preserved for a certain time in the .

Whichever connection method we use, MariaDB has a maximum number of simultaneous connections, which can be changed at runtime. When the limit is reached, if more clients try to connect they will receive an error. This prevents MariaDB from consuming all the server resources and freezing or crashing. See .

Configuration

MariaDB has many settings that control the server behavior. These can be set up when starting mysqld (), and the vast majority are also accessible as . These can be classified in these ways:

• Dynamic or static;

• Global, session, or both.

Note that server system variables are not to be confused with . The latter are not used for MariaDB configuration.

Configuration Files

MariaDB can use several . Configuration files are searched in several locations, including in the user directory, and if present they all are read and used. They are read in a consistent order. These locations depend on the operating system; see . It is possible to tell MariaDB which files it should read; see .

On Linux, by default the configuration files are called my.cnf. On Windows, by default the configuration files can be called my.ini or my.cnf. The former is more common.

If a variable is mentioned multiple times in different files, the occurrence that is read last will overwrite the others. Similarly, if a variable is mentioned several times in a single file, the occurrence that is read last overwrites the others.

The contents of each configuration file are organized by option groups. MariaDB Server and client programs read different groups. The read groups also depend on the MariaDB version. See for the details. Most commonly, the [server] or [mysqld] groups are used to contain all server configuration. The [client-server] group can be used for options that are shared by the server and the clients (like the port to use), to avoid repeating those variables multiple times.

Dynamic and Static Variables

Dynamic variables have a value that can be changed at runtime, using the SQL statement. Static variables have a value that is decided at startup (see below) and cannot be changed without a restart.

The page states if variables are dynamic or static.

Scope

A global system variable is one that affects the general behavior of MariaDB. For example determines the size of the InnoDB buffer pool, which is used by read and write operations, no matter which user issued them. A session system variable is one that affects MariaDB behavior for the current connection; changing it will not affect other connected users, or future connections from the current user.

A variable could exist in both the global and session scopes. In this case, the session value is what affects the current connection. When a user connects, the current global value is copied to the session scope. Changing the global value afterward will not change existing connections.

The page states the scope of each variable.

Global variables and some session variables can only be modified by a user with the privilege (typically root).

Syntax

To see the value of a system variable:

A longer syntax, which is mostly useful to get multiple variables, makes use of the same pattern syntax that is used by the operator:

To modify the global or session value of a dynamic variable:

Notice that if we modify a global variable in this way, the new value will be lost at server restart. For this reason we probably want to change the value in the configuration file too.

For further information see:

• The statement.

• The statement.

Setting System Variables with Startup Parameters

System variables can be set at server startup without writing their values into a configuration file. This is useful if we want a value to be set once, until we change it or restart MariaDB. Values passed in this way override values written in the configuration files.

The general rule is that every global variable can be passed as an argument of mysqld by prefixing its name with -- and by replacing every occurrence of _ with - in its name.

For example, to pass bind_address as a startup argument:

Debugging Configuration

Mistyping a variable can prevent MariaDB from starting. We cannot set a variable that doesn't exist in the MariaDB version in use. In these cases, an error is written in the .

Having several configuration files and configuration groups, as well as being able to pass variables as command-line arguments, brings a lot of flexibility but can sometimes be confusing. When we are unsure about which values will be used, we can run:

Status Variables

MariaDB status variables and some system tables allow external tools to monitor a server, building graphs on how they change over time, and allow the user to inspect what is happening inside the server.

cannot be directly modified by the user. Their values indicate how MariaDB is operating. Their scope can be:

• Global, meaning that the value is about some MariaDB activity.

• Session, meaning that the value measures activities taking place in the current session.

Many status variables exist in both scopes. For example, at global level indicates how much time the CPU was used by the MariaDB process (including all user sessions and all the background threads). At session level, it indicates how much time the CPU was used by the current session.

The status variables created by a plugin, usually, use the plugin name as a prefix.

The statement prints the values of the status variables that match a certain pattern.

Some status variables values are reset when is executed. A possible use:

_{This page is licensed: CC BY-SA / Gnu FDL}