CODEWITHSUNDEEP
mysqlcommand-line
rwired
rwired

Reputation: 1133

How do I Interrupt 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?

Upvotes: 30

Views: 47236

Answers (6)

Razzi Abuissa
Razzi Abuissa

Reputation: 4122

As of MySQL 5.7 (released in 2013), ^C cancels a query in progress and returns you to the mysql shell. ^C currently only exits if there is no query running.

Previously, Control+C in mysql interrupted the current statement if there was one, or exited mysql if not. Now Control+C interrupts the current statement if there was one, or cancels any partial input line otherwise, but does not exit.

MySQL 5.7 release notes

Upvotes: 3

Jesus Iniesta
Jesus Iniesta

Reputation: 12519

Bit late, but maybe my answer will help someone.

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

  1. Run mysqladmin processlistand find the process id of the query by the command

  2. Run mysqladmin kill <query_id>.

    Assuming a query ID 123456789 you'd just need to run: mysqladmin kill 123456789

Upvotes: 5

John Ostrowick
John Ostrowick

Reputation: 85

Use mysql --sigint-ignore

and to clear a line, use control+U

Upvotes: 4

rwired
rwired

Reputation: 1133

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.

Upvotes: 12

Schwern
Schwern

Reputation: 165110

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

Upvotes: 17

Bill Karwin
Bill Karwin

Reputation: 562558

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.

Upvotes: 21

Related Questions