Sqlite drop table operation not rollbacked during a revision up/downgrade #1554
-
|
Describe the bug Expected behavior To Reproduce
Here is my revision file: from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision: str = "1.0"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
"person",
sa.Column("name", sa.String(), nullable=False),
sa.Column(
"gender",
sa.Enum("MALE", "FEMALE", name="person_gender_model_enum", create_constraint=True),
nullable=False,
),
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column(
"created_at",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.PrimaryKeyConstraint("id")
)
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table("person")
# ### end Alembic commands ###
op.drop_table("toto") # I added this command manually to provoke an error during the SQL transaction of the downgradeHere is my env.py file main methods: ...
def run_migrations_offline() -> None:
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url,
target_metadata=target_metadata,
transactional_ddl=True,
transaction_per_migration=False,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
)
with context.begin_transaction():
context.run_migrations()
def run_migrations_online() -> None:
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section, {}),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
transactional_ddl=True,
transaction_per_migration=False,
)
with context.begin_transaction():
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()Error Although the logs indicate a ROLLBACK in an opened SQL transaction, the table is still deleted in the database file at the end of the command. Versions.
Additional context Thanks. Have a nice day! |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments 3 replies
-
|
Another thing I don't understand: why the SQL transaction is still created during a upgrade()/downgrade() with the configuration transactional_ddl=False, transaction_per_migration=False ?
So why the debug logs of sqlalchemy.engine.Engine still show a transaction is opened and closed? Even if I delete the with context.begin_transaction(): statement in the coding of the run_migrations_online() function, a transaction is still opened and closed according to the logs. :/ |
Beta Was this translation helpful? Give feedback.
-
|
I try several things, and I get some very strange behaviors sometimes, like this one: I don't know what I did particularly for this case, but the command finished with the 0 code, which means OK, but the logs show the SQL transaction finished by a ROLLBACK for an unknown reason, and in the database file, the table person is still created, but there is no version created in the alembic_version table. So it proves that the INSERT INTO and the UPDATE commands have been rollbacked, but not the two CREATE TABLE, and I don't understand why the person table has not been dropped although the logs show it was done. And I still don't know why a transaction is created... |
Beta Was this translation helpful? Give feedback.
-
|
the Python SQLite driver does not do transactional DDL by default. you have to take the steps documented here for SQlite's transactional DDL features to work with the Python sqlite3 driver. |
Beta Was this translation helpful? Give feedback.
-
Thanks for the answer. def run_migrations_online() -> None:
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section, {}),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
@event.listens_for(connectable, "connect")
def do_connect(dbapi_connection, connection_record):
# disable pysqlite's emitting of the BEGIN statement entirely.
# also stops it from emitting COMMIT before any DDL.
dbapi_connection.isolation_level = None
@event.listens_for(connectable, "begin")
def do_begin(conn):
# emit our own BEGIN
conn.exec_driver_sql("BEGIN EXCLUSIVE")
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
transactional_ddl=True,
transaction_per_migration=False,
)
# with context.begin_transaction():
context.run_migrations()Here are the commands and logs: Is it normal to still have the BEGIN (implicit) log? |
Beta Was this translation helpful? Give feedback.
-
|
The rollback you see is caused by the connection being closed then it exits the If you de-comment the |
Beta Was this translation helpful? Give feedback.
-
Oops! My eyes are tired after all these tests, I forgot to uncomment this line indeed. Everything works well now as expected. Thank you both very much! Regards. |
Beta Was this translation helpful? Give feedback.
the Python SQLite driver does not do transactional DDL by default. you have to take the steps documented here for SQlite's transactional DDL features to work with the Python sqlite3 driver.