How to intercept a long query in mysql command line tool without exiting mysql?

Question

When debugging SQL statements, if I accidentally execute a query using the mysql command line, which is displayed with lots of results (even if the query itself is executed within a reasonable amount of time), the only way I know is to stop the endless stream from exiting - CTRL-C.

Unfortunately, this brings me back to the shell, forcing me to log in and select the database again.

To avoid this, I started running mysql with the -sigint-ignore option, so CTRL-C is ignored.

Now I need a way to interrupt the output of these long queries.

Is there a keyboard shortcut that will do this?

Answer 1

You can use --pager to transfer your output to a pager, such as less , which will give you control over the output. Not just killing him, but swapping, searching and even saving output is better than your terminal window.

There is also a switch --safe-updates or -U aka --i-am-a-dummy , which protects you from classless update and delete , and also automatically sets a limit of 1000 (changes using select_limit).

All this can be set by default in ~/.my.cnf .

 [mysql] pager safe-updates 

Answer 2

Not a keyboard shortcut.

The only choice is to open another session, use SHOW PROCESSLIST , and then KILL QUERY , which you want to end.

You can also use the mysqladmin command line tool to execute these commands.

In any case, you need to log in. Thus, there are not many advantages, just hitting Ctrl-C.

Answer 3

From current mysql docs:

Starting with MySQL 5.1.10, typing Control-C causes mysql to try to kill the current report. If this cannot be or Control-C is typed again before the instruction is killed, mysql exits. Control-C used to call mysql to exit in all cases.

Since I used version 5.0.67, it seems mysql update would be a better solution. However, I accepted Schwern's answer because it was quickly implemented and works like a dream.

Answer 4

A bit late, but maybe my answer will help someone.

A way to kill a specific mysql query through the command line:

• Identify the process ID of this request with the command: mysqladmin processlist
• Assuming Id is 5736748 to kill the query you should enter: mysqladmin kill 5736748

Answer 5

Use mysql --sigint-ignore

and clear the line, use control + U