> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/server-management/variables-and-modes/old_mode.md). # OLD\_MODE The [old\_mode](/docs/server/server-management/variables-and-modes/server-system-variables.md#old_mode) system variable was introduced to replace the [old](/docs/server/server-management/variables-and-modes/server-system-variables.md#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](/docs/server/server-management/variables-and-modes/sql_mode.md) and `OLD_MODE`. [SQL\_MODE](/docs/server/server-management/variables-and-modes/sql_mode.md) 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](/docs/release-notes/community-server/about/feature-deprecation-policy.md). You can check the variable's local and global value with: ```sql SELECT @@OLD_MODE, @@GLOBAL.OLD_MODE; ``` You can set the `OLD_MODE` either from the [command line](/docs/server/server-management/starting-and-stopping-mariadb/mariadbd-options.md) (option `--old-mode`) or by setting the [old\_mode](/docs/server/server-management/variables-and-modes/server-system-variables.md#old_mode) system variable. Non-default old mode features are deprecated by design, and a warning is issued when set. ## Modes The different values of `OLD_MODE` are: ### 2\_DIGIT\_YEAR From MariaDB 13.0, restores support for the two-digit [`YEAR(2)`](/docs/server/reference/data-types/date-and-time-data-types/year-data-type.md) data type, which is otherwise no longer accepted. Like other `OLD_MODE` flags, it is deprecated by design and intended only as a temporary migration aid. ### COMPAT\_5\_1\_CHECKSUM From [MariaDB 10.9](/docs/release-notes/community-server/old-releases/10.9/what-is-mariadb-109.md), the [--old option](/docs/server/server-management/variables-and-modes/server-system-variables.md#old) 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](/docs/release-notes/community-server/old-releases/10.9/what-is-mariadb-109.md), the [--old option](/docs/server/server-management/variables-and-modes/server-system-variables.md#old) 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](/docs/release-notes/community-server/old-releases/11.2/what-is-mariadb-112.md). The non-locking copy ALTER introduced in [MDEV-16329](https://jira.mariadb.org/browse/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](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/ignore.md). ### 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](/docs/server/reference/sql-statements/administrative-sql-statements/show/show-collation.md) are NULL. It is automatically set when a MySQL Connector/NET connection is determined. From [MariaDB 10.11.7](/docs/release-notes/community-server/10.11/10.11.7.md), [MariaDB 11.0.5](/docs/release-notes/community-server/old-releases/11.0/11.0.5.md), [MariaDB 11.1.4](/docs/release-notes/community-server/old-releases/11.1/11.1.4.md), [MariaDB 11.2.3](/docs/release-notes/community-server/old-releases/11.2/11.2.3.md). ### NO\_PROGRESS\_INFO Don't show progress information in [SHOW PROCESSLIST](/docs/server/reference/sql-statements/administrative-sql-statements/show/show-processlist.md). ### OLD\_FLUSH\_STATUS From [MariaDB 11.5](/docs/release-notes/community-server/old-releases/11.5/what-is-mariadb-115.md), restores the pre-[MariaDB 11.5](/docs/release-notes/community-server/old-releases/11.5/what-is-mariadb-115.md) behavior of [FLUSH STATUS](/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush.md#flush-status). ### SESSION\_USER\_IS\_USER From [MariaDB 11.7](/docs/release-notes/community-server/old-releases/11.7/what-is-mariadb-117.md), restores the pre-[MariaDB 11.7](/docs/release-notes/community-server/old-releases/11.7/what-is-mariadb-117.md) behavior of [SESSION\_USER](/docs/server/reference/sql-functions/secondary-functions/information-functions/session_user.md). ### UTF8\_IS\_UTF8MB3 From [MariaDB 10.6.1](/docs/release-notes/community-server/10.6/10.6.1.md), the main name of the previous 3 byte `utf` [character set](/docs/server/reference/data-types/string-data-types/character-sets.md) 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](/docs/server/reference/data-types/date-and-time-data-types/time.md) value is cast to a [DATETIME](/docs/server/reference/data-types/date-and-time-data-types/datetime.md), the date part will be `0000-00-00`, not [CURRENT\_DATE](/docs/server/reference/sql-functions/date-time-functions/curdate.md) (as dictated by the SQL standard). ## OLD\_MODE and Stored Programs In contrast to [SQL\_MODE](/docs/server/server-management/variables-and-modes/sql_mode.md), [stored programs](/docs/server/server-usage/stored-routines.md) use the current user's `OLD_MODE`value. Changes to `OLD_MODE` are not sent to replicas. ## Examples This example shows how to get a readable list of enabled OLD\_MODE flags: ```sql SELECT REPLACE(@@OLD_MODE, ',', '\n'); +---------------------------------------------------+ | REPLACE(@@OLD_MODE, ',', '\n') | +---------------------------------------------------+ | NO_DUP_KEY_WARNINGS_WITH_IGNORE | | NO_PROGRESS_INFO | +---------------------------------------------------+ ``` Adding a new flag: ```sql 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: ```sql SET @@OLD_MODE = REPLACE(@@OLD_MODE, 'NO_PROGRESS_INFO', ''); ``` How to check if a flag is set: ```sql SELECT @@OLD_MODE LIKE '%NO_PROGRESS_INFO'; +------------------------------------+ | @@OLD_MODE LIKE '%NO_PROGESS_INFO' | +------------------------------------+ | 1 | +------------------------------------+ ``` From [MariaDB 11.3](/docs/release-notes/community-server/old-releases/11.3/what-is-mariadb-113.md): ```sql 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 | +---------+------+--------------------------------------------------------------------------+ ``` *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}