Re: signal handling in mysql cli

Hello Sebastien,

You should stop top-posting, it inverts the flow of your investigation 
making it harder to follow. (see bottom)

On 6/21/2016 12:45 PM, Sebastien FLAESCH wrote:
> The process list show my (killed) thread as follows:
>
> mysql> show processlist;
> +----+---------+-----------------+-------+---------+------+----------+------------------+
>
> | Id | User    | Host            | db    | Command | Time | State    |
> Info             |
> +----+---------+-----------------+-------+---------+------+----------+------------------+
>
> | 20 | root    | localhost:48203 | test1 | Query   |    0 | starting |
> show processlist |
> | 21 | mysuser | localhost:48209 | test1 | Sleep   |  182 |          |
> NULL             |
> +----+---------+-----------------+-------+---------+------+----------+------------------+
>
>
> Why it is in "Sleep" state?!?
>
> Seb
>
> On 06/21/2016 06:27 PM, Sebastien FLAESCH wrote:
>> FYI, I get the same problem with MySQL 5.7.13.
>>
>> Seb
>>
>> On 06/21/2016 04:59 PM, Sebastien FLAESCH wrote:
>>> Seems that after KILL QUERY mysql-thread-id, a call to
>>> mysql_stmt_close(stmt-handle) hangs...
>>>
>>> This did not happen in 5.6 ...
>>>
>>> Will try 5.7.13 ...
>>>
>>> Seb
>>>
>>> On 06/21/2016 04:03 PM, Sebastien FLAESCH wrote:
>>>> Hi all,
>>>>
>>>> The technique described in this thread is working fine with MySQL
>>>> 5.6 (libmysqlclient).
>>>>
>>>> Basically, in a SIGINT signal handler, we establish a new connection
>>>> to perform a
>>>>
>>>>     KILL QUERY pid
>>>>
>>>> ...
>>>>
>>>> But with 5.7 (5.7.11) we get now a different result:
>>>>
>>>> A) The query is still interrupted, but we no longer get an SQL error
>>>> -1317.
>>>>
>>>> B) For some reason, the program does not want to exit() - (must
>>>> investigate)
>>>>
>>>> Any clues?
>>>>
>>>>
>>>> With mysql it's working fine:
>>>>
>>>> mysql> select sleep(10);
>>>> ^C^C -- query aborted
>>>> +-----------+
>>>> | sleep(10) |
>>>> +-----------+
>>>> +-----------+
>>>> 1 row in set (2.79 sec)
>>>>
>>>> mysql> \q
>>>> Bye
>>>>
>>>>
>>>>
>>>> Thanks!
>>>> Seb
>>>>
>>>> On 12/03/2014 05:25 PM, Sebastien FLAESCH wrote:
>>>>> Hi all,
>>>>>
>>>>> I have a similar question regarding KILL QUERY usage:
>>>>>
>>>>> We have a C client program using libmysqlclient.so, it is a
>>>>> single-threaded program.
>>>>>
>>>>> When running a long query, how can I send the KILL QUERY command
>>>>> when a SIGINT (CTRL-C)
>>>>> is caught? (of course we implement a signal handler, so we keep the
>>>>> control)
>>>>>
>>>>> => Is is safe to establish a new connection to the server in the
>>>>> signal handler, using
>>>>> mysql_init() + mysql_real_connect(), and execute the KILL QUERY
>>>>> with the mysql thread
>>>>> id I got from the initial mysql_init() / mysql_real_connect()?
>>>>>
>>>>> I made some tests, and it seems to work fine, the long query
>>>>> returns SQL error -1317:
>>>>> "Query execution was interrupted" (which is expected)
>>>>>
>>>>> We want to support SQL interruption properly, so please someone
>>>>> from the dev team,
>>>>> give me a clear answer... I don't want to use a side effect or
>>>>> undocumented feature.
>>>>>
>>>>> Doing all this stuff in a signal handler is certainly risky... no?
>>>>>
>>>>> I could not find the information in the documentation
>>>>> (mysql_real_connect).
>>>>>
>>>>> I wish there would be an API like mysql_cancel_query(), similar to
>>>>> Oracle's OCI
>>>>> OCIBreak().
>>>>>
>>>>> Thanks!
>>>>> Seb
>>>>>
>>>>>
>>>>>
>>>>> On 12/02/2014 05:13 PM, walter harms wrote:
>>>>>> hi list,
>>>>>>
>>>>>> when i use CTRL-C to break a query that works fine in interactive
>>>>>> mode.
>>>>>>
>>>>>> mysql> select sleep(10) ;
>>>>>> ^CCtrl-C -- sending "KILL QUERY 24289" to server ...
>>>>>> Ctrl-C -- query aborted.
>>>>>> +-----------+
>>>>>> | sleep(10) |
>>>>>> +-----------+
>>>>>> +-----------+
>>>>>> 1 row in set (0.86 sec)
>>>>>>
>>>>>> but when i use the noninteractive mode
>>>>>>    timeout 5 mysql -BAN -e "select now(); select sleep (100) ;
>>>>>> select now() "
>>>>>>
>>>>>> i looks like that  but "show full processlist;" shows otherwise
>>>>>> and that is true
>>>>>> as a list of long running querys showed.
>>>>>>
>>>>>> Is there a way to make it behave like the interactive version ?
>>>>>> Now it is a bit confusing for everyone.
>>>>>>
>>>>>> re,
>>>>>>   wh
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

You did not KILL the entire client session(CONNECTION), you only killed 
the QUERY that the session was executing. The client remains connected 
and the session remains active. The reason it is in Sleep state is 
because the server is waiting for the client to send its next command.

http://dev.mysql.com/doc/refman/5.7/en/kill.html

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.

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