lower_case_table_names
This page is part of MariaDB's Enterprise Documentation.
The parent of this page is: System Variables for MariaDB Enterprise Server
Topics on this page:
Overview
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.
See also: System Variables for MariaDB Enterprise Server 10.6, in 10.6 CS, in 10.5 ES, in 10.5 CS, in 10.4 ES, in 10.4 CS, in 10.3 ES, in 10.3 CS, in 10.2 ES, and in 10.2 CS
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.
PARAMETERS
Command-line | --lower_case_table_names[=#] |
Configuration file | Supported |
Dynamic | No |
Scope | Global |
Data Type | INT UNSIGNED |
Minimum Value | 0 |
Maximum Value | 2 |
Default Value | 0 |
EXAMPLES
Case-Sensitive Names
When the lower_case_tables_names
system variable is set to 0 (the default on Unix-based systems), table names, table aliases, and database names are compared in a case-sensitive manner:
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
Case-Insensitive Names
When the lower_case_tables_names
system variable is set to 1 (the default on Windows), table names and database names are stored in lowercase and compared in a case-insensitive manner:
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)
Special Mode for Case-Insensitive File Systems
When the lower_case_table_names
system variable is set to 2, the server uses a special mode for case-insensitive file systems. In this mode, table names and database names are stored as declared, but they are compared in lowercase.
If the lower_case_table_names
system variable is set to 0 and the server is using a case-insensitive file system, the value is automatically set to 2. In that case, the following error will be written to the error log:
[Warning] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
For example, this would happen on Mac OS X when using HFS+ or APFS, except when APFS is used in case-sensitive mode.
If the lower_case_table_names
system variable is set to 2 and the server is using a case-sensitive file system, the value is automatically set to 0. In that case, the following error will be written to the error log:
[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.
For example, this would usually happen on Linux, since it almost always uses a case-sensitive file system.
CHANGE HISTORY
Release Series | History |
---|---|
10.6 Enterprise |
|
10.6 Community |
|
10.5 Enterprise |
|
10.5 Community |
|
10.4 Enterprise |
|
10.4 Community |
|
10.3 Enterprise |
|
10.3 Community |
|
10.2 Enterprise |
|
10.2 Community |
|
EXTERNAL REFERENCES
Information specific to MariaDB SkySQL can be found on the lower_case_table_names page in the SkySQL Documentation.