Troubleshooting MySQL Connection Attempts

MySQL connection issues are pretty common because of how it handles user accounts. A lot of people get used to the idea that a username is unique and represents ONE account, and they expect the same thing on MySQL.

One Username, Multiple Accounts

However, MySQL accounts are identified by a username -AND- the location that the connection is coming from. So a user account in MySQL like this:

'testuser'@'localhost'

…means “The account with the username testuser when connecting from localhost.”

You might have another user account with the same username but a different host:

'testuser'@'1.2.3.4'

…which means “The account with the username testuser when connecting from the IP address 1.2.3.4.”

When you try to connect to MySQL, the MySQL server will first try to find the correct user account based on the username you’ve provided and the location you’re connecting from. If it doesn’t find an account, then it’ll just fail.

If it DOES find an account, then it will attempt to authenticate against that account using the password you provided.

Again, it doesn’t matter that the usernames of two accounts are the same – that location part makes them COMPLETELY different. So testuser@localhost and testuser@1.2.3.4 can have different passwords and different permissions, for example.

Location Types

There are 3 types of locations that can be used in an account name:

1. A hostname like “foo.com” or “localhost”
2. An IP address like “1.2.3.4” or “127.0.0.1”
3. The wildcard character “%” that means “a connection from ANY location”

So you could legitimately have all of these accounts:

'testuser'@'localhost'
'testuser'@'foobar.com'
'testuser'@'127.0.0.1'
'testuser'@'4.5.6.7'
'testuser'@'%'

Now, MySQL has an order of preference. Let’s say you DID have all of the above accounts and you were trying to connect to the database from the server itself, so the hostname is localhost and the IP is 127.0.0.1. So your connection attempt technically matches 3 accounts here:

'testuser'@'localhost'
'testuser'@'127.0.0.1'
'testuser'@'%'

Which one does MySQL chooose?

Order of Preference
The order of preference used when checking for an account is:

1. IP address
2. Hostname
3. Wildcard

So MySQL will first look for:

'testuser'@'127.0.0.1'

…and if it finds an account matching that, it stops and will try to authenticate against it. If it doesn’t find that account, then it will next look for:

'testuser'@'localhost'

…and if it finds an account matching that, it stops and will try to authenticate against it. If it doesn’t find that account, then it will next look for:

'testuser'@'%'

…and if it finds an account matching that, it stops and will try to authenticate against it. If it doesn’t find that account, then it fails.

Name Resolution

There is a small catch here. By default, MySQL will try to perform name resolution during the connection attempt. What this means is that it can already see your IP address (in the above example, IP address 127.0.0.1), but it doesn’t YET know that 127.0.0.1 resolves to the hostname “localhost”.

Without doing the name resolution, MySQL can only look for accounts that have the IP address or the wildcard. However, by default, it WILL attempt to do that name resolution so that it can check for user accounts with the hostname, too.

So if name resolution was turned off, then an account like testuser@localhost or testuser@foo.com would never be checked. The name resolution is controlled by a configuration setting in your my.ini (Windows) or my.cnf (Linux) file called:

skip_name_resolve

If skip_name_resolve is set to 1, then MySQL will -not- do the name resolution, so all your accounts have to be identified by either IPs or the wildcard.

If it’s commented out or set to 0, then MySQL -will- do the name resolution.

Many servers turn off name resolution simply for performance and simplicity reasons.

What Do You Do?

Troubleshooting a connection attempt for someone does require you to be able to connect with an admin account, so if you can’t even get to that, then you might need to talk to your server administrator about resetting the password for MySQL’s root account, but that should only be a last resort if no admins at all can get in.

Now, if you’re having trouble connecting to MySQL and you get this error message:

Access denied for user 'foobar'@'1.2.3.4" (using password: YES)

…then you need to log into the server as an admin and go check out the mysql.user table and pull all records where User=’foobar’ (or whatever username is in the message). Then check to see if skip-name-resolve is enabled (you’ll use the _ instead of – in the name here):

SHOW VARIABLES LIKE 'skip_name_resolve';

Based on that, check the results from the mysql.user table and figure out which user account matches the connection attempt.

If one doesn’t exist, you might need to create an account. However, if one DOES exist, then you might just simply have the wrong password.