MySQL Bugs: #86058: Issue calling mysql_stmt_execute and mysql_real

Bug #86058	Issue calling mysql_stmt_execute and mysql_real_connect from different thread
Submitted:	24 Apr 2017 16:15	Modified:	26 Apr 2017 21:53
Reporter:	Vishnu Rajendran	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	5.1.73	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When attempting a connection on one thread while a query is executing in another thread via mysql_stmt_execute, I am seeing an issue where the thread trying to connect fails, tries to reconnect, and then the other thread that called mysql_stmt_exeucte fails and cashes due to what appear. There are usually two types of crashes, with the following coredumps (one happens to be from valgrind, but either can occur in either case):

Dump 1: From valgrind
Process terminating with default action of signal 11 (SIGSEGV): dumping core
Access not within mapped region at address 0x3
at 0x66D804: my_real_read (net.c:897)
by 0x66DCA4: my_net_read (net.c:1005)
by 0x668589: cli_safe_read (client.c:691)
by 0x669298: cli_read_query_result (client.c:2816)
by 0x64C861: execute (libmysql.c:2490)
by 0x64CFDF: cli_stmt_execute (libmysql.c:2575)
by 0x64DC90: mysql_stmt_execute (libmysql.c:2921)
// beyong this is code that is part of company specific libraries, so it will probably not be helpful

Dump 2: From a normal crash
#0  0x000000000066cf9f in my_real_read (net=0x7fb31800e700, complen=0x7fb32afd8b60) at net.c:818
#1  0x000000000066d305 in my_net_read (net=0x0) at net.c:1005
#2  0x0000000000667bea in cli_safe_read (mysql=0x0) at client.c:691
#3  0x000000000066a2fa in cli_advanced_command (mysql=0x7fb31800e700, command=<value optimized out>, header=0x0, header_length=0, arg=0x0, arg_length=0, skip_check=0 '\000', stmt=0x0) at client.c:824
#4  0x0000000000647861 in mysql_ping (mysql=0x0) at libmysql.c:1422
// company specific library functions

I have followed the steps that are listed here as well:
https://dev.mysql.com/doc/refman/5.7/en/c-api-threaded-clients.html
Where I call mysql_library_init() in my main thread before I initialize the database objects that go on to call mysql_init(). While this documentation also states that mysql_init() will call mysql_thread_init(), I also tried calling it separately, directly after mysql_init() is called. These steps have not worked for me, so I tried locking around all the places where the mysql api functions, and this has solved the issue. The problem is, now the queries are not getting issued concurrently, which defeats the purpose of having multiple db connections. Each thread also has its own MYSQL pointer, so I am not sure what the issue is with me trying to connect from one thread, while sending a query in the other. The connections even show up in SQLyog as two separate connections to the same database, similar to if there where 2 processes that are connected to the database at the same time. Please let me know what the issue is here and what steps I should take to allow multiple threads from a single process to be able to connect and query the database concurrently, if possible.

How to repeat:
Create 2 separate MYSQL pointers in two separate threads and attempt to use (most likely any) mysql C API function from those threads concurrently. 

Suggested fix:
I am not sure what the fix should be. I initially started to use valgrind because I saw that this was due a double free error. I then noticed that this wasn't happening in our library and found that it occurred only when multiple threads where trying to access the database. My guess is that the double free is just a symptom of how I am trying to use the library. It appears that this library can be used in a multithreaded manner without locking around the function calls. Please let me know what needs to be done. Thanks.

Hello Vishnu Rajendran,

Thank you for the bug report.
We suggest you to upgrade according how to mentioned at https://www.mysql.com/support/eol-notice.html

Thanks,
Chiranjeevi.

Do you happen to know what the issue that leads to this behavior? Was it something that was fixed in the next version? It may be difficult to push through an upgrade for this library without mentioning the reason that we want to do this. I'm not sure which release notes I should be checking. If you could give me this information, I could forward this to the team in our company that works on it and we will be able to upgrade the library.

To provide some additional info, I am using boost::asio in order to make the requests in the application. I have run a test program that uses pthread and this works. Is there a reason that the one with boost::asio cannot work with this API? As far as I know both the test application and the actual one are issuing asynchronus queries, so it is odd that one works while the other doesn't. I do know that boost::asio is blocking vs pthread which is not, although I make the test application block by adding a sleep in each thread before they receive the result. The test application is still able to work in that case.