1

I have a query running for some hours, if I cancel this query what is going to happen to the database?

The query is an ALTER TABLE that adds a new column.

UPDATE: I executed the script this way

mysql -u username -p database

mysql> ALTER TABLE `table_name` ADD COLUMN `new_column` INT(1);
Daniel Moura
  • 215
  • 2
  • 11
  • what was the command you run to run the ALTER TABLE ? was it from an script from the mysql command line ? does mysql shows you any error on the error file referencing to it ? – Prix Oct 18 '10 at 19:11
  • @Prix I updated the question. – Daniel Moura Oct 18 '10 at 19:14
  • is this the only command ? do you have phpmyadmin install so you can check if the change occurred ? – Prix Oct 18 '10 at 19:17
  • @Prix the table is very large, it is expected to take so long to run. I want to know if I can cancel this without making any harm. – Daniel Moura Oct 18 '10 at 19:20
  • The query finished now, so I won't cancel it anymore. But I still want to know the answer to this. – Daniel Moura Oct 18 '10 at 19:23
  • I think i'd be quite scared of the chance of data corruption from cancelling an ALTER TABLE mid-way through. – Tom O'Connor Oct 18 '10 at 22:00

2 Answers2

2

AFAIK you could run:

SHOW PROCESSLIST;

To see what thread it is then kill it:

KILL [CONNECTION | QUERY] thread_id;

During ALTER TABLE, the kill flag is checked before each block of rows are read from the original table. If the kill flag was set, the statement is aborted and the temporary table is deleted.

About the side effects i won't be able to tell your exactly what could happen.

Prix
  • 4,881
  • 3
  • 24
  • 25
  • Generally, nothing will happen. It's possible it could take down MySQL on your server, in which case you just restart MySQL and you're good. – Ben Oct 18 '10 at 20:15
  • Each connection to mysqld runs in a separate thread so aslong as you kill the right thread there won't be any problem with your MySQL server going down specially if you are using the additional option to tell whether it is a connection a query or a thread_id alone – Prix Oct 18 '10 at 20:34
0

I could be wrong here, but if you don't specify a BEGIN TRANSACTION, each and every command is a self-contained transaction.

As such, when you cancel the query there shouldn't be any side effects.

Hubert Kario
  • 6,361
  • 6
  • 36
  • 65
  • I thought, although ICBW, that alter table and so on commands (ones that alter the table structure, etc) aren't covered by transactions, and happen regardless. – Tom O'Connor Oct 18 '10 at 21:59
  • I'm quite sure that all commands are covered by transactions. The only ones that are not are `create database`, `drop database` and `alter database`, table alterations *should* be covered by transactions (I could list at least a few usage scenarios in which it is necessary). – Hubert Kario Oct 18 '10 at 22:33