MySQL Bugs: #90288: C API does not send the command to MySQL Server when MYSQL_OPT

Bug #90288	C API does not send the command to MySQL Server when MYSQL_OPT_RECONNECT is set
Submitted:	3 Apr 2018 13:31	Modified:	4 Jun 2018 12:09
Reporter:	Peter VARGA	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S1 (Critical)
Version:	5.7.21	OS:	SUSE (12.3)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86
Tags:	API, C, mysql_query

Description:
I have this very strange issue and I cannot reproduce it - it occurs once in ~12 hours **only** for this table:

    CREATE TABLE `ReferenceWaves` (
      `ReferenceWave` datetime NOT NULL COMMENT 'DateTime of the Reference Wave',
      `InstrumentId` int(11) NOT NULL,
      `Triggered` datetime DEFAULT NULL COMMENT 'When it was triggered'
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Assures FAFWE doesn''t use this reference wave when restarted';
    
    ALTER TABLE `ReferenceWaves`
      ADD UNIQUE KEY `ReferenceWave` (`ReferenceWave`,`InstrumentId`);

Then running this command

    SELECT COUNT(*) FROM MyISAM_ElliottWavesCore.ReferenceWaves 
          WHERE ReferenceWave = '2018-04-02 17:06:00' AND InstrumentId = 73

does not return from the `mysql_query()` C function. This occurs more or less once in 12 hours - in the meantime around 10'000 queries like this have been executed without any problem.

Running `SELECT * FROM information_schema.PROCESSLIST WHERE ID=6981;` - where `ID` is the thread id for the query - returns this:

    ID  	USER	HOST	                                  DB	COMMAND	TIME STATE	INFO
    6981	FAEWE	aaeb-app206ly.aaeb-holding.local:52492	(null)	Sleep	1836		(null)

After around 5 hours this thread apparently is killed automatically by MySQL and not visible anymore in the `information_schema.PROCESSLIST`.

The table `MyISAM_ElliottWavesCore.ReferenceWaves` has ~4'800 rows and the total size - including the index - is <150KB. I have tables which have million of rows and have size > 1GB but there is never this issue - it is **only in this table** and very unpredictable. You can imagine how frustrating it is.

Any idea what is going wrong?

It is running on SLES 12 SP3 connected to MySQL 5.7.21 via client 5.7.19 programmed in C/C++.

How to repeat:
Not possible because very unpredictable.

Technical data:

x) SLES 12.3 running on XenServer 7.4 as VM
x) compiled with gcc 7.3 using -std=c++17 and almost every warning option.
x) using glibc 2.27 which is separately linked into the project because SLES 12.3 uses 2.22
x) MySql Community Server 5.7.21
x) C API client 5.7.19

After reviewing the log file I realized that mysql_query() didn't send the command to the MySQL server and this let it appeared as it would hang. But it is a "normal" behaving from the server: When it didn't receive anything then it cannot process the command.

Unfortunately it happens even in this - default - constellation:

Technical data:

x) SLES 12.3 running on XenServer 7.4 as VM
x) compiled with gcc 7.3 using -std=c++17 and almost every warning option.
x) SLES 12.3 glibc 2.22
x) MySql Community Server 5.7.21
x) C API client 5.7.21

Detailed information here:
https://dba.stackexchange.com/questions/203304/mysql-query-is-not-passing-the-command-to-t...

Also in C API 5.7.21

According to the Stackoverflow thread a member found the solution. The issue comes up when MYSQL_OPT_RECONNECT is set.

This should be investigated by the programmers.

I have these settings in my.cnf:
interactive_timeout = 31536000
wait_timeout        = 31536000

Therefore there was NEVER the situation that something timeout - check the MCVE and it is iterating in a loop without any delay.

Updated the title.

Hi,

There's not enough info to investigate this issue. 

With regards to timeout, there's many reasons why connection can be terminated and it's up to you to handle that (and reconnect properly) in the C application. I can only guess how your code looks like as all you provided is the query that has no reason to fail.

I'd say the issue is in your code but have not enough information to say how/where. What makes no sense to me is you said you seen the query in the processlist and then you say the query was never sent?! Can you elaborate on that?

kind regards
Bogdan

I see the THREAD in the process list which I can retrieve because I have the "open" connection, NOT the command.

There is NO chance that the connection runs into a timeout as you can see in the MCVE. All commands are executed immediately with almost no delay.

Hi,

This is the second time you mention MCVE, I don't see any example in this report, let alone one I can use to reproduce/verify this issue?

kind regards
Bogdan

http://s000.tinyupload.com/index.php?file_id=04462125953807653454

Check also the Stackoverflow thread
https://dba.stackexchange.com/questions/203304/mysql-query-is-not-passing-the-command-to-t...

AND also the chat:
https://chat.stackexchange.com/rooms/75807/discussion-between-al-bundy-and-gerard-h-pille

It works now.

@Your comment:
I never had a chance to handle the timeout as you mentioned because there NEVER was a timeout. The command just hasn't been submitted and that is the fact.

Hi,

Thanks for posting the example but I'm having issues reproducing this, test is running for 6 hours now and is still running fine.

> I never had a chance to handle the timeout as you mentioned because there NEVER was a timeout. The command just hasn't been submitted and that is the fact.

This is a weird behavior..

the changes I made to your example

in the cpp i just changed conn parameters:

        if ( nullptr == mysql_real_connect( mysqlConnection, "localhost", "msandbox", "msandbox", "trt", 5720, "/tmp/mysql_sandbox5720.sock", 0) )

and in the Library I changed the include path:

#include <my_global.h>
#include <mysql.h>

and built with
g++ -std=c++11  `/home/arhimed/mysql/5.7.20/bin/mysql_config --cflags --libs` mysql-empty-command.cpp -o tr

run with
LD_LIBRARY_PATH=/home/arhimed/mysql/5.7.20/lib/ ./trt 10000000000000

and it's running without a problem

and this is with  autoReconnectMySQL = true;

If I kill the thread from mysql the app stops with error as expected

I now killed it and recompiled with autoReconnectMySQL = false ; and will let it run for some hours but I'm having issues reproducing the problem

all best
Bogdan

I would nominate you for the Nobel price if you could reproduce it with the MCVE.

As I describe in the links the MCVE does not work WHEN IT IS EMBEDDED into my project. There is a very clear note regarding this.
You don't have to run it for hours. After few seconds - WHEN IT IS EMBEDDED INTO MY PROJECT - it already fails.

As I write there, it looks like I run into some very strange static initialization issues.

May be you study the Stackoverflow thread and the chat and then you save lot of your time. You can cancel the running test.

I could send you a video where you see how the program stops, but I don't know how it should help you. Unfortunately there is no way I can provide you the project in which the MCVE fails.

Hi Peter.

> I would nominate you for the Nobel price if you 
> could reproduce it with the MCVE.

You and I have a very different understanding of what MCVE means!
MCVE = Minimal, Complete, and Verifiable example

so this is neither complete nor verifiable?!

> As I describe in the links 

I really don't plan to chase your issue trough different forums you posted the problem. This is MySQL Bug tracking system. I see what's in it. Chasing your issue trough different third party forums really is not something I can do. You said you have MCVE but you don't. The code you actually posted don't do nothing?! So how do you believe I can verify the existance of the bug if the code you call MCVE does not show the bug?

>  There is a very clear note regarding this.

Not in this report. 

Thanks
Bogdan

In 8.0.11 this bug has been fixed. The bug is in the C API library 5.7.22. 

1) Running 8.0.11 C API library against the MySQL 5.7.22 server works.
2) Running 5.7.22 C API library against the MySQL 8.0.11 server fails.

May be your programmer can now easier find the problem so it is fixed in C API library 5.7.23

Guys,

as programmer I understand how unbelievable difficult it may be to find a bug.

I propose this way how you may find this bug easier:
1) I would provide you - not here, but to an email address - the executable where this bug is shown.
2) I would provide additional information regarding the needed environment. This is the gcc version and the path structure. If it is easier for you I can upload all files from the path structure. Due to the fact it is "isolated" it doesn't modify the existing distribution directories and files. This is - compressed - ~320 MB.

What do you think?