1

I have a large insert into statement that takes a long time to complete, so I killed it, but then it just sitting on processlist with status killed, and then I'm unable to restart mysql properly. I have to reboot. It happens everytime. please see log trace below. How do I handle this kind of situation?

mysql> show processlist;
+----+------+--------------------+--------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id | User | Host               | db           | Command | Time | State  | Info                                                                                                 |
+----+------+--------------------+--------------+---------+------+--------+------------------------------------------------------------------------------------------------------+                                                                                        |
|  6 | root | 127.0.0.1:49971    | xxxxxx | Killed  |  826 | update | insert ignore into xxxxxx.xxxxxxs_tmp2 (xxxxxx, reshoot) values ('xxx.xxx',1),('0- |
|  7 | root | localhost          | xxxxxx | Query   |    0 | NULL   | show processlist                                                                                     |
+----+------+--------------------+--------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> quit
Bye
[root@x-xxx-x-xx ~]# service mysqld restart
mysqld: unrecognized service
[root@x-xxx-x-xx ~]# service mysql restart
Shutting down MySQL.................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................... ERROR! 
 ERROR! Failed to stop running server, so refusing to try to start.
[root@x-xxx-x-xx ~]# service mysql status
 SUCCESS! MySQL running (4465)
[root@x-xxx-x-xx ~]# mysql -u root -xxxxx
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I'm guessing it's because it's trying to rollback all the rows that it inserted. is this right? here's the mysql error log:

110209 19:59:33 [Note] Event Scheduler: Purging the queue. 0 events
110209 19:59:34  InnoDB: Starting shutdown...
110209 20:09:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110209 20:09:16 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --log_slow_queries is not \
set
110209 20:09:16 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110209 20:09:16  InnoDB: Using Linux native AIO
110209 20:09:16  InnoDB: Initializing buffer pool, size = 6.0G
110209 20:09:17  InnoDB: Completed initialization of buffer pool
110209 20:09:17  InnoDB: highest supported file format is Barracuda.
110209 20:09:19  InnoDB: 1.1.4 started; log sequence number 16772431917
110209 20:09:20 [Warning] 'proxies_priv' entry '@ root@xx-xxx-xx-xx' ignored in --skip-name-resolve mode.
110209 20:09:20 [Note] Event Scheduler: Loaded 0 events
110209 20:09:20 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.8'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
110209 20:13:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110209 21:07:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110209 21:07:08 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --log_slow_queries is not \
set
110209 21:07:08 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110209 21:07:08  InnoDB: Using Linux native AIO
110209 21:07:08  InnoDB: Initializing buffer pool, size = 6.0G
110209 21:07:09  InnoDB: Completed initialization of buffer pool
110209 21:07:10  InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 16798669157
110209 21:07:11  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 16803911680
InnoDB: Doing recovery: scanned up to log sequence number 16809154560
InnoDB: Doing recovery: scanned up to log sequence number 16813133382
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 219970 row operations to undo
InnoDB: Trx id counter is 1400
110209 21:07:20  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 5\
0 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Starting in background the rollback of uncommitted transactions
110209 21:13:34  InnoDB: Rolling back trx with id 1200, 219970 rows to undo

InnoDB: Progress in percents: 1110209 21:13:34  InnoDB: 1.1.4 started; log sequence number 16813133382
 2110209 21:13:34 [Warning] 'proxies_priv' entry '@ root@xx-xxx-xx-xx' ignored in --skip-name-resolve mode.
 3 4110209 21:13:35 [Note] Event Scheduler: Loaded 0 events
110209 21:13:35 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.8'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

1 Answer 1

1

If you are using MySQL 5.5, try using a delayed insert instead of insert.

When you use KILL, a thread-specific kill flag is set for the thread. In most cases, it might take some time for the thread to die because the kill flag is checked only at specific intervals: [...]

An INSERT DELAYED thread quickly flushes (inserts) all rows it has in memory and then terminates.

http://dev.mysql.com/doc/refman/5.5/en/kill.html

The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost.

http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .