lower_case_table_names

USAGE

The lower_case_table_names system variable can be set in a configuration file:

[mariadb]
lower_case_table_names=1

The lower_case_table_names system variable cannot be set at runtime.

DETAILS

The lower_case_table_names system variable determines whether table names, table aliases, and database names are compared in a case-sensitive manner, and whether tablespace files are stored on disk in a case-sensitive manner.:

• When set to 0 (the default on Unix-based systems), table names, table aliases, and database names are compared in a case-sensitive manner.

• When set to 1 (the default on Windows), table names and database names are stored in lowercase and compared in a case-insensitive manner.

• When set to 2, table names and database names are stored as declared, but they are compared in lowercase. This value is rejected if the server is using a case-sensitive file system. If set to 0 and the server is using a case-insensitive file system, the value is automatically set to 2. For example, this would happen on Mac OS X when using HFS+ or APFS, except when APFS is used in case-sensitive mode. If set to 2 and the server is using a case-sensitive file system, the value is automatically set to 0. For example, this would usually happen on Linux, since it almost always uses a case-sensitive file system.

EXAMPLES

SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);

SELECT * FROM tab;

Empty set (0.000 sec)

SELECT * FROM TAB;

ERROR 1146 (42S02): Table 'TAB' doesn't exist

SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);

SELECT * FROM tab;

Empty set (0.000 sec)

SELECT * FROM TAB;

Empty set (0.000 sec)

[Warning] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive

[Warning] lower_case_table_names was set to 2, even though your the file system '/var/lib/mysql/' is case sensitive.
   Now setting lower_case_table_names to 0 to avoid future problems.

CHANGE HISTORY

EXTERNAL REFERENCES

• Information specific to MariaDB SkySQL can be found on the lower_case_table_names page in the SkySQL Documentation.