Perl & MySQL 8.0

If you just migrated to MySQL 8.0, you may have seen that the default authentication plugin has been changed to a more secure one: caching_sha2_password and I’ve already written some articles about it.

Now let’s discover how Perl users can deal with MySQL 8.0.

The driver to use MySQL with Perl is perl-DBD-MySQL. MySQL 8.0 is supported but the new authentication plugin might not be. This depends of the mysql library linked during compilation of the module.

problem connecting to MySQL 8.0

The error you may encounter is the following:

DBI connect('host=localhost','fred',...) failed: Authentication plugin
 'caching_sha2_password' cannot be loaded: 
/usr/lib64/mysql/plugin/caching_sha2_password.so: 
cannot open shared object file: No such file or directory at ./perl_example.pl line 8.

So if you encounter this problem when using perl-DBD-MySQL to connect to MySQL 8.0, you may have a driver that doesn’t support yet the new plugin. If this is the case, the fastest, easiest but not safest solution is to use the older authentication plugin: mysql_native_password.

mysql_native_password

To verify which authentication plugin the user uses, you can run the following query:

mysql> select user, plugin from mysql.user where user='fred';
+------+-----------------------+
| user | plugin                |
+------+-----------------------+
| fred | caching_sha2_password |
+------+-----------------------+

You can see that this is the new one used in MySQL 8.0. Let’s change it:

mysql> alter user 'fred' identified 
       with 'mysql_native_password' by 'mysecurepasswd';

Pay attention that you need to set the password.

Please do not modify manually the mysq.user table with any update statement.

caching_sha2_password

If you want to use the new safer authentication mechanism, you need to verify if your perl-DBD-MySQL module is linked with a library that supports it:

$ ldd /usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so | grep  'mysql\|maria'
libmysqlclient.so.18 => /usr/lib64/mysql/libmysqlclient.so.18 (0x00007f0f632ee000)

This version (libmysqlclient.so.18) doesn’t support the new authentication plugin. You need to have at least libmysqlclient.so.21 or libmariadb.so.3.

By default on CentOS/RHEL/OL 7, perl-DBD-MySQL is linked with an old version of mariadb-libs (5.5) or using the mysql-community-libs-compat (especially if you upgraded to 8.0.x).

In latest Fedora, this is not the case:

$ ldd /usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so | grep  'mysql\|maria'
libmariadb.so.3 => /lib64/libmariadb.so.3 (0x00007fecbc8b6000)

This library is installed by mariadb-connector-c-3.0.10 that supports MySQL 8.0’s new authentication mechanism too.

In case you want to use caching_sha2_password anyway with CentOS/RHEL/OL 7.x, I’ve made this rpm that is compiled with the new MySQL library:

$ ldd /usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so | grep 'mysql\|maria'
libmysqlclient.so.21 => /usr/lib64/mysql/libmysqlclient.so.21 (0x00007f0b045fb000)

$ rpm -qf /usr/lib64/mysql/libmysqlclient.so.21
mysql-community-libs-8.0.16-1.el7.x86_64

conclusion

MySQL 8.0 becomes more and more popular, even other connectors than the native ones are now supporting it. Of course if you want to use it one system not yet using the latest releases, you need to have recent libraries but this is not very complicated.