Thursday, October 26, 2006

A Fork in the Code

I am waiting and seeing how this new MySQL Enterprise Edition shapes up and how it affects the Community Edition, the Community, and MySQL users as a whole, but everything I have heard about it so far, from official press releases, initial public feedback, and information given to me, I am very impressed with it. This move shows every sign of being well-planned, designed to work positively for all parties concerned, from MySQL staff to paying customers to community members. What I see is added value for paying customers, better reasons for purchasing the paid edition, a focusing of MySQL's resources to better develop the product as well as to devote support to their customers, and importantly, an enabling of the community to be even greater participants than before. Some things taken for granted by some community members such as frequent binary updates may cause some disappointment, but I view this as a small price to pay, and an encouragement to become more literate with the source code, more involved in the process. Something for nothing is an illusion, it is so important in the long run that the community puts back in as much or more as it gets out.

To echo the sentiments of others, it is an honor to be part of this community, to help it along as we are able. MySQL shows no signs of fading away despite naysayers along the way, and its continued existence and popularity attest to its success.

Thursday, October 12, 2006

I can't see my databases!

This common complaint uttered fairly frequently on the forums and some similar related issues appears to stem from some basic misunderstandings of the MySQL account structure. The most common culprit is the "anonymous guest" account set up by default coupled with an initial lack of knowledge of how to connect to MySQL. Personally I have no use for such accounts and remove them when I find them. Presumably the intent is to allow anonymous users minimal access to MySQL, where they are limited to using the `test` database which is also set up by default. Compounding this issue is the Command Line Interface start-menu shortcut created by the installer versions of the Windows packages, which connects users as 'root' automatically and oftentimes unbeknownst to them. Then when the user goes to connect via some other means and presents incorrect credentials out of ignorance, then there is a chance that this guest account may be inadvertantly invoked.

So there they are, connected to MySQL, yet nothing is right. All they can see is the `test` database, and for 5.0 or better, the `information_schema` database. This might also happen if, in order to connect from a remote node, an account such as 'root' is creating with the remote node's name, but not granted any actual privileges.

So therefore the first thing to do when this situation occurs is to execute the following statement:
SELECT user(), current_user();
to find out exactly how one is connected, particularly if there is any doubt. Then one could go back to a known method for connecting as 'root' or otherwise correct the credentials.

Wednesday, October 04, 2006

Phoenix MySQL Meetup

It looks like there's going to be a MySQL Meetup forming in the Phoenix area. A few of us are planning to get together next month for an inaugural meeting. There seems to be any number of people in the Valley of the Sun who are interested in MySQL or related topics, so it will be interesting to see if this meetup gets off the ground and attracts any sort of meaningful membership.

Tuesday, October 03, 2006

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.