Should MariaDB Docker Containers really be using MacOS setting for lower_case_table_names?

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

TL;DR: Despite being set to use Linux(?) (see OS/Arch here: the mariadb:lts image from Docker Hub has the config option 'lower_case_table_names' set to 2, which is the MacOS setting. And this setting breaks the ability to download sql dumps from phpMyAdmin and import them into mariadb in Docker. Can/should mariadb set that value at run time 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, but I don't think the host OS matters (much). 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:


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 ( )

And 2 seems to be the worst of all worlds: 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 official mariadb:lts docker image end up with the MacOS setting for lower_case_table_names? Is this something you have control over in the creation of the image? Or is it somehow magically deduced by Docker when it makes the magic Docker things happen? But then why does it think the container OS is MacOS for the mariadb:lts image?

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 wow was this confusing to figure out, and it's no doubt tripped up others, since it's pretty deep in the mariadb setup, and not obvious at all that it's set in a strange way.


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.