MySQL的bug备忘录

bug 1

Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 1")

原因：utf-8编码问题
解决方法：修改utf-8编码

# 查看编码
mysql> show variables like '%char%';
# 修改编码
set character_set_client = utf8;
set character_set_server = utf8;
set character_set_connection = utf8;
set character_set_database = utf8;
set character_set_results = utf8;
set collation_connection = utf8_general_ci;
set collation_database = utf8_general_ci;
set collation_server = utf8_general_ci;

重启MySQL服务。

bug 2

mysql.connector.errors.NotSupportedError) Authentication plugin 'caching_sha2_password' is not supported

原因：MySQL版本原因，身份验证机制从原来的 mysql_native_password 更改为 caching_sha2_password。
解决方法：

pip install MySQL-connector-python 

bug 3

Unknown or incorrect time zone: 'Asia/Shanghai'

原因：MySQL中mysql数据库中的表time_zone缺失
解决方法：
从https://dev.mysql.com/downloads/timezones.html下载POSIX standard的时区表，解压得到.sql文件，从MySQL导入：

use mysql;
source PATH\timezone_posix.sql

bug 4

修改密码时：

ERROR 1064 (42000): You have an corresponds to your MySQL server ord(`lihaiming1992`)' at line 1

解决方法：

SET PASSWORD = '123456'
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';