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
Improve this question

1 Answer 1

Reset to default
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

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.