mysql数据库报错1062_MySQL数据库常用报错解决方法

MySQL数据库常用报错解决方法

MySQL数据库常用报错解决方法

MySQL报错解决方法1)Warning: (3719, u"'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")

2)pymysql.err.InternalError: (1130, u"Host '10.0.0.5' is not allowed to connect to this MySQL server")

3) ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'

4) ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password:yes)

5) ERROR 1142 (42000): SELETE command denied to user 'root'@'localhost' for table 'user'

1)Warning: (3719, u"‘utf8’ is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")

报错如下：

解决方法： python 2.7+mysql:8.0

查看mysql字符集：mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

根据上述查询结果，修改sql语句；

修改前 = ‘CREATE DATABASE IF NOT EXISTS database_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;’

修改后 = ‘CREATE DATABASE IF NOT EXISTS database_name DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;’

2)pymysql.err.InternalError: (1130, u"Host ‘10.0.0.5’ is not allowed to connect to this MySQL server")

报错原因： 不容许本地机器连远程mysql服务；

解决步骤：

本机连接MySQL数据库：# mysql -uroot -p123456

操作mysql数据库mysql>use mysql;

mysql>select 'host' from user where user='root';

mysql>update user set host='%' where user='root';

mysql>flush privileges;

3) ERROR 1062 (23000): Duplicate entry ‘%-root’ for key ‘PRIMARY’

报错场景： 开启MySQL远程操作时，会报1062错误

[[email protected] ~]# mysql -uroot -p123456

mysql> update mysql.user set host='%' where user='root';

ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'

若此处报错，则不予理会,不予理会,不予理会. 重要的事说三遍

//接着 刷新权限 或者服务重启操作

mysql> flush privileges;

mysql> select host,user from mysql.user ;

+-----------+---------------+

| host | user |

+-----------+---------------+

| % | mysqld |

| % | root |

| localhost | mysql.session |

| localhost | mysql.sys |

| localhost | root |

+-----------+---------------+

5 rows in set (0.00 sec)

如上所示，root用户对应的是root-%，即可远程登陆了。

4) ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password:yes)

报错原因： 这类错误，一般情况是由于登陆密码错误导致的；

解决方法： 重置密码

跳过MySQL密码认证过程，方法为修改my.cnf文件，如下：

[[email protected] ~]# vim /etc/my.cnf

在文档内搜索mysqld定位到[mysqld]文本段：

在[mysqld]后面任意一行添加“skip-grant-tables”用来跳过密码验证的过程，如下：

[mysqld]

#

skip-grant-tables //跳过表的权限验证，用户可以执行增删改查

重启MySQL

[[email protected] ~]# service mysqld restart

Redirecting to /bin/systemctl restart mysqld.service

直接使用mysql进入mysql

[[email protected] ~]# mysql

mysql>

修改root的密码

mysql>update mysql.user set password=password("你的新密码") where user="root";

mysql>flust privileges；

mysql>quit

若出现ERROR 1054(42S22) Unknown column ‘password’ in ‘field list’，

错误原因为： 5.7版本下的mysql数据库下已经没有password这个字段了，password字段改成了authentication_string，则使用以下命令：

mysql>update mysql.user set authentication_string=password('123456') where user='root';

编辑my.cnf，恢复原来需要密码认证，即将skip-grant-tables删除

重启MySQL

[[email protected] ~]# service mysqld restart

Redirecting to /bin/systemctl restart mysqld.service

使用新修改的密码登陆MySQL，登陆成功，完整的操作流程如下：

[[email protected] ~]# mysql -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[[email protected] ~]# vim /etc/my.cnf

[[email protected] ~]# service mysqld restart

Redirecting to /bin/systemctl restart mysqld.service

[[email protected] ~]# mysql

mysql> update mysql.user set authentication_string=password('123456') where user='root';

Query OK, 0 rows affected, 1 warning (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 1

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> quit

Bye

[[email protected] ~]# vim /etc/my.cnf

[[email protected]~]# service mysqld restart

Redirecting to /bin/systemctl restart mysqld.service

[[email protected] ~]# mysql -uroot -p123456

5) ERROR 1142 (42000): SELETE command denied to user ‘root’@‘localhost’ for table ‘user’

报错场景： 登陆数据库，操作数据库(增删改查)时，报ERROR 1142 (42000)的错误；

错误定位： MySQL ERROR 1142 (42000)为mysql grants权限不足引起的问题

解决方法： 修改mysql.user表的权限

查看mysql所有用户信息的权限mysql> select * from mysql.user\G

*************************** 1. row ***************************

Host: localhost

User: root

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: Y

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

从以上可以看出，很多权限都是N，即表示没有相应的权限；

修改权限，根据需要选择mysql> update mysql.user set Select_priv='Y' where user='root';

mysql> update mysql.user set Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y' where user='root';

查看权限，all privileges是对root主机的所有用户执行最大的权限mysql> show grants;

+---------------------------------------------------------------------+

| Grants for [email protected] |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

2 rows in set (0.00 sec)

MySQL数据库常用报错解决方法相关教程