Bundle multiple column changes in single mysql ALTER TABLE

[vitaly numenta]

Alembic 0.7.4, mysql, MacOS X

Last time I needed to add eight columns to a 10 million-row table using alembic migration, it took about 17 hours because each add_column was executed individually. With mysql, each individual ALTER TABLE command results in a full copy of the entire table. So, in my case, that was 8 whopping implicit full table copy operations (one per added column) that took 17 hours to complete :(

So, I was excited when I noticed the new context manager op.batch_alter_table, thinking that it will do the right thing with mysql, namely group all the column operations inside a single generated ALTER TABLE statement (allowed by mysql), and thus hoping to reduce my next set of column migrations to a single ALTER TABLE statement. So, I upgraded to Alembic 0.7.4 and used `op.batch_alter_table(...) as batch_op:` context manager and nested my column changes inside this context as `batch_op.add_column(...)`, etc.

Disappointingly, op.batch_alter_table generated a bunch of *individual* ALTER TABLE statements for this table, instead of a single ALTER TABLE with multiple column operations in it.

Am I doing something wrong? What's the right way to cause alembic to generate a single mysql ALTER TABLE statement with *multiple* column operations in it?

Thank you,

Vitaly

[Michael Bayer]

yes, let’s look at the docs:

http://alembic.readthedocs.org/en/latest/batch.html#batch-mode-with-databases-other-than-sqlite

"The batch mode directive will run the “recreate” system regardless of backend if the flag recreate='always' is passed"

i didn’t think the MySQL use case was going to hit this quickly for this, so good luck, it’s pretty experimental.

[vitaly numenta]

Hi Michael,

thank you for your quick follow-up and explanation. I was trying to get something different from alembic; mysql supports this syntax:

ALTER TABLE tbl_name alter_specification, alter_specification, ...

for example: ALTER TABLE `addresses` DROP COLUMN c, DROP COLUMN d, ADD COLUMN mobile VARCHAR(40);

I would like to be able to do the above in in alembic. 

However, instead of the single ALTER TABLE statement, alembic generates multiple ALTER TABLE statements instead like this:

ALTER TABLE `addresses` DROP COLUMN c;

ALTER TABLE `addresses` DROP COLUMN d;

ALTER TABLE `addresses` ADD COLUMN mobile VARCHAR(40);

In large mysql tables, this is incredibly slow, because mysql makes a full copy for each of these ALTER TABLE statements, while maintaining a Write-Lock on the original table; I learned this the hard way last Friday. Per http://dev.mysql.com/doc/refman/5.1/en/alter-table.html: "In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready..."

[vitaly numenta]

I filed an issue for this here: https://bitbucket.org/zzzeek/alembic/issue/271/add-support-for-mysql-alter-table-with

[Michael Bayer]

vitaly numenta <vitaly.kru...@gmail.com> wrote:

> On Tuesday, February 10, 2015 at 7:38:10 AM UTC-8, Michael Bayer wrote:
>
>
> thank you for your quick follow-up and explanation. I was trying to get something different from alembic; mysql supports this syntax:
>
> ALTER TABLE tbl_name alter_specification, alter_specification, ...
>
> for example: ALTER TABLE `addresses` DROP COLUMN c, DROP COLUMN d, ADD COLUMN mobile VARCHAR(40);
>
> I would like to be able to do the above in in alembic.

oh. Well, for now, this is easy, just send out that SQL directly:

op.execute("ALTER TABLE `addresses` DROP COLUMN c, DROP COLUMN d, ADD COLUMN mobile VARCHAR(40)”)

that will solve your problem right now.

i see your issue report and I don’t have a solution for that right now.

[vitaly numenta]

Thank you Michael. I will do that in the future, until there is a supported alembic way for it.

Best,

Vitaly