|
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)
|
|