Problems connecting to MySQL v4.0 using DBD::mysql

dbdmysql-problem.md

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], ...).

The Problem

There is another MySQL parameter which helps shed light on what is going wrong, skip_secure_auth. All the standard client tools treat that flag as equivalent to secure_auth = FALSE and it has one major advantage for sufferers of this bug: DBD::mysql does not choke on the .cnf file. It looks like DBD::mysql sees secure_auth as a flag rather than a parameter, so if it is read, the driver treats it as true even if it should be false/disabled. So if we modify the .cnf to the following, the problem shrinks a little.

[client]
host = 10.0.0.1
port = 3306
user = monitor
password = ...
skip_secure_auth

Now all standard tools continue to work, and DBD::mysql stops thinking secure_auth has been enabled. Unfortunately, DBD::mysql seems to ignore the flag altogether, so we still need to pass mysql_skip_secure_auth => 1 in the code.

So if that is correct it means the DBD::mysql code needs to make two fixes to stop these problems.

1. Stop seeing secure_auth as a flag (and ignoring its value), and
2. start honouring skip_secure_auth (which is a flag).

The first fix would mean .cnf files can stay as they are and do not need one section per client tool. The second fix would mean DBD::mysql honours the .cnf file and does not need to be nursed along by duplicating options in driver params (in the calling code).