lower_case_table_names compatibility shift between Windows development and Linux production

Direct answer

Keep the Linux server at its default (lower_case_table_names=0) and make all DDL and queries use lowercase identifiers only. This works because Windows forces table names to lowercase (lower_case_table_names=1) and Linux will store exactly the case you give – if you always give lowercase, the names match on both platforms.

When a dump/reload is unavoidable

If you need mixed‑case table names or want the server to perform case‑insensitive lookups, you must change lower_case_table_names to 1 (or 2 on a case‑preserving filesystem) and then reload the data, because the variable can only be set at startup and changing it after InnoDB tablespaces are initialized makes existing tables inaccessible.

Likely explanation

On Windows the default value is 1, which forces MySQL to store table names in lowercase and to compare them case‑insensitively. On most Linux distributions the default is 0, which preserves the exact letter case of identifiers and performs case‑sensitive lookups. When a database created on Windows (lowercase file names) is moved to Linux, a query that uses the original mixed‑case name fails because Linux distinguishes MyTable from mytable.

Confirmed facts

• MySQL derives the effective lower_case_table_names from the startup option and the underlying filesystem: on case‑insensitive filesystems (Windows, macOS default) a setting of 0 is ignored and the server behaves as if 1; on case‑sensitive filesystems (most Linux) the setting is honored.
• Value 2 is available only on case‑sensitive, case‑preserving filesystems (e.g., ext4 with default options). It stores names as given but performs lookups in lowercase, providing case‑insensitive behavior without forcing lowercase storage.

Steps needed for this case

1. Check the current effective value on each server:

SHOW VARIABLES LIKE 'lower_case_table_names';

2. If both servers already show the same value (preferably 1 for uniform case‑insensitivity), no further action is required.
3. If Linux shows 0 and you cannot dump/reload:
   • Ensure all CREATE TABLE, ALTER TABLE, and DROP TABLE statements use lowercase identifiers.
   • Write application queries using lowercase table names (or normalize them to lowercase before sending).
   • Verify that no existing table name differs only in case; if such a pair exists, you must rename one of them to avoid duplication.
4. If you can afford a dump/reload and want uniform case‑insensitive behavior:
   • Set lower_case_table_names=1 in my.cnf (or my.ini) on both Windows and Linux.
   • Restart the MySQL service on each host.
   • Reload the logical dump.
5. If you consider using lower_case_table_names=2 on Linux ext4:
   • Confirm the filesystem preserves case (run touch test; touch TEST; ls – only one file should appear if case‑insensitive, two if case‑sensitive).
   • Ensure no two table names differ only in letter case; otherwise MySQL will treat them as the same object and reject the second creation.
   • Add lower_case_table_names=2 to my.cnf and restart MySQL.

Missing diagnostic detail

To decide whether a change is needed, you must know the current effective value of lower_case_table_names on both the Windows development and Linux production servers. If this information is unavailable, ask for the output of SHOW VARIABLES LIKE 'lower_case_table_names'; from each host.