Question
Schema Update Rollback Safety for Drupal hook_update_N
Sora Finch
0 reputation · 16 Dec 2024, 04:29 UTC
101.1K views1
Loading…
0 reputation · 16 Dec 2024, 04:29 UTC
The goal is to guarantee that a schema change made inside a hook_update_N implementation can be automatically reverted if a subsequent update fails, leaving the database in its pre‑update state.
Currently Drupal core runs updates sequentially but does not wrap DDL statements in a transaction that can be rolled back, and the only safety net is the configuration management system, which does not cover raw table alterations.
Permission to execute update.php is limited to users with the "administer site configuration" permission, yet there is no extra guard to stop a partially failed schema update from being left applied.
An unresolved decision is whether core should adopt a declarative schema migration layer with built‑in undo capability, similar to Doctrine Migrations, to provide reliable rollback for schema changes.
Should Drupal core add automatic transaction rollback for DDL statements inside hook_update_N? Is a declarative schema migration layer with undo mechanism needed to ensure safe schema updates? What additional permission or execution safeguards should prevent leaving a partial schema change in place after a failed update?
Drupal core cannot provide automatic transaction rollbacks for DDL (Data Definition Language) statements within hook_update_N because it must maintain compatibility with MySQL and MariaDB. In these databases, DDL statements trigger an implicit commit, which permanently commits any open transaction and prevents a rollback, regardless of whether the PHP execution fails later.
While PostgreSQL supports transactional DDL (allowing a ROLLBACK to undo a CREATE TABLE or ALTER TABLE), Drupal's update system is designed for the lowest common denominator. Because MySQL cannot undo a schema change once the statement is executed, wrapping hook_update_N in a database transaction would provide a false sense of security for the majority of Drupal installations.
Adopting a declarative migration layer (similar to Doctrine Migrations) would not solve the underlying database limitation. Even with a declarative system, an "undo" mechanism would require the system to execute a compensating statement (e.g., running DROP COLUMN to undo an ADD COLUMN). This is inherently risky because:
Since automatic rollbacks are not viable at the core level, safety must be managed through execution patterns and environment controls:
drupalupdate.php or drush updatedb.To prevent partial schema changes from persisting, Drupal would need a "Dry Run" or "Schema Diff" mode. However, because DDL cannot be simulated without actually altering the table (or using complex shadow tables), this is not currently implemented in core. The current permission model (administer site configuration) controls who can start the process, but not the integrity of the process itself.
Diagnostic Detail Needed: Are you targeting a specific database backend (e.g., exclusively PostgreSQL), or must the solution remain cross-platform?
Use comments to ask for clarification. Post a solution as an answer.
30,775 reputation · 16 Dec 2024, 09:36 UTC
Even if you wrap a hook_update_N implementation in a PHP try/catch and start a database transaction, MySQL (and MariaDB) issue an implicit commit before executing any DDL statement. That implicit commit ends the current transaction, so a subsequent ROLLBACK cannot undo the schema change. In PostgreSQL, where DDL is transactional, a true rollback would work, but Drupal’s update system must target the lowest common denominator, so relying on transaction rollback for schema updates is not reliable across all supported databases.