Bug #105229 Reconnect option is not working
Submitted: 14 Oct 2021 14:16 Modified: 23 May 2022 21:44
Reporter: Ilya Goldberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:8.0 OS:CentOS (7)
Assigned to: CPU Architecture:x86 (AMD)

[14 Oct 2021 14:16] Ilya Goldberg
Description:
Our application is based on Qt and using "MYSQL_OPT_RECONNECT=1" option before establishing a connection.
The idea behind it is that MySQL client should reconnect if connection dropped with MySQL server so the connection would be always up.
It was working as expected when using MySQL 8.0.21.
Without changing our application upgraded to MySQL 8.0.26. and now while running the application getting the following error: "The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior".
The error appears after connection timeout (wait_timeout is 8 hours) is passed.
Somewhere between versions 8.0.21 and 8.0.26 the reconnect feature has been affected.

How to repeat:
In order to save time do the following:
1. Change global wait_timeout to 60 seconds
2. Use an application which sets MYSQL_OPT_RECONNECT=1 option.
3. Use MySQL 8.0.21 and establish a connection with database.
4. Wait for more than 60s.
5. Try to use the connection (read or write) - this should work.

6. Repeat steps 3-5 while using MySQL 8.0.26. In step 5 an error appears.

Suggested fix:
This is probably the commit that might have affected reconnect: https://github.com/mysql/mysql-server/commit/14508bbb1790697c28659dd051fbc855cd3b5da9#diff...
[14 Oct 2021 15:04] MySQL Verification Team
Hi Mr. Goldberg,

Thank you for your bug report.

We have tried to repeat your behaviour. We used mysql CLI with --reconnect option and have set LOCAL value of the wait_timeout. Reconnection worked without problems.

Can you try whether it works for you. If it does, you will have to check what your application does.

Waiting on your feedback.
[15 Oct 2021 10:50] Ilya Goldberg
Thank you for your prompt response.
You are right, we tried using CLI and reconnect option is working but perhaps the test with CLI is not sufficient as C/C++ API might be using a different implementation from CLI.
I have prepared a very simple C program to test MySQL 8.0.26.
The steps to reproduce the issue:
1. Login to mysql and make sure that wait_timeout=5 is set lower than current 10s wait in the test program.
2. In the provided test program you will need to change credentials, database name and other settings in mysql_real_connect() call. Also both mysql_query() commands might need an update for your database content.

The test program code attached as a file.

On CentOS it can be compiled using a simple command:
gcc mysql_reconnect.c -I/usr/include/mysql/ -L/usr/lib64/mysql -lmysqlclient -o mysql_reconnect

When running the program on my CentOS box I am getting the following:

[root@unitname ~]# ./mysql_reconnect
100
Waiting for 10s
The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
[15 Oct 2021 10:51] Ilya Goldberg
A simple test program for MySQL reconnect option

Attachment: mysql_reconnect.c (application/octet-stream, text), 2.01 KiB.

[15 Oct 2021 11:01] Ilya Goldberg
Here is a comparison run of the test program:

Server version: 8.0.23 MySQL Community Server - GPL

# ./mysql_reconnect
100
Waiting for 10s
200

Server version: 8.0.26 MySQL Community Server - GPL

# ./mysql_reconnect
100
Waiting for 10s
The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
[15 Oct 2021 12:44] MySQL Verification Team
Hi Mr. Goldberg,

Thank you for your attachment.

However, we did notice some errors in your code, so it is clear why it does not work for you. Please, look at our examples, like mysqltest.cc or mysql_client_test.cc on how to use those options correctly.

Unrelated to this is that you declared that your CPU is ARM that belongs to the x86 family.

Not a bug.
[18 Oct 2021 7:44] Ilya Goldberg
Thank you for checking the attached test program and pointing out to the examples.
Our processor type is AMD x86, sorry for the typo and confusion.

Prior to getting into the provided mysql_client_test.cc example, I just wonder how is my test program can be working as expected with MySQL 8.0.23 and does not work (in particular the MYSQL_OPT_RECONNECT option) with MySQL 8.0.26?
Even if we assume that the test program contains some errors, there should not be any behaviour difference between two different MySQL versions on such a basic operation.
[18 Oct 2021 9:52] Ilya Goldberg
I am looking at mysql_client_test.cc program.

On line 14237 there is a method defined to "Test MYSQL_OPT_RECONNECT, Bug#1579".
The method name is test_opt_reconnect().
There are following steps inside the method:
1. Call mysql_client_init() and check the output.
2. Check reconnect flag in MYSQL structure is 0.
3. Call mysql_options with MYSQL_OPT_RECONNECT by setting it to true.
4. Check reconnect flag in MYSQL structure is 1.
5. Call mysql_real_connect() with credentials.
6. Check reconnect flag in MYSQL structure is still 1.
7. Close mysql_close().

Looking at the above steps, these are actually do not test the reconnect functionality but rather a flag inside MYSWL structure, which does not prove that reconnect is working.

At the same time my test program doing the following:
1. Call mysql_init() and check the output.
2. Call mysql_options with MYSQL_OPT_RECONNECT by setting it to true.
3. Call mysql_real_connect() with credentials.
4. Run mysql_query() - simple query to get data from one of the tables.
5. Wait for time bigger than global wait_timeout
6. Run similar mysql_query() and check the results
7. Close mysql_close().

My short test program is intended to test the scenario where there is a wait time between two queries longer than the wait_timeout set globally.

I would appreciate if you can point out what I am doing wrong compared to the examples that you mentioned?
[18 Oct 2021 12:39] MySQL Verification Team
Hi,

You do not need to follow both examples. Looking at mysql.cc should be enough.
[18 Oct 2021 12:51] MySQL Verification Team
Hi,

Also, since --reconnect works fine with mysql CLI in 8.0.26 release, it means it is a further proof that you have problem in your code. This is, because, mysql CLI is built on the same C API that you are using.
[18 Oct 2021 13:06] Ilya Goldberg
Can you please change the status back to Open as not convinced that this is not a bug until someone can point out to me what is wrong with just a few commands testing program that I've provided.
[27 Mar 2022 21:35] Andrew Victor
hi,

I am seeing the same problem with MySQL 8.0.26.

This issue cannot be tested with the --reconnect option in mysql CLI since that does not use the
   mysql_options(mysql_con, MYSQL_OPT_RECONNECT, &reconnect)
function to enable the automatic re-connection to the server.
The mysql CLI monitors the state of the connection itself and manually calls a reconnect() method if there are any errors:
   https://github.com/mysql/mysql-server/blob/8.0/client/mysql.cc#L3020

I will attach a stand-alone test program showing the problem below.  To run the test:
 * Adjust the server credential at the top of the file.
 * Compile it:
     g++ mysql_autoreconnect_test.cc -I/usr/include/mysql/ -L/usr/lib64/mysql -lmysqlclient -o mysql_autoreconnect_test
 * Run it.

Expected output:
   Sending query: SET SESSION wait_timeout=5
   No result-set

   Sending query: SELECT 1
   Result: 1

   Waiting for 10s
   Sending query: SELECT 2
   Result: 2

   Test successfully completed

Output against a mysql 8.0.26 server:
   Sending query: SET SESSION wait_timeout=5
   No result-set

   Sending query: SELECT 1
   Result: 1

   Waiting for 10s
   Sending query: SELECT 2
   mysql_query error: 1
   [4031] The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
[27 Mar 2022 21:36] Andrew Victor
Test program for MYSQL_OPT_RECONNECT

Attachment: mysql_autoreconnect_test.cc (text/x-c++src), 2.34 KiB.

[28 Mar 2022 13:49] MySQL Verification Team
Hi Mr. Goldberg,

Thank you for your test case.

We managed to reproduce the problem with the latest 8.0.28.

Verified as reported.
[28 Mar 2022 13:50] MySQL Verification Team
Hi Mr. Goldberg,

Please, do note that this is not a forum for MySQL support. This is just a forum for reporting bugs and you have reported a veritable bug, which is now verified.
[28 Mar 2022 13:58] Ilya Goldberg
Thank you for the bug confirmation on MySQL version 8.0.28.
We would be interested to find out when it is fixed and which version of MySQL will have the fix in it?

Thanks.
[28 Mar 2022 14:04] MySQL Verification Team
Hi Mr. Goldberg,

Nobody knows in which release will this get fixed.

We are recommending to you to do the same that we are doing. When a new release comes out, for example 8.0.29, you should read Release Notes that are found on dev.mysql.com.

Scheduling of bug fixes is a very long process and nobody can tell you which release (or version) might contain the patch for any particular bug.
[28 Mar 2022 14:40] Andrew Victor
Reference to another report of the same bug:   https://bugs.mysql.com/bug.php?id=106384

That bug report does refer to one suspicious change [comment 5 Feb 13:12] in the commit (14508bbb1790697c28659dd051fbc855cd3b5da9) that supposedly broke it.
Investigating that change might speed-up the investigation and fix.
[23 May 2022 21:44] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.30 release, and here's the proposed changelog entry from the documentation team:

Applications that previously used the MySQL client library to perform an
automatic reconnection to the server received the following mysql_query
error after the server was upgraded: [4031] The client was disconnected by
the server because of inactivity. See wait_timeout and interactive_timeout
for configuring this behavior.

Thank you for the bug report.