mariadb_schema

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

mariadb_schema is a data type qualifier that allows one to create MariaDB native date types in a SQL_MODE that has conflicting data type translations.

mariadb_schema was introduced in MariaDB versions 10.3.24, 10.4.14, 10.5.5.

For example in SQL_MODE=ORACLE, if one creates a table with the DATE type, it will actually create a DATETIME column to match what an Oracle user is expecting. To be able to create a MariaDB DATE in Oracle mode one would have to use mariadb_schema:

CREATE TABLE t1 (d mariadb_schema.DATE);

mariadb_schema is also shown if one creates a table with DATE in MariaDB native moved and then does a SHOW CREATE TABLE in ORACLE mode:

SET sql_mode=DEFAULT;
CREATE OR REPLACE TABLE t1 (
  d DATE
);
SET SQL_mode=ORACLE;
SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------+
| Table | Create Table                                                 |
+-------+--------------------------------------------------------------+
| t1    | CREATE TABLE "t1" (
  "d" mariadb_schema.date DEFAULT NULL
) |
+-------+--------------------------------------------------------------+

When the server sees the mariadb_schema qualifier, it disables sql_mode specific data type translation and interprets the data type literally, so for example mariadb_schema.DATE is interpreted as the traditional MariaDB DATE data type, no matter what the current sql_mode is.

The mariadb_schema prefix is displayed only when the data type name would be ambiguous otherwise. The prefix is displayed together with MariaDB DATE when SHOW CREATE TABLE is executed in SQL_MODE=ORACLE. The prefix is not displayed when SHOW CREATE TABLE is executed in SQL_MODE=DEFAULT, or when a non-ambiguous data type is displayed.

Note, the mariadb_schema prefix can be used with any data type, including non-ambiguous ones:

CREATE OR REPLACE TABLE t1 (a mariadb_schema.INT);
SHOW CREATE TABLE t1;
+-------+--------------------------------------------------+
| Table | Create Table                                     |
+-------+--------------------------------------------------+
| t1    | CREATE TABLE "t1" (
  "a" int(11) DEFAULT NULL
) |
+-------+--------------------------------------------------+

When is mariadb_schema prefix used

Currently the mariadb_schema prefix is only used in the following cases:

History

When running with SQL_MODE=ORACLE, MariaDB server translates the data type DATE to DATETIME, for better Oracle compatibility:

SET SQL_mode=ORACLE;
CREATE OR REPLACE TABLE t1 (
  d DATE
);
SHOW CREATE TABLE t1;
+-------+---------------------------------------------------+
| Table | Create Table                                      |
+-------+---------------------------------------------------+
| t1    | CREATE TABLE "t1" (
  "d" datetime DEFAULT NULL
) |
+-------+---------------------------------------------------+

Notice, DATE was translated to DATETIME.

This translation may cause some ambiguity. Suppose a user creates a table with a column of the traditional MariaDB DATE data type using the default sql_mode, but then switches to SQL_MODE=ORACLE and runs a SHOW CREATE TABLE statement:

SET sql_mode=DEFAULT;
CREATE OR REPLACE TABLE t1 (
  d DATE
);
SET SQL_mode=ORACLE;
SHOW CREATE TABLE t1;

Before mariadb_schema was introduced, the above script displayed:

CREATE TABLE "t1" (
  "d" date DEFAULT NULL
);

which had two problems:

  • It was confusing for the reader: its not clear if it is the traditional MariaDB DATE, or is it Oracle-alike date (which is actually DATETIME);
  • It broke replication and caused data type mismatch on the master and on the slave (see MDEV-19632).

To address this problem, starting from the mentioned versions, MariaDB uses the idea of qualified data types:

SET sql_mode=DEFAULT;
CREATE OR REPLACE TABLE t1 (
  d DATE
);
SET SQL_mode=ORACLE;
SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------+
| Table | Create Table                                                 |
+-------+--------------------------------------------------------------+
| t1    | CREATE TABLE "t1" (
  "d" mariadb_schema.date DEFAULT NULL
) |
+-------+--------------------------------------------------------------+

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.