rwired rwired - 5 months ago 18
MySQL Question

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

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

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