Shulou(Shulou.com)06/01 Report--

Recently, I helped a client reset the root password of mysql 8.0.11 and encountered a strange problem.

Operating System Version: RHEL 7.6

Database version: mysql 8.0.11

According to the official mysql 8.0 documentation, mysql user passwords are limited and hardcoded, without specifying how long the password is.

Warning

The MySQL user name length limit is hardcoded in MySQL servers and clients, and trying to circumvent it by modifying the definitions of the tables in the mysql database does not work.

You should never alter the structure of tables in the mysql database in any manner whatsoever except by means of the procedure that is described in Section 2.11, "Upgrading MySQL". Attempting to redefine MySQL's system tables in any other fashion results in undefined and unsupported behavior. The server is free to ignore rows that become malformed as a result of such modifications.

In addition, mysql 8.0 no longer supports the password() function, so you can't change a user's password by updating the mysql.user table as mysql 5.x did.

However, when the root password of mysql 8.0 is reset, an error is encountered:

[mysql@mysql8~]$ mysql -uroot -p -S /home/mysql/mysql/tmp/mysql.sock

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

--update mysql.user Failed to reset user password

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

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,authentication_string from mysql.user;

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

| user | host | authentication_string |

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

| root | % | 123456 |

| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

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

5 rows in set (0.00 sec)

--password function is not supported by mysql8.0

mysql> update user set authentication_string =PASSWORD('123456') where user='root' and host='%' ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('

123456') where user='root' and host='%'' at line 1

mysql>

--Strangely enough, resetting the root password to 6 digits with the alter command is an error, but not a syntax error

mysql> alter user 'root'@'%' identified by '123456'; ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%' --However, increasing the root password length to 8 digits succeeded

mysql> ALTER USER root IDENTIFIED WITH mysql_native_password BY '123456789';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;

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

| user | host | authentication_string |

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

| root | % | *CC67043C7BCFF5EEA5566BD9B1F3C74FD9A5CF5D |

| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

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

5 rows in set (0.00 sec)

However, mysql 8.0 reset root password on my local centos 7.6 lacks no password length limit, and both libraries use the same parameter file.

There is no official explanation about the length of the user password, and there is no information about the length of the password on the Internet. It is very strange!

From " ITPUB blog," link: http://blog.itpub.net/29357786/viewspace-2704307/, if you need to reprint, please indicate the source, otherwise you will be investigated for legal responsibility.

Yantai Medical University Gastrointestinal Hospital http://www.ytwcbyy.com/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.