The problem was here: ---TRANSACTION 154B1E00, ACTIVE 265942 sec rollback mysql tables in use 1, locked 1 ROLLING BACK 297751 lock struct(s), heap size 35387832, 74438247 row lock(s), undo log entries 66688203 MySQL thread id 37, OS thread handle 0x7f11bc4b9700, query id 110 localhost pau query end delete from ensemblmotive
Your first query was trying to delete rows from the table and probably you killed it. The rollback process has started and then, you weren't able to acquire a lock on that table until the end of rollback process. As you rebooted the server, all the locks were released and ... luck you not having your data corrupted. -- *Wagner Bianchi* 2015-05-17 13:06 GMT-03:00 Suresh Kuna <sureshkumar...@gmail.com>: > Your drop index table needs the table rebuild and locked the drop table. In > this case, you have to drop table rather than drop indexes or deleting > data. > > On Sun, May 17, 2015 at 7:11 AM, Pau Marc Muñoz Torres <paum...@gmail.com> > wrote: > > > i solved the problem by rebooting my computer. i just drop the table in > > seconds > > > > thanks > > > > Pau Marc Muñoz Torres > > skype: pau_marc > > http://www.linkedin.com/in/paumarc > > http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ > > > > > > 2015-05-17 12:00 GMT+02:00 Pau Marc Muñoz Torres <paum...@gmail.com>: > > > > > this is the innodb output, > > > > > > i tried to kill the process using kil, kill query and kill connection > > but > > > doesn't worked. what can i do? > > > > > > thanks > > > > > > 150517 11:50:46 INNODB MONITOR OUTPUT > > > ===================================== > > > Per second averages calculated from the last 3 seconds > > > ----------------- > > > BACKGROUND THREAD > > > ----------------- > > > srv_master_thread loops: 140779 1_second, 121940 sleeps, 13482 > 10_second, > > > 11383 background, 7600 flush > > > srv_master_thread log flush and writes: 154479 > > > ---------- > > > SEMAPHORES > > > ---------- > > > OS WAIT ARRAY INFO: reservation count 2091707, signal count 9920537 > > > Mutex spin waits 26944439, rounds 111751039, OS waits 966302 > > > RW-shared spins 5087632, rounds 68696066, OS waits 929958 > > > RW-excl spins 2980761, rounds 27893952, OS waits 158867 > > > Spin rounds per wait: 4.15 mutex, 13.50 RW-shared, 9.36 RW-excl > > > ------------ > > > TRANSACTIONS > > > ------------ > > > Trx id counter 154B1E14 > > > Purge done for trx's n:o < 154B1E0A undo n:o < 0 > > > History list length 1136 > > > LIST OF TRANSACTIONS FOR EACH SESSION: > > > ---TRANSACTION 0, not started > > > MySQL thread id 67, OS thread handle 0x7f11bc426700, query id 244 > > > localhost pau > > > SHOW ENGINE INNODB STATUS > > > ---TRANSACTION 154B1E00, ACTIVE 265942 sec rollback > > > mysql tables in use 1, locked 1 > > > ROLLING BACK 297751 lock struct(s), heap size 35387832, 74438247 row > > > lock(s), undo log entries 66688203 > > > MySQL thread id 37, OS thread handle 0x7f11bc4b9700, query id 110 > > > localhost pau query end > > > delete from ensemblmotive > > > -------- > > > FILE I/O > > > -------- > > > I/O thread 0 state: waiting for completed aio requests (insert buffer > > > thread) > > > I/O thread 1 state: waiting for completed aio requests (log thread) > > > I/O thread 2 state: waiting for completed aio requests (read thread) > > > I/O thread 3 state: waiting for completed aio requests (read thread) > > > I/O thread 4 state: waiting for completed aio requests (read thread) > > > I/O thread 5 state: waiting for completed aio requests (read thread) > > > I/O thread 6 state: waiting for completed aio requests (write thread) > > > I/O thread 7 state: waiting for completed aio requests (write thread) > > > I/O thread 8 state: waiting for completed aio requests (write thread) > > > I/O thread 9 state: waiting for completed aio requests (write thread) > > > Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , > > > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > > > Pending flushes (fsync) log: 1; buffer pool: 1 > > > 13648332 OS file reads, 34442363 OS file writes, 1064506 OS fsyncs > > > 84.73 reads/s, 16384 avg bytes/read, 49.74 writes/s, 1.75 fsyncs/s > > > ------------------------------------- > > > INSERT BUFFER AND ADAPTIVE HASH INDEX > > > ------------------------------------- > > > Ibuf: size 1, free list len 6150, seg size 6152, 5407097 merges > > > merged operations: > > > insert 0, delete mark 206521397, delete 0 > > > discarded operations: > > > insert 0, delete mark 0, delete 0 > > > Hash table size 276671, node heap has 101 buffer(s) > > > 370.88 hash searches/s, 150.28 non-hash searches/s > > > --- > > > LOG > > > --- > > > Log sequence number 117269257408 > > > Log flushed up to 117269225038 > > > Last checkpoint at 117268694768 > > > 1 pending log writes, 0 pending chkp writes > > > 319455 log i/o's done, 0.75 log i/o's/second > > > ---------------------- > > > BUFFER POOL AND MEMORY > > > ---------------------- > > > Total memory allocated 137363456; in additional pool allocated 0 > > > Dictionary memory allocated 130206 > > > Buffer pool size 8191 > > > Free buffers 0 > > > Database pages 5931 > > > Old database pages 2170 > > > Modified db pages 4679 > > > Pending reads 0 > > > Pending writes: LRU 120, flush list 0, single page 0 > > > Pages made young 22819462, not young 0 > > > 89.73 youngs/s, 0.00 non-youngs/s > > > Pages read 13648346, created 340720, written 33498386 > > > 84.73 reads/s, 0.00 creates/s, 47.49 writes/s > > > Buffer pool hit rate 929 / 1000, young-making rate 76 / 1000 not 0 / > 1000 > > > Pages read ahead 0.00/s, evicted without access 0.00/s, Random read > ahead > > > 0.00/s > > > LRU len: 5931, unzip_LRU len: 0 > > > I/O sum[8119]:cur[172], unzip sum[0]:cur[0] > > > -------------- > > > ROW OPERATIONS > > > -------------- > > > 0 queries inside InnoDB, 0 queries in queue > > > 1 read views open inside InnoDB > > > Main thread process no. 1173, id 139714143528704, state: flushing log > > > Number of rows inserted 0, updated 0, deleted 74140498, read 74808849 > > > 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s > > > ---------------------------- > > > END OF INNODB MONITOR OUTPUT > > > ============================ > > > > > > > > > Pau Marc Muñoz Torres > > > skype: pau_marc > > > http://www.linkedin.com/in/paumarc > > > http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ > > > > > > > > > 2015-05-17 10:31 GMT+02:00 Pothanaboyina Trimurthy < > > > skd.trimur...@gmail.com>: > > > > > >> Hi Pou, > > >> Before killing those connections first check for the undo log entries > > >> from the engine innodb status. If there are too many undo log entries > it > > >> will take some time to clean up those entries. If you force fully kill > > >> those connections there are more chances to crash the DB instance. > > >> On 17 May 2015 1:54 pm, "Adarsh Sharma" <eddy.ada...@gmail.com> > wrote: > > >> > > >>> Hi Pou, > > >>> > > >>> This is the reason why your drop commands taking too much time > because > > >>> they > > >>> are in waiting state.Even it is quite surprising to me the purpose of > > the > > >>> delete command. I would say ,kill all pids ( 37,58,59,66 ) and just > > drop > > >>> the table ( it will delete everything ). Please take a backup if > > needed. > > >>> > > >>> mysql > drop table ensemblmotive ; > > >>> > > >>> Thanks, > > >>> Adarsh > > >>> > > >>> > > >>> > > >>> > > >>> On Sun, 17 May 2015 at 13:44 Pau Marc Muñoz Torres < > paum...@gmail.com> > > >>> wrote: > > >>> > > >>> > this is my process list > > >>> > > > >>> > > > >>> > > > >>> > > > >>> > > > +----+------+-----------+------+---------+--------+---------------------------------+----------------------------------+ > > >>> > | Id | User | Host | db | Command | Time | > > >>> > State | Info > | > > >>> > > > >>> > > > >>> > > > +----+------+-----------+------+---------+--------+---------------------------------+----------------------------------+ > > >>> > | 37 | pau | localhost | UTR | Killed | 260012 | query > > >>> > end | delete from ensemblmotive | > > >>> > | 58 | pau | localhost | UTR | Query | 81396 | Waiting for > table > > >>> > metadata lock | drop index iutr on ensemblmotive | > > >>> > | 59 | pau | localhost | UTR | Query | 45331 | Waiting for > table > > >>> > metadata lock | drop table ensemblmotive | > > >>> > | 66 | pau | localhost | UTR | Query | 0 | > > >>> > NULL | show processlist > | > > >>> > > > >>> > > > >>> > > > +----+------+-----------+------+---------+--------+---------------------------------+----------------------------------+ > > >>> > > > >>> > process with id 37 have been there for a long time, i tried to kill > > it > > >>> and > > >>> > drop the table. what can i do? > > >>> > > > >>> > > > >>> > Pau Marc Muñoz Torres > > >>> > skype: pau_marc > > >>> > http://www.linkedin.com/in/paumarc > > >>> > http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ > > >>> > > > >>> > > > >>> > 2015-05-17 7:23 GMT+02:00 Adarsh Sharma <eddy.ada...@gmail.com>: > > >>> > > > >>> >> Hi Pau, > > >>> >> > > >>> >> Ideally drop table should not take that much time , you have to > > check > > >>> if > > >>> >> your command is executing or it is in waiting stage. May be you > are > > >>> not > > >>> >> able to get lock on that table. > > >>> >> > > >>> >> Cheers, > > >>> >> Adarsh Sharma > > >>> >> > > >>> >> > > >>> >> On Sat, 16 May 2015 at 23:34 Pau Marc Muñoz Torres < > > paum...@gmail.com > > >>> > > > >>> >> wrote: > > >>> >> > > >>> >>> Hello every body > > >>> >>> > > >>> >>> i have a big table in my sql server and i want to delete it, it > > also > > >>> >>> have > > >>> >>> some indexes. I tried to "drop table" and "delete" commands but i > > >>> >>> eventually get a time out. Wath can i do with it, does it exist > any > > >>> >>> method > > >>> >>> to delete tables quicly? > > >>> >>> > > >>> >>> i know that drop and delete are not equivalent but i want to get > > rid > > >>> of > > >>> >>> all > > >>> >>> information inside > > >>> >>> > > >>> >>> thanks > > >>> >>> > > >>> >>> Pau Marc Muñoz Torres > > >>> >>> skype: pau_marc > > >>> >>> http://www.linkedin.com/in/paumarc > > >>> >>> http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ > > >>> >>> > > >>> >> > > >>> > > > >>> > > >> > > > > > > > > > -- > -- > -- > Thanks > Suresh Kuna >
