Not Able To Connect

There has a been and continues to be a lot of discussion on the forums for people who are not able to connect to MySQL for one reason or another. Some aspects of this issue have been dealt with, particularly Bill Karwin's article which he published on the MySQLForge Wiki, which I have linked into any number of MySQL Forum threads by now.

I should like to conduct a broader discussion, as to some users, not able to connect does not mean strictly that an initial handshake of any sort could be established with the server, but includes a far broader scope of problems ranging from the server not even being installed let alone running, scripting languages, e.g. PHP, being misconfigured, invalid credentials, or even false assumptions about such things as access from remote computers. When the user is not able to connect, they are oftentimes unable to express the manner in which they are unable to connect, or unaware that there are many ways in which one is unable to connect, and so stating that they are unable to connect and asking for assistance without further explanation is a common occurence.

Client misconfiguration, particularly PHP, is a major issue, but again a Forge article adequately covers, for PHP at least, the possible causes and solutions, and the necessary steps, to enable MySQL support properly. I was able to configure PHP prior to this article being available, but I have prior knowledge and experience, not to mention the patience to sift through a lot of extraneous material to find the specific instructions needed.

Assuming that the client is properly configured, then the problem boils down to three possibilities:

1. The client can't connect
2. The client's host is not allowed to connect
3. Access is denied

Bill's article adequately discusses this first possibility, and the latter two are in reality two sides of the same coin. At this point it is necessary to explain to people the fact that the MySQL user authorization model treats users connecting from different client hosts to be completely different users, with, potentially, completely different passwords and privileges.

Somewhere along in here, also, someone is bound to discover the anonymous guest account which is created by default. This seems to create more problems and confusion, to be more confusing than helpful, since it allows the user to connect as any otherwise unknown username, from the local host, but not to actually accomplish anything except with the test database. Usually the first things I do are to remove this account and the test database.

So the only default account of any real substance is the root account, and this account, again, is only for access from the local host. People have these concepts that the remote host should be added to some list, or some option should be enabled, that all accounts suddenly work from all hosts, and so forth. Personally I should like to see the ability to copy all authorization records for one host to another, or copy one username to another. I know it's easy enough to do this manually using SQL statements, but showing a first-time user how to do this is not the easiest thing in the world.

Another issue in here is when MySQL sees the connecting node as something other than what the user thinks of it as. MySQL either sees the remote host as an IP address, or some name derived either from the hosts file or some other mechanism. Sometimes MySQL sees a domain name, particularly in the case of a Windows client connecting to a server running on Windows. I have a machine that boots either to Linux or to Windows, and MySQL on a Windows Server system sees it differently depending on how it's booted necessitating multiple accounts. That's why it's important to pay attention to the complete error message text. Oftentimes a user will post on the forums only the number or a short phrase, leaving insufficient information to give a correct diagnosis and solution.

Finally there is the scenario where the user did not present the credentials he thought he did. Particularly in PHP it's possible to badly code a call to mysql_connect, such that it doesn't connect, maybe bad credentials, but then not check for errors. Then the next call will attempt to connect using some default set of credentials which is probably wrong. Proper error checking is the answer here, but sometimes incomplete error checking is even more confusing since the user might see the default credentials mentioned, and have no clue where they originated.