Invalid SQL is generated when attempting to change server default to CURRENT_TIMESTAMP

[GoldstHa]

op.alter_column('micros_menu_items', 'warehouse_last_update',
            existing_type=mysql.TIMESTAMP(),
            server_default=sa.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
            existing_nullable=True)

emits this:

ALTER TABLE otg.micros_menu_items ALTER COLUMN warehouse_last_update SET DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

...which is invalid.

This would be syntactically valid, but still fail:

ALTER TABLE otg.micros_menu_items ALTER COLUMN warehouse_last_update SET DEFAULT 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP';

This seems to be the only valid way:

ALTER TABLE otg.micros_menu_items
CHANGE COLUMN warehouse_last_update warehouse_last_update TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

I understand that in older versions of MySQL simply making a TIMESTAMP column NOT NULL would have the same effect, but newer versions MySQL need that default clause in order to have an auto-initializing/updating timestamp column.

[zzzeek]

we don't have explicit support for MySQL's "ON UPDATE" clause right now, this is in SQLAlchemy as sqlalchemy/sqlalchemy#4652 where we will add a mysql_on_update and we also welcome pull requests in this regard, for the Alembic side there would also be a mysql_on_update parameter consumed by the **kw of AlterColumnOp. Any solution that's in explicit support of ON UPDATE needs the upstream sqlalchemy issue implemented first.

The "CHANGE" syntax is part of the MySQL Alembic dialect right now however it only takes effect when the column's name is changing. If you add "name='warehouse_last_update'" to your operation it might emit what you want here, please try that.

additionally, need to know the scope of the syntax here, can you please share exact MySQL / MariaDB version in use, additionally can you confirm that without the "ON UPDATE" part, the existing ALTER TABLE syntax for change of server defaut still works ? I'm sort of guessing this is MySQL 8 where they have changed all kinds of syntaxes.

[GoldstHa]

"name='warehouse_last_update'" worked. Thank you!

I'm actually working with both MySQL 5.5.59 and 8.0.16. The results are the same on both. The ALTER TABLE ... ALTER COLUMN ... SET DEFAULT ...; syntax is correct for other datatypes. ALTER TABLE otg.micros_menu_items ALTER COLUMN warehouse_last_update SET DEFAULT CURRENT_TIMESTAMP; doesn't work and neither does ALTER TABLE otg.micros_menu_items ALTER COLUMN warehouse_last_update SET DEFAULT 'CURRENT_TIMESTAMP';

[zzzeek]

mariadb supports:

ALTER TABLE x ALTER COLUMN colname SET DEFAULT CURRENT_TIMESTAMP

MySQL does not. will now try the CHANGE syntax, and see if MariaDB supports that, I bet it doesn't and we have to switch based on mysql / mariadb.

[zzzeek]

so you can use CHANGE for all server default changes on both DBs. But I'm not able to find any reasoning for what is different and I don't think I'm going to do it that way, because I bet it does something very different internally like lock the table more aggressively, or something like that. but nobody knows. so at the moment leaning towards regexp for CURRENT_TIMESTAMP / NOW w datetime column only.

[zzzeek]

OK "ON UPDATE" is even bundled into how we reflect the default on MySQL in SQLAlchemy right now, that makes sqlalchemy/sqlalchemy#4652 a lot more weird

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Use CHANGE COLUMN for MySQL / MariaDB DateTime server default change https://gerrit.sqlalchemy.org/1277

[GoldstHa]

Both ALTER TABLE ... CHANGE COLUMN ... ; and ALTER TABLE ... MODIFY COLUMN ...; are valid for setting the default value of a TIMESTAMP column to CURRENT_TIMESTAMP. When I run them and compare status variables, Innodb_rows_inserted goes up for both, but not by the amount I'd expect if it were doing a full table rebuild... so I'm not sure I'm on the right track with that. Duration seems to be roughly the same though. From what I've read, MODIFY COLUMN does do a full table rebuild.

[GoldstHa]

Beginning with 5.6, there's support for online DDL and beginning with 8.0 there's support for atomic DLL, though at the moment I don't have much experience with either. When I do, I may make some suggestions for making safer, non-blocking DDL statements.

I've also been working on a heuristic for arranging operations when using --autogenerate in a way that is guaranteed to not fail. With transactional DDL, I assume it's no big deal. But with non-transactional DDL it's a real pain to 'rollback' only those changes that went through.

[zzzeek]

well for this issue it's between ALTER COLUMN and CHANGE COLUMN so I have it doing the CHANGE syntax based on a conservative check only.