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

Today, I looked at a MySQL connection problem for my colleagues. It was interesting that there were two users, one with a normal connection and the other with a wrong connection.

The error log provided by the developer is as follows:

{resin-33} java.net.SocketException

{resin-33} MESSAGE: java.net.ConnectException: Connection timed out

. . .

{resin-33} java.net.SocketException: java.net.ConnectException: Connection timed out

{resin-33} at com.mysql.jdbc.StandardSocketFactory.connect (StandardSocketFactory.java:156)

{resin-33} at com.mysql.jdbc.MysqlIO. (MysqlIO.java:284)

{resin-33} at com.mysql.jdbc.Connection.createNewIO (Connection.java:2706)

{resin-33} at com.mysql.jdbc.Connection. (Connection.java:1485)

You can see that a timeout exception was thrown when connecting to the database, but there was no problem for them to use the telnet xxxx 3306 port, and obviously the direction of the problem seemed to be permissions.

Of course, I also saw a lot of warning messages in error.log. It seems that there is something wrong with him when parsing this IP message.

[Warning] IP address' 10.127.xxxx' could not be resolved: Temporary failure in name resolution

[Warning] IP address' 10.127.xxxx' could not be resolved: Temporary failure in name resolutio

So what was the operation like at that time? because this operation has been going on for a long time, let's simply repeat the problem. First of all, we use grants to create users, of course, we still recommend create user.

> grant usage on *. * to webadmin@'10.127.xxx.xxx' identified by 'testmysql.com'

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

At this time, you can view the results of show grants with the permission of usage, which is similar to the connect role in oracle, that is, at least you can connect to the database.

> show grants for webadmin@'10.127.xxx.xxx'

+-- +

| | Grants for webadmin@10.127.xxx.xxx |

+-- +

| | GRANT USAGE ON *. * TO 'webadmin'@'10.127.xxx.xxx' |

+-- +

1 row in set (0.00 sec), followed by access rights. For example, developers don't know which permission is appropriate, so say you give me all permissions, so DBA said yes, wrote down *. *, and then the problem came.

> GRANT ALL PRIVILEGES ON *. * TO 'webadmin'@'10.127.xxx.xxx'; uses show grants to check permissions and finds that the original usage permissions are gone.

> show grants for webadmin@'10.127.xxx.xxx'

+-- +

| | Grants for webadmin@10.127.xxx.xxx |

+-- +

| | GRANT ALL PRIVILEGES ON *. * TO 'webadmin'@'10.127.xxx.xxx' |

+-- +

Where is the permission of 1 row in set (0.00 sec)? I have done the same test in MySQL 5.5,5.7. I found that if the permission of *. * is used, the permission of usage will disappear. This problem is still related to some configurations. For the time being, no matching description is found in the bug list.

In fact, one kind of repair is to specify the database name, such as database test, when granting permissions, then use grants xxxxx on test.* to webadmin@'xxxx'.

Of course, I can simulate this problem, and it is much easier to solve it. My developer classmate provided me with this user's password. I made a simple backup and began to reconfigure permissions.

Create user, grant [privilege] to test.* to webadmin@'xxxx'

But soon received a message from the developer saying that the error in the program has changed, and now it is this mistake.

{main} Attempted reconnect 3 times. Giving up.

{main} at com.mysql.jdbc.Connection.createNewIO (Connection.java:2781)

{main} at com.mysql.jdbc.Connection. (Connection.java:1485)

{main} at com.mysql.jdbc.NonRegisteringDriver.connect (NonRegisteringDriver.java:266)

{main} at org.apache.commons.dbcp.DriverConnectionFactory.createConnection (DriverConnectionFactory.java:38)

This mistake made me look a little confused. I said I couldn't restart the Dafa, but it still didn't work after the restart, and finally I began to locate and found that it might be related to the password. So I reconfigured the permissions according to the encrypted string of the backup, and there was no problem. It shows that there is a problem with the password provided to me by the development classmate, but fortunately with the backup, this problem can continue to move forward in this situation of crossing the river by feeling the stones.

Personal Wechat official account, welcome to scan the code to follow

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.