The Ultimate Guide to High Availability with MariaDB

OLD_MODE

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

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

Contents

  1. OLD_MODE and Stored Programs
  2. Examples

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:

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.

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.
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).
IGNORE_INDEX_ONLY_FOR_JOINFrom MariaDB 10.9, --old option is deprecated. This option allows behaviour of --old option for disabling the index only for joins, but allow it for ORDER BY.
COMPAT_5_1_CHECKSUMFrom MariaDB 10.9, --old option is deprecated. This option allows behaviour of --old option for enabling old-style checksum for CHECKSUM TABLE that MySQL 5.1 supports

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.

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.