Question
Moodle Core and Database Systems: Interoperability of Schema‑Change Rollback
Kavi Byte
0 reputation · 18 Jun 2020, 18:22 UTC
77.7K views1
Loading…
0 reputation · 18 Jun 2020, 18:22 UTC
The goal is to determine whether Moodle’s core upgrade engine can guarantee a safe rollback of a schema change across all supported database systems without relying on external backups. Moodle executes DDL statements through XMLDB without wrapping them in a transaction that can be rolled back; PostgreSQL permits transactional DDL and thus automatic rollback, while MySQL and MariaDB implicitly commit DDL, preventing such rollback. The upgrade process records executed changes but does not generate inverse SQL, leaving administrators to depend on backups or manual scripts for any rollback attempt. An open discussion in the Moodle tracker questions whether to add a DB‑agnostic rollback layer (storing reverse XMLDB changes) or to keep relying on external backups, with no agreed‑upon implementation timeline.
35275 reputation · 19 Jun 2020, 06:11 UTC
Moodle cannot safely roll back a schema change on MySQL or MariaDB without external backups. Because these database systems trigger an implicit commit for Data Definition Language (DDL) statements, any schema modification is finalized the moment it is executed. Since Moodle's upgrade engine does not currently generate or store inverse SQL operations, there is no mechanism to undo a change once the DDL has been processed by the server.
To implement a rollback layer that ensures consistency across PostgreSQL, MySQL, and MariaDB, Moodle would need to transition from a forward-only migration to a bidirectional state tracking system. This would require:
moodleadd_field), the system must generate and store a corresponding inverse operation (e.g., drop_field) before execution.DROP operation is destructive and cannot be reversed by SQL alone.Deferring schema execution to a transactional wrapper only when supported by the DBMS is a viable optimization for PostgreSQL, but it does not solve the interoperability problem. If Moodle adopted this approach, it would create a divergent recovery experience: PostgreSQL users would have atomic upgrades, while MySQL/MariaDB users would still require manual intervention.
To maintain a consistent core experience, Moodle should continue to treat backups as the primary recovery mechanism or implement a full DB-agnostic layer. Relying on a hybrid approach (transactional for some, manual for others) increases the risk of application-state mismatch during failed upgrades.
To verify the current schema version and ensure no partial upgrades have occurred, use the following CLI approach:
# Check the current version via the Moodle CLI (if available) or DB query
php admin/cli/upgrade.php --check
Warning: Never attempt to manually revert DDL changes without also reverting the version number in the database, as the upgrade engine will skip necessary steps in future attempts.
Use comments to ask for clarification. Post a solution as an answer.
35,275 reputation · 18 Jun 2020, 19:25 UTC
While a DB-agnostic rollback layer using inverse XMLDB operations addresses structural consistency, it introduces a significant risk regarding data persistence. A structural rollback (e.g., executing a DROP COLUMN to reverse an ADD COLUMN) is functionally simple, but reversing a destructive change is not.
If an upgrade modifies a column type or drops a field to normalize data, a simple inverse SQL statement cannot recover the original values. To ensure a truly safe rollback without external backups, the system would need to implement:
Without these mechanisms, an automated rollback layer would only restore the schema, while the data remains permanently altered or lost.