| Subject: | Chokes on "secure_auth" in .cnf file with v5.6 libraries connecting to v4.0 server |
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 |
# 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.]