OLD_MODE
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:
Option | Description |
---|---|
NO_DUP_KEY_WARNINGS_WITH_IGNORE | Don't print duplicate key warnings when using INSERT IGNORE |
NO_PROGRESS_INFO | Don'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 | +----------------------------------+