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:


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:

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.
UTF8_IS_UTF8MB3From MariaDB 10.6.1, the main name of the previous 3 byte utf character set has been changed to utf8mb3. If set, the default, utf8 is an alias for utf8mb3. If not set, utf8 would be an alias for utf8mb4.
ZERO_DATE_TIME_CASTWhen a TIME value is cast to a DATETIME, the date part will be 0000-00-00, not CURRENT_DATE (as dictated by the SQL standard).

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.


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

| REPLACE(@@OLD_MODE, ',', '\n')                                          |

Adding a new flag:


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

How to unset a flag:


How to check if a flag is set:

|                                  1 |


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.