Flask-migrate: Migrate detects same thing again and again and not the default values

[deveshaggrawal19]

I'm trying to migrate for new columns with default values, but it is not reading those changes, rather only default values of boolean type are read that too again and again. I don't know why this is happening. Can you please guide me.

(env) devesh@SERVER:/srv/www/waasle$ python manage.py db migrate
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.autogenerate.compare] Detected type change from TINYINT(display_width=1) to Boolean() on 'orders.complete'
INFO [alembic.autogenerate.compare] Detected type change from TINYINT(display_width=1) to Boolean() on 'tranactions.type'
INFO [alembic.autogenerate.compare] Detected type change from TINYINT(display_width=1) to Boolean() on 'users.confirmed'
Generating /srv/www/waasle/migrations/versions/4da4194e339d_.py ... done
(env) devesh@SERVER:/srv/www/waasle$ python manage.py db upgrade
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 8735ffa147c0 -> 4da4194e339d, empty message

[miguelgrinberg]

I don't know either, it has to be related to have your models changed, combined with the Alembic configuration you are using. Read this for background.

In any case, sometimes the easiest way forward is to edit the migration scripts by hand to make them accurate and move on. Expecting Alembic will always figure out your migrations perfectly is not realistic, in my opinion.

[peterschutt]

A little late but I only came across this today after encountering something similar and Mike Bayer has covered it:

https://bitbucket.org/zzzeek/alembic/issues/46/mysqltinyint-display_width-1-vs-saboolean

MySQL BOOLEAN types always feature in revisions because the MySQL BOOLEAN is just an alias for TINYINT(1). SQLAlchemy will not make the assumption that a TINYINT(1) is meant to be a BOOLEAN as it might not be the case. As such, every time the revision is run, it compares the TINYINT in the inspected type to the BOOLEAN in the metadata type and determines that they are not the same.

You can either define Booleans in your model as TINYINT(display_width=1) or create your own type comparison function that overrides the type checking for the specific instance:

http://alembic.zzzcomputing.com/en/latest/autogenerate.html#comparing-types

[miguelgrinberg]

This issue will be automatically closed due to being inactive for more than six months. Seeing that I haven't responded to your last comment, it is quite possible that I have dropped the ball on this issue and I apologize about that. If that is the case, do not take the closing of the issue personally as it is an automated process doing it, just reopen it and I'll get back to you.

[charlbury]

@miguelgrinberg I am having the same problem trying to have a true/false boolean field using Microsoft SQL. Every time I try to migrate it tells me that it has detected a change, even though I am not changing anything for this field or table.

Active = db.Column(db.boolean, unique=False, nullable=False)
Whenever i run flask db migrate, I get the message

INFO [alembic.autogenerate.compare] Detected type change from BIT() to Boolean() on 'RewardEvents.Active'

Can you guide me as to how I should be implementing a Boolean for MS SQL here?

[miguelgrinberg]

@charlbury The only odd thing is that the column type should be db.Boolean, with uppercase B. Maybe that's the problem? Other than that I'm not sure, this is an Alembic problem, not Flask-Migrate. I believe BIT is the correct boolean column type for MS SQL.

[charlbury]

Thanks for the prompt response. I have changed from the use of SQL Alchemy Boolean to have the fields defined as Bits. I've done this by importing SQL Alchemy's MSSQL dialect at the top of my models.py and then setting the column type to be Bit by using SQL ALchemy's dialect specific bit()

from sqlalchemy.dialects import mssql
...
Active = db.Column(mssql.BIT, unique=False, nullable=False)

[grovduck]

I struggled with this one for a while as well and followed the advice of @peterschutt to create a custom function to define compare_type. In the env.py file that gets written by flask-migrate, I used this custom function to call mysql TINYINT(1) as SQLAlchemy Boolean types the same:

def my_compare_type(
    context, inspected_column, metadata_column, inspected_type,
    metadata_type
):
    from sqlalchemy.dialects import mysql
    from sqlalchemy.sql import sqltypes
    if (
        isinstance(inspected_type, mysql.types.TINYINT)
        and isinstance(metadata_type, sqltypes.Boolean)
    ):
        return False
    return None

and in the call to context.configure, change compare_type=True to compare_type=my_compare_type.

with connectable.connect() as connection:
    context.configure(
        connection=connection,
        target_metadata=target_metadata,
        process_revision_directives=process_revision_directives,
        render_as_batch=True,
        compare_type=my_compare_type,
        **current_app.extensions['migrate'].configure_args
    )

I don't know if this is the right way to do it, but it correctly stopped trying to change these types.