VirtioFS causing inconsistent MySQL and MariaDB case-sensitivity glitches

[evanelias]

• [x] I have tried with the latest version of Docker Desktop
• [x] I have tried disabling enabled experimental features
• [x] 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)