Skip to content

Long query gets stuck despite max_statement_time being set  #23

@bgedik

Description

@bgedik

I am on Ubuntu 18, using oursql 0.9.3.2, and /usr/lib/x86_64-linux-gnu/libmysqlclient.so.20

I am using oursql to connect to 2 different servers: MariaDB 10.1.38 and MariaDB 10.3.15:

Expected behavior (MariaDB 10.1.38):

conn = oursql.connect(host='maridb-10.1.38', ...)
curs = conn.cursor() 
curs.execute("SET SESSION max_statement_time=1")
curs.execute("SHOW variables like 'max_statement_%'")
curs.fetchall()
>> [(u'max_statement_time', u'1.000000')]
curs.execute(long_query)
curs.fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "oursqlx/cursor.pyx", line 122, in oursql.Cursor.execute (oursqlx/oursql.c:20330)
  File "oursqlx/statement.pyx", line 402, in oursql._Statement.execute (oursqlx/oursql.c:13119)
  File "oursqlx/statement.pyx", line 127, in oursql._Statement._raise_error (oursqlx/oursql.c:9947)
oursql.UnknownError: (1969, 'Query execution was interrupted (max_statement_time exceeded)', None)

Unexpected behavior (MariaDB 10.3.15):

conn = oursql.connect(host='maridb-10.3.15', ...)
curs = conn.cursor()
curs.execute("SET SESSION max_statement_time=1")
curs.execute("SHOW variables like 'max_statement_%'")
curs.fetchall()
>> [(u'max_statement_time', u'1.000000')]
curs.execute(long_query)
curs.fetchall()
... Gets stuck

I have 3 interesting observations:

  • looking at 'processlist' on the server side confirms that the query was actually canceled (connection is in sleep mode)
  • running the same sequence using the 'mysql' CLI of MariaDB 10.1.38 against the MariaDB 10.3.15 server works fine (query is interrupted).
  • running the same sequence using libmysqlclient from C code works.

Any hints as to how to debug this will be highly appreciated.

Below is my C++ program that executes the same using libmysqlclient:

#include <my_global.h>
#include <mysql.h>

#include <iostream>
using namespace std;

int finish_with_error(MYSQL *con)
{
    cerr << mysql_error(con) << endl;
    mysql_close(con);
    return 1;
}

int main()
{
    cout << "MySQL client version: " << mysql_get_client_info() << endl;

    MYSQL *con = mysql_init(NULL);

    if (con == NULL)
    {
        cerr << "mysqlinit() failed" << endl;
        return 1;
    }

    if (mysql_real_connect(con, "maridb-10.3.15", "<user>", "<password>", "<database>", 0, NULL, 0) == NULL)
    {
        return finish_with_error(con);
    }

    if (mysql_query(con, "SET SESSION max_statement_time=1"))
    {
        return finish_with_error(con);
    }

    if (mysql_query(con, "<long running query>"))
    {
        return finish_with_error(con);
    }

    MYSQL_RES *result = mysql_store_result(con);
    if (result == NULL)
    {
        return finish_with_error(con);
    }
    mysql_free_result(result);

    mysql_close(con);
    return 0;
}
g++ -o test test.cpp $(mysql_config --cflags --libs)
./test

MySQL client version: 5.7.26
Query execution was interrupted (max_statement_time exceeded)

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions