Flask-migrate: sqlalchemy.exc.OperationalError: when trying to migrate existing database

[git-bone]

Hi Miguel,

I'm having issues migrating a sqlite database (dev version, production is mysql). I needed a change on String length. The migration script is created as follows:

def upgrade(): # ### commands auto generated by Alembic - please adjust! ### op.alter_column('answer', 'answer', existing_type=sa.VARCHAR(length=255), type_=sa.String(length=1024), existing_nullable=True)

however, when trying to upgrade, it gives me a whole lot of information in console (copy over here https://shrib.com/#rqyZSZa2WgSaMbVjOm1T) , but I really cannot see anything useful that is causing this. The error trail ends with this:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "ALTER": syntax error [SQL: 'ALTER TABLE answer ALTER COLUMN answer TYPE VARCHAR(1024)'] (Background on this error at: http://sqlalche.me/e/e3q8)

any advice on how to solve this?

many thanks!

[miguelgrinberg]

SQLite does not support making modifications to existing columns. The "batch mode" feature in Alembic was designed to overcome this limitation: https://alembic.sqlalchemy.org/en/latest/batch.html#batch-mode-with-autogenerate

[git-bone]

Thanks for the quick reply, and a solution so it seems, thanks!

Do you happen to know if mysql will upgrade without issues with the current migration file or are any other steps required?

[miguelgrinberg]

If you generate the migration using batch mode, then sqlite will do the move and copy, and MySQL will do it as before with an ALTER statement.

[git-bone]

Thanks for the support Miguel

[git-bone]

The solution works fine with adding the necessary line to env.py, thanks!