mariadb_schema
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 TABLEis 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:
- For MariaDB native
DATE
type when running SHOW CREATE TABLE inORACLE
mode.
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:
<<code>
SET sql_mode=DEFAULT;
CREATE OR REPLACE TABLE t1 (
d DATE
);
SET SQL_mode=ORACLE;
SHOW CREATE TABLE t1;
<</code>>
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 actuallyDATETIME
); - 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 ) | +-------+--------------------------------------------------------------+