MySQL User Created but Unable to Log In via Socket – ChatDBA Diagnosis and Solution

Problem

The user reported that after upgrading from MySQL 5.6 to 5.7, a newly created user sky1@% could not log in via the socket, receiving an Access denied error.

Experiment

1. Submit the problem to ChatDBA

We first fed the issue to ChatDBA to see its analysis.

ChatDBA suggested checking several possible causes:

Permission configuration issue : the user may not have socket login permission.

Database configuration issue

Operating‑system permission issue

Anonymous user interference

2. ChatDBA assists in troubleshooting

ChatDBA identified that the user sky1 could not log in and listed potential reasons, including the presence of anonymous users that might be matched first.

3. ChatDBA provides a solution

After inspection, it was found that the permissions were fine, but an anonymous user existed. Deleting the anonymous user resolved the login failure.

4. Experiment summary

Although the steps solved the immediate problem, the root cause was not fully explained. Further investigation showed that when no password is supplied, login succeeds, and the current user information is:

mysql> select current_user();
+------------------+
| current_user    |
+------------------+
| @localhost       |
+------------------+
1 row in set (0.00 sec)

mysql> select user();
+-------------------+
| user              |
+-------------------+
| sky1@localhost   |
+-------------------+
1 row in set (0.00 sec)

ChatDBA explained that the issue stemmed from MySQL's user‑matching rules:

Using sky1@% for socket login matches the host as localhost .

The mysql.user table contains two anonymous accounts (''@localhost and ''@centos76).

In MySQL 5.6, the anonymous ''@localhost entry is matched before the explicit sky1@% account, causing the access‑denied error because the anonymous account has no password.

MySQL 5.7 changed the matching algorithm so that socket connections no longer prioritize anonymous users; however, after an upgrade, the metadata was not refreshed, leaving the old behavior in place.

Therefore, removing the anonymous user fixes the problem.

Ask ChatGPT‑4o

We also submitted the same question to ChatGPT‑4o to compare the responses.