By Evan Elias | June 7, 2022
When running database servers on multiple operating systems, data and schema portability can be challenging due to identifier case sensitivity differences. The lower_case_table_names server variable in MySQL and MariaDB can help, but it is often misunderstood. In this post, I’ll provide in-depth guidance on this tricky subject, and show how Skeema can assist.
Operating systems and database servers
MySQL and MariaDB database servers frequently run on Linux, especially in production. However, it’s also common to run dev database environments locally on engineers' laptops, running a mix of MacOS and Windows. This situation presents some portability challenges: by default, directory paths and file names are case-sensitive on Linux, but case-insensitive on MacOS and Windows.
In MySQL and MariaDB, each database schema has its own subdirectory on the server, containing separate files for each table. This means the operating system’s case-sensitivity (or lack thereof) directly affects how database and table names are interpreted in your SQL queries. On a case-insensitive OS, SELECT * FROM foo and SELECT * FROM Foo are equivalent; but on a case-sensitive OS, foo and Foo refer to completely different tables – one of which likely does not exist, resulting in a query error!
So, when developers test their code locally on MacOS or Windows dev DBs, some queries may work fine, only to unexpectedly break in production on Linux due to its case-sensitivity. CI systems and staging environments can help to catch this earlier, but even then, it can be a confusing source of errors which wastes time to debug.
Adding to the problem, the database server operating system may be opaque in a modern tech stack:
- Most cloud managed databases (e.g. AWS RDS, GCP CloudSQL) run on Linux under the hood. But one major exception is Azure Database for MySQL, which runs on Windows servers.
- When engineers use Docker Desktop for Windows or Mac, it internally uses a Linux VM. So, internal Docker volumes have case-sensitive filesystems – even though the host OS is case-insensitive!
- However, with Docker Desktop, if you bind-mount a directory from the Windows or Mac host into the container (for use as MySQL’s data dir), then this directory is actually case-insensitive, despite it running on a Linux VM.
Confused yet? Don’t worry – even if you’re not sure whether your DB server OS is case-sensitive, the DB auto-detects this upon initialization, and helpfully chooses a default for its lower_case_table_names behavior accordingly. So that’s great! … unless your various environments use a mix of different multiple operating systems for databases, in which case the default value may cause painful portability headaches. Let’s dive deeper!
The lower_case_table_names variable
MySQL and MariaDB have a lower_case_table_names global variable, or “LCTN” for short. This variable works like an enum with 3 possible values, affecting case-sensitivity for names of databases, tables, and views as follows:
lower_case_table_names=0: Names are case-sensitive. Default value for Linux database servers. Cannot be used natively on Windows or MacOS in a safe manner.lower_case_table_names=1: Names are case-insensitive, and are automatically converted to all-lowercase internally and in all DB metadata. Default value for Windows database servers. Can be used on any OS.lower_case_table_names=2: Names are case-insensitive, but retain their original casing internally and in DB metadata. Default value for MacOS database servers. Cannot be used natively on Linux.
If you have multiple database environments, differences in the LCTN setting may cause portability problems. Since LCTN=1 converts names to lowercase internally, this affects information_schema and SHOW commands, which are used by logical dump tools. This means a logical dump obtained from an LCTN=1 server will have all database/table/view names in lowercase, which can be problematic if later imported into an LCTN=0 server where names are case-sensitive. Note that this affects data dumps (e.g. mysqldump, mydumper, mysqlsh dump methods, etc) as well as schema dumps (skeema init, skeema pull, skeema format)!
There’s one other extremely important aspect of LCTN: you can’t change it after installation in recent database versions. (Older versions may let you, but even then, you shouldn’t!)
Typically, global variables are either “dynamic” or “static”; the former can be changed at runtime, while the latter requires restarting the database server to change. LCTN is a rare case that is neither of these: you can override it upon first initializing your database server, but after that, it’s effectively frozen. At that point, the only way to safely change it is to perform a logical dump, wipe, re-init, and reload all tables in your DB! And even then, a dump from an LCTN=1 server may require careful manually editing in order to restore the original casing of names.
Ensuring data and schema OS portability
There are several possible approaches to consider, presented here in order from most-preferred to least:
Option A: Converge on a single database server operating system
For example, use Linux everywhere. If local development databases are needed on MacOS or Windows, use local Linux VMs, and avoid mapping directories from the host into the VM. For example, when using Docker Desktop, use the default Docker volumes instead of bind-mounts for the database’s data directory.
Alternatively, use Windows everywhere. If local development databases are needed, run MySQL natively on Windows, rather than using Docker Desktop, WSL, or any other Linux VM-based solution. If a managed cloud database is needed (for production or otherwise), consider Azure Database for MySQL.
Option B: Voluntarily use all-lowercase names
If you intentionally only ever use lowercase names for databases/tables/views, your data and schema dumps are completely portable across OSes, regardless of LCTN setting. Skeema’s lint-name-case option can be used to enforce an all-lowercase policy.
Option C: Use LCTN=1 everywhere
This essentially achieves the same result as option B. However, since LCTN=1 isn’t the default on Linux or MacOS, it’s easy to forget when setting up a new environment. And if you have existing environments with LCTN=0 or 2, it is difficult to switch, as described above.
Option D: Use LCTN=0 on Linux, and LCTN=2 on Mac/Windows
Because LCTN=2 retains original name casing, it’s mostly cross-compatible with LCTN=0. One downside here is remembering to configure LCTN=2 on Windows (instead of its normal default of LCTN=1). Another issue relates to LCTN=2’s handling of views, which is arguably buggy; more on that below.
Option E: Use default LCTN everywhere, and be careful about dump sources
If you can treat Windows (LCTN=1) database servers as “import only”, and never obtain any kind of logical dump from them, this will mostly work out OK. If you use views, you also need to avoid getting logical dumps from MacOS (LCTN=2) database servers.
With options D or E, if your production environment runs on Linux, take extra care to have a Linux-based database in your CI environment as well. This is necessary to catch queries using the wrong name case for databases/tables/views, which will work fine on Mac/Windows development but fail in production.
Gotchas with lower_case_table_names=2
Some effects of LCTN=2 are surprising, to say the least.
Views get mangled
Even though LCTN=2 is supposed to preserve the original casing of names, it actually forces view names to all-lowercase anyway. Worse, all references to table names in the body of the view (as returned by information_schema or SHOW CREATE VIEW) also get forced to all-lowercase. This means that dumps of view definitions from an LCTN=2 server are potentially not portable to an LCTN=0 server. Unfortunately, previous MySQL bug reports on this topic have been closed, repeatedly (and again).
In Skeema Premium CLI, this means you should avoid using skeema pull --update-views on a database server that uses LCTN=2.
The MySQL manual is misleading about InnoDB table names
Reading the manual, you would get the impression that LCTN=2 causes InnoDB table names to be forced to lowercase, but this is not entirely correct! The corresponding file names for the tables may be forced to lowercase, but table metadata (information_schema and SHOW CREATE TABLE) correctly retains original casing, as do logical dumps. This is a good thing, but nonetheless the manual should be clearer.
Other LCTN=2 quirks
Triggers: ordinarily, trigger names are case-sensitive, which affects statements like DROP TRIGGER and SHOW CREATE TRIGGER. This is true with both LCTN=0 and LCTN=1. Yet with LCTN=2, trigger names inexplicably become case-insensitive.
Metadata queries: In SHOW CREATE TABLE commands, as well as certain information_schema tables/columns, the result will return name-casing that just matches however it was supplied in the query or statement. (This also happens with LCTN=1, but is less problematic for apps and tools there, since they can safely query metadata using lowercase names always.)
SHOW CREATE VIEW in MariaDB: Ordinarily, this output omits schema name qualifiers when they match the currently-selected default database. However, with LCTN=2 in MariaDB, this behavior appears buggy: it depends on whether or not your USE statement expressed the database name in all-lowercase.
These oddities are mostly just minor annoyances for database automation engineers, but in combination they do justify some healthy skepticism about whether LCTN=2 can safely be relied upon. In any case, Skeema internally uses work-arounds for all of these issues.
As of v1.8, Skeema fully supports database servers with any lower_case_table_names setting and any operating system. Learn more about Skeema v1.8 in our release announcement.