mysql_skip_secure_auth not skipping secure auth

Question

Edit: From what I can tell, the root cause of this problem is that the right Perl modules are being installed, but the wrong mysql.so file is being loaded.

my $dsn = "DBI:mysql:"
    . "database=$db;"
    . "host=$dbhost;"
    . "mysql_ssl=$dbssl;"
    . "mysql_skip_secure_auth=1;";

I recently attempted to upgrade our version of DBD::mysql, but kept coming across the DBI connect('database=mydb;host=myhost','myuser',...) failed: Connection using old (pre-4.1.1) error.

After many hours of debugging, and determining that the correct option of not updating the password hashing methods of our mysql tables was not possible with our overall system, I found that with DBD::Mysql 4.027 you could declare "mysql_skip_secure_auth" as part of your dsn.

However, this doesn't seem to work.

If I runmysql -h $myhost -u $myuser -p --skip-secure-auth, I am able to connect without incident, but trying to do this with DBI/DBD::mysql, I always run into the error above, as if the directive is being ignored.

• Relevant documentation

I have also tried using mysql_read_default_file with the same option set, as well as simply mysql_skip_secure_auth in the DSN. No combination of any of these things has worked.

Am I missing something?

EDIT:

Trace output (edited to remove sensitive information):

imp_dbh->mysql_dr_connect: host = |{host}|, port = 0, uid = {user}, pwd = {pwd}
imp_dbh->mysql_dr_connect: Skipping secure auth
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)




my $versions = DBI->installed_versions;
foreach (keys %$versions) {
    print "\n$_: " . %$versions->{$_};
}

DBD::SQLite: 1.26                                                               
DBD::ExampleP: 12.014310                                                          
DBD::Sponge: 12.010002                                                             
DBD::Gofer: 0.015057                                                             
DBD::DBM: 0.06                                                                    
DBD::mysql: 4.027                                                                  
DBI: 1.618

Answer 1

You are right that things are a little tricky in this area, but the challenges seem to stem from the C mysql lib rather than DBD::mysql.

First, pass the param as a driver option rather than as part of the main DSN string: my @dsn = ("DBI:mysql:database=$db;host=$dbhost;mysql_ssl=$dbssl", $user, $pissword, { mysql_skip_secure_auth => 1 });

That should work as long as you are not using a .cnf file.

To authenticate via a file there are some constraints. my @dsn = ("DBI:mysql:"database=$db;host=$dbhost;mysql_ssl=$dbssl;" . "mysql_read_default_file=$absolute_path.cnf", undef, undef, { mysql_skip_secure_auth => 1 });

Ensure your file does not contain an entry for secure_auth, otherwise the C lib mistakenly treats it as enabled, even if the entry has it disabled.

Any line similar to this, with a disabling (falsy) value secure_auth = FALSE must be changed to skip_secure_auth (and confirm that standard tools such as mysql and mysqldump work as before).

The C lib has a bug in that it treats secure_auth = FALSE as secure_auth = TRUE and in my opinion it has a second bug in that it ignores skip_secure_auth instead of honouring it.

According to the documentation at http://dev.mysql.com/doc/refman/5.6/en/mysql-options.html secure_auth options are not generally supported, but it is strange that one form is (mis)implemented while another form is ignored.

The above 'works' if your code knows it is connecting to a v4.0 server, but I would much rather not mess the code with server-specific logic. I'm still hoping there's a way to get the C library patched so this can be done purely via the .cnf file as the standard tools do.

As an addendum, if there's some reason you cannot use skip_secure_auth, you will need to omit all secure_auth entries from your [client] section and instead add secure_auth = FALSE to each section needed by your tools, ie [mysql], [mysqldump], etc, which is clearly horrible.