0

I'm trying to drop a table containing several hundred thousand column-based records. Normally when creating the database I use a column-based engine (infinidb) but in this case I forgot to include the ENGINE statement. So the database is pretty much unusable for my needs. Now I have a database full of tables that are taking forever to drop (it's been two hours and nothing has happened). I tried the ALTER TABLE table ENGINE=INFINIDB command but again, it's taking forever (see above re: two hours). EDIT: The first command I tried was DROP TABLE. It hung with every single table. Then I tried the ALTER command in case that was faster for some reason, but it wasn't.

Is there another way to get rid of this database? E.g. manually going into the /mysql/ directory and deleting the database? I guess I could just rename it and leave it, but I'd rather get rid of it entirely so it's not taking up space.

ale19
  • 1,327
  • 7
  • 23
  • 38
  • dropping a table shouldn't take long, even for tables with millions of rows. are you sure it's not a connection issue? – Patrick Jun 22 '16 at 14:06
  • `DROP != ALTER` create new table with engine you need. copy-update data from old table. drop old one. rename new one – Alex Jun 22 '16 at 14:10
  • does anybody use this db at the moment? or you are the only client? is there any process which is trying to insert or update data while you execute your `DROP` or `ALTER` queries? – Alex Jun 22 '16 at 14:19
  • What engine did the tables end up with? – Shadow Jun 22 '16 at 14:24
  • I'm assuming InnoDB since I didn't specify an engine. Is there a way to specify the engine using a CREATE TABLE AS SELECT command? – ale19 Jun 22 '16 at 14:34

2 Answers2

0

First of all you said Can't drop table. But in post you mentioned ALTER TABLE table ENGINE=INFINIDB.

But DROP != ALTER it is two different things.

So you can do following:

  • CREATE new table with same structure but engine you need.
  • copy(UPDATE) data from old table to the one you just created.
  • DROP old table.
  • RENAMErename new one to old name
Alex
  • 16,739
  • 1
  • 28
  • 51
  • See my edit above. The first command I tried was DROP TABLE. That didn't work, so I tried altering the table to see if I could change the command that way. I can create a new table just fine, but I want to be able to drop the old one because it's taking up space... – ale19 Jun 22 '16 at 14:19
  • does anybody use this db at the moment? or you are the only client? is there any process which is trying to insert or update data while you execute your DROP or ALTER queries? – Alex Jun 22 '16 at 14:20
0

It turned out that another process (a website) was using this database and had a couple of queries that got 'stuck' in the SQL server and caused the table to hang due to the database using the wrong engine, which I'm assuming was InnoDB since I didn't specify an engine when I initially used the "CREATE TABLE table1 AS SELECT * FROM table2" command. We finally managed to wipe the database and start over. Thanks for your help.

ale19
  • 1,327
  • 7
  • 23
  • 38