OLD_MODE

The old_mode system variable was introduced in 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 .

You can check the variable's local and global value with:

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

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

Modes

The different values of OLD_MODE are:

COMPAT_5_1_CHECKSUM

From , the 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 , the 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 . The non-locking copy ALTER introduced in 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 .

NO_NULL_COLLATION_IDS

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

NO_PROGRESS_INFO

Don't show progress information in .

OLD_FLUSH_STATUS

From , restores the pre- behavior of .

SESSION_USER_IS_USER

From , restores the pre- behavior of .

UTF8_IS_UTF8MB3

From , the main name of the previous 3 byte utf 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 value is cast to a , the date part will be 0000-00-00, not (as dictated by the SQL standard).

OLD_MODE and Stored Programs

In contrast to , 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:

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:

From :

_{This page is licensed: CC BY-SA / Gnu FDL}