incapaz de matar e sair de uma consulta de inserção grande corretamente

Question

Eu tenho uma inserção grande na instrução que leva muito tempo para ser concluída, então eu a matei, mas depois é só sentar na lista de processos com status morto, e então não consigo reiniciar o mysql corretamente. Eu tenho que reiniciar. Isso acontece toda vez. por favor, veja o log abaixo. Como faço para lidar com esse tipo de situação?

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)

Acredito que seja porque está tentando reverter todas as linhas inseridas. isto está certo? aqui está o log de erro do mysql:

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

    

Answer 1

Se você estiver usando o MySQL 5.5, tente usar uma inserção atrasada em vez de inserir.

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.

link

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.

link