How to change the default character set of mysql using docker-compose?

Question

When I save the string of Chinese, mysql rise the error "Exception Value:
(1366, "Incorrect string value: '\xE5\xB0\x8F\xE6\x98\x8E' for column 'name' at row 1")",I check the character of mysql,it show this:

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

And my docker-compose.yml is as fellow:

web:
    image: yetongxue/docker_test:1.2
    links:
      - "db"
    ports:
      - "8100:8000"
    volumes:
      - "/Users/yetongxue/docker_v/docker_test/media:/root/media"
    restart: always

db:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: qwerasdf
      MYSQL_DATABASE: docker_db
    restart: always
    volumes:
      - "/Users/yetongxue/docker_v/docker_test/db:/var/lib/mysql"

I know how to set the character of mysql with my.cnf,but how can I do this in the docker-compose.yml? Anybody know this? Thanks!

Thanks for asking this. It's silly that the image defaults to latin1! — rinogo, Oct 22 '21 at 19:15

score 101 · Accepted Answer · edited Feb 19 '19 at 14:46

101

You can either build your own mysql image where you modify my.cnf, or modify the command that starts mysql's daemon with --character-set-server=utf8mb4 and --collation-server=utf8_unicode_ci.

web:
    image: yetongxue/docker_test:1.2
    links:
      - "db"
    ports:
      - "8100:8000"
    volumes:
      - "/Users/yetongxue/docker_v/docker_test/media:/root/media"
    restart: always

db:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: qwerasdf
      MYSQL_DATABASE: docker_db
    restart: always
    volumes:
      - "/Users/yetongxue/docker_v/docker_test/db:/var/lib/mysql"
    command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci']

I recommend using utf8mb4, as it can store up to "4 bytes per multibyte character" (https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html)

edited Feb 19 '19 at 14:46

sourcerebels

5,140
1
32
52

answered May 25 '18 at 12:33

Javier Arias

2,329
3
15
26

1

[ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4' – The Godfather Dec 07 '18 at 21:23
try with character-set-server = utf8 – Javier Arias Dec 09 '18 at 20:36
4

For people getting here in the context of K8s here is what youe need to append to the `spec.containers`: `args: ["--character-set-server", "utf8mb4", "--collation-server", "utf8mb4_unicode_ci"]` – Leo Jul 22 '19 at 08:54
4

Great answer, even though the `mysqld` command is not necessary. If you use instead `command: ['--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci']`, it will also work. – Guillermo Barreiro Jan 15 '21 at 10:53
Why this does not work for `image: mariadb:10.8.3`? – Eugen Konkov Jun 15 '22 at 15:13

Dragonphy · Answer 2 · 2021-03-25T03:33:56.383

I read the GitHub issue as @Ziemowit Stolarczyk listed. All of them are not exactly correct. The GitHub answer is -e LANG=C.UTF-8. I use it in my docker-compose.yml, but mysql prints out like this:

mysql>  show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

It doesn't work for database and server. So I add a configuaration in docker-compose.yml as the following.

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

The whole yaml file is:

version: "3"
services:
  mysql:
    container_name: mysql
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: root
      LANG: C.UTF-8
    volumes:
      - ./mysql:/var/lib/mysql
    ports:
      - "3306:3306"
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

Here is the final output.

mysql>  show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

REFERENCE

score 6 · Answer 3 · answered Dec 05 '18 at 10:15

If you wanted to create your own docker image with the settings above, instead of specifying them in docker-compose, you can do so like:

FROM mysql:5.5.60

RUN ln -sf /usr/share/zoneinfo/Europe/London /etc/localtime

VOLUME ["/docker-entrypoint-initdb.d"]

CMD ["--character-set-server=utf8mb4", "--collation-server=utf8mb4_general_ci", "--skip-character-set-client-handshake"]

score 5 · Answer 4 · answered Mar 01 '21 at 09:33

The answer of @Javier Arias is great but still, some non-utf8 settings are left on my site like below.

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

It was causing some issues related to the utf8 characters displaying after docker-compose exec mysql bash and making queries.

After adding one ENVIRONMENT setting: LANG: C.UTF_8 I get:

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

So I think the final docker-compose.yml should be:

db:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: qwerasdf
      MYSQL_DATABASE: docker_db
      LANG: C.UTF_8  # this one should be added
    restart: always
    volumes:
      - "/Users/yetongxue/docker_v/docker_test/db:/var/lib/mysql"
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

You can read the whole thread related to this here https://github.com/docker-library/mysql/issues/131.

If I may, the var env of LANG should be C.UTF-8 (with dash) – wolfgunner Mar 02 '22 at 08:26 — wolfgunner, Mar 02 '22 at 08:26

score 2 · Answer 5 · answered Feb 09 '23 at 15:55

Most of answers are good, but probably the best docker-compose with right charsets and great structure I have found is here.

version: '2'
services:
  mysql:
    image: mysql:5.7
    ports:
      - '3306:3306'
    volumes:
      - ./sql-scripts/:/docker-entrypoint-initdb.d/
    command:
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_unicode_ci
      - --skip-character-set-client-handshake
    environment:
      MYSQL_ROOT_PASSWORD: pass
      MYSQL_DATABASE: test

The result:

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Also SQL (and .sh) files in the sql-scipts directory will be executed during startup (e.g. to import your existing dump at the first container start)

score 1 · Answer 6 · answered Aug 17 '17 at 07:53

1

The compose file is used for running containers (and can build images). You cannot customize the images inside the docker-compose file. You need to create a custom image that extends the mysql image. Create a Dockerfile:

FROM mysql:5.7
RUN <command to update my.cnf>

Then build this image:

docker build -t <image-name> .

Update compose file:

db:
    image: <image-name-specified-above>

answered Aug 17 '17 at 07:53

yamenk

46,736
10
93
87

Yes, this is a effective method. I have found the method I wanted just now,you can set in your docker-compose.yml like this: command: ['mysqld', '--character-set-server=utf8'] . Here is the example link: https://coding.net/u/twang2218/p/docker-lnmp/git/blob/master/docker-compose.yml – 叶同学 Aug 17 '17 at 08:24

score 1 · Answer 7 · answered Nov 29 '21 at 02:01

try with character-set-server = utf8

docker run --name mysql-server -t -e MYSQL_DATABASE="docker_db" -e MYSQL_USER="username" -e MYSQL_PASSWORD="qwerasdf" -e MYSQL_ROOT_PASSWORD="YjA0OTYwZDBiN2EwNWFjMTRjZGU3Yjcy" -d mysql --character-set-server=utf8 --collation-server=utf8_bin --default-authentication-plugin=mysql_native_password

score 1 · Answer 8 · answered Nov 06 '22 at 02:17

I tried to solve this by setting command in docker-compose.yml

command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci']

in a lot of variations, but it didn't work for me for any magic reason. My tables collations and collation of connection were still latin1_swedish_ci, although collation_server was utf8_unicode_ci.

I solved it by setting this options to mysql config file (my.cnf) directly and setting my.cnf as volume.

my.cnf:

[mysqld]
init_connect=‘SET collation_connection = utf8_unicode_ci’
character-set-server = utf8
collation-server = utf8_unicode_ci

[client]
default-character-set = utf8

docker-compose.yml:

version: '3.1'

services:

  db:
    image: mariadb
    restart: always
    ports:
      - 3306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=example
      - MYSQL_DATABASE=test
      - MYSQL_USER=testuser
      - MYSQL_PASSWORD=testpassword
    volumes:
      - ./my.cnf:/etc/mysql/my.cnf

p.s. my.cnf is placed near docker-compose.yml in this example. You can put it in another folder, but you should change path in volumes part

score 0 · Answer 9 · answered Oct 04 '21 at 06:35

0

In my case, the problem was that my SQL-dump file had been exported without UTF-8 encoding and I had to manually fix the dump to have the correct characters.

answered Oct 04 '21 at 06:35

Ilmari Kumpula

831
1
10
18

score 0 · Answer 10 · answered Jul 01 '23 at 19:47

0

This works for me:

db:
image: mysql:8.0
restart: always
environment:
  LANG: C.UTF-8
  MYSQL_ROOT_PASSWORD: mysecretpassword
  MYSQL_DATABASE: web_servicedb
  MYSQL_USER: myuser
  MYSQL_PASSWORD: mypassword
  MYSQL_INITDB_CHARSET: utf8mb4

LANG parameter makes the magic, also check this issue: https://github.com/docker-library/mysql/issues/131

answered Jul 01 '23 at 19:47

Steveen G. Mendoza

1

This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/34627793) – Yogendra Jul 06 '23 at 09:08

How to change the default character set of mysql using docker-compose?

10 Answers10

REFERENCE