1

I am in the process of removing 4 columns from my (MySQL/innodb) table,

simple code,

ALTER TABLE database_name.table_name
DROP COLUMN A,
DROP COLUMN B,
DROP COLUMN C,
DROP COLUMN D;

It has been running for close to 20 hours now. It's a large table 30GB / 8560000 rows, large for what i have experience with anyway, local instance on an old desktop computer.

When I check the Server Status on the Administration panel it is telling me that the Server has 'Stopped'. However in the Action/Respose window at the bottom of the Query window that row is showing 'Running..' and the icon is that spinning/thinking animation you expect.

There is around 4 KB/s Traffic but no read or writes, InnoBuffer usage is at 99.9% which should be around 30GB, CPU is between 1.5 and 2.

I can open another instance of the server and that shows it is running but still has the old columns listed in the Schema. (I have have refrained from querying the db through that instance)

I am just wondering what is happening exactly? Is this 'normal' and I should just be patient?

  • To alter a table which takes endless amount of time try removing index in the table then drop the columns. – James Oct 16 at 6:44
  • Do you mean simply drop a Column without the alter table command? – Ashley Reid Oct 16 at 10:07
  • If your table table_name has index as well as the records are huge in size. try removing index from the table then use the alter... drop column then create index in the table. – James Oct 16 at 10:09
  • ah I see, although, the table already doesn't have an index. – Ashley Reid Oct 16 at 10:18
  • would there be any speed increase in creating a new table, using just the columns I want to keep? – Ashley Reid Oct 16 at 10:19
0

Open another connection to the same server (just click the home screen tile again, which you used to open the current connection). It will tell you if the server is available or not. Maybe the UI got stuck somehow and did not update properly when the operation ended. Don't forget the connection could also have timed out and you need to re-connect (which usually happens automatically, but somethings things just go wrong).

0

Trying to drop multiple column as I was, on a large (for my machine) MySQL database.

The best approach I have found was to create a new table omitting the column's I was trying to drop.

The alter table command was taking days before getting hung up/breaking the connection with my db. Using code like the following I was able to achieve what I needed in a little over two hours.

CREATE TABLE new_table_name AS
    SELECT column_E,column_F,column_G
    FROM old_table_name;

More detailed instruction, including how to manage indexes and other considerations, can be found here as was linked in James's comment below my original question above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.