Connecting to MySQL

davies has asked for the wisdom of the Perl Monks concerning the following question:

Brethren and sistren,

Having no other language available, I wrote about 6K lines of VB6 to take some accounting spreadsheets, put their contents into a MySQL database and manipulate the database to produce reports. The CodeReaper, aka Bill Door, announced not long ago that VB6 will no longer be supported, and that future versions of MessWare may cause VB6 programmes to crash. I took this as a wonderful excuse to teach myself Perl, so that I can port the programme to a more portable language. Lest this sound like an overdose of hubris, this was the first application I ever wrote using either VB6 or MySQL, and it works. At least, it crashes no more frequently than I can convincingly blame on MessWare.

The first thing the VB programme does is try to connect to a server. This, therefore, is the first thing I have tried to teach myself in Perl. Sad to relate, I have come a cropper, and would appreciate some help.

I can connect to the server using the following code:

use strict;
use DBI;
use warnings;
use diagnostics;

my $dsn       = 'MySQL';
my $user      = 'JHD';
my $password  = 'JHD';

my $dbh       = DBI->connect("DBI:ODBC:$dsn",$user,$password) ||
                die "Got error $DBI::errstr when connecting to $dsn\n"
+;

exit (0);
[download]

However, in the various manual pages I have read, ISTR reading that connecting via ODBC is deprecated. I understand that TIMTOWTDI, so if I should give up and use whatever works, please let me know. Attempting to to it the BEST way, I have tried several variants on the following code:

use strict;
use DBI;
use warnings;
use diagnostics;

my $sqlhost  = 'localhost';
my $sqlbase  = 'debloat';
my $user     = 'JHD';
my $password = 'JHD';

my $dbh = DBI->connect("DBI:mysql:database=$sqlbase;host=$sqlhost",
                       $user, $password, {RaiseError => 1});

exit (0);
[download]

This crashes as follows:

C:\Perl\bin>perl -w practice\sqltest.pl
Uncaught exception from user code:
DBI connect('database=debloat;host=localhost;port=3306',
'JHD',...) failed:
Client does not support authentication protocol 
requested by server; consider
upgrading MySQL client at practice\sqltest.pl line 11

DBI::__ANON__('undef', 'undef') called at 
C:/Perl/site/lib/DBI.pm line 664
DBI::connect('DBI', 
'DBI:mysql:database=debloat;host=localhost;port=3306
', 'JHD', 'JHD', 'HASH(0x15d4f58)')
called at practice\sqltest.pl line 11
[download]

Full – possibly too full, but please flame gently – details of my configuration appear at the end. There are two things that concern me in this report. One is the “upgrade” advice. I believe that I am using the latest version, but I am far from sure that I have understood everything I need to. My concern here is that the manual says things like

I recommend using the win32clients package for installing DBD::mysql under Win32, available for download on www.tcx.se. The following steps have been required for me: - The current Perl versions (5.6, as of this writing) do have a problem with detecting the C libraries. I recommend to apply the following patch:

Since I am using v5.8.something, as detailed below, I am scared that the documentation (which came with the installation) is out of date, and that my time R’ing TFM is wasted because I’m R’ing T wrong FM. Any pointers in this regard would be greatly appreciated. I am also concerned that my installation of MySQL may be to blame. The system started with 4.0.12, but I have recently upgraded to 4.1.7, which uses different passwords. I think I have applied the patches correctly, and the start of the DESCRIBE USER output is as follows:

Field	Type	Null	Key	Default	Extra
Host	varchar(60)		PRI
User	varchar(16)		PRI
Password	varchar(41)
Select_priv	enum('N','Y')			N

My second concern is the __ANON__ call in the crash report. This makes me fear that I have formatted the userid and password worngly. Wrnogly. Incorrectly. Again, pointers would be appreciated, especially those that indicate how I should interpret the output and work out for myself what the problem is.

As a prat^wnovice, I get only five votes per day, which may not be enough to ++ helpful responses as quickly as they deserve. It’s only a number, though, isn’t it? :-)

TIA,

John Davies

System configuration
Dell Latitude 2.4 GHz, 512Mb RAM, 20Gb Hard disc
Losedows 2000
File system is FAT
ActiveState Perl 5.8
ppm output:
ppm> query *
Querying target 1 (ActivePerl 5.8.4.810)

1.	ActivePerl-DocTools	0.04	Perl extension for Documentation TOC Gene~
2.	ActiveState-Relocat~	0.03	Relocate a Perl installation
3.	ActiveState-Rx	0.60	Regular Expression Debugger
4.	Archive-Tar	1.07	Manipulates TAR archives
5.	Compress-Zlib	1.22	Interface to zlib compression library
6.	Data-Dump	1.01	Pretty printing of data structures
7.	DBD-mysql	2.9003	MySQL driver for the Perl5 Database Inter~
8.	DBD-ODBC	1.11	ODBC Driver for DBI
9.	DBI	1.47	Database independent interface for Perl
10.	Digest-HMAC	1.01	Keyed-Hashing for Message Authentication
11.	Digest-MD2	2.03	Perl interface to the MD2 Algorithm
12.	Digest-MD4	1.1	Perl interface to the MD4 Algorithm
13.	Digest-SHA1	2.06	Perl interface to the SHA-1 Algorithm
14.	File-CounterFile	1.01	Persistent counter class
15.	Font-AFM	1.18	Interface to Adobe Font Metrics files
16.	HTML-Parser	3.34	HTML parser class
17.	HTML-Tagset	3.03	Data tables useful in parsing HTML
18.	HTML-Tree	3.18	build and scan parse-trees of HTML
19.	IO-Zlib	1.01	IO:: style interface to Compress::Zlib
20.	libwin32	0.21	A collection of extensions that aims to p~
21.	libwww-perl	5.75	Library for WWW access in Perl
22.	MD5	2.02	Perl interface to the MD5 Algorithm (obso~
23.	PPM3	3.1	Perl Package Manager: locate, install, up~
24.	SOAP-Lite	0.55	Library for Simple Object Access Protocol~
25.	Tk	800.024	A Graphical User Interface Toolkit
26.	URI	1.27	Uniform Resource Identifiers (absolute an~
27.	Win32-AuthenticateU~	0.02	Win32 User authentication for domains
28.	Win32-OLE	0.1403	OLE Automation extensions
29.	XML-Parser	2.34	A Perl module for parsing XML documents
30.	XML-Simple	2.09	Easy API to read/write XML (esp config fi~

The MySQL monitor launches with:
Your MySQL connection id is 18 to server version: 4.1.7-nt

Please let me know if further configuration data would help.

Comment on Connecting to MySQL Select or Download Code

Replies are listed 'Best First'.
Re: Connecting to MySQL by jbrugger (Parson) on Apr 14, 2005 at 12:55 UTC
When you use a visual tool like MySQL Administator (from MySQL.com), go to 'Startup Variables', Security, and place a mark at 'Use old passwords' (Use old password encryotion method (needed for 4.0 and older clients), restart the server (i had to restart windows, not just the service), and it should work. (or place this in your my.ini) `#Use old password encryption method (needed for 4.0 and older clients) +. old-passwords` [download] "We all agree on the necessity of compromise. We just can't agree on when it's necessary to compromise." - Larry Wall.	[reply] [d/l]
Re: Connecting to MySQL by polettix (Vicar) on Apr 14, 2005 at 12:59 UTC
I've used ActiveState Perl 5.8.something with MySql in Win32 without problems, but I worked with an installation of MySQL that came with EasyPHP-1.7, that is MySQL v. 4.0.15. From the error message about the authentication protocol, I guess that there's been some change in MySQL that probably forces you to recompile your client applications. In this case, I'm talking of the DBD::mysql module, of course: chances are that it's been compiled against an older version of libmysqlclient.whatever that didn't support the new authentication scheme. On a pragmatical point of view, I'd suggest any of the following: On a very lazy ground, try to downgrade your MySQL and see if you've success. If you're happy, you're done :) The laziest solution would be installing EasyPHP 1.7 (beware that the latest release is 1.8). Peruse MySQL documentation about this (possibly) new authentication scheme. If I guessed well about it, I also guess that there should be a configuration to set some deprecated way of sticking to the "older" auth scheme, given the fact that 4.1.7 isn't so distant from 4.0.15 IMHO; Recompile the DBD::mysql module by your own - here I can't help you, I've used only precompiled modules when I needed them in Win32, but I don't do Win very much. Flavio (perl -e "print(scalar(reverse('ti.xittelop@oivalf')))") Don't fool yourself.	[reply]
Re: Connecting to MySQL by RazorbladeBidet (Friar) on Apr 14, 2005 at 12:57 UTC
Can you try starting a trace? I've worked with DBI to MySql before but unfortunately cannot replicate your situation. Perhaps someone with a working example can help more, but let's see what we can find out from the trace. -------------- "But what of all those sweet words you spoke in private?" "Oh that's just what we call pillow talk, baby, that's all."	[reply]
Re^2: Connecting to MySQL by davies (Monsignor) on Apr 15, 2005 at 13:29 UTC
OK, the code now looks like this: `use strict; use DBI; use warnings; use diagnostics; my $sqlhost = 'localhost'; my $sqlbase = 'debloat'; my $user = 'JHD'; my $password = 'JHD'; DBI->trace(5); my $dbh = DBI->connect( "DBI:mysql:database=$sqlbase;host=$sqlhost;port=3306", $user, $password); exit (0);` [download] I don't know if trace level 5 is appropriate. If you want me to re-run at another level, just say the word. I removed the "RaiseError" setting as it didn't seem to help, and put the port in because the traces were showing the port as 0, and I just hoped that it might be something to do with that. I don't think it is, but it does seem to have cut down slightly on the length of the trace output. The output is: DBI 1.48-ithread default trace level set to 0x0/5 (pid 1448) -> DBI->connect(DBI:mysql:database=debloat;host=localhost;port=330 +6, JHD, ) -> DBI->install_driver(mysql) for MSWin32 perl=5.008004 pid=1448 r +uid=0 euid =0 install_driver: DBD::mysql version 2.9004 loaded from C:/Perl/s +ite/lib/DB D/mysql.pm New DBI::dr (for DBD::mysql::dr, parent=, id=) dbih_setup_handle(DBI::dr=HASH(0x1b61a24)=>DBI::dr=HASH(0x1c63324) +, DBD::mys ql::dr, 0, Null!) dbih_make_com(Null!, 0, DBD::mysql::dr, 84, 0) thr#15d4064 dbih_setup_attrib(DBI::dr=HASH(0x1c63324), Err, Null!) SCALAR(0x1c +4a3cc) (al ready defined) dbih_setup_attrib(DBI::dr=HASH(0x1c63324), State, Null!) SCALAR(0x +1b64d78) ( already defined) dbih_setup_attrib(DBI::dr=HASH(0x1c63324), Errstr, Null!) SCALAR(0 +x1b19f9c) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x1c63324), TraceLevel, Null!) 0 (a +lready def ined) dbih_setup_attrib(DBI::dr=HASH(0x1c63324), FetchHashKeyName, Null! +) 'NAME' ( already defined) <- install_driver= DBI::dr=HASH(0x1b61a24) !! warn: 0 CLEARED by call to connect method -> connect for DBD::mysql::dr (DBI::dr=HASH(0x1b61a24)~0x1c63324 ' +database=d ebloat;host=localhost;port=3306' 'JHD' **** HASH(0x1c65bf8)) thr#15d40 +64 New DBI::db (for DBD::mysql::db, parent=DBI::dr=HASH(0x1c63324), i +d=HASH(0x1 bfc33c)) dbih_setup_handle(DBI::db=HASH(0x1c4a294)=>DBI::db=HASH(0x1b1abcc) +, DBD::mys ql::db, 1b5d96c, HASH(0x1bfc33c)) dbih_make_com(DBI::dr=HASH(0x1c63324), 1c373d4, DBD::mysql::db, 60 +0, 0) thr# 15d4064 dbih_setup_attrib(DBI::db=HASH(0x1b1abcc), Err, DBI::dr=HASH(0x1c6 +3324)) SCA LAR(0x1b65120) (already defined) dbih_setup_attrib(DBI::db=HASH(0x1b1abcc), State, DBI::dr=HASH(0x1 +c63324)) S CALAR(0x1b6603c) (already defined) dbih_setup_attrib(DBI::db=HASH(0x1b1abcc), Errstr, DBI::dr=HASH(0x +1c63324)) SCALAR(0x1b6600c) (already defined) dbih_setup_attrib(DBI::db=HASH(0x1b1abcc), TraceLevel, DBI::dr=HAS +H(0x1c6332 4)) 0 (already defined) dbih_setup_attrib(DBI::db=HASH(0x1b1abcc), FetchHashKeyName, DBI:: +dr=HASH(0x 1c63324)) 'NAME' (already defined) dbih_setup_attrib(DBI::db=HASH(0x1b1abcc), HandleSetErr, DBI::dr=H +ASH(0x1c63 324)) undef (not defined) dbih_setup_attrib(DBI::db=HASH(0x1b1abcc), HandleError, DBI::dr=HA +SH(0x1c633 24)) undef (not defined) imp_dbh->connect: dsn = database=debloat;host=localhost;port=3306, uid + = JHD, pw d = JHD imp_dbh->MyLogin: dbname = debloat, uid = JHD, pwd = JHD,host = localh +ost, port = 3306 imp_dbh->mysql_dr_connect: host = localhost, port = 3306, uid = JHD, p +wd = JHD imp_dbh->mysql_dr_connect: client_flags = 2 imp_dbh->mysql_dr_connect: <-Client does not support authentication pr +otocol req uested by server; consider upgrading MySQL client error 1251 recorded: + Client do es not support authentication protocol requested by server; consider u +pgrading M ySQL client <> DESTROY(DBI::db=HASH(0x1c4a294)) ignored for outer handle (inne +r DBI::db= HASH(0x1b1abcc) has ref cnt 1) -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x1b1abcc)~INNER) thr# +15d4064 DESTROY for DBI::db=HASH(0x1b1abcc) ignored - handle not init +ialised ERROR: 1251 'Client does not support authentication protocol re +quested by server; consider upgrading MySQL client' (err#0) <- DESTROY= undef at C:/Perl/site/lib/DBD/mysql.pm line 132 via pr +actice\sql test.pl line 12 dbih_clearcom 0x1c4a294 (com 0x1a48d34, type 2) done. !! ERROR: 1251 'Client does not support authentication protocol re +quested by server; consider upgrading MySQL client' (err#0) <- connect= undef at C:/Perl/site/lib/DBI.pm line 598 -> $DBI::errstr (&) FETCH from lasth=HASH >> DBD::mysql::dr::errstr <- $DBI::errstr= 'Client does not support authentication protocol +requested by server; consider upgrading MySQL client' DBI connect('database=debloat;host=localhost;port=3306','JHD',. +..) failed : Client does not support authentication protocol requested by server; + consider upgrading MySQL client DBI connect('database=debloat;host=localhost;port=3306','JHD',...) fai +led: Clien t does not support authentication protocol requested by server; consid +er upgradi ng MySQL client at practice\sqltest.pl line 12 -- DBI::END !! ERROR: 1251 CLEARED by call to disconnect_all method -> disconnect_all for DBD::mysql::dr (DBI::dr=HASH(0x1b61a24)~0x1c +63324) thr #15d4064 <- disconnect_all= (not implemented) at C:/Perl/site/lib/DBI.pm li +ne 677 via practice\sqltest.pl line 0 ! -> DESTROY in DBD::_::common for DBD::mysql::dr (DBI::dr=HASH(0x1c +63324)~INN ER) thr#15d4064 ! <- DESTROY= undef during global destruction dbih_clearcom 0x1b61a24 (com 0x1c373d4, type 1) done. ! <> DESTROY for DBI::dr=HASH(0x1b61a24) ignored (inner handle gone) [download] I hope this makes more sense to you (or someone else) than it does to me. If there is a pointer that would make sense to a Perl newbie on how to interpret this, I would love to know about it, as it seems to be a useful technique. Thanks again for your help & advice, John Davies	[reply] [d/l] [select]
Re: Connecting to MySQL by jpeg (Chaplain) on Apr 14, 2005 at 14:25 UTC
ok, a few things: ODBC is fine. In some cases, it's the only way to connect to a database engine. DBI is more flexible, but whatever works works. As others have said, mysql changed the password scheme from 4.0.x->4.1.x. You can either workaround (as others have showed you how) or upgrade your clients or patch old ones (as you tried). I tried to find the clients that you installed at tcx.se, but all I see is a link to www.mysql.com. Do you know about ppm? ppm is the easiest way to install what you need with windows perl. I do remember having this error some time ago and I used ppm to upgrade the DBD-mysql package. That gives me a \Perl\site\lib\auto\DBD\mysql.dll (no version, 201K, mod time: 8/24/04) that Just Works with 4.1 mysql servers using the new authentication scheme. I'm sorry that we can't say for sure what went wrong, but it's almost certain that the patches you tried to apply didn't get applied, or else you wouldn't get the error. -- jpeg	[reply]
Re: Connecting to MySQL by nmcfarl (Pilgrim) on Apr 14, 2005 at 15:21 UTC
As others indicated, this error happens when there is a version mismatch between the mysql client software, and the MySQL Database server version. To date I've only encountered this problem when the server is Newer than the client. I'd suggest upgrading the client - as new versions of MySQL tend to accumulate useful features, but downgrading the server as suggested above should work. You can also start the server in old passwords mode -- but really just upgrade the client if you can. Here are mysql.com's docs on the problem.	[reply]
Re: Connecting to MySQL by davies (Monsignor) on Apr 15, 2005 at 08:36 UTC
Thanks for all the replies. I've so far done everything in MySQL from a text interface, so I tried setting the old password option in the my.ini file and rebooting, but I got the same error. I also upgraded the packages. DBD-mysql went up from 2.9003 to 2.9004, and DBI from 1.47 to 1.48. The documentation I have said I should be using 2.9003 or later, but as I said in the original post, I wasn't sure if the documentation was out of date. However, these changes didn't help, either. The reported line number of the error moved from 664 to 666. Good job I'm not superstitious! I don't want to downgrade MySQL, for two reasons. One is that I want the sub-query functionality that is available in 4.1.x but not 4.0.x. The other is that there have been some attempts to breach our security recently. They have failed so far, but I don't want my systems to be responsible for any successes, so I'm rather keen on the added security of the new password system. I haven't got trace to work yet, but I haven't made more than a superficial attempt. I'll RTFM and try again, and report back. I'll also see what happens when I'm back in London on Monday, as I have two 4.0.15 servers on Linux boxen that I can try to connect to. They are going to be upgraded soon, but not next week. If I'm still stuck, I'll try removing my entire MySQL system and re-installing from scratch. Beyond that, it looks as though I'm going to have to use the ODBC connection. I'll keep reading and trying, though. The slithy tove has not been brilliged that can outgrabe me! Just to make things worse, the hotel last night was out of Trappist beer. What's a monk supposed to drink? :-) Thanks again, John Davies	[reply]
Re: Connecting to MySQL Redux by davies (Monsignor) on Apr 19, 2005 at 13:42 UTC
OK, I've identified the problem, so I'm writing this in the hope that it will be of interest to all those who helped, and helpful to others who may encounter the same problem. After going through all the tests I mentioned, it is definitely a password issue. As suggested back in December on the MySQL web site here, the Windows Perl setup still does not support the new MySQL password system. The latest versions of the packages are: DBD-mysql 2.9004, DBI 1.48. There is a 2.9006 version of DBD-mysql on CPAN, but this does not appear under the ppm repositories for Windows, so I suspect it may not be available for Windows. If an upgrade has been done to, or a clean install of, a version of MySQL with the new password system, then new passwords will by default be installed. If the configuration file is changed to old passwords, this will apply only to newly created passwords, and NOT to existing passwords. These can be changed by the command update user set password = old_password(string) where user = username; MySQL 4.1.x seems to accept a mixture of new and old passwords quite happily. So, I have a test user set up with restricted access with an old password so I can play with Perl, and all other users set up with new format passwords. I'll keep an eye out on the upgrades, and report back if & when I find one that solves the problem finally. Update 28/4/5 DBD-mysql 2.9006 is now available from the default ActiveState repositories under ppm, and it solves the problem. Connections using the stronger password encryption work perfectly. Thanks again to all those who helped. Regards, John Davies	[reply]