flask SQLAlchemy db_migrate failing on large migration using SmallInteger

[markgalup]

Hello Miguel,

I owe you a debt of thanks for this tremendous effort - I use your databases all day everyday. Thank you.

I've never run in to an issue with migrations before, but today I encountered many errors. I was added something like 60 columns, and out of those about 10-15 were the type db.Column(db.SmallInteger, default=False) Every time one of those rows was processed, the migrate would throw an error. I checked my code for hours, but ultimately found that if I just kept running migrate, it would jump through these issues, and eventually migrated all columns. Tracebacks and pip freeze are below:

| => python ./db_migrate.py 
/Users/user/.pyenv/versions/2.7.18/envs/venv-py2/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py:176: Warning: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
  cursor.execute("SET NAMES %s" % charset_name)
Traceback (most recent call last):
  File "./db_migrate.py", line 21, in <module>
    api.upgrade(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
  File "/Users/user/.pyenv/versions/2.7.18/envs/venv-py2/lib/python2.7/site-packages/migrate/versioning/api.py", line 186, in upgrade
    return _migrate(url, repository, version, upgrade=True, err=err, **opts)
  File "<string>", line 2, in _migrate
  File "/Users/user/.pyenv/versions/2.7.18/envs/venv-py2/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 167, in with_engine
    return f(*a, **kw)
  File "/Users/user/.pyenv/versions/2.7.18/envs/venv-py2/lib/python2.7/site-packages/migrate/versioning/api.py", line 366, in _migrate
    schema.runchange(ver, change, changeset.step)
  File "/Users/user/.pyenv/versions/2.7.18/envs/venv-py2/lib/python2.7/site-packages/migrate/versioning/schema.py", line 93, in runchange
    change.run(self.engine, step)
  File "/Users/user/.pyenv/versions/2.7.18/envs/venv-py2/lib/python2.7/site-packages/migrate/versioning/script/py.py", line 154, in run
    script_func(engine)
  File "/Users/user/dev/ad_database_CURRENT/db_repository/versions/030_migration.py", line 207, in upgrade
    post_meta.tables['audiobook'].columns['qc_eng_3_complete'].create()
  File "/Users/user/.pyenv/versions/2.7.18/envs/venv-py2/lib/python2.7/site-packages/migrate/changeset/schema.py", line 595, in create
    stmt = table.update().values({self: engine._execute_default(self.default)})
AttributeError: 'NoneType' object has no attribute 'update'

appdirs==1.4.3
Babel==1.3
bcrypt==3.1.7
blinker==1.3
cffi==1.15.0
click==7.1.2
configparser==4.0.2
contextlib2==0.6.0.post1
coverage==3.7.1
decorator==3.4.2
Flask==1.1.4
Flask-Babel==0.9
Flask-Bcrypt==0.6.2
Flask-Login==0.2.11
Flask-Mail==0.9.1
Flask-OpenID==1.2.4
Flask-SQLAlchemy==2.5.1
Flask-Uploads==0.2.1
Flask-WhooshAlchemy==0.56
Flask-WTF==0.14.3
flipflop==1.0
guess-language==0.2
importlib-metadata==2.1.3
itsdangerous==0.24
Jinja2==2.11.3
MarkupSafe==0.23
MySQL-python==1.2.5
packaging==16.8
pathlib2==2.3.7.post1
pbr==5.9.0
pycparser==2.21
pyparsing==2.2.0
python-bcrypt==0.3.1
python-openid==2.2.5
pytz==2015.4
scandir==1.10.0
schedule==0.5.0
six==1.9.0
speaklater==1.3
SQLAlchemy==1.4.36
sqlalchemy-migrate==0.13.0
sqlparse==0.1.15
Tempita==0.5.2
typing==3.10.0.0
Unidecode==1.0.23
Werkzeug==1.0.1
Whoosh==2.7.0
WTForms==2.0.2
zipp==1.2.0

I'm reluctant to push this to my production server in case there are more significant issues. I appreciate any help very much.

Best,
MG

[miguelgrinberg]

This is probably an Alembic issue. Flask-Migrate doesn't do any of this, I just take care of the integration of Alembic with Flask. You may want to create an example that reproduces the problem and get advice from the Alembic team on it.

[markgalup]

Ok got it - that makes sense. I'll do more digging, but it's sounding like maybe you're saying that passing the DATABASE_URL in the CLI command here is overriding a variable that's built in to the Flask app elsewhere. I believe I remember seeing DATABASE_URI, so I'll confirm.

Thanks!
Mark

[miguelgrinberg]

DATABASE_URL is an application specific environment variable that I normally use in my applications. If you haven't done anything in the application to read this variable and install it in the Flask-SQLAlchemy configuration, then setting this variable is not going to do anything.

[markgalup]

Ah ok, I think I am realizing my misconception. I was thinking the DATABASE_URL variable was self-contained in the Flask-Migrate package. I'll make sure the application is creating the environment variable and then see how this goes.

Thanks!
Mark

[markgalup]

Miguel, confirming that I resolved this by establishing the environment var, and it's all working correctly now! Thank you very much again for all your help.

Best,
Mark

[miguelgrinberg]

Awesome, congrats!