How do I Interupt a long query in the mysql command line tool without quitting mysql?

Question

While debugging SQL statements, if I accidentally execute a query in using the mysql command line that outputs at lot of results (even if the query itself executes in reasonable time), the only way I know of to stop the endless stream of output is CTRL-C.

Unfortunately this puts me back in the shell, forcing me to login and select the database again.

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

Now I'd like a way to interrupt the output of those long queries.

Is there a keyboard shortcut that will do this?

Answer 1

Not a keyboard shortcut.

The only choice is to open another session, use SHOW PROCESSLIST and then KILL QUERY the one you want to terminate.

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

Either way, it requires you to login. So it's not much of an advantage over just hitting Ctrl-C.

Answer 2

You can use --pager to have your output passed to a pager such as less which will give you control over the output. Not just killing it, but also paging, searching and even storing the output better than your terminal window gives you.

There's also the --safe-updates or -U switch aka --i-am-a-dummy which protects you from clauseless updates and deletes and also auto limits selects to 1000 (modifyable with select_limit).

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

[mysql]
pager
safe-updates

Answer 3

From the current mysql docs:

As of MySQL 5.1.10, typing Control-C causes mysql to attempt to kill the current statement. If this cannot be done, or Control-C is typed again before the statement is killed, mysql exits. Previously, Control-C caused mysql to exit in all cases.

Since I was using version 5.0.67 seems that updating mysql would be the best fix. However I have accepted Schwern's answer because it was quick to implement and works like a dream.