Why can't I create more than one schema?

[aruku]

I'm using mysql:8.0, with MYSQL_DATABASE: pvet_production_db, and two scripts in docker-entrypoint-initdb.d: one that creates all the tables and inserts data (1_database.sql.gz) and another one that does some pseudoanonymization on the data (2_anonymize.sql.gz), and both work on the schema specified on the variable (the first file has a use statement, the second one does not but still works); up to here everything works as expected. Now I would like to add another schema, but for some reason, I can't: I added another file (0_test_database.sql.gz) which does the same as 1_database.sql.gz on a different schema, but it doesn't work. I also added 0_create_schemas.sql with just the creation statements for both schemas, but no luck. I can see these files being listed during the bootstrap of the container, but it is like nothing happens. From my testing, it looks like only the operations for the schema defined in the MYSQL_DATABASE happen.

I also added https://github.com/docker-library/postgres/issues/179#issuecomment-510133799 (replacing psql with mysql), and it is picked up but doesn't do anything either.

What am I missing?

[tianon]

Unfortunately, I'm not sure what the problem might be :sweat_smile:

Does the container have any useful logs when it fails? The best I can figure is that maybe the script failed, but there should be something in the logs. :eyes:

[aruku]

I have a few scripts that operate on two schemas. Only the scripts that use the schema defined in MYSQL_DATABASE work, the others do not. If I change the variable, the working scripts change too. Shouldn't all of them work no matter the schema on the variable?

There is nothing in /var/log/mysqld.log. The logs from the container start don't say anything about the success or failure of the scripts:

2023-07-12 09:04:47 2023-07-12 07:04:47+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/0_database.sql
2023-07-12 09:04:48 
2023-07-12 09:04:48 
2023-07-12 09:04:48 2023-07-12 07:04:48+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/1_database.sql.gz
2023-07-12 09:05:02 
2023-07-12 09:05:02 
2023-07-12 09:05:02 2023-07-12 07:05:02+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/2_anonymize.sql.gz
2023-07-12 09:05:02 
2023-07-12 09:05:02

Although those empty lines look suspicious...

[yosifkit]

Can you share some representative part of the sql files?

Just some guesses at debugging: If the separate scripts don't each start with a USE, then they will be using the database name defined in MYSQL_DATABASE since the script sets --database="$MYSQL_DATABASE" when processing each .sql file.

I did a quick test with the following docker run with this sql file and it would fail to start if I didn't have the CREATE DATABASE line.

$ docker run -it --rm  -e MYSQL_DATABASE=db1 -e MYSQL_ROOT_PASSWORD=12345 -v "$PWD/data/:/var/lib/mysql/" -v "$PWD/init/:/docker-entrypoint-initdb.d/" --user "$(id -u):$(id -g)" mysql:8

CREATE DATABASE IF NOT EXISTS db2;
USE db2;

CREATE TABLE Persons ( PersonID int, Name varchar(255) );
SELECT * FROM Persons;

[aruku]

Here: 0_database.sql

CREATE DATABASE  IF NOT EXISTS `pvet_test_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `pvet_test_db`;
-- MySQL dump 10.13  Distrib 8.0.33, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: pvet_test_db
-- ------------------------------------------------------
-- Server version	8.0.33

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

1_database.sql.gz

-- MySQL dump 10.13  Distrib 8.0.18, for Linux (x86_64)
--
-- Host: 127.0.0.1    Database: pvet_production_db
-- ------------------------------------------------------
-- Server version	8.0.18-google

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `pvet_production_db`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `pvet_production_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `pvet_production_db`;