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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: [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

325e2a6e8ffd4041b6bdf5b357676559.png

进入容器内部查看

docker exec -it mysql /bin/bash

f3ca8c56da41412a9d16a7ca7e266f7a.png

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

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

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


e0b1b182478b4ed7ae453341e045bcef.png

注意

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

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
18天前
|
并行计算 Python
Python错误笔记(一):CUDA initialization: CUDA unknown error - this may be due to an incorrectly set up env
这篇文章讨论了CUDA初始化时出现的未知错误及其解决方案,包括重启系统和安装nvidia-modprobe。
74 0
|
2月前
|
Windows
网页制作- Code Page,Character Set,语种对应列表
网页制作- Code Page,Character Set,语种对应列表
|
6月前
|
资源调度 前端开发
编译第三方前端项目时候出现Syntax Error: TypeError: Cannot set properties of undefined (setting ‘parent‘)
编译第三方前端项目时候出现Syntax Error: TypeError: Cannot set properties of undefined (setting ‘parent‘)
440 0
|
6月前
|
API Android开发 开发者
failed to set system property error code: 0x18
failed to set system property error code: 0x18
255 1
|
6月前
|
druid Java
Error attempting to get column ‘createTime‘ from result set的异常
Error attempting to get column ‘createTime‘ from result set的异常
401 0
|
6月前
|
Python
gyp ERR! stack Error: Can‘t find Python executable “python“, you can set the PYTHON env variable.
gyp ERR! stack Error: Can‘t find Python executable “python“, you can set the PYTHON env variable.
162 1
|
6月前
|
XML Java 数据库连接
mybatis和mybatiplus中Error attempting to get column ‘xx‘ from result set
mybatis和mybatiplus中Error attempting to get column ‘xx‘ from result set
156 0
|
11月前
|
分布式计算 Hadoop 大数据
|
分布式计算 Java Hadoop
Error: JAVA_HOME is incorrectly set. Please update C:\hadoop-2.5.1\conf\hadoop-env.cmd ‘
Error: JAVA_HOME is incorrectly set. Please update C:\hadoop-2.5.1\conf\hadoop-env.cmd ‘