Skip to content

Long query gets stuck despite max_statement_time being set  #23

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)

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

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