Bug #89076 Application has timeout exception,Still find queries getting executed on backend

• Submitted: 29 Dec 2017 7:39
• Modified: 22 Jan 2018 5:33
• Reporter: Chaitanya Tondlekar
• Status: Verified
• Category: Connector / NET
• Severity: S1 (Critical)
• Version: 5.7.19
• OS: Linux (AWS RDS)
• CPU Architecture: Any
• Tags: Application timeout, Execution Timeout .

[29 Dec 2017 7:39] Chaitanya Tondlekar

Description:
Hello All,

We have found some undesired behaviour of MySQL.

We observed that when any request from application(.Net/Java) has timeout exception, we still find the queries getting executed on back end.

For .Net Application,we are using Mysql.data(version 6.10) driver.

We have kept command timeout for 10 secs while our SP gets executed in 30 secs,
then .Net application gives the timeout exception after 10 seconds while the query is getting executed on back end till it finishes its execution.

At the back end, we observed the state as "query end" for the query which is getting executed.

For Java Application,we are using JDBC Driver.

We have kept command timeout for 10 secs while our SP gets executed in 30 secs,
then java application does not give timeout exception after 10 seconds while the query is getting executed on back end till it finishes its execution.

At the back end, we observed the state as "query end" for the query which is getting executed.

when query execution completes, java gives timeout exception.

How to repeat:
Table: inserttest

Create Table: CREATE TABLE `inserttest` (
  `data` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Stored Procedure:

DELIMITER $$
USE `testing`$$
DROP PROCEDURE IF EXISTS `USP_TestingInserts`$$
CREATE DEFINER=`root`@`%` PROCEDURE `USP_TestingInserts`()
BEGIN
		DECLARE i INT DEFAULT 0;
		WHILE i <= 5000000 DO
				INSERT INTO `inserttest`(`data`)
				VALUES
				('ABC');
				-- SELECT LAST_INSERT_ID();	
				-- if (i=100) then
-- 				begin
-- 					DO SLEEP (100);
-- 				end;
-- 				end if;
			SET i = i + 1;
		END WHILE;
	END$$
DELIMITER ;

.Net Application code:

try
            {
                using (MySqlConnection con = new MySqlConnection(constr))
                {
                    con.Open();
                    using (MySqlCommand cmd = new MySqlCommand("USP_TestingInserts", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = 10;
                        cmd.ExecuteNonQuery();
                    }                    
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception ex " + ex.Message);
                //throw;
            }

Java Application code

try  {
            Connection conn = DriverManager.getConnection(dbURL, user, password);

            Statement statement = conn.createStatement();
            statement.setQueryTimeout(10);
            statement.executeQuery("call USP_TestingInserts()");
            System.out.println("Stored procedure called successfully!");

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

[29 Dec 2017 11:21] Chiranjeevi Battula

Hello Chaitanya,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2015 with Connector/NET 6.10.5 version.

Thanks,
Chiranjeevi.

[29 Dec 2017 11:22] Chiranjeevi Battula

Screenshot

Attachment: Bug_89076.png (image/png, text), 138.07 KiB.

[29 Dec 2017 11:22] Chiranjeevi Battula

Screenshot

Attachment: Bug_89076_1.png (image/png, text), 89.90 KiB.

[22 Jan 2018 5:33] Chaitanya Tondlekar

Hello Chiranjeevi Battula,

Is there any to any alternate solution/way to mitigate the above issue?

If yes, kindly help us on the same.

[11 Jan 2021 9:56] Liviu Chircu

I am having a similar problem using the C driver (libmysqlclient).  I can reproduce the issue 100%, by using a 2-second MYSQL_OPT_READ_TIMEOUT on the following query:

"INSERT INTO tb VALUES(1, 1, SLEEP(2))"

I am using auto-commit.  The query fails due to time out, however it is still executed on the backend!  This causes issues on the application side, as it then retries the query and gets a "duplicate PRIMARY KEY" error 1062.

Is there any way to fix this without switching to transactions? "BEGIN TRANSACTION", ..., "COMMIT".  Thank you!