Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 100831
Status: resolved
Worked: 2 hours (120 min)
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: NICZERO [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Critical
Broken in:
  • 4.027
  • 4.028
  • 4.029
Fixed in: (no value)



Subject: Chokes on "secure_auth" in .cnf file with v5.6 libraries connecting to v4.0 server
Download (untitled) / with headers
text/plain 386b
To connect to v4.0 servers from v5.6 clients via a .cnf (read_defaults_file) file, the .cnf must contain "secure_auth = FALSE". That works for all MySQL client tools except DBD::mysql, which in contrast requires that no such line exists. The full description including trace log and (horrible unworkable) workaround is at: https://gist.github.com/larig/ea36a3f52d0979f7351f (attached)
Subject: bug.txt
Download bug.txt
text/plain 4.5k
# Problems Connecting to MySQL via DBD::mysql Something very strange happens when trying to connect to a MySQL v4.0 server using DBD::mysql with a v5.6 library and a .cnf configuration file. The driver doesn't just ignore a necessary configuration setting, it seems to positively reverse it, causing all perl connections to fail. ## Background We need a programmatic MySQL client (perl) to be able to connect to all of our databases, including MySQL v5.6 and MySQL v4.0. We therefore need a client of at least v5.6 with configuration allowing it to connect to lower versions. We are using ``` MySQL v5.6.21-70.1 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release 70.1, Revision 698) DBD::mysql v4.029 ``` We always connect using a .cnf file: ``` [client] host = 10.0.0.1 port = 3306 user = monitor password = ... ``` There are many advantages to this, which is why it is mandatory working practice at this site. 1. The credentials are kept separate from the code; maximise eyes on the code, minimise eyes on the credentials. 2. Connectivity problems are trivial to diagnose because the code connects the same way as the CLI; if the code says it cannot connect, simply test with the CLI using exactly the same credentials file. [The irony of this claim is evident.] 3. Maintenance is easy. If the port of the service changes or there is a server switchover, simply edit exactly one file or symlink; code does not get touched. ## Using MySQL CLI Clients If we run the v5.6 CLI client against a v4.0 server it fails with the error ``` ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) ``` The solution is to add a configuration line to disable secure authentication. ``` [client] host = 10.0.0.1 port = 3306 user = monitor password = ... secure_auth = FALSE ``` With that .cnf file all standard tools (eg mysql, mysqldump, mytop) can connect to the server. ## Using DBD::mysql Doing similar via DBD::mysql ``` perl -MDBI -E'DBI->connect(q{DBI:mysql:test; mysql_read_default_file=/root/wip/db.cnf})' ``` produces the error ``` DBI connect('test;mysql_read_default_file=/root/wip/db.cnf','',...) failed: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) at -e line 1. ``` So the driver is picking up the .cnf file but for this particular client the ```secure_auth``` line does not work. There is actually a workaround, which might shed light on matters because the behaviour gets even stranger. ``` perl -MDBI -E'DBI->connect(q{DBI:mysql:test;mysql_read_default_file=/root/wip/db.cnf}, undef, undef, { mysql_skip_secure_auth => 1 })' ``` Fails with exactly the same error as above. The DBI trace log is quite interesting: ``` -> connect for DBD::mysql::dr (DBI::dr=HASH(0xbbc5e0)~0xa2ece0 ... imp_dbh->connect: dsn = test;mysql_read_default_file=/root/wip/db.cnf, uid = , pwd = imp_dbh->my_login : dbname = test, uid = NULL, pwd = NULL,host = NULL, port = NULL imp_dbh->mysql_dr_connect: host = |NULL|, port = 0, uid = NULL, pwd = NULL imp_dbh->mysql_dr_connect: Skipping secure auth imp_dbh->mysql_dr_connect: Reading default file /root/wip/db.cnf. imp_dbh->bind_type_guessing: 0 imp_dbh->use_server_side_prepare: 0 imp_dbh->mysql_dr_connect: client_flags = 2 imp_dbh->mysql_dr_connect: <- --> do_error Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) error 2049 recorded: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) ``` Note the line ```Skipping secure auth``` and then the message ```secure_auth enabled```. However, if that same code is used with a modified .cnf file: ``` [client] host = 10.0.0.1 port = 3306 user = monitor password = ... [mysql] secure_auth = FALSE ``` it actually succeeds. Yes, the driver only works if both it is passed the parameter and it does not see the ```secure_auth``` line in the .cnf file. Unfortunately this is no use to me because the code would need to 1. have server-specific logic, or 2. parse some non-DBI section of the .cnf file to trigger passing the mysql_ parameter, both of which are horrible, requiring magic. It would also require the .cnf file to exclude ```secure_auth``` from the ```client``` stanza and instead mention it in every non-DBI stanza ([mysql], [mysqldump], [mytop], [myisamchk], ...). I'm guessing this bug hasn't been identified because it's an unusual use case. [StackOverflow is full of advice saying you should not be connecting to old MySQL, which is no use to people for whom connecting to old MySQL is part of the job spec.]
I've updated that gist with new findings. We can avoid messing about with the .cnf file by using ``` skip_secure_auth ``` instead of ``` secure_auth = FALSE ``` Using that, all standard clients continue to work, and DBD::mysql stops thinking that secure_auth has been enabled. But DBD::mysql ignores the option [still not sure how that is done] and so the server default of secure_auth is still in effect. That allows the driver to connect if passed (at call time) the ```mysql_skip_secure_auth => 1``` param. But I still cannot find a way to get DBD::mysql to honour the .cnf file and avoid having server-specific logic in the code. -- Nic On Wed Dec 10 10:43:17 2014, NICZERO wrote: Show quoted text
> To connect to v4.0 servers from v5.6 clients via a .cnf > (read_defaults_file) file, the .cnf must contain "secure_auth = > FALSE". That works for all MySQL client tools except DBD::mysql, > which in contrast requires that no such line exists. > > The full description including trace log and (horrible unworkable) > workaround is at: > https://gist.github.com/larig/ea36a3f52d0979f7351f > (attached)
Download (untitled) / with headers
text/plain 343b
The bug is in the MySQL client C library. I've located the line and written a patch. I will then test whether that fixes the problem without upsetting anything else and submit that to be considered for absorption. [I suggest this report is left open for a month so I can add subsequent notes which will be useful to some users of DBD::mysql.]
Download (untitled) / with headers
text/plain 124b
Closing this as the bug is confirmed as being with the C API. http://bugs.mysql.com/bug.php?id=75425 is my bug report there.
Thanks a lot for being so pro-active!


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.