kill query and prepared statements

Sebastien FLAESCH <[email protected]> Tue, 10 Jan 2017 14:55:42 +0100

Newsgroups	gmane.comp.db.mysql.general
Organization	Four Js Development Tools
Message-ID	<[email protected]>

Hi all,

I have reported this problem before, but I raise it again, since I still get this problem with 5.7.17

See attached code:

I want to interrupt a long running statement with CTRL-C by starting a new connect to make a KILL QUERY.

I am using the same technique as the mysql client code.

The difference here is that my code is using PREPARED STATEMENTS with mysql_stmt_prepare() etc.

Problem: After interrupting the first query with CTRL-C, the call to mysql_stmt_close() hangs...

Maybe I am missing some new connection or statement option...?!?

IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with 5.6(.16) !!!!

Please can someone from MySQL C API team try to reproduce and confirm?

Thanks!
Seb


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

kill_prepared_query.c (text/x-csrc, 3.5 KB)

#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <signal.h>

#include <mysql.h>
#include <mysqld_error.h>


static char * c_host = "orion";
static char * c_user = "mysuser";
static char * c_auth = "fourjs";
static int    c_port = 3308;
static char * c_sock = NULL;
static char * c_dbnm = "test1";

static int executing_query;
static unsigned long thread_id;

static void kill_query(void)
{
    char cmd[50];
    MYSQL *h;
    h = mysql_init(NULL);
    if (!mysql_real_connect(h, c_host, c_user, c_auth,
                            c_dbnm, c_port, c_sock,
                            CLIENT_FOUND_ROWS)) {
        fprintf(stderr, "kill_query: Could not connect (err=%d)\n", mysql_errno(h));
        return;
    }
    sprintf(cmd, "KILL QUERY %ld", thread_id);
    if (mysql_query(h, cmd) != 0) {
        fprintf(stderr, "Could not execute %s.", cmd);
    }
    mysql_close(h);
}

static void handle_ctrlc_signal(int sig)
{
    fprintf(stdout, "SIGINT caught! executing_query = %d\n", executing_query);
    if (executing_query) {
        executing_query = 0;
        kill_query();
    }
    return;
}


int main(int argc, char ** argv)
{
    MYSQL * conn;
    MYSQL_STMT * stmt;
    int i, s;
    unsigned long ct = (unsigned long) CURSOR_TYPE_READ_ONLY;
    const char * sqlstmt = "select benchmark(1000000000, md5('when will it end?'))";

    signal(SIGINT, handle_ctrlc_signal);

    conn = mysql_init(NULL);

    if (!mysql_real_connect(conn, c_host, c_user, c_auth,
                            c_dbnm, c_port, c_sock,
                            CLIENT_FOUND_ROWS)) {
        fprintf(stderr, "Could not connect (err=%d)\n", mysql_errno(conn));
        return -1;
    }

    thread_id = mysql_thread_id(conn);
    fprintf(stdout, "MySQL thread ID: %ld\n", thread_id);

    for (i=0; i<3; i++) {

        fprintf(stdout, "\nRound %d:\n", i+1);

        fprintf(stdout, "Allocating statement handle...");
        stmt = mysql_stmt_init(conn);
        if (stmt==NULL) {
            fprintf(stderr, "Could not create statement handle (err=%d)\n", mysql_errno(conn));
            return -1;
        }
        fprintf(stdout, "   handle = %p\n", (void*) stmt);

        fprintf(stdout, "Preparing statement %p ...\n", (void*) stmt);
        s = mysql_stmt_prepare(stmt, sqlstmt, (unsigned long) strlen(sqlstmt));
        if (s!=0) {
            fprintf(stderr, "Could not prepare statement (err=%d)\n", mysql_errno(conn));
            return -1;
        }

        fprintf(stdout, "Setting cursor type to read only for %p ...\n", (void*) stmt);
        s = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void *) &ct);
        if (s!=0) {
            fprintf(stderr, "Could not set cursor type (err=%d)\n", mysql_errno(conn));
            return -1;
        }

        fprintf(stdout, "Executing statement %p ...\n", (void*) stmt);
        executing_query = 1;
        s = mysql_stmt_execute(stmt);
        if (s!=0) {
            if (mysql_errno(conn) == 1317) {
                fprintf(stdout, "Statement interrupted by user...\n");
            } else {
                fprintf(stderr, "Could not execute the query (err=%d)\n", mysql_errno(conn));
                return -1;
            }
        }

        fprintf(stdout, "Closing/freeing statement handle %p ...\n", (void*) stmt);
        s = mysql_stmt_close(stmt);
        if (s!=0) {
            fprintf(stderr, "Could not close statement handle (err=%d)\n", mysql_errno(conn));
            return -1;
        }

        fprintf(stdout, "Round done.\n");
    }

    return 0;
}