This system database contains crucial metadata about the server, including information schema, statistics, and optimizer hints, for internal operations.
mariadb_schema is a data type qualifier that allows one to create MariaDB native date types in an SQL_MODE that has conflicting data type translations.
In , if you create a table with the DATE type, it actually creates a DATETIME column to match what an Oracle user is expecting. To be able to create a MariaDB DATE in Oracle mode, you would have to use mariadb_schema:
mariadb_schema is also shown if you create a table with DATE in MariaDB native mode and then do a in ORACLE mode:
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 is executed in . The prefix is not displayed when is executed in SQL_MODE=DEFAULT, or when a non-ambiguous data type is displayed.
The mariadb_schema prefix is only used in the following case:
For a MariaDB native type when running in .
When running with , MariaDB server translates the data type DATE to DATETIME, for better Oracle compatibility:
Notice, DATE was translated to DATETIME.
This translation may cause some ambiguity. Suppose you create a table with a column of the traditional MariaDB DATE data type using the default SQL mode, but then switche to SQL_MODE=ORACLE and run a statement:
Before mariadb_schema was introduced, the above script displayed:
This 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 ).
To address this problem, starting from the mentioned versions, MariaDB uses the idea of qualified data types:
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE t1 (d mariadb_schema.DATE);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
) |
+-------+--------------------------------------------------------------+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
) |
+-------+--------------------------------------------------+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
) |
+-------+---------------------------------------------------+SET sql_mode=DEFAULT;
CREATE OR REPLACE TABLE t1 (
d DATE
);
SET SQL_mode=ORACLE;
SHOW CREATE TABLE t1;CREATE TABLE "t1" (
"d" DATE DEFAULT NULL
);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
) |
+-------+--------------------------------------------------------------+