OLD_MODE

The old_mode system variable was introduced in MariaDB 5.5.35 to replace the old variable with a new one with better granularity.

MariaDB supports several different modes which allow you to tune it to suit your needs.

The most important ways for doing this are with SQL_MODE and OLD_MODE.

SQL_MODE is used for getting MariaDB to emulate behavior from other SQL servers, while OLD_MODE is used for emulating behavior from older MariaDB or MySQL versions.

OLD_MODE is a string with different options separated by commas (',') without spaces. The options are case insensitive.

Normally OLD_MODE should be empty. It's mainly used to get old behavior when switching to MariaDB or to a new major version of MariaDB, until you have time to fix your application.

Between major versions of MariaDB various options supported by OLD_MODE may be removed. This is intentional as we assume that the application will be fixed to conform with the new MariaDB behavior between releases.

You can check the local and global value of it with:

SELECT @@OLD_MODE, @@GLOBAL.OLD_MODE;

You can set the OLD_MODE either from the command line (option --old-mode) or by setting the old_mode system variable.

The different values of OLD_MODE are:

OLD_MODE and Stored Programs

In contrast to SQL_MODE, stored programs use the current user's OLD_MODEvalue.

Changes to OLD_MODE are not replicated to slaves.

Examples

This example shows how to get a readable list of enabled OLD_MODE flags:

SELECT REPLACE(@@OLD_MODE, ',', '\n');
+-------------------------------------------------------------------------+
| REPLACE(@@OLD_MODE, ',', '\n')                                          |
+-------------------------------------------------------------------------+
| NO_DUP_KEY_WARNINGS_WITH_IGNORE,
NO_PROGRESS_INFO |
+-------------------------------------------------------------------------+

Adding a new flag:

SET @@OLD_MODE = CONCAT(@@OLD_MODE, ',NO_PROGRESS_INFO');

If the specified flag is already ON, the above example has no effect but does not produce an error.

How to unset a flag:

SET @@OLD_MODE = REPLACE(@@OLD_MODE, 'NO_PROGRESS_INFO', '');

How to check if a flag is set:

SELECT @@OLD_MODE LIKE '%NO_PROGRESS_INFO';
+------------------------------------+
| @@OLD_MODE LIKE '%NO_PROGESS_INFO' |
+------------------------------------+
|                                  1 |
+------------------------------------+