Web lists-archives.com

Re: deleting big tables




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 <paumarc@xxxxxxxxx>
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.adarsh@xxxxxxxxx>:
>
>> 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 <paumarc@xxxxxxxxx>
>> 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/
>>>
>>
>