As of MySQL 8.0.34 and 8.1.0 's libmysqlclients deprecates MYSQL_OPT_RECONNECT

[yoku0825]

The MySQL client library currently supports performing an automatic reconnection to the server if it finds that the connection is down and an application attempts to send a statement to the server to be executed. Now, this feature is deprecated and subject to removal in a future release of MySQL.

The related MYSQL_OPT_RECONNECT option is still available but it is also deprecated. C API functions [mysql_get_option()](https://dev.mysql.com/doc/c-api/8.1/en/mysql-get-option.html) and [mysql_options()](https://dev.mysql.com/doc/c-api/8.1/en/mysql-options.html) now write a deprecation warning to the standard error output when an application specifies MYSQL_OPT_RECONNECT. (WL #15766)

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-34.html#mysqld-8-0-34-deprecation-removal
https://dev.mysql.com/doc/relnotes/mysql/8.1/en/news-8-1-0.html#mysqld-8-1-0-deprecation-removal

I seem DBD::mysql is using only one place in

DBD-mysql/dbdimp.c

Lines 2118 to 2121 in 7117e4c

	#if MYSQL_VERSION_ID >= 50013
	my_bool reconnect = FALSE;
	mysql_options(result, MYSQL_OPT_RECONNECT, &reconnect);
	#else

Current status is deprecated and it would work several minor updates.
But the after the deadline, this method raises would be errored.
(This depends libmysqlclient.so 's version, not Server's version)

[philip]

It's worth noting that setting it to false also emits an error, so DBD::mysql always causes this error as of 8.0.34/8.1.0:

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

[maxbarry]

Ubuntu rolled out libmysqlclient21 8.0.34-0ubuntu0.22.04.1 overnight and I'm getting flooded with these warnings from cron-triggered Perl scripts.

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

As a workaround, I was able to downgrade libmysqlclient21 on Ubuntu with:

~$ apt list -a libmysqlclient21
Listing... Done
libmysqlclient21/jammy-updates,jammy-security,now 8.0.34-0ubuntu0.22.04.1 amd64 [installed,automatic]
libmysqlclient21/jammy 8.0.28-0ubuntu4 amd64

~$ sudo apt install libmysqlclient21=8.0.28-0ubuntu4
~$ sudo apt-mark hold libmysqlclient21

edit 18-AUG-23: Now that a fix has been released, you can undo the above with:

sudo apt-mark unhold libmysqlclient21
sudo apt update
sudo apt upgrade

[youradds]

Same issue as of last night! Having to set the crons to devnull, otherwise I'm getting spammed every 1 minute the cron runs :(

[daviding58]

Same here as of today with Ubuntu 20.04.
In my opinion that warning should be given by the MySQL code only if a call tries to set the option to TRUE.
MySQL's own website recommends setting it to FALSE here

It doesn't make any difference if I use the MariaDB or MySQL packages as they both seem to go through libmysqlclient21 and there isn't a libmariadbclient alternative.

[jaohbib]

This bricks some of my nagios checks

[lionslair]

Has been giving me an email from webmin status monitor when it checks if MySQL / Mariadb is up

[joemstar]

I am having the exact same issue. I have been getting flooded with webmin emails. Can anyone give some specifics on how to change this setting?

[covex]

You would need to rebuild the perl DBD as mentioned in the first post and remove completely MYSQL_OPT_RECONNECT setup, until then the libmysql will produce this warning even with false setup. Otherwise need to wait for fix and update in your distro.

[pali]

In my opinion that warning should be given by the MySQL code only if a call tries to set the option to TRUE. MySQL's own website recommends setting it to FALSE here

Yea, this really looks like an issue in MySQL client library, which basically broke compatibility with existing applications and scripts which lot of people are using. You should report this issue to mysql bug tracker.

[nneul]

https://bugs.launchpad.net/ubuntu/+source/mysql-8.0/+bug/2031548

This is nuts that they consider "change the runtime output" as appropriate during a minor version/security update. That's never appropriate unless it's something extreme/severe, and even then is questionable.

[pali]

I think you would have to fill it into mysql bug tracker: https://bugs.mysql.com/
I doubt that Oracle monitors stackoverflow or some launchpad issue tracker for mysql related issues.

[nneul]

https://bugs.mysql.com/bug.php?id=112089

[boospy]

Nice, hundreds of mails today.... hopefully a fix will come soon also from canonical

[edlerluca]

A possible hotfix is to supress all errors that are sent to STDERR within the causing perl script.
My nagios check_mysql_health plugin works again after adding the following line at the beginning of the plugin's perl script.

open STDERR, '>/dev/null';

If something else would cause an error you would not recognise it but it should do it's work until there is a regular update that fixes the behaviour.

[PitWenkin]

https://bugs.mysql.com/bug.php?id=112089

"This is now a verified bug report." … whatever that means in regard to a solution

[jk2]

Hotfix to suppress STDERR only during connection establishment and still getting reason if connection fails:

use DBI;
open(my $olderr, ">&", STDERR);
close STDERR;
open(STDERR, ">/dev/null");
my $dbh = DBI->connect(...);
close STDERR;
open(STDERR, '>&', $olderr);
unless ($dbh) {
  die "Could not connect database: ". $DBI::errstr;
}

[japharl]

ubuntu is releasing upgraded version shortly with it's (own?) patch : https://bugs.launchpad.net/ubuntu/+source/mysql-8.0/+bug/2031548

[minusdavid]

ubuntu is releasing upgraded version shortly with it's (own?) patch : https://bugs.launchpad.net/ubuntu/+source/mysql-8.0/+bug/2031548

After an apt-get update, I can see a new libdbd-mysql-perl on 20.04 and 22.04 now.

[maxbarry]

Now fixed for me on 22.04 with newly-released libdbd-mysql-perl 4.050-5ubuntu0.22.04.1 - thank you!

[casimirloeber]

Any luck in this landing in main DBD::mysql?

Ubuntu makes it very difficult to rollback to 8.0.33 without doing gymnastics.

I would love to be able to just do a cpanm DBD::mysql and call it a day. I don't use ubuntu perl module packages and install everything via cpanm.

[minusdavid]

It looks like the last release of DBD::mysql was Jan 2019, last commit in this repo was June 2021, but maybe there wasn't anything this urgent since then.

Hopefully @dveeden has a chance to look at it soon.

[minusdavid]

The patch in the Ubuntu package looks simple enough but not trivial for most since it's in the C and requires a full rebuild of the package.

From 7a5c9efbcf7d055f8586f4e678df3509c7cb645f Mon Sep 17 00:00:00 2001
From: Andy Orr <andy.orr@436software.com>
Date: Wed, 16 Aug 2023 11:47:47 -0400
Subject: [PATCH] MYSQL_OPT_RECONNECT is deprecated in MySQL 8.0.34.  Removed
 support.

https://github.com/perl5-dbi/DBD-mysql/issues/354
---
 dbdimp.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/dbdimp.c b/dbdimp.c
index 717ed8e..51bb18d 100644
--- a/dbdimp.c
+++ b/dbdimp.c
@@ -2115,7 +2115,7 @@ MYSQL *mysql_dr_connect(
         we turn off Mysql's auto reconnect and handle re-connecting ourselves
         so that we can keep track of when this happens.
       */
-#if MYSQL_VERSION_ID >= 50013
+#if MYSQL_VERSION_ID >= 50013 && MYSQL_VERSION_ID < 80034
       my_bool reconnect = FALSE;
       mysql_options(result, MYSQL_OPT_RECONNECT, &reconnect);
 #else

[minusdavid]

Ah, silly me, Andy has already posted the pull request: #355

Champion, @aorr436

[scoobscoob]

Yes , Ubuntu released a fix last night.

https://bugs.launchpad.net/ubuntu/+source/mysql-8.0/+bug/2031548

Confirmed fixed on 20.04.2 LTS server.

[lionslair]

Same here no more notifications

[tonvoon]

We had to patch this as

#if defined(MARIADB_BASE_VERSION) || MYSQL_VERSION_ID >= 50013 && MYSQL_VERSION_ID < 80034

otherwise it failed to compile properly on MariaDB 5.5.

[pali]

Above change is still not enough, as you should not dereference sock and I'm sure that in future versions of MySQL (when reconnect is removed) above change cause crashes or compile issues or other problems again.

I addressed also these problems in following pull request for DBD-MariaDB: perl5-dbi/DBD-MariaDB#192

[minusdavid]

Great to see a response from you @pali as one of the biggest contributors to DBD::mysql.

Do you know who has commit privileges for the repo?

[pali]

I think that dveeden is still maintainer of this repo and should have full privileges.

[dveeden]

I do have privileges. However I won't merge any PR's that give the false impression that DBD::mysql is maintained as it is not. So for MySQL 8.1 support you really should not use DBD::mysql.

To get DDB::mysql to be supported the Unicode issues etc. need to be fixed and a lot of cleanup would be needed to make the code maintainable. I don't have the resources to do this at this point.

I'm not happy about this situation, but this is just how it is now.

Side note: I also don't have any plans to continue the work on DBD::mysqlx

[maxbarry]

Oh gosh. That's a shame, but also understandable. As a heavy user of DBI/DBD::mysql, does this mean we're likely stuck on MySQL 8.0 forever?

Also does this bit of README.md need to be updated?

This module is maintained and supported

[minusdavid]

I do have privileges. However I won't merge any PR's that give the false impression that DBD::mysql is maintained as it is not. So for MySQL 8.1 support you really should not use DBD::mysql.

Is there a different library that you would recommend using? Should DBD::MariaDB be seen as the successor?

To get DDB::mysql to be supported the Unicode issues etc. need to be fixed and a lot of cleanup would be needed to make the code maintainable. I don't have the resources to do this at this point.

Since DBD::mysql is widely used, perhaps a call should be put out to find a new maintainer? Perhaps that's something @pali would want to do?

[pali]

Is there a different library that you would recommend using? Should DBD::MariaDB be seen as the successor?

Yea, DBD::MariaDB is keeping compatibility with both MySQL and MariaDB client libraries and servers. Has most of the DBD-mysql reported bug fixed (including these 80+ from this DBD-mysql issue tracker) and lot of other improvements. But it is not for "legacy" application which depends on specific (lets say buggy) DBD-mysql behavior.

Since DBD::mysql is widely used, perhaps a call should be put out to find a new maintainer? Perhaps that's something @pali would want to do?

No, few years ago there was already attempt to fix DBD-mysql bugs (UNICODE and security) and it failed because of lot of DBD-mysql applications which depends on buggy behavior. That is why there is DBD-MariaDB. So I think the current state of DBD-mysql is the correct, provides support for existing legacy applications which cannot be upgraded and which should stay to use older MySQL client. Applications which do not depend on fixed mysql behavior can be upgraded to new client and server versions and so also to DBD-MariaDB (and maybe also to other database engines).

[dveeden]

I do have privileges. However I won't merge any PR's that give the false impression that DBD::mysql is maintained as it is not. So for MySQL 8.1 support you really should not use DBD::mysql.

Is there a different library that you would recommend using? Should DBD::MariaDB be seen as the successor?

To get DDB::mysql to be supported the Unicode issues etc. need to be fixed and a lot of cleanup would be needed to make the code maintainable. I don't have the resources to do this at this point.

Since DBD::mysql is widely used, perhaps a call should be put out to find a new maintainer? Perhaps that's something @pali would want to do?

No DBD::MariaDB should not be seen as the successor to DBD::mysql.

I'll try to see if I can cleanup DBD::mysql in the next few weeks to have it in a maintainable state again.

[minusdavid]

No DBD::MariaDB should not be seen as the successor to DBD::mysql.

I'll try to see if I can cleanup DBD::mysql in the next few weeks to have it in a maintainable state again.

Thanks, @dveeden ! After you do that, would you be looking to hand over the reins to someone else?

[nneul]

"failed because of lot of DBD-mysql applications which depends on buggy behavior. "

This may be a bit off topic, but is there a list or reference of what the 'buggy behaviors being relied on' are?

[dveeden]

Fixed in master

[casimirloeber]

Thanks a million @dveeden !!

Is it deemed safe to use master with all the other fixes and such that have been merged into it? Or is there going to be a new CPAN release at some point?

Thanks again!

[dveeden]

@casimirloeber I am planning to release a new CPAN release in the next few weeks. For what I know the master version should be fine, but I want to do more testing before doing a release.

[casimirloeber]

Thank you very much @dveeden. I am grateful for your efforts and really appreciate your work.

[casimirloeber]

I am happy to report that I have been running master in production on some very high traffic applications with zero issues. Thanks @dveeden for the great work!