48

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!

Improve this question
1
  • Thanks for asking this. It's silly that the image defaults to latin1! Commented Oct 22, 2021 at 19:15

11 Answers 11

Reset to default
106

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

[ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'
try with character-set-server = utf8
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"]
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.
Why this does not work for image: mariadb:10.8.3?
18

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

Improve this answer

Comments

6

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"]
Improve this answer

1 Comment

Why do you need skip-character-set-client-handshake?
6

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.

Improve this answer

1 Comment

If I may, the var env of LANG should be C.UTF-8 (with dash)
6

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)

Improve this answer

Comments

3

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

Improve this answer

Comments

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>
Improve this answer

1 Comment

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: coding.net/u/twang2218/p/docker-lnmp/git/blob/master/…
1

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
Improve this answer

Comments

1

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

Improve this answer

1 Comment

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review
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.

Improve this answer

Comments

0

According to the MariaDB documentation, you can specify the character set in more detail. For more information, refer to the documentation here: https://mariadb.com/kb/en/server-system-variables/#character_set_collations

Use the following Bash script as an example for configuring the character set in MariaDB on Docker.

# install MariaDB
docker run -d --name mariadb-1 \
  --restart=always \
  mariadb:latest \
  --collation-server=utf8mb4_unicode_ci \
  --character-set-server=utf8mb4 \
  --skip-character-set-client-handshake \
 
docker exec -it mariadb-1 /bin/bash

# Here comes the Magic
echo 'character_set_collations=utf8mb3=utf8mb3_uca1400_ai_ci, ucs2=ucs2_uca1400_ai_ci, utf8mb4=utf8mb4_unicode_ci, utf16=utf16_uca1400_ai_ci, utf32=utf32_uca1400_ai_ci' >> /etc/mysql/my.cnf

# Optional: Install the Nano text editor for easier configuration
apt-get update
apt-get --yes install nano

This example demonstrates how to set the character set configuration. You can adjust other settings according to your specific requirements.

Remember to restart the container after making these changes.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.