By Evan Elias | Updated September 30, 2025
When running database servers on multiple operating systems, data and schema portability can be a challenge, due to differences in identifier case sensitivity. The lower_case_table_names server variable in MySQL and MariaDB can help, but it is often misunderstood. In this post, we’ll provide in-depth guidance on this tricky subject, and show how Skeema’s linter 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. Portability is always tricky in this situation: by default, directory paths and file names are case-sensitive on Linux, but case-insensitive on MacOS and Windows.
But why does that matter for databases? 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 distinct tables – one of which probably doesn’t exist, resulting in a query error.
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.
Even if you’re not sure whether your DB server OS is case-sensitive, MySQL/MariaDB auto-detects this upon first initialization, and selects a reasonable OS-dependent default for its identifier casing behaviors. However, if your various environments use a mix of different operating systems for databases, this default behavior may actually cause painful headaches with portability.
The lower_case_table_names variable
To override the default name-casing behaviors, MySQL and MariaDB have a global variable called lower_case_table_names, which we’ll abbreviate as “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 dumps 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. This affects data dumps (e.g. mysqldump, mydumper, mysqlsh dump methods) 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. Typically, global variables are either “dynamic” (changeable at runtime) or “static” (requires a server restart to change). LCTN is a rare case that is neither of these: you can only override it upon the very first initialization of your database server. After that, it’s effectively frozen. Once you have any table data, the only way to safely change lower_case_table_names is to perform a logical dump, wipe the server, re-init the server, 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 best to worst:
Option A: Enforce an all-lowercase naming policy
If all of your databases, tables, and views already only have all-lowercase names, then your data and schema dumps are completely portable across OSes, regardless of LCTN setting. Configure Skeema’s linter with lint-name-case=error to enforce the strict all-lowercase naming policy, and be sure to run it in your CI environment to catch any non-lowercase names in new tables moving forwards.
Option B: Converge on a uniform OS for database servers
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. If using Docker Desktop, use the default Docker volumes for storage, instead of bind-mounts.
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 C: Use LCTN=1 everywhere
This essentially achieves the same result as option A. However, since LCTN=1 isn’t the default on Linux or MacOS, it’s easy to forget when setting up a new environment. And as previously mentioned, if you have existing environments with LCTN=0 or 2, it is extremely difficult to switch.
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 various quirks and edge-case bugs; more on that in the next section.
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.
Gotchas with lower_case_table_names=2
Some effects of LCTN=2 are “surprising” – and it’s not the good kind of surprise.
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. Sadly, previous MySQL bug reports on this topic have been closed, time after time.
In Skeema, this means you should avoid using skeema pull --update-views on a database server that uses LCTN=2. And unfortunately, there isn’t a client-side workaround that we can use to side-step the problem.
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 could be clearer.
Other LCTN=2 metadata quirks
Skeema is extensively tested on multiple operating systems and all values of LCTN, and internally it uses work-arounds for the quirks in this section, but other tools in the MySQL/MariaDB ecosystem could break in odd ways.
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.
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, strangely this behavior is only enabled if your USE statement expressed the schema name in all-lowercase.
Metadata query results: 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. But in some other situations, the result is returned in lowercase, even though the server metadata was supposed to retain the original casing.
Proc/func metadata in recent MariaDB: Server releases from May 2025 onwards fixed the previous metadata issue in some scenarios, but that fix inadvertently introduced a new bug affecting metadata for routines.
In combination, these oddities give an impression of LCTN=2 being somewhat brittle and under-tested on the server side. For important databases, we strongly recommend using either LCTN=0 or LCTN=1 instead.
Need assistance with a tricky lower_case_table_names situation? In addition to developing Skeema, we provide expert consulting services for MySQL and MariaDB. Reach out to enquire about a hands-on consulting engagement or advisory retainer.