lower_case_table_names

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.

USAGE

The lower_case_table_names system variable can have its default value configured via SkySQL Configuration Manager.

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.

SYNONYMS

SCHEMA

PARAMETERS

Command-line

--lower_case_table_names[=#]

Configuration file

Supported

Dynamic

No

Scope

Global

Data Type

INT UNSIGNED

Minimum Value

0

Maximum Value

2

Product Default Value

0

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

0 (choices: 0, 1, 2)

Single Node Analytics

10.6

0 (choices: 0, 1, 2)

Replicated Transactions

10.4

0 (choices: 0, 1, 2)

10.5

0 (choices: 0, 1, 2)

10.6

0 (choices: 0, 1, 2)

Single Node Transactions

10.4

0 (choices: 0, 1, 2)

10.5

0 (choices: 0, 1, 2)

10.6

0 (choices: 0, 1, 2)

GCP

Multi-Node Analytics

10.6

0 (choices: 0, 1, 2)

Single Node Analytics

10.6

0 (choices: 0, 1, 2)

Replicated Transactions

10.4

0 (choices: 0, 1, 2)

10.5

0 (choices: 0, 1, 2)

10.6

0 (choices: 0, 1, 2)

Single Node Transactions

10.4

0 (choices: 0, 1, 2)

10.5

0 (choices: 0, 1, 2)

10.6

0 (choices: 0, 1, 2)

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

0 (choices: 0, 1, 2)

Enterprise Server With Replica(s)

10.4

0 (choices: 0, 1, 2)

10.5

0 (choices: 0, 1, 2)

10.6

0 (choices: 0, 1, 2)

Enterprise Server Single Node

10.4

0 (choices: 0, 1, 2)

10.5

0 (choices: 0, 1, 2)

10.6

0 (choices: 0, 1, 2)

GCP

ColumnStore Data Warehouse

10.6

0 (choices: 0, 1, 2)

Enterprise Server With Replica(s)

10.4

0 (choices: 0, 1, 2)

10.5

0 (choices: 0, 1, 2)

10.6

0 (choices: 0, 1, 2)

Enterprise Server Single Node

10.4

0 (choices: 0, 1, 2)

10.5

0 (choices: 0, 1, 2)

10.6

0 (choices: 0, 1, 2)

PRIVILEGES

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.

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES