MySQL Bugs: #106384: MySQL client library does not perform reconnect action

Bug #106384	MySQL client library does not perform reconnect action
Submitted:	5 Feb 2022 13:04	Modified:	28 Feb 2022 23:10
Reporter:	Kamil Holubicki (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
MySQL C client library does not perform auto-reconnect action as described here:
https://dev.mysql.com/doc/c-api/8.0/en/c-api-auto-reconnect.html

This is because of changes introduced in commit
14508bbb1790697c28659dd051fbc855cd3b5da9

Versions prior to this commit (I've tested on 8.0.23) do reconnect as described.

The question is if the above-mentioned commit is "the new way" of handling such errors (that client software should detect them and handle), or it is the regression? In my opinion, this is the regression, because it probably affects all other connectors based on the C client library, and the documentation says that this should still work. Also, the logic that handled reconnection before the fix is still there in cli_advanced_command() function.

How to repeat:
It is easy to reproduce using the client library test. Please see attached patch with the test I've implemented. The test works fine on 8.0.23. Fails on 8.0.26/27/28. I didn't test it myself on 24, 25.

Suggested fix:
Update the documentation/MYSQL C client API to say that auto-reconnect does not happen automatically and it is up to the client to handle the error or fix the auto-reconnection functionality (I have no particular code solution for this ATM)

client reconnect test (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: reconnect-test.patch (text/x-patch), 917 bytes.

Additionally we've found one suspicious change in the above-mentioned commit.

Method:
int Protocol_classic::read_packet()

  /*
    XXX: This place seems erroneous: why we keep thread for unusable
    socket and shutdown it for other states?
    Probably should be NET_ERROR_SOCKET_RECOVERABLE instead of
    NET_ERROR_SOCKET_UNUSABLE.
  */
  return m_thd->net.error == NET_ERROR_SOCKET_UNUSABLE ? 1 : -1;

Hello Kamil Holubicki ,

Thank you for the report and contribution.

regards,
Umesh

Posted by developer:
 
Reconnect is off by default according to https://dev.mysql.com/doc/c-api/8.0/en/c-api-auto-reconnect.html

You will have to turn it on first.

Hi Kamil, 

Thank you for providing this patch, however as Joro said, this patch won't be implemented has the reconnect needs to be activated to work as you expect.

Thank you for using and contributing to MySQL

lefred.

Hi Georgi, Hi Frederic,

Yes, I know that reconnection has to be enabled. This is what my test does. And the reconnection does not work even if I enable it. 
Or do I miss something?

hi,

This appears to be the same issue as reported in https://bugs.mysql.com/bug.php?id=105229

Regarding the comment "the reconnect needs to be activated to work", the test code provided by Kamil Holubicki does enable the MYSQL_OPT_RECONNECT option.
ie, the 3rd parameter in client_connect() specifies whether auto-reconnect is enabled or not.
    MYSQL *tmysql = client_connect(0, MYSQL_PROTOCOL_DEFAULT, true);
It does not use the MYSQL_OPT_RECONNECT option, but rather set the "mysql->reconnect" variable directly.  Which is what calling
   mysql_options(con, MYSQL_OPT_RECONNECT, &reconnect)
does anyway.

I've attached a standalone test program in https://bugs.mysql.com/bug.php?id=105229 should you rather want to try reproduce the issue with that.

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.

While this was fixed in MySQL 8.0.28 and never caused any troubles anymore in both 8.0.28 and 8.0.29, it now pops up again in MySQL 8.0.30. Tested on Percona for MySQL 8.0.30, also see related issue:

- https://jira.percona.com/browse/PS-7999
- https://forums.percona.com/t/mysql-8-0-24-8-0-25-connection-problems-with-federated-storag...

Can you please reopen this issue and look into it? I couldn't find any related changelog entries.

Thanks, Philip

in mysql.err (Percona for MySQL 8.0.30 on Debian Bullseye) we get the following errors:

```
2022-11-23T22:52:24.848022+01:00 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001160 - Got an error writing communication packets
2022-11-23T22:52:24.848233+01:00 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001156 - Got packets out of order
2022-11-23T22:52:24.848355+01:00 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001160 - Got an error writing communication packets
2022-11-23T22:52:24.848387+01:00 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001156 - Got packets out of order
```

This only happens on MySQL servers which actively use the FEDERATED engine. We are using `interactive_timeout` and `wait_timeout` default values (8h).

it looks like the regression in MySQL 8.0.30 comes from this CHANGELOG entry / API change:

> API: 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. (Bug #105229, Bug #34007830)

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