mysql_close doesn't kill locked sql requests
- by Nikita
I use mysqld Ver 5.1.37-2-log for debian-linux-gnu
I perform mysql calls from c++ code with functions mysql_query.
The problem occurs when mysql_query execute procedure, procedure locked on locked table, so mysql_query hangs. If send kill signal to application then we can see lock until table is locked.
Create the following SQL table and procedure
CREATE TABLE IF NOT EXISTS `tabletolock`
(
`id` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
)ENGINE = InnoDB;
DELIMITER $$
DROP PROCEDURE IF EXISTS `LOCK_PROCEDURE` $$
CREATE PROCEDURE `LOCK_PROCEDURE`()
BEGIN
SELECT id INTO @id FROM tabletolock;
END $$
DELOMITER;
There are sql commands to reproduce the problem:
1. in one terminal execute lock tables tabletolock write;
2. in another terminal execute call LOCK_PROCEDURE();
3. In first terminal exeute show processlist and see
| 2492 | root | localhost |
syn_db | Query | 12 | Locked |
SELECT id INTO @id FROM tabletolock |
Then perfrom Ctrl-C in second terminal to interrupt our procudere and see processlist again. It is not changed, we already see locked select request and can teminate it by unlock tables or kill commands.
Problem described is occured with mysql command line client.
Also such problem exists when we use functions mysql_query and mysql_close.
Example of c code:
#include <iostream>
#include <mysql/mysql.h>
#include <mysql/errmsg.h>
#include <signal.h>
// g++ -Wall -g -fPIC -lmysqlclient dbtest.cpp
using namespace std;
MYSQL * connection = NULL;
void closeconnection()
{
if(connection != NULL)
{
cout << "close connection !\n";
mysql_close(connection);
mysql_thread_end();
delete connection;
mysql_library_end();
}
}
void sigkill(int s)
{
closeconnection();
signal(SIGINT, NULL);
raise(s);
}
int main(int argc, char ** argv)
{
signal(SIGINT, sigkill);
connection = new MYSQL;
mysql_init(connection);
mysql_options(connection, MYSQL_READ_DEFAULT_GROUP, "nnfc");
if (!mysql_real_connect(connection, "127.0.0.1", "user", "password",
"db", 3306, NULL, CLIENT_MULTI_RESULTS))
{
delete connection;
cout << "cannot connect\n";
return -1;
}
cout << "before procedure call\n";
mysql_query(connection, "CALL LOCK_PROCEDURE();");
cout << "after procedure call\n";
closeconnection();
return 0;
}
Compile it, and perform the folloing actions:
1. in first terminal local tables tabletolock write;
2. run program ./a.out
3. interrupt program Ctrl-C. on the screen we see that closeconnection function is called, so connection is closed.
4. in first terminal execute show processlist and see that procedure was not intrrupted.
My question is how to terminate such locked calls from c code?
Thank you in advance!
