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?

Ashley Reid
  • 11
  • 4

2 Answers2

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).

Mike Lischke
  • 1,573
  • 9
  • 11
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.

Ashley Reid
  • 11
  • 4