WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version

Question

As I have seen here, Beginning with MySQL 8.0.34, the automatic reconnection feature is deprecated. I have a lot of Perl scripts that connect to a MySQL database with something like:

my $dbh = DBI->connect( "DBI:mysql:database=$db_name;host=$db_host",
                         $db_user, $db_pass, {'RaiseError' => 1,
                         mysql_enable_utf8 => 1} );

Which are now throwing a warning:

WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.

I have tried changing the Perl code to:

my $dbh = DBI->connect( "DBI:mysql:database=$js_db_name;host=$js_db_host",
                         $js_db_user, $js_db_pass, {'RaiseError' => 1,
                         mysql_enable_utf8 => 1, mysql_auto_reconnect => 0} );

But the warning persists. I have tried adding reconnect=false to the [client] section of the config file at /etc/my.cnf as suggested here. Also without success.

I have even tried to change the Perl DBI driver for MySQL which has code like this (mysql.pm):

    if ($this && ($ENV{MOD_PERL} || $ENV{GATEWAY_INTERFACE})) {
        $this->{mysql_auto_reconnect} = 1;
    }

To avoid setting the mysql_auto_reconnect attribute to 1. But that does not work either. There may be additional code but I would avoid changing a standard library if possible.

I have tried updating both DBI and DBD::mysql, but they seem to be up to date:

$ cpanm DBI
DBI is up to date. (1.643)
$ cpanm DBD::mysql
DBD::mysql is up to date. (4.050)

Any Perl script being executed that opens a connection throws the message to stderr.

The behaviour of DBD::mysql is documented as follows:

This attribute determines whether DBD::mysql will automatically reconnect to mysql if the connection be lost. This feature defaults to off; however, if either the GATEWAY_INTERFACE or MOD_PERL envionment variable is set, DBD::mysql will turn mysql_auto_reconnect on. Setting mysql_auto_reconnect to on is not advised if 'lock tables' is used because if DBD::mysql reconnect to mysql all table locks will be lost. This attribute is ignored when AutoCommit is turned off, and when AutoCommit is turned off, DBD::mysql will not automatically reconnect to the server. It is also possible to set the default value of the mysql_auto_reconnect attribute for the $dbh by passing it in the %attr hash for DBI-connect>. Note that if you are using a module or framework that performs reconnections for you (for example DBIx::Connector in fixup mode), this value must be set to 0.

But I was expecting to be able to bypass that by setting mysql_auto_reconnect => 0. It is something that must be set explicitly because MySQL is now set to no reconnect by default:

$ mysql --help | egrep "^reconnect"
reconnect                                 FALSE

Any ideas to disable the reconnect feature and get rid of the warning?

Weasand

i think that the flag is automatically inserted, so as long as the driver isn't updated, the problem remains, but that is on my part only speculation – Kimberly

Suggest bug report with MySQL and whichever of the Perl modules seems to be at fault. – Grindlay

Answer 1

DBD::mysql always sets MYSQL_OPT_RECONNECT to false, there's nothing your code can do to change that. Setting it to any value, including false, emits the error.

See also: https://github.com/perl5-dbi/DBD-mysql/issues/354

Kotz

thank you for your feedback! I think you mean that "DBD::mysql always sets MYSQL_OPT_RECONNECT to true." – Weasand

Nope, it always set it to false and then uses its own reconnect mechanism. – Kotz

The problem seems to be at the libmysqlclient21 library that has been recently updated. Either this library should not emit a warning when the MYSQL_OPT_RECONNECT option is set to false, or BDB::mysql should not set the option at all instead of setting it to false. – Weasand

It looks like there is now an update to the libdbd-mysql-perl on ubuntu, and it seems to have fixed the issue! – Alonzo

Running Ubuntu 20.04.6 LTS with latest updates (2023-09-28) and this problem is not going away. Have the latest version of libdbd-mysql-perl. Very nasty problem for high traffic web sites error logs that run Perl scripts. Hoping they will fix it soon or I will start migrating to other things. – Bausch

Answer 2

The following fixed the issue for me, on Ubuntu 20.04.6 LTS (Focal Fossa):

sudo apt update
sudo apt install libdbd-mysql-perl

(as suggested in comments from OP and others)

Stencil

I've seen the same issue on a 20.04.06 LTS but this package was already installed at its latest version. – Lyford