The Ultimate Guide to High Availability with MariaDB

Should MariaDB Docker Containers really be using MacOS setting for lower_case_table_names when hosted in Windows?

You are viewing an old version of this question. View the current version here.

TL;DR: When hosted on Windows and the /var/lib/mysql folder is mapped to the host file system, MariaDB sets the config option 'lower_case_table_names' to 2, which is the MacOS setting. This setting breaks the ability to download sql dumps from phpMyAdmin and import them into Mariadb, when Docker is hosted on Windows. Can/should mariadb set that value at run time to 1, based on the host OS?

Funny story... exported a DB through phpMyAdmin on cPanel. Server is a Linux VMS.

Running MariaDB inside a docker container (mariadb:lts image). Docker happens to be hosted on Windows. Stick the sql dump from phpMyAdmin into the docker-entrypoint-initdb.d folder, so that when the MariaDB container is created, it will spring to life with a copy of the database from the server, simple enough, right?

Nope, continual problems with setting autoincrement values. MariaDB reports that they can't be set on a field that is not a key. But the DB is configured properly, it's working on the Linux server after all. What's going on??

The phpMyAdmin dump essentially recreates the tables with three statements:

CREATE TABLE...

ALTER TABLE... (to setup the primary key)

ALTER TABLE... (to setup the auto increment)

Interestingly, using mysqldump manually on the command line of the server, and using the --single_transaction flag, results in a dump that runs no problem when starting up the MariaDB docker image. The --single_transaction flag causes all the table data to be crunched down into the CREATE TABLE statement.

OK, so what the heck is going on? Lots of convoluted debugging and experimenting later....

Everything works fine if the table name in question is all lower case. But if the table name in question contains an upper case character, the ALTER TABLE statements aren't working. Why oh why??

Running SELECT @@lower_case_table_names on DB in the Docker container reveals that the setting is: 2

2 is the MacOS setting, according to the docs ( https://mariadb.com/kb/en/identifier-case-sensitivity/ )

And 2 seems to be the worst of all worlds (if you're in Windows): names are stored as declared (so with the upper case characters intact) but compared as lowercase... which is guaranteed to never work??

Now, there's more magic than this going on somewhere, because I found that if you run the ALTER TABLE statements completely separately from the CREATE TABLE statements, lo and behold they do seem to work. But if you want to run everything together as part of one set of SQL commands, no way. And that is kind of important when people all over the internet are getting single .sql dumps from cPanel every day, and expecting them to be portable.

Anyway, one solution that works: whip up a .cnf file for MariaDB at start up time, and set lower_case_table_names to 1 (the Windows setting) and then everything is stored lowercase and not compared in a case sensitive manner. The SQL dump straight from phpMyAdmin now works as expected. Yahoo!

So... question: how did the mariadb docker image end up with the MacOS setting for lower_case_table_names? (Spoiler: it's Windows fault, see below in the comments.)

Clearly, this is a setting that is meant to match the OS in normal operation, and probably should match the host OS when operating inside Docker? Setting a .cnf file yourself as I've done is a solid workaround, but it would be much better if it defaulted to the preferred Windows setting at startup instead of guessing that the MacOS setting is the right one.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.