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