How to make Docker MySQL use unicode (utf-8) with initialization scripts?

Question

I want to insert some initial data into my db, some of which uses characters not available with latin1, so I switch to unicode (utf-8) with the following command:

command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

This works fine for when I add rows after my container has been started, however it doesn't affect the rows from my init script. Is there a way to fix this?

For reference in my docker-compose.yml file I have the following setup:

  mysql:
    image: mysql:latest
    environment:
      MYSQL_ROOT_PASSWORD: [REDACTED]
      MYSQL_DATABASE: [REDACTED]
      MYSQL_USER: [REDACTED]
      MYSQL_PASSWORD: [REDACTED]
    volumes:
      - mysql_data:/var/lib/mysql
      - ./mysql-init:/docker-entrypoint-initdb.d/:ro
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    networks:
      - mynetwork
    ports:
      - "3307:3306"

An example insert statement from my init script:

INSERT INTO vodovod_ulice (naziv)
VALUES ('Ulica Vuka Karadžića'); -- can't render `ž` or `ć`

Answer 1

I suspect that the initialisation script is not being properly run because you have an existing volume. MySQL checks if /var/lib/mysql already exists and will only run those scripts if it doesn't.

So first do

docker-compose down -v

Then use docker volume ls to check that the associated volume is gone. Now do docker-compose up.

├── docker-compose.yml
├── mysql-init
│   └── create-table.sql

🗎 docker-compose.yml

version: "3"

services:
  mysql:
    image: mysql:latest
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: password
    volumes:
      - mysql_data:/var/lib/mysql
      - ./mysql-init:/docker-entrypoint-initdb.d/:ro
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

volumes:
  mysql_data:

🗎 mysql-init/create-table.sql An initialisation script that creates table using the code provided and then inserts a record with non-ASCII characters.

CREATE TABLE `vodovod_ulice` (
  `id` int NOT NULL AUTO_INCREMENT,
  `naziv` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `naziv` (`naziv`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into vodovod_ulice (naziv) values ('Ulica Vuka Karadžića');

Check the content of the table to confirm that the characters are represented correctly.