[ERROR] COLLATION ‘utf8_unicode_ci‘ is not valid for CHARACTER SET ‘latin1‘
发布时间
阅读量：
阅读量
这段文本描述了一个关于MySQL字符集配置的问题。以下是总结后的摘要：
错误原因：CollATION 'utf8unicodeci'与CHARACTER SET latin1不兼容
解决方法：
- 删除/mydata/mysql/conf/my.cnf文件
- 修改my.cnf中的配置：
ini [client] defaultcharacterset=utf8 [mysql] default-character-set=utf8 [mysqld] collationserver = utf8general_ci charactersetserver = utf8 skip-character-set-client-handshake skip-name-resolve- 重新启动MySQL容器后不再出现报错
默认情况下，默认字符集为latin1：
`bash
mysql> show variables like 'characterset%';
-> character_set collation' -> "latin1"
修改完成后可以安全地新建mysql库、表和插入数据
[ERROR] COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'
错误来源是：
参与了b站‘谷粒商城’项目的开发，
在视频教程中配置了数据库字符集编码设置，
后来发现MySQL容器频繁重启，
导致持续性的重启问题。
通过以下命令可以查看日志：
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这个目录中进行过如下字符集编码设置的操作存在配置错误
具体来说，在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修改内容
[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安装并运行出容器后，请您务必先配置好字符集编码设置后再进行数据库库-表-数据插入操作。
全部评论 (0)
还没有任何评论哟~