0

I have a table with over 3 million records, I tried dropping it in MySQL Workbench, it freezes every-time, I logged into sql command prompt through my shell and did

drop table table_name

it froze.

I tried deleting the records in the table 

Delete from table_name where `ID` > 100000;

it froze

I thought maybe I'd let it run for awhile after two hours it's still running:

How can I delete this table?

Edward
  • 3,061
  • 6
  • 32
  • 52
  • http://stackoverflow.com/questions/10871388/dropping-table-makes-mysql-hang – Leo Mar 31 '14 at 23:20
  • Quick suggestion: try using the mysql (terminal) client, if this happens again, then there is something there. Also, are there any dependencies between this table and some other table? – ipinak Mar 31 '14 at 23:23
  • @ipinak no there are no foreign key constraints on this table. – Edward Mar 31 '14 at 23:26
  • Can't you just `truncate` it? –  Mar 31 '14 at 23:30
  • I thought that was what I was doing with the delete by ID, but i'll try truncate, thanks. – Edward Mar 31 '14 at 23:32
  • If you are using myisam you can just delete the files – iamkrillin Mar 31 '14 at 23:34
  • What is the storage engine for the table? MyISAM? InnoDB? something else? If it's InnoDB, and it's not in it's own file (innodb_file_per_table), there's a lot of information to be written to the InnoDB logs, the binary logs, and the InnoDB tablespace to mark the space used by the table as free space. Also, are there any transactions that are holding locks on the table? A `SHOW PROCESSLIST` may give an indication what the session doing the DROP TABLE is doing, or what it's waiting on. A restart of the mysql server would clear outstanding locks, but recovery might take a while. – spencer7593 Mar 31 '14 at 23:51
  • This question might be better asked on dba.stackexchange.com. – spencer7593 Mar 31 '14 at 23:51

1 Answers1

1

Just some ideas:

Try removing Indexes 

 DROP INDEX 'INDEXNAME' ON 'table_name'

Then try removing constraints if there are any

ALTER TABLE table_name
DROP FOREIGN KEY Foreign_Key_Name

and then try dropping it?

Elias
  • 2,602
  • 5
  • 28
  • 57