OLD_MODE

You are viewing an old version of this article. View the current version here.

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

The most important value for doing this are SQL_MODE and OLD_MODE.

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

SQL_MODE is for having MariaDB emulate how other SQL servers are doing things while OLD_MODE is for getting 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 to be 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 command line (option --old-mode) or by setting the old_mode system variable.

The different values of OLD_MODE are:

OptionDescription
NO_DUP_KEY_WARNINGS_WITH_IGNOREDon't print duplicate key warnings when using INSERT IGNORE
NO_PROGRESS_INFODon't show progress information in SHOW PROCESSLIST.

OLD_MODE and stored programs

In contrast to SQL_MODE, stored programs uses the current users value of OLD_MODE.

Changes to OLD_MODE is 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 |
+----------------------------------+

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.