MySQL Bugs: #112023: executeQuery() function get stuck on disconnection of MySQL Server

Bug #112023	executeQuery() function get stuck on disconnection of MySQL Server
Submitted:	10 Aug 2023 3:39	Modified:	19 Sep 2023 10:46
Reporter:	DURGESH KUMAR	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / C++	Severity:	S2 (Serious)
Version:	mysql-connector-c++-8.1.0-linux-glibc2.2	OS:	Red Hat (RHEL 8.3)
Assigned to:		CPU Architecture:	x86
Tags:	executeQuery()

Description:
I am using libmysqlcppconn.so lib (mysql-connector-c++-8.1.0-linux-glibc2.2) for executing query with MySQL Server (v 8.0.21) and if during runtime I turn off MySQL Server (by removing Ethernet cable), the executeQuery() function does not throw any exception and it got stuck for a very long time. If I check output of 'ss | grep mysql' on OS, the mysql connection remains in ESTAB state even after the disconnection.
Please provide solution of throwing exception related to timeout/session-close on disconnection of MySQL server.

How to repeat:
During calling of executeQuery() remove Ethernet cable between client and Server and observe the output of 'ss | grep mysql'.

Suggested fix:
executeQuery() function shall throw Exception on disconnection of MySQL Server during runtime.

Hi,

There are many timeouts in MySQL Server that you can use to have the TCP/IP link disconnected.

They are all described in our Reference Manual.

Tune out all of the relevant ones and let us know if the behaviour has changed.

We are waiting on your feedback.

I tried wait_timeout and interactive_timeout variable at Server side but no improvement on client side. I am not getting Exception and executeQuery() is getting stuck forever. 
Please find the code section:

	m_mysqlStatement = NULL;
	m_mysqlPreparedStatement = NULL;
	m_databaseConnection = NULL;
	m_resultSet = NULL;
	m_tableName="LOG";
        m_databaseServerIP="192.0.0.94";
	m_databaseServerPort="3306";
	url="tcp://"+m_databaseServerIP+":"+m_databaseServerPort;

	const string strUser(USER);
	const string strPassword(PASSWORD);
	const string strDatabase(DATABASE);

    m_driver = get_driver_instance();
	/* create a database databaseConnectionnection using the Driver */
    m_databaseConnection = m_driver -> connect(url, strUser, strPassword);
	/* turn off the autocommit */
    m_databaseConnection -> setAutoCommit(1);
	/* select appropriate database schema */
    m_databaseConnection -> setSchema(strDatabase);
    m_mysqlStatement = m_databaseConnection->createStatement();

try
	{
        log="..... MySQL INSERT Query"

            m_mysqlStatement -> executeQuery (log);
    }
	catch (SQLException &e)
	{
			if (e.getErrorCode() != 0)
			{
        cout << "ERROR: SQLException in " << __FILE__;
        cout << " (" << __func__<< ") on line " << __LINE__ << endl;
        cout << "ERROR: " << e.what();
        cout << " (MySQL error code: " << e.getErrorCode();
        cout << ", SQLState: " << e.getSQLState() << ")" << endl;

			   /*
				Error: 1047 SQLSTATE: 08S01 (ER_UNKNOWN_COM_ERROR)
				Message: Unknown command
				*/
		if (e.getErrorCode() == 1047)
		{
		cout << "\nYour server does not seem to support Prepared Statements at all. ";
		}
/*if error code=2006 point logDest to file*/
		else if (e.getErrorCode() == 2006)
		{
				return 3;
		}
        /*if error code=2013 point logDest to file*/
        /*Error Code 2013 : Lost connection to MySQL server during query*/
        else if (e.getErrorCode() == 2013) 
         {
                return 3;
          }
	}
	}

catch (std::runtime_error &e)
	{
		cout << "ERROR: runtime_error in " << __FILE__;
		cout << " (" << __func__ << ") on line " << __LINE__ << endl;
		cout << "ERROR: " << e.what() << endl;
		
	}

Hi Mr. KUMAR,

Have you tried the following timeouts on the server side:

* connect_timeout
* interactive_timeout
* lock_wait_timeout
* innodb-lock_wait_timeout
* net_read_timeout
* net_write_timeout
* lock_wait_timeout
* ssl-session-cache-timeout
* max-execution-time

Let us know how does it go .....

Hi Durgesh,

First of all, we need to correct the severity of the bug. S1 corresponds to a complete loss of service, a significant functionality is missing, a system that hangs indefinitely; and there is no available workaround.

Although the operation freezes for a long time, unplugging the cable does not look like a normal operation. Therefore, the severity is set to S2.

Now about the problem you reported:

Did you try using the timeout options (OPT_READ_TIMEOUT and OPT_WRITE_TIMEOUT) for Connector/C++?

For more information you can check this online manual page:

https://dev.mysql.com/doc/connector-cpp/1.1/en/connector-cpp-connect-options.html

Also, please check the previous comment from Sinisa and his suggestions about the server timeouts. This will help with moving the progress of this bug.

Thanks.

example2

Attachment: example2.zip (application/zip, text), 446.95 KiB.

Dear MySQL Team,
Thanks for your suggestion. 
I tried OPT_WRITE_TIMEOUT and OPT_READ_TIMEOUT using ConnectOptionsMap.

OPT_WRITE_TIMEOUT is not working but OPT_READ_TIMEOUT is working.

I tried Example 2 (https://dev.mysql.com/doc/connector-cpp/1.1/en/connector-cpp-examples-complete-example-2.h...) with slight modification.

Please find attached file (example2.zip) for summary.

Although, ConnectOptionsMap method of initiating a session with OPT_READ_TIMEOUT is working, but please tell the reason why the following method of initiating a connection is not throwing Exception on unplugging the cable.

  con = driver->connect("tcp://192.0.0.93:3306", "username", "password");

Dear MySQL Team,
I would appreciate your reply on my last query.
Also, please provide detailed information about OPT_READ_TIMEOUT and OPT_WRITE_TIMEOUT (user manual has very limited information).
If I am using OPT_READ_TIMEOUT while initiating a connection, I am getting time out in fetching data from MySQL Server(if the query execution time is higher) but it is working fine with my earlier requirement of generating Exception on disconnection.

Dear MySQL Team,
Please reply to previously asked queries.

Hi Durgesh,

Thank you for your reply.

The Connector/C++ legacy (or JDBC) driver is using MySQL C API and libmysqlclient library for the network communication. Whenever libmysqlclient returns errors (due to timeouts or other reasons) the Connector/C++ throws the corresponding exceptions.

The Connector options OPT_WRITE_TIMEOUT and OPT_READ_TIMEOUT are being translated to libmysqlclient options MYSQL_OPT_WRITE_TIMEOUT and MYSQL_OPT_READ_TIMEOUT.

You can read more details on this online manual page:

https://dev.mysql.com/doc/c-api/8.0/en/mysql-options.html

This is a simplified picture about unplugging the cable: it is the default behavior of libmysqlclient (and therefore Connector/C++) that it relies on TCP socket to report reading or writing errors due to timeouts or other issues. If the socket is waiting on incoming packets (reading mode) it does not try to ping the server to check if the connection is still valid. From this perspective it is no different than waiting on a long operation on the server. That is why you are getting timeouts even when a connection is intact, but the server takes longer time to respond.

Due to the above limitation of MySQL network protocol the only way to detect problems and avoid hanging connections is setting the timeouts.
The reading and writing timeouts have to be not too short to trigger errors for longer server operations, but they must be not too long either to be more responsive in case of networking issues such as physical disconnect events.

Here is a bug report for your reference, which describes what happens on C API level:

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

It is expected behavior and timeout options allow controlling the waiting time during read/write operations.
Closing this report as "Not a bug".