[ERROR] COLLATION ‘utf8_unicode_ci‘ is not valid for CHARACTER SET ‘latin1‘-阿里云开发者社区

[ERROR] COLLATION ‘utf8_unicode_ci‘ is not valid for CHARACTER SET ‘latin1‘

2023-05-31 230

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，集群版 2核4GB 100GB

云数据库 RDS MySQL，高可用版 2核4GB 50GB

简介： [ERROR] COLLATION ‘utf8_unicode_ci‘ is not valid for CHARACTER SET ‘latin1‘

错误来源是：

跟着b站的谷粒商城项目做，前面的视频中设置了数据库的字符集编码，但是后面自己发现了MySQL容器重启报错，不停的在重启

查看log信息可以使用命令：docker logs 容器id

错误如下：

2022-07-18 05:15:28+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2022-07-18 05:15:28+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
  command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.n6HlSOVhxC
  2022-07-18T05:15:28.495305Z 0 [ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
2022-07-18T05:15:28.496127Z 0 [ERROR] Aborting
2022-07-18 05:15:28+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2022-07-18 05:15:28+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
  command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.6KcBJxhiq2
  2022-07-18T05:15:28.922147Z 0 [ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
2022-07-18T05:15:28.923008Z 0 [ERROR] Aborting
2022-07-18 05:15:29+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2022-07-18 05:15:29+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
  command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.P12cpvKWXz
  2022-07-18T05:15:29.435644Z 0 [ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
2022-07-18T05:15:29.436512Z 0 [ERROR] Aborting
2022-07-18 05:15:30+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2022-07-18 05:15:30+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
  command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.6R8npalvaH
  2022-07-18T05:15:30.099778Z 0 [ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
2022-07-18T05:15:30.100634Z 0 [ERROR] Aborting
2022-07-18 05:15:31+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2022-07-18 05:15:31+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
  command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.r9xLrd1ydi
  2022-07-18T05:15:31.162485Z 0 [ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
2022-07-18T05:15:31.163422Z 0 [ERROR] Aborting
2022-07-18 05:15:33+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2022-07-18 05:15:33+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
  command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.WCNU9W4Cge
  2022-07-18T05:15:33.112496Z 0 [ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
2022-07-18T05:15:33.113309Z 0 [ERROR] Aborting
2022-07-18 05:15:36+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2022-07-18 05:15:36+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
  command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.QD3th2xOxE
  2022-07-18T05:15:36.562999Z 0 [ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
2022-07-18T05:15:36.563865Z 0 [ERROR] Aborting
2022-07-18 05:15:43+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2022-07-18 05:15:43+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
  command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.tgaMZHREXm
  2022-07-18T05:15:43.288878Z 0 [ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
2022-07-18T05:15:43.289861Z 0 [ERROR] Aborting
2022-07-18 05:15:56+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2022-07-18 05:15:56+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
  command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.Hty5diwdpa
  2022-07-18T05:15:56.530008Z 0 [ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
2022-07-18T05:15:56.530843Z 0 [ERROR] Aborting
2022-07-18 05:16:22+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.36-1debian10 started.
2022-07-18 05:16:22+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
  command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.foO50I6KAZ
  2022-07-18T05:16:22.437806Z 0 [ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
2022-07-18T05:16:22.438653Z 0 [ERROR] Aborting

也就是说，前面我在

vi /mydata/mysql/conf/my.cnf

设置的如下字符集编码内容是有问题

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

发现删除my.cnf文件MySQL容器又可以正常启动

然后把my.cnf文件做出如下修改

[client]
default_character_set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation_server = utf8_general_ci
character_set_server = utf8
skip-character-set-client-handshake
skip-name-resolve

重启MySQL容器

docker restart mysql

进入容器内部查看

docker exec -it mysql /bin/bash

修改字符集编码成功，退出容器重启MySQL也不存在报错

默认MySQL5.7的字符集编码是：

show variables like 'character\_set\_%';

注意

docker安装完MySQL并run出容器后，建议请先修改完字符集编码后再新建mysql库-表-插数据

[ERROR] COLLATION ‘utf8_unicode_ci‘ is not valid for CHARACTER SET ‘latin1‘

热门文章

最新文章

相关电子书