CODEWITHSUNDEEP
mysqldockerdocker-compose
叶同学
叶同学

Reputation: 903

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

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!

Upvotes: 48

Views: 63755

Answers (11)

Konstantin XFlash Stratigenas
Konstantin XFlash Stratigenas

Reputation: 607

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.

Upvotes: 0

Steveen G. Mendoza
Steveen G. Mendoza

Reputation: 11

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

Upvotes: 1

bora89
bora89

Reputation: 3609

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)

Upvotes: 6

andrey
andrey

Reputation: 71

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

Upvotes: 2

HoseiniPeyrov
HoseiniPeyrov

Reputation: 41

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

Upvotes: 1

Ilmari Kumpula
Ilmari Kumpula

Reputation: 1465

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.

Upvotes: 0

Dragonphy
Dragonphy

Reputation: 388

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

Upvotes: 17

Ziemowit Stolarczyk
Ziemowit Stolarczyk

Reputation: 1039

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.

Upvotes: 6

Javier Arias
Javier Arias

Reputation: 2478

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)

Upvotes: 106

Javier Arias
Javier Arias

Reputation: 2478

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"]

Upvotes: 6

yamenk
yamenk

Reputation: 51778

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>

Upvotes: 1

Related Questions