I am trying to follow the advice of this SO answer to add columns to an existing table with ~150k rows. This is running on the localhost of my MacBook Pro. Here is the script I am running with the source command from in the MySQL command line:
# Create a temporary new table.
CREATE TABLE psf_new LIKE psf;
ALTER TABLE psf_new ADD COLUMN filter varchar(25) AFTER filename;
ALTER TABLE psf_new ADD COLUMN aperture varchar(50) AFTER filter;
# Add some of the indexes we'll need in the future.
CREATE INDEX filename_index ON psf_new (filename);
CREATE INDEX filter_index ON psf_new (filter);
CREATE INDEX aperture_index ON psf_new (aperture);
CREATE INDEX psf_x_center_index ON psf_new (psf_x_center);
CREATE INDEX psf_y_center_index ON psf_new (psf_y_center);
# Copy over the existing data from the old table.
INSERT INTO psf_new (
id,
filename,
psf_x_center,
psf_y_center,
model_x_center,
model_y_center,
psf_flux, sky,
psf,
model_fraction) SELECT * FROM psf;
# Rename and drop.
RENAME TABLE psf_new TO psf, psf TO psf_old;
DROP TABLE psf_old;
Everything seems to work OK until it hits the INSERT statement and then it just stops. I understand that this is a expensive operation (the database dump I took right before I tried this is 500M). But I'm not seeing any CPU activity from the MySQL processes or the deamon, there is plenty of free memory, and minimal IO activity (I'm getting all this from the OSX Activity Monitor).
When I abort the query with CTRL-C and check on things I can see that my new table has been created but it has no data. I do this with SELECT count(id) FROM psf_new;. When I try to run that same command on the psf table I get a small burst of CPU and IO and then nothing happens, the CLI prompt is not returned unless I CTRL-C again.
UPDATE:
So, embarrassingly, the solution was to restart my machine. Things seem to be working as expected now. If anyone can provide any insight on what may have happened, maybe something like a stale transaction that needed to be rolled back, that would be appreciated.
