mysql遇到的报错和解决方法_mysql 4058-优快云博客

1.在修改mysql的root用户密码为123456时，直接用

update user set authentication_string=‘123456’ where user=‘root’;语句是不行的，修改成功但是无法登录。应该用

update user set authentication_string=‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ where user=‘root’;

update user set plugin=“mysql_native_password”;

修改成功，使用mysql -uroot -p123456可以登录

2.执行show databases;报错ERROR 1449 (HY000): The user specified as a definer (‘mysql.infoschema’@‘localhost’) does not exist，这时候查看是否有mysql.infoschema用户mysql中执行

use mysql;

select User,authentication_string,Password_require_current from user;如果已经存在这个用户但还是报错，那就用delete语句

delete from user where user=‘mysql.infoschema’;删掉这个用户

重新创建这个用户

CREATE USER ‘mysql.infoschema’@‘localhost’ IDENTIFIED BY ‘Aa#12345678’;

GRANT SELECT ON . TO mysql.infoschema@localhost;

3.在第2条中重新创建用户报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements，这个是密码复杂度不符合要求

show global variables like ‘%validate_password%’;查看密码复杂度，validate_password_policy数值0、1、2或相应的符号值LOW、MEDIUM、STRONG，不同的等级密码要求不一样

如果是MEDIUM，即上面sql中Aa#12345678就是符合的

如果查询到的密码复杂度是空的（Empty set (0.01 sec），应该是没有安装密码复杂读插件

install plugin validate_password soname ‘validate_password.so’;安装插件

4.在使用DB_1000w.sql时，可能会报错：ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATAin its declaration and binary logging is enabled(you might want to use the less safe log_bin_trust_function_creators variable)

执行：

set global log_bin_trust_function_creators = 1;