In a development Docker setup, I needed to upgrade the MySQL database from version 5.6 to version 8. The data was stored as a Docker volume in a data directory. After the update, I encountered the following error:
my_db.mysql | 2024-08-16T09:40:21.463770Z 2 [ERROR] [MY-010520] [Server] Invalid (old?) table or database name 'mysql.sock'
my_db.mysql | 2024-08-16T09:40:21.468287Z 2 [ERROR] [MY-010784] [Server] Failed to open dir /var/lib/mysql/mysql.sock
Apparently, MySQL 8 treats every file or directory in the data directory as a database table, whereas in the previous version, MySQL 5, only directories were recognized as database tables.
As the the changelog of MySql 8 states:
Because the data dictionary provides information about database objects, the server no longer checks directory names in the data directory to find databases. Consequently, the --ignore-db-dir option and ignore_db_dirs system variable are extraneous and have been removed. Update system configurations and application programs accordingly.
As a result, all files in the data directory volume, in addition to the directories, are mistakenly recognized as database tables–for example, the mysql.sock file, which is created by Docker.
The solution is to separate the actual database tables from the metadata files. We need to move all table files to a dedicated data directory and configure MySQL to use this new location.
Then let’s move all database data to dedicated data dir:
mkdir data
mv * ./data/And specify the dataDir in my.cnf:datadir=/var/lib/mysql/data
Now MySql will only look for database table files in this directory and Docker still can write to the main MySql directory.
And, most importantly, the database will start up again.