[CONJ-1039] setQueryTimeout not honored by CallableStatement for procedures depending on security context (2.7 only) - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 2.7.7
Fix Version/s: 2.7.8
Component/s: Other
Labels:
None

Description

When as a non-SUPER user calling a stored procedure with a DEFINER having full privileges and DEFINER security context no timeout happens when using CallableStatement with setQueryTimeout set.

It is only this combination that leads to no timeout being triggered.
If the connection user has full privileges: times out just fine
If the procedure has a non-full priv. definer, or INVOKER security context: times out just fine
Using regular Statment instead of CallableStatement: times out just fine
C/J 3.x instead of 2.7.x: times out just fine.

How to reproduce:

SQL setup

CREATE USER user@localhost IDENTIFIED BY 'Secret23!';

GRANT ALL ON test.* TO user@localhost;

DELIMITER //

CREATE DEFINER=root@localhost PROCEDURE p_r_d() SQL SECURITY DEFINER

BEGIN

	SELECT SLEEP(60), "hello";

END;

//

CREATE DEFINER=root@localhost PROCEDURE p_r_i() SQL SECURITY INVOKER

BEGIN

	SELECT SLEEP(60), "hello";

END;

//

CREATE DEFINER=user@localhost PROCEDURE p_u_d() SQL SECURITY DEFINER

BEGIN

	SELECT SLEEP(60), "hello";

END;

//

CREATE DEFINER=user@localhost PROCEDURE p_u_i() SQL SECURITY INVOKER

BEGIN

	SELECT SLEEP(60), "hello";

END;

//

DELIMITER ;

Java test code:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

import java.sql.CallableStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class CS0492599 {

    public static void run_query(String title, String query, String connect_url) {

	System.out.print(title);

	System.out.print(" -> ");

	try {

	    Connection con = DriverManager.getConnection(connect_url);

            Statement st = con.createStatement();

	    st.setQueryTimeout(10);

            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {

                System.out.print(rs.getString(1));

                System.out.print(": ");

                System.out.println(rs.getString(2));

            con.close();

            System.out.println();

        } catch (SQLException e) {

            System.out.println(e.getMessage());

    public static void run_callable(String title, String query, String connect_url) {

	System.out.print(title);

	System.out.print(" -> ");

	try {

	    Connection con = DriverManager.getConnection(connect_url);

            CallableStatement st = con.prepareCall(query);

	    st.setQueryTimeout(10);

            boolean rs = st.execute();

	    if (rs) {

		System.out.print("success");

	    } else {

		System.out.print("fail");

            con.close();

            System.out.println();

        } catch (SQLException e) {

            System.out.println(e.getMessage());

    public static void main(String[] args){

	String connect_url = "jdbc:mariadb://localhost/test"

	    + "?user=user"

	    + "&password=Secret23!";

	System.out.println("using Statement:");

	run_query("root, definer", "CALL p_r_d;", connect_url);

	run_query("root, invoker", "CALL p_r_i;", connect_url);

	run_query("user, definer", "CALL p_u_d;", connect_url);

	run_query("user, invoker", "CALL p_u_i;", connect_url);

	System.out.println("using CallableStatement:");

	run_callable("root, definer", "CALL p_r_d;", connect_url);

	run_callable("root, invoker", "CALL p_r_i;", connect_url);

	run_callable("user, definer", "CALL p_u_d;", connect_url);

	run_callable("user, invoker", "CALL p_u_i;", connect_url);

Using 2.7.7 this leads to:

using Statement:

root, definer -> (conn=52) Query execution was interrupted (max_statement_time exceeded)

root, invoker -> (conn=53) Query execution was interrupted (max_statement_time exceeded)

user, definer -> (conn=54) Query execution was interrupted (max_statement_time exceeded)

user, invoker -> (conn=55) Query execution was interrupted (max_statement_time exceeded)

using CallableStatement:

root, definer -> success

root, invoker -> (conn=58) Query timed out

user, definer -> (conn=60) Query timed out

user, invoker -> (conn=62) Query timed out

Using 3.0.9 or 3.1.0:

using Statement:

root, definer -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)

(conn=64) Query execution was interrupted (max_statement_time exceeded)

root, invoker -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)

(conn=65) Query execution was interrupted (max_statement_time exceeded)

user, definer -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)

(conn=66) Query execution was interrupted (max_statement_time exceeded)

user, invoker -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)

(conn=67) Query execution was interrupted (max_statement_time exceeded)

using CallableStatement:

root, definer -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)

(conn=68) Query execution was interrupted (max_statement_time exceeded)

root, invoker -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)

(conn=69) Query execution was interrupted (max_statement_time exceeded)

user, definer -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)

(conn=70) Query execution was interrupted (max_statement_time exceeded)

user, invoker -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)

(conn=71) Query execution was interrupted (max_statement_time exceeded)

Attachments

Activity

Ascending order - Click to sort in descending order

Diego Dupin added a comment - 2023-01-11 13:13 - edited

Query Timeout are handle this way :

when using Statement and client side prepared statement, connector will prefix "SET STATEMENT max_statement_time=XXX FOR " to commands, either in 2.x and 3.x version (when server permit this command)
same for Server side prepared statement in 3.x

When using CallableStatement, underlying, connector use server side prepare statement, in order to retrieve output parameters if any.

The thing is that 3.x is a complete rewrite, and server side prepared statement now use pipelining and assume prepare command will succeed (expect server > 10.6 - if not the case, it still will work, but will be a little less performant). That permits to use "SET STATEMENT max_statement_time=XXX FOR <command>" easily

in 2.x, that's different, PREPARE command is immedialty executed when creating CallableStatement, not assuming that PREPARE won't fails. setQueryTimeout() can only be set after CallableStatement creation, so that would means closing previous PREPARE and re-PREPARE with "SET STATEMENT max_statement_time=XXX FOR" prefix.
So current implementation is having an internal timeout, that when reaching timeout will executes a "KILL QUERY XX" statement on another connection.

The problem in this specific case is that the command "KILL QUERY xx" ends with an error "You are not the owner of thread xx".

CALL statement can be prepared without issue. So specifically when using CallableStatement, connector can work partially the same way than 3.0, solving this problem.

Diego Dupin added a comment - 2023-01-11 13:13 - edited Query Timeout are handle this way : when using Statement and client side prepared statement, connector will prefix "SET STATEMENT max_statement_time=XXX FOR " to commands, either in 2.x and 3.x version (when server permit this command) same for Server side prepared statement in 3.x When using CallableStatement, underlying, connector use server side prepare statement, in order to retrieve output parameters if any. The thing is that 3.x is a complete rewrite, and server side prepared statement now use pipelining and assume prepare command will succeed (expect server > 10.6 - if not the case, it still will work, but will be a little less performant). That permits to use "SET STATEMENT max_statement_time=XXX FOR <command>" easily in 2.x, that's different, PREPARE command is immedialty executed when creating CallableStatement, not assuming that PREPARE won't fails. setQueryTimeout() can only be set after CallableStatement creation, so that would means closing previous PREPARE and re-PREPARE with "SET STATEMENT max_statement_time=XXX FOR" prefix. So current implementation is having an internal timeout, that when reaching timeout will executes a "KILL QUERY XX" statement on another connection. The problem in this specific case is that the command "KILL QUERY xx" ends with an error "You are not the owner of thread xx". CALL statement can be prepared without issue. So specifically when using CallableStatement, connector can work partially the same way than 3.0, solving this problem.

Diego Dupin added a comment - 2023-01-18 18:06

correction available through snapshot :

<repositories>

    <repository>

        <id>sonatype-nexus-snapshots</id>

        <name>Sonatype Nexus Snapshots</name>

        <url>https://oss.sonatype.org/content/repositories/snapshots</url>

    </repository>

</repositories>

<dependencies>

    <dependency>

        <groupId>org.mariadb.jdbc</groupId>

        <artifactId>mariadb-java-client</artifactId>

        <version>2.7.8-SNAPSHOT</version>

    </dependency>

</dependencies>

Diego Dupin added a comment - 2023-01-18 18:06 correction available through snapshot : <repositories> <repository> <id>sonatype-nexus-snapshots</id> <name>Sonatype Nexus Snapshots</name> <url>https: //oss.sonatype.org/content/repositories/snapshots</url> </repository> </repositories> <dependencies> <dependency> <groupId>org.mariadb.jdbc</groupId> <artifactId>mariadb-java-client</artifactId> <version> 2.7 . 8 -SNAPSHOT</version> </dependency> </dependencies>

MariaDB Connector/J

setQueryTimeout not honored by CallableStatement for procedures depending on security context (2.7 only)

Details

Description

Attachments

Activity

People

Dates

Git Integration