Question
DBIx::Class::Migration::Version rollback safety for non-transactional DDL
Neel Nea
0 reputation · 15 Jan 2026, 04:26 UTC
11.6K views
Loading…
0 reputation · 15 Jan 2026, 04:26 UTC
DBIx::Class::Migration::Version provides a structured way to manage schema evolution using up and down methods. While this framework tracks versioning via a metadata table, the actual execution of Data Definition Language (DDL) statements depends on the underlying database engine's capabilities.
In environments using databases that do not support transactional DDL, such as MySQL, a failure during a migration sequence can leave the schema in a partially migrated state. Because these changes cannot be wrapped in a single transaction, the migration metadata table may not accurately reflect the physical state of the database.
When designing a down method to reverse these changes, there is uncertainty regarding how to ensure atomicity or state consistency when a partial failure occurs during the forward migration.
DBIx::Class::Migration::Version be configured to handle partial DDL failures in non-transactional engines?34775 reputation · 15 Jan 2026, 05:18 UTC
Short answer: you cannot make DBIx::Class::Migration::Version atomic on MySQL or MariaDB, because the limitation is in the database engine, not the framework. CREATE, ALTER, and DROP cause implicit commits, so a failed up or down run leaves a partially applied schema while the version-tracking table reflects only the last completed step. The realistic strategy is defensive down scripts, pre-migration verification, and a tested backup — not configuration.
On PostgreSQL, a failing migration rolls back cleanly and the version table stays truthful. On MySQL, each DDL statement commits as it executes. If step three of five fails, steps one and two are permanent, but the version table was never updated for them. Any later upgrade or downgrade run now operates on assumptions that no longer match reality.
There is no switch that makes non-transactional DDL transactional. What you control is the content of your migration files:
perldown steps idempotent. Guard every statement so re-running after a partial failure does not compound damage — for example, check whether a column or table exists before dropping it, inside the Perl downgrade file.down against a live non-transactional database. Reserve downgrades for development and CI.Before running down, compare the physical schema against what the recorded version implies — do not trust the version table alone:
mysqldump --no-data) and diff it against the schema your target version expects.down then up cycle on a scratch copy loaded with production-shaped data, and confirm the schema diff is empty afterward.To see the failure mode yourself, run a down on a scratch database with a deliberately failing statement in the middle, then inspect both the schema and the version table — the mismatch is exactly what your guards must tolerate.
Implicit-commit behavior varies across MySQL/MariaDB versions and statement types, and version-table handling differs across DBIx::Class::Migration releases — confirm against your installed versions. Also note that a successful downgrade can still destroy data irreversibly (dropped columns are gone); transactional safety and data safety are separate problems, and only the backup addresses the second one.
Use comments to ask for clarification. Post a solution as an answer.
5,940 reputation · 15 Jan 2026, 09:49 UTC
When a migration runs on a non‑transactional engine, the framework cannot roll back automatically. A common pattern is to wrap each statement in a guard that checks the current state before executing it. DBIx::Class::Migration::Version exposes a do helper that can be used like this:
sub up {
my $self = shift;
$self->do(q{CREATE TABLE IF NOT EXISTS new_tbl (id INT)});
$self->do(q{ALTER TABLE new_tbl ADD COLUMN name VARCHAR(255)});
}
Using IF NOT EXISTS (or IF EXISTS for drops) keeps the migration idempotent, so a partial failure can be retried or a down script can safely reverse only the objects that actually exist.
Before running down, introspect the live schema:
my $has_tbl = $schema->storage->dbh->selectrow_array('SHOW TABLES LIKE "new_tbl"');
if ($has_tbl) { $self->do('DROP TABLE new_tbl'); }
This check prevents a DROP from failing if the table was never created, and it keeps the version table in sync with reality.