Fixing Migration Issues When Upgrading to Version 1.42.1

Introduction

Version 1.42.0 introduced some database schema changes that unfortunately caused migration issues for many users. Version 1.42.1 has been released as a hotfix, but requires a specific cleanup procedure before installation to ensure a smooth upgrade process. This article walks you through the necessary steps to fix these migration problems and successfully upgrade to version 1.42.1.

Issue Background

The 1.42.0 release included changes to the tenant management system that attempted to introduce a new multi-tenancy architecture. These changes were implemented through Flyway migrations in the seatmap-editor service. However, this implementation contained significant flaws that resulted in database inconsistencies and application errors after migration. Specifically, the following issues were identified:

  • Addition of an unused tenant_id column to the organization table
  • Creation of redundant role entries that conflict with existing permission structures
  • Introduction of incomplete tenant-related tables that were not properly integrated with the existing data model
  • Problematic migration script (version ‘75’) that left the database in an inconsistent state

Pre-Installation Cleanup Script

Before upgrading to version 1.42.1, you must run the following SQL cleanup script to remove the problematic database changes introduced in version 1.42.0:

ALTER TABLE organization DROP COLUMN tenant_id;
DELETE FROM users_roles WHERE role_id = 4;
DELETE FROM role WHERE id = 4;
DROP SEQUENCE tenant_id_seq;
DROP TABLE tenants_admins;
DROP TABLE tenant;
DELETE FROM flyway_schema_history WHERE version = '75';

This script performs the following actions:

  1. Removes the unused tenant_id column from the organization table
  2. Deletes the problematic role assignments and role definition
  3. Drops the tenant ID sequence generator
  4. Removes the incomplete tenant-related tables
  5. Removes the broken migration entry from Flyway’s schema history to allow proper re-migration

Step-by-Step Upgrade Process

1. Backup Your Database

Before performing any migration fix, create a complete backup of your PostgreSQL 15+ database:

pg_dump -U your_username -d your_database_name > pre_migration_fix_backup.sql

2. Apply the Cleanup Script

Connect to your PostgreSQL 15+ database using your preferred database client and execute the cleanup script provided above. Ensure you have sufficient privileges to modify schema and delete data.

# Example using psql command line client
psql -U your_username -d your_database_name -f cleanup_script.sql

Alternatively, you can run these commands directly through any PostgreSQL client tool like pgAdmin, DBeaver, or DataGrip.

3. Verify Cleanup Completion

To verify the cleanup was successful, check that:

  • The tenant_id column no longer exists in the organization table
  • No records exist in users_roles with role_id = 4
  • No record exists in role with id = 4
  • The tenant_id_seq sequence, tenants_admins table, and tenant table no longer exist
  • No entry with version ‘75’ exists in the flyway_schema_history table

4. Install Version 1.42.1

After the cleanup is complete, proceed with the installation of version 1.42.1. Since migrations are managed by Flyway in the seatmap-editor service, deploy the updated service version:

# Example deployment commands - adjust according to your deployment process
# For Docker-based deployments
docker pull your-registry/seatmap-editor:1.42.1
docker stop seatmap-editor
docker run -d --name seatmap-editor your-registry/seatmap-editor:1.42.1

# Or if using Kubernetes
kubectl set image deployment/seatmap-editor seatmap-editor=your-registry/seatmap-editor:1.42.1

The seatmap-editor service will automatically run the corrected Flyway migrations during startup.

5. Verify Application Functionality

Once the upgrade is complete, verify that all application functionality is working correctly:

  • Log in as an administrator user
  • Check organization management features
  • Verify user role assignments work properly
  • Test critical business flows

Troubleshooting

If you encounter issues during this process, consider the following PostgreSQL 15+ and Flyway-specific solutions:

Error: “Column tenant_id does not exist”

This suggests the column was already removed. You can proceed with the rest of the cleanup script.

Error: “Relation tenant_id_seq does not exist”

The sequence may have been named differently or already removed. You can proceed with the rest of the cleanup script.

Error: “Role with ID 4 not found”

The problematic role may have already been removed. Proceed with the rest of the cleanup script.

Application Errors After Upgrade

If you encounter application errors after completing the upgrade to 1.42.1, check the seatmap-editor service logs for specific error messages. Common issues include:

  • Missing database indices that may need to be recreated
  • Cached data that needs to be cleared
  • Application services that need to be restarted
  • Flyway checksum mismatches (can be fixed with CALL flyway_schema_history_fix(); if your DBA has created this procedure)
  • Permission issues with the database user used by the seatmap-editor service

Conclusion

The 1.42.1 release addresses the critical issues introduced in version 1.42.0 and provides a more stable platform. By carefully following the cleanup procedure outlined in this article, you can ensure a smooth transition to the latest version without the database inconsistencies that plagued the previous release.

For additional support or if you encounter issues not covered in this guide, please contact our technical support team.

Note: Always test this procedure in a staging environment before applying it to production systems.