Managing Database Schema Changes in Mautic - Doctrine Migrations

3 min read

Doctrine is an Object-Relational Mapper (ORM) integrated into Mautic to bridge the gap between PHP code and database schema. It maps Mautic’s PHP entities (e.g., Lead, Campaign) to database tables, ensuring consistency and reducing manual SQL intervention.

Why Use Doctrine?

  • Schema Management: Automatically syncs code changes with the database.
  • Version Control: Tracks schema changes via migrations (timestamped scripts).
  • Safety: Minimises risks of manual errors during updates.

Migrations are critical during upgrades or customisations. They ensure that database changes (e.g., adding the is_read_only column) are applied consistently across environments.

Logical Steps to Resolve Failed Migrations

Scenario: After upgrading Mautic, you encounter an error:

Unknown column 't0.is_read_only' in 'SELECT'

Step 1: Check Migration Status

Identify pending or failed migrations:

php bin/console doctrine:migrations:status --env=prod

This displays:

  • Executed Migrations: Already applied.
  • Available Migrations: Pending (e.g., Mautic\Migrations\Version20241227065658).

Step 2: Run Pending Migrations

Apply missing migrations:

php bin/console doctrine:migrations:migrate --env=prod

If this fails, proceed to manual steps.

Step 3: Manual Intervention

If the migration for is_read_only is missing:

  1. Add the Column Manually:
    ALTER TABLE form_fields ADD COLUMN is_read_only TINYINT(1) DEFAULT 0;
  2. Apply & Mark the Migration as Executed:
    php bin/console doctrine:migrations:version --add "Mautic\Migrations\Version20241227065658" --env=prod
    Replace the version with the correct timestamp from app/migrations/.

Post-Update Procedures

After resolving migration issues:

1. Clear Cache

php bin/console cache:clear --env=prod

This prevents stale schema definitions from causing errors.

2. Verify Functionality

Test forms, campaigns, and reports to ensure the schema change (e.g., is_read_only) works as expected.

3. Check Logs

Monitor var/logs/ for recurring errors (This may differ on your project):

tail -f var/logs/prod.log

Best Practices for Mautic Admins

  1. Backup First

    • Database: Use mysqldump.
    • Files: Archive app/migrations/ and config/.

  2. Test in Staging
    Replicate production environments to validate migrations before deploying.

  3. Avoid doctrine:schema:update in Production
    This command can delete custom fields or columns. Use migrations instead.

  4. Document Changes
    Track migration versions and manual fixes for audits.

  5. Monitor Deprecation Warnings
    Example: doctrine:schema:update without --complete is deprecated.

Further Notes and Common Issues

1. Missing Columns

  • Cause: Migrations skipped during upgrades.
  • Fix: Run migrations or manually add columns (see earlier example).

2. Stuck Migrations

  • Symptom: doctrine:migrations:migrate claims no migrations are pending, but errors persist.
  • Fix (Use with caution as data can be removed): Reset migration versions:
    php bin/console doctrine:migrations:version --add --all --env=prod  
php bin/console doctrine:migrations:migrate --env=prod

3. Custom Fields and Doctrine Limitations

Doctrine struggles with dynamic schema changes (e.g., adding custom fields via the UI). Use plugins or manual SQL for such cases.

4. Upgrade-Specific Issues

For Mautic 5.x upgrades, run:

php bin/console mautic:update:apply

This handles core schema changes.

Resources

Related post