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.

In other words, OLD_MODE options are by design deprecated from the day they were added and will eventually be removed as any other deprecated feature.

You can check the variable's local and global value 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.

Non-default old mode features are deprecated by design, and from MariaDB 11.3, a warning will be issued when set.

Modes

The different values of OLD_MODE are:

COMPAT_5_1_CHECKSUM

From MariaDB 10.9, the --old option is deprecated. This option allows behaviour of the --old option for enabling the old-style checksum for CHECKSUM TABLE that MySQL 5.1 supports

IGNORE_INDEX_ONLY_FOR_JOIN

From MariaDB 10.9, the --old option is deprecated. This option allows behaviour of the --old option for disabling the index only for joins, but allow it for ORDER BY.

LOCK_ALTER_TABLE_COPY

From MariaDB 11.2. The non-locking copy ALTER introduced in MDEV-16329 should be beneficial in the vast majority of cases, but scenarios can exist which significantly impact performance. For example, RBR on tables without a primary key. When non-locking ALTER is performed on such a table, and DML affecting a large number of records is run in parallel, the ALTER can become extremely slow, and further DML can also be affected. If there is a chance of such scenarios (and no possibility of improving the schema by immediately adding primary keys), ALTER should be performed with the explicit LOCK=SHARED clause. If this is also impossible, then LOCK_ALTER_TABLE_COPY flag should be added to the old_mode variable until the schema can be improved.

NO_DUP_KEY_WARNINGS_WITH_IGNORE

Don't print duplicate key warnings when using INSERT IGNORE.

NO_NULL_COLLATION_IDS

A compatibility setting to support connectors (in particular MySQL Connector/NET) that give an exception when collation ids returned by SHOW COLLATION are NULL. It is automatically set when a MySQL Connector/NET connection is determined. From MariaDB 10.11.7, MariaDB 11.0.5, MariaDB 11.1.4, MariaDB 11.2.3.

NO_PROGRESS_INFO

Don't show progress information in SHOW PROCESSLIST.

OLD_FLUSH_STATUS

From MariaDB 11.5, restores the pre-MariaDB 11.5 behavior of FLUSH STATUS.

SESSION_USER_IS_USER

From MariaDB 11.7, restores the pre-MariaDB 11.7 behavior of SESSION_USER.

UTF8_IS_UTF8MB3

From 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_CAST

When 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 sent to replicas.

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 |
+------------------------------------+

From MariaDB 11.3:

SET @@OLD_MODE = CONCAT(@@OLD_MODE, ',NO_PROGRESS_INFO');
Query OK, 0 rows affected, 1 warning (0.000 sec)

SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1287 | 'NO_PROGRESS_INFO' is deprecated and will be removed in a future release |
+---------+------+--------------------------------------------------------------------------+