VirtioFS causing inconsistent MySQL and MariaDB case-sensitivity glitches

[evanelias]

• I have tried with the latest version of Docker Desktop
• I have tried disabling enabled experimental features
• I have uploaded Diagnostics
• Diagnostics ID: A81596AF-9730-413D-8EA4-27C266ABB1E7/20230426222413

Expected behavior

Bind-mounting a directory to /var/lib/mysql should result in a fully-functional database

Actual behavior

If VirtioFS is enabled, and the database server container uses a bind mount for its data directory, random errors will result if any database (schema) name contains at least one uppercase character.

In this situation, the db server sometimes randomly/inconsistently returns filesystem-related errors for SQL statements that should not return an error: for example, SHOW TABLES may return "Unknown database", or a perfectly valid CREATE TABLE may return "File not found (Errcode: 2 - No such file or directory)". In both cases, the error message references a lowercased version of the mixed-case database name.

Information

The problem only happens when using VirtioFS. This might be related to #6243, but I didn't see anything in there specifically about case-sensitivity / case-insensitivity oddities, so I am not certain.

I can reproduce this with the official images for MySQL 5.5, 5.6, and 5.7 from https://hub.docker.com/_/mysql . Likewise for all modern versions of MariaDB from https://hub.docker.com/_/mariadb . It does not seem to be reproducible with MySQL 8 though, perhaps because MySQL 8 completely replaced the data dictionary implementation used in prior versions.

Creating several tables very rapidly will increase the chance of reproduction, but it's still very random and inconsistent.

It's probably somehow related to lower_case_table_names=2, which is the mode automatically enabled when /var/lib/mysql is a case-insensitive MacOS directory, but that mode normally does not cause these inconsistent/random errors. I have been using MySQL for 20 years in a wide range of environments (including Facebook's db team), and have deep experience with lower_case_table_names (see my blog post here), and have not encountered this specific glitchy behavior before.

For background: in MySQL, each database (schema) is stored in a separate directory, and using lower_case_table_names=2 is likely forcing some of the directory path lookups to all-lowercase; but for some reason, with VirtioFS, there's inconsistent behavior as to whether the downcased version of a directory name works properly.

• macOS Version: MacOS Ventura 13.3.1
• Intel chip or Apple chip: Intel
• Docker Desktop Version: Docker Desktop 4.18.0 (104112)

Output of /Applications/Docker.app/Contents/MacOS/com.docker.diagnose check

[2023-04-26T22:42:57.289745000Z][com.docker.diagnose][I] set path configuration to OnHost
Starting diagnostics

[PASS] DD0027: is there available disk space on the host?
[PASS] DD0028: is there available VM disk space?
[PASS] DD0018: does the host support virtualization?
[PASS] DD0001: is the application running?
[PASS] DD0017: can a VM be started?
[PASS] DD0016: is the LinuxKit VM running?
[PASS] DD0011: are the LinuxKit services running?
[PASS] DD0004: is the Docker engine running?
[PASS] DD0015: are the binary symlinks installed?
[PASS] DD0031: does the Docker API work?
[PASS] DD0013: is the $PATH ok?
[PASS] DD0003: is the Docker CLI working?
[PASS] DD0038: is the connection to Docker working?
[PASS] DD0014: are the backend processes running?
[PASS] DD0007: is the backend responding?
[PASS] DD0008: is the native API responding?
[PASS] DD0009: is the vpnkit API responding?
[PASS] DD0010: is the Docker API proxy responding?
[SKIP] DD0030: is the image access management authorized?
[PASS] DD0033: does the host have Internet access?
[PASS] DD0018: does the host support virtualization?
[PASS] DD0001: is the application running?
[PASS] DD0017: can a VM be started?
[PASS] DD0016: is the LinuxKit VM running?
[PASS] DD0011: are the LinuxKit services running?
[PASS] DD0004: is the Docker engine running?
[PASS] DD0015: are the binary symlinks installed?
[PASS] DD0031: does the Docker API work?
[PASS] DD0032: do Docker networks overlap with host IPs?
No fatal errors detected.

Steps to reproduce the behavior

In one terminal tab, create a new directory that will be used for the bind mount, and start a MySQL 5.7 container:

$ mkdir /tmp/testnamecase

$ docker run --rm -it --name casingtest -p 3308:3306/tcp -e MYSQL_ROOT_PASSWORD=password -v /tmp/testnamecase:/var/lib/mysql mysql:5.7

Once the server has finished initializing, run the MySQL client in another terminal tab. Create a mixed-case database name, USE the database, and then run various statements. In this case, some of the SHOW TABLES generated an error, as did one of the CREATE TABLE statements; but it's somewhat random between runs.

$ docker exec -it casingtest mysql -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database MixedCase;
Query OK, 1 row affected (0.01 sec)

mysql> use MixedCase;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
ERROR 1049 (42000): Unknown database 'mixedcase'
mysql> show tables;
+---------------------+
| Tables_in_mixedcase |
+---------------------+
| t1                  |
+---------------------+
1 row in set (0.00 sec)

mysql> create table t2 (id int); create table t3 (id int); create table t4 (id int);
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> create table t5 (id int); create table t6 (id int); create table t7 (id int);
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

ERROR 29 (HY000): File './mixedcase/' not found (Errcode: 2 - No such file or directory)
mysql> show tables;
ERROR 1049 (42000): Unknown database 'mixedcase'
mysql> show tables;
+---------------------+
| Tables_in_mixedcase |
+---------------------+
| t1                  |
| t2                  |
| t3                  |
| t4                  |
| t5                  |
| t6                  |
+---------------------+
6 rows in set (0.00 sec)

[fredericdalleau]

@evanelias, thanks for the test case!
The first question that came to my mind is : do you know if your host mac OS filesystem is case sensitive or case insensitive? Linux is case sensitive, and a whole class of problems might arise when mapped on top of a case insensitive file system. It would be interesting to test mouting a case sensitive file system.
The second question is : With the test case you provided, how likely is it to reproduce, for example if I create 10 or 100 tables, am I sure to reproduce ?
Thanks

[evanelias]

On the host, it's a normal case-insensitive APFS filesystem. With gRPC FUSE, a case-insensitive APFS filesystem directory can be bind-mounted as MySQL's data directory, and everything works perfectly even though the container is Linux and otherwise case-sensitive. This is a fairly common way to run MySQL or MariaDB on Macs for dev and local testing. MySQL auto-detects the scenario of a case-insensitive Mac data directory, and automatically enables its lower_case_table_names=2 compatibility setting. It's only with VirtioFS that this starts causing random errors.

With the test case I provided, the SHOW TABLES errors seem to happen pretty often, i.e. running SHOW TABLES gives me an error 50% of the time (if the previously-selected database via USE had an upper-case character in its name). The CREATE TABLE errors are more sporadic and definitely seem to depend on how rapidly the tables are created. Executing three CREATE TABLE in a row via the MySQL CLI seems to reproduce maybe 25-50% of the time on my machine, yielding an error for one of the three CREATEs.

However, executing five CREATE TABLE concurrently via a Golang program reproduces it 100% of the time on my machine. That's how I first encountered this -- my software (https://github.com/skeema/skeema) has integration testing logic to cover MySQL's lower_case_table_names=2 mode, which relies on bind-mounting a Mac directory to MySQL's data dir. These tests previously worked flawlessly, but I found it started giving these strange errors after switching to VirtioFS.

Thanks!

[evanelias]

I apparently typo'ed github.com in the link in my previous reply, please don't click the original link as it is a domain squatter. My apologies, I've edited to be the correct link now.

[maximbashevoy]

This still seems to be an issue (Mac M1, Sonoma 14.4.1, Docker Desktop 4.29.0, engine 26.0.0, compose 2.26.1-desktop.1) except switching away from VirtioFS no longer helps (if /var/lib/mysql is not mounted to host, everything works without any issues).

Creating a database with upper case letters in the name (testABC) from mysql dump results in (but not always!):

ERROR 29 (HY000) at line 245 in file: 'db.sql': File './testabc/' not found (Errcode: 2 - No such file or directory)

Also, dropping database testABC will not always work on first try (as root from mysql console inside the mysql container). Multiple attempts seem to eventually succeed.

Importing a table with upper case letters (from a mysqldump) results in (but again, not always on the first table with upper case in its name, sometimes it manages to create 5 tables, sometimes 20):

ERROR 1146 (42S02) at line 341 in file: 'db.sql': Table 'testabc.test_ABC' doesn't exist

The only workarounds are to use MySQL 8 or set lower_case_table_names=1 (yes, 1, for Windows). In the latter case though all tables will import, but will all have lower case names.

[BenceSzalai]

I've just encountered the same issue.

Docker Desktop: 4.34.0 (165256)
Docker Engine: 27.2.0
OS: macOS Sonoma 14.6.1
DB Image: mariadb:10.5.8
Filesystem of the bind mount for ~/db:/var/lib/mysql: Case insensitive APFS.

Exact issue: I have a database with uppercase letters in the name. When I try to execute SQL statement SHOW FULL TABLES FROM `MyDataBase`; I get: [42000][1049] Unknown database 'mydatabase'.

The actual database is stored on my drive as ~/db/MyDataBase with uppercase letters in the name.

Switching to gRPC FUSE fixes the issue.