Q&A
./infra/mysql/Dockerfile の中身はどうなってますか？
MySQLの文字化けが直らない
タイトル通りです。
Docker内のDBに接続して、seederを実行しているのですが、MySQL内を覗くと文字化けが起こってしまいます。
お力添えいただけると幸いです。
DBのVariable_name
以下3点が正しいため、設定は問題ないと認識しております。
- character_set_database
- character_set_server
- character_set_system
+--------------------------+--------------------------------+ | 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 | utf8mb3 | | character_sets_dir | /usr/share/mysql-8.0/charsets/ | +--------------------------+--------------------------------+
mysql> select * from users; +----+-----------------------------------------+-----------+------------+------------------------------+---------------------+---------------------+ | id | company | last_name | first_name | email | created_at | updated_at | +----+-----------------------------------------+-----------+------------+------------------------------+---------------------+---------------------+ | 1 | ????????????????????????????? | ?? ? | ?? ?? | hiroshi.kato@example.net | 2021-05-04 06:07:19 | 2021-05-04 06:07:19 | | 2 | ???????????????????????????? | ?? ??? | ?? ?? | tsuda.kana@example.com | 2021-05-04 06:07:19 | 2021-05-04 06:07:19 | | 3 | ??????????????????????????????????? | ?? ?? | ?? ?? | tsuda.yosuke@example.net | 2021-05-04 06:07:19 | 2021-05-04 06:07:19 | | 4 | ???????????????????????????????????? | ?? ?? | ?? ?? | wakamatsu.hanako@example.org | 2021-05-04 06:07:19 | 2021-05-04 06:07:19 | | 5 | ?????????????????????????????? | ?? ?? | ?? ? | eyamamoto@example.net | 2021-05-04 06:07:19 | 2021-05-04 06:07:19 | | 6 | ???????????????????? | ?? ? | ? ?? | koizumi.satomi@example.net | 2021-05-04 06:07:19 | 2021-05-04 06:07:19 | | 7 | ??????????????????? | ?? ?? | ?? ?? | kazuya03@example.org | 2021-05-04 06:07:19 | 2021-05-04 06:07:19 | | 8 | ????????????????????????? | ??? ?? | ?? ?? | thirokawa@example.net | 2021-05-04 06:07:19 | 2021-05-04 06:07:19 | | 9 | ?????????????????????????????? | ?? ?? | ?? ?? | sayuri.koizumi@example.com | 2021-05-04 06:07:19 | 2021-05-04 06:07:19 | | 10 | ??????????????????????????????????????? | ?? ??? | ?? ?? | tsuda.jun@example.net | 2021-05-04 06:07:19 | 2021-05-04 06:07:19 | +----+-----------------------------------------+-----------+------------+------------------------------+---------------------+---------------------+
コード一覧
該当しそうな箇所の、以下3つのプログラムを添付させていただきます。
- /app/docker-compose.yml
- /app/infra/mysql/my.cnf
- /app/backend/config/database.php
// 格納場所：/app/docker-compose.yml version: "3.9" services: app: build: ./infra/php volumes: - ./backend:/work web: image: nginx:1.20-alpine ports: - 10080:80 volumes: - ./backend:/work - ./infra/nginx/default.conf:/etc/nginx/conf.d/default.conf working_dir: /work db: build: ./infra/mysql volumes: - db-store:/var/lib/mysql volumes: db-store:
# 格納場所：/app/infra/mysql/my.cnf [mysqld] # character set / collation character_set_client = utf8mb4 collation_server = utf8mb4_unicode_ci # timezone default-time-zone = SYSTEM log_timestamps = SYSTEM # Error Log log-error = mysql-error.log # Slow Query Log slow_query_log = 1 slow_query_log_file = mysql-slow.log long_query_time = 1.0 log_queries_not_using_indexes = 0 # General Log general_log = 1 general_log_file = mysql-general.log [mysql] default-character-set = utf8mb4 [client] default-character-set = utf8mb4
// 格納場所：/app/backend/config/database.php 'mysql' => [ 'driver' => 'mysql', 'url' => env('DATABASE_URL'), 'host' => env('DB_HOST', '127.0.0.1'), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE', 'forge'), 'username' => env('DB_USERNAME', 'forge'), 'password' => env('DB_PASSWORD', ''), 'unix_socket' => env('DB_SOCKET', ''), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'prefix_indexes' => true, 'strict' => true, 'engine' => null, 'options' => extension_loaded('pdo_mysql') ? array_filter([ PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'), ]) : [],
試したこと
１．Dockerを再起動してseederを実行
docker-compose down --rmi all --volumes --remove-orphans docker-compose up docker-compose exec app bash php artisan migrate:fresh --seed
2．DBを削除
drop databaseで削除し、その後1を再度行いました。
factory と seederも一応
# UserFactory return [ 'company' => $this->faker->realText(rand(15, 40)), 'last_name' => $this->faker->name(), 'first_name' => $this->faker->name(), 'email' => $this->faker->unique()->safeEmail(), 'created_at' => now(), 'updated_at' => now(), ];
// UserSeeder public function run() { User::factory()->count(10)->create(); }
お力添えいただけると幸いです。
どうぞ、よろしくお願いいたします。
回答1件
0
ベストアンサー
多分、私が書いた記事を参考にしていただいたと思うんですが、
mysql/mysql-server は /etc/mysql/conf.d/ 配下の設定ファイルを読み込まないようでした。
mysql/mysql-server:8.0 のデフォルトの /etc/my.cnf
$ docker run -it mysql/mysql-server:8.0 cat /etc/my.cnf [Entrypoint] MySQL Docker Image 8.0.24-1.2.2-server # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock secure-file-priv=/var/lib/mysql-files user=mysql pid-file=/var/run/mysqld/mysqld.pid
!include ディレクティブが設定されてないため、COPYした my.cnf が読み込まれません。
./infra/mysql/Dockerfile
FROM mysql/mysql-server:8.0 # ... COPY ./my.cnf /etc/my.cnf RUN chmod 644 /etc/my.cnf
./infra/mysql/my.cnf
[mysqld] # default skip-host-cache skip-name-resolve datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock secure-file-priv=/var/lib/mysql-files user=mysql pid-file=/var/run/mysqld/mysqld.pid # character set / collation character_set_server = utf8mb4 collation_server = utf8mb4_ja_0900_as_cs_ks # timezone default-time-zone = SYSTEM log_timestamps = SYSTEM # Error Log log-error = mysql-error.log # Slow Query Log slow_query_log = 1 slow_query_log_file = mysql-slow.log long_query_time = 1.0 log_queries_not_using_indexes = 0 # General Log general_log = 1 general_log_file = mysql-general.log [mysql] default-character-set = utf8mb4 [client] default-character-set = utf8mb4
MySQL8.0.2以降のバージョンを利用している場合は、utf8mb4_ja_0900_as_cs_ks の文字列照合順序を利用するのがお勧めです。
https://mysqlserverteam.com/mysql-8-0-kana-sensitive-collation-for-japanese-ja_jp/
確認
$ docker-compose down -v $ docker-compose build db $ docker-compose up -d db $ docker-compose exec db bash # mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} ${MYSQL_DATABASE} > show variables like '%char%'; +--------------------------+--------------------------------+ | 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 | utf8mb3 | | character_sets_dir | /usr/share/mysql-8.0/charsets/ | +--------------------------+--------------------------------+
こんな感じになってればokなはずです。
投稿2021/05/04 08:36
総合スコア888
具体的なコードまで、誠に有難うございます！
無事、解決しました。
他の参考記事だと、以下の3つのみ変更できれば良いと記載がありましたが、全て変えた方が良いんですね。
character_set_database
character_set_server
character_set_system
解決してよかったです！
文字コードは揃えておいて損はないと思います！
あなたの回答
tips
プレビュー
