Handshaking Problems with Net::MySQL and MySQL 4.1

Album Cover: Black Holes and Revelations

"Come ride with me through the veins of history. I'll show you a god who falls asleep on the job."
Muse / Knights of Cydonia

Posted on May 28, 2007 8:50 PM in Programming
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

I'm working on a Perl script that needs to be able to connect to a MySQL 4.1 server, so rather than going the DBI route, I figured I'd give Net::MySQL a try, since it's one of the options listed at MySQL's site.

After getting Net::MySQL and a couple of its prerequisite packages installed, I started testing my script, only to find that it was acting like everything was working fine when really it wasn't actually inserting data into my database. Initially I expected the usual things like a bad username/password combination, etc., but then it got to a point where I really had no clue what was going on.

To make things worse, the Net::MySQL API doesn't really take error handling into consideration. Sure, there's the is_error() method, but all it does is tell you that an error occurred, rather than what the error actually was. Eventually I found the debug parameter of the constructor, which, when set to true, prints out the packets of every database exchange. That led me to the following:

Net::MySQL::_execute_command():
3B 00 00 00 03 49 4E 53 45 52 54 20 49 4E 54 4F ;....INSERT.INTO
20 73 74 61 74 69 6F 6E 73 20 56 41 4C 55 45 53 .stations.VALUES
20 28 32 32 2C 20 27 48 42 4F 27 2C 20 27 48 6F .(22,.'HBO',.'Ho
6D 65 20 42 6F 78 20 4F 66 66 69 63 65 27 29 me.Box.Office')

Net::MySQL::_execute_command():
16 00 00 03 FF 13 04 23 30 38 53 30 31 42 61 64 ........08S01Bad
20 68 61 6E 64 73 68 61 6B 65 .handshake

If you look really hard, you'll see a "Bad handshake" message in there. Seeing that, I did some Googling and found that the problem may be related to the fact that MySQL changed its authentication protocol in version 4.1. However, to make things more confusing, the changes listed for the latest version of Net::MySQL actually list the authentication protocol change as one of the things addressed:

Support 4.1 authenticaiton [sic] protocol.

So right now I'm left pretty puzzled and thinking I may just need to go the DBI route after all. Hopefully my next post on the subject has more to do with the script I'm working on and less to do with annoying MySQL authentication problems.

Update: No such luck. It turns out my web host is running Perl 5.6.0 and DBI isn't compatible with that anymore. Maybe this is the excuse I've been looking for to finally learn Python.

Another Update: I guess it just isn't my night. My web host is running Python 2.2.2 and according to Writing MySQL Scripts with Python DB-API, that just won't do:

Verify that your version of Python is 2.3.4 or later, and that the MySQLdb module is installed.

Oh well, I picked up some high-level knowledge about Python in the process (thanks to Dive Into Python) and now, at least, my only remaining option is blatantly clear: PHP. I know that works.

Comments

Ri on September 13, 2007 at 10:48 AM:

Post Comments

If you feel like commenting on the above item, use the form below. Your email address will be used for personal contact reasons only, and will not be shown on this website.

Name:

Email Address:

Website:

Comments:

Check this box if you hate spam.