sqlalchemy
Unexpected warning for computed column when migrating with MariaDB
Describe the bug
Warning is emitted when generating migration for model with computed column.
Expected behavior
No warning emitted.
To Reproduce
Code
# app.py
import os
import flask
import flask_sqlalchemy as fsa
import flask_migrate as fm
import sqlalchemy as sa
app = flask.Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
f'mariadb+pymysql://{os.environ['DB_USER']}:{os.environ['DB_PASSWORD']}'
'@localhost:{os.environ['DB_PORT']}/{os.environ['DB_NAME']}'
)
db = fsa.SQLAlchemy(app)
migrate = fm.Migrate(app, db)
class RecordWithComputed(db.Model):
id = sa.Column(sa.Integer, primary_key=True)
created_at = sa.Column(sa.DateTime, nullable=False)
deleted_at = sa.Column(sa.DateTime)
is_active_record = sa.Column(sa.Boolean, sa.Computed('CASE WHEN deleted_at IS NULL THEN TRUE ELSE NULL END'))
Steps to reproduce
- Create and apply first migration.
- Try creating second migration.
Error
Output for first migration
flask db migrate -m "first migration"
INFO [alembic.runtime.migration] Context impl MariaDBImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'record_with_computed'
Generating /Users/eganma/junk/computed-column-warning/migrations/versions/15d57df5db98_first_migration.py ... done
Output for second migration (no changes made to app.py)
flask db migrate -m "second migration"
INFO [alembic.runtime.migration] Context impl MariaDBImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
/Users/eganma/Library/Caches/pypoetry/virtualenvs/computed-column-warning-swI5pdsd-py3.12/lib/python3.12/site-packages/alembic/autogenerate/compare.py:1034: UserWarning: Computed default on record_with_computed.is_active_record cannot be modified
util.warn("Computed default on %s.%s cannot be modified" % (tname, cname))
INFO [alembic.env] No changes in schema detected.
Strings being compared at compare.py:1029
rendered_metadata_default = 'casewhendeleted_atisnullthentrueelsenullend'
rendered_conn_default = 'casewhendeleted_atisnullthen1elsenullend'
Versions.
- OS: macOS
- Python: 3.12.0
- Alembic: 1.14.1
- SQLAlchemy: 2.0.38
- Database: MariaDB 10.11.6
- DBAPI: pymysql 1.1.1
Additional context
Doesn't look like this is causing problems for me at this time, but previous reports I found (#1151, #1462) were focused on postgres. Issue seems to stem from TRUE being reported as the boolean equivalent 1 when comparing the model against the schema.
did you try changing your model to use "1" instead of "TRUE"?
@CaselIT any suggestions?
not really. this is surprising though, since mysql/mariadb should return the sql that was executed when a table is reflected, contrary to postgresql where it generates the sql from its internal representation.
The fact that true becomes 1 seems to indicate that it's not really always the case
I did. That does get rid of the warning, and alembic reports a clean "no changes in schema detected" as expected.
Again, this is not a stopping me from doing anything. Just thought it would be useful to let you know.
if true reliably becomes 1 I guess it could be added as a normalization rule.
Re-ran the migration to compare what is emitted with echo=True with what show create table ... returns.
Migration file
"""asdf
Revision ID: 4f0b24eb20f6
Revises:
Create Date: 2025-02-11 15:04:16.599130
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = '4f0b24eb20f6'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('record_with_computed',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('deleted_at', sa.DateTime(), nullable=True),
sa.Column('is_active_record', sa.Boolean(), sa.Computed('CASE WHEN deleted_at IS NULL THEN TRUE ELSE NULL END', ), nullable=True),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('record_with_computed')
# ### end Alembic commands ###
SQLAlchemy
flask db upgrade
INFO [sqlalchemy.engine.Engine] SELECT DATABASE()
INFO [sqlalchemy.engine.Engine] [raw sql] {}
INFO [sqlalchemy.engine.Engine] SELECT @@sql_mode
INFO [sqlalchemy.engine.Engine] [raw sql] {}
INFO [sqlalchemy.engine.Engine] SELECT @@lower_case_table_names
INFO [sqlalchemy.engine.Engine] [raw sql] {}
INFO [alembic.runtime.migration] Context impl MariaDBImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO [sqlalchemy.engine.Engine] DESCRIBE `migrations`.`alembic_version`
INFO [sqlalchemy.engine.Engine] [raw sql] {}
INFO [sqlalchemy.engine.Engine] DESCRIBE `migrations`.`alembic_version`
INFO [sqlalchemy.engine.Engine] [raw sql] {}
INFO [sqlalchemy.engine.Engine]
CREATE TABLE alembic_version (
version_num VARCHAR(32) NOT NULL,
CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)
INFO [sqlalchemy.engine.Engine] [no key 0.00004s] {}
INFO [alembic.runtime.migration] Running upgrade -> 4f0b24eb20f6, asdf
INFO [sqlalchemy.engine.Engine]
CREATE TABLE record_with_computed (
id INTEGER NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL,
deleted_at DATETIME,
is_active_record BOOL GENERATED ALWAYS AS (CASE WHEN deleted_at IS NULL THEN TRUE ELSE NULL END),
PRIMARY KEY (id)
)
INFO [sqlalchemy.engine.Engine] [no key 0.00005s] {}
INFO [sqlalchemy.engine.Engine] INSERT INTO alembic_version (version_num) VALUES ('4f0b24eb20f6') RETURNING alembic_version.version_num
INFO [sqlalchemy.engine.Engine] [generated in 0.00008s] {}
INFO [sqlalchemy.engine.Engine] COMMIT
MariaDB
CREATE TABLE `record_with_computed` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created_at` datetime NOT NULL,
`deleted_at` datetime DEFAULT NULL,
`is_active_record` tinyint(1) GENERATED ALWAYS AS (case when `deleted_at` is null then 1 else NULL end) VIRTUAL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci