Migrations failing when upgrading to 1.12.1

[WoodyWoodsta]

What went wrong?

What happened:

• Upgrading to 1.12.1 from 1.11.3, the migration job that runs shows the following error:

Apply all migrations: admin, alerts, auth, auth_token, base, contenttypes, email, exotel, fcm_django, google, heartbeat, labels, mobile_app, oss_installation, phone_notifications, schedules, sessions, slack, social_django, telegram, twilioapp, user_management, webhooks, zvonok
Running migrations:
source=engine:app google_trace_id=none logger=apps.alerts.migrations.0063_migrate_channelfilter_slack_channel_id Starting migration to populate slack_channel field.
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/django/db/backends/utils.py", line 87, in _execute
return self.cursor.execute(sql)
^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.SyntaxError: syntax error at or near "JOIN"
LINE 3: JOIN alerts_alertreceivechannel AS arc ON arc.id = cf.al...
^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/etc/app/manage.py", line 34, in <module>
execute_from_command_line(sys.argv)
File "/usr/local/lib/python3.12/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
utility.execute()
File "/usr/local/lib/python3.12/site-packages/django/core/management/__init__.py", line 436, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/usr/local/lib/python3.12/site-packages/django/core/management/base.py", line 412, in run_from_argv
self.execute(*args, **cmd_options)
File "/usr/local/lib/python3.12/site-packages/django/core/management/base.py", line 458, in execute
output = self.handle(*args, **options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/django/core/management/base.py", line 106, in wrapper
res = handle_func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/django/core/management/commands/migrate.py", line 356, in handle
post_migrate_state = executor.migrate(
^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/django/db/migrations/executor.py", line 135, in migrate
state = self._migrate_all_forwards(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
state = self.apply_migration(
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
state = migration.apply(state, schema_editor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/django/db/migrations/migration.py", line 132, in apply
operation.database_forwards(
File "/usr/local/lib/python3.12/site-packages/django/db/migrations/operations/special.py", line 193, in database_forwards
self.code(from_state.apps, schema_editor)
File "/etc/app/apps/alerts/migrations/0063_migrate_channelfilter_slack_channel_id.py", line 29, in populate_slack_channel
cursor.execute(sql)
File "/usr/local/lib/python3.12/site-packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers

What did you expect to happen:

• Migrations run successfully

How do we reproduce it?

1. Upgrade to 1.12.1

Grafana OnCall Version

v1.12.1

Product Area

Helm/Kubernetes/Docker

Grafana OnCall Platform?

Kubernetes

User's Browser?

No response

Anything else to add?

No response

[mrbaowei]

this issue has also been reproduced on my end.

[mrbaowei]

this issue has also been reproduced on my end.

Rolling back to v1.12.0 is not a problem, but this problem will occur on v1.12.1.

[alext-extracellular]

can also confirm this is happening on a fresh installation using the given docker-compose files.

[lucasfcnunes]

Same issue on migrating from oncall 1.11.0 to 1.12.1.
Oncall 1.12.0 works fine.

[Elocanelon]

Same issue installing helm chart v1.12.1, I use the v1.12.0 and the chart deployed all successfully

[Lamovich]

Same issue on migrating from oncall 1.11.5 to 1.12.1

Let me add: after unsuccessful migration and rollback of the deployment to version 1.11.5, routes in integration in the web stopped showing. Through api query routes are visible.
I had to restore the database from backup.

[mwheeler-ep]

Just started a development instance and ran into this on both postgres and sqlite.

I'm not sure if this is correct - but this is what I did to work around the issue for getting a dev instance to start up. (please take care if using this patch set against a prod instance) - also not sure how performant these migrations would be in the real world.

diff --git a/engine/apps/alerts/migrations/0063_migrate_channelfilter_slack_channel_id.py b/engine/apps/alerts/migrations/0063_migrate_channelfilter_slack_channel_id.py
index dab5a459..9b589024 100644
--- a/engine/apps/alerts/migrations/0063_migrate_channelfilter_slack_channel_id.py
+++ b/engine/apps/alerts/migrations/0063_migrate_channelfilter_slack_channel_id.py
@@ -15,14 +15,20 @@ def populate_slack_channel(apps, schema_editor):
     logger.info("Starting migration to populate slack_channel field.")
 
     sql = f"""
-    UPDATE {ChannelFilter._meta.db_table} AS cf
-    JOIN {AlertReceiveChannel._meta.db_table} AS arc ON arc.id = cf.alert_receive_channel_id
-    JOIN {Organization._meta.db_table} AS org ON org.id = arc.organization_id
-    JOIN {SlackChannel._meta.db_table} AS sc ON sc.slack_id = cf._slack_channel_id
-                                   AND sc.slack_team_identity_id = org.slack_team_identity_id
-    SET cf.slack_channel_id = sc.id
-    WHERE cf._slack_channel_id IS NOT NULL
-      AND org.slack_team_identity_id IS NOT NULL;
+
+    with temp as (
+        SELECT cf_s.slack_channel_id as slack_channel_id, sc.id as id 
+        FROM {ChannelFilter._meta.db_table} AS cf_s
+        JOIN {AlertReceiveChannel._meta.db_table} AS arc ON arc.id = cf_s.alert_receive_channel_id
+        JOIN {Organization._meta.db_table} AS org ON org.id = arc.organization_id
+        JOIN {SlackChannel._meta.db_table} AS sc ON sc.slack_id = cf_s._slack_channel_id
+                                    AND sc.slack_team_identity_id = org.slack_team_identity_id
+        WHERE org.slack_team_identity_id IS NOT NULL and cf_s._slack_channel_id IS NOT NULL
+    )
+    UPDATE {ChannelFilter._meta.db_table} as update_cf
+    SET slack_channel_id = temp.id
+    FROM temp
+    where update_cf.slack_channel_id = temp.slack_channel_id
     """
 
     with schema_editor.connection.cursor() as cursor:
diff --git a/engine/apps/alerts/migrations/0064_migrate_resolutionnoteslackmessage_slack_channel_id.py b/engine/apps/alerts/migrations/0064_migrate_resolutionnoteslackmessage_slack_channel_id.py
index 4f492e31..a59254ca 100644
--- a/engine/apps/alerts/migrations/0064_migrate_resolutionnoteslackmessage_slack_channel_id.py
+++ b/engine/apps/alerts/migrations/0064_migrate_resolutionnoteslackmessage_slack_channel_id.py
@@ -17,15 +17,24 @@ def populate_slack_channel(apps, schema_editor):
     logger.info("Starting migration to populate slack_channel field.")
 
     sql = f"""
-    UPDATE {ResolutionNoteSlackMessage._meta.db_table} AS rsm
-    JOIN {AlertGroup._meta.db_table} AS ag ON ag.id = rsm.alert_group_id
+
+    with temp as (
+    SELECT rsm_t._slack_channel_id as _slack_channel_id, sc.id as id
+    FROM {ResolutionNoteSlackMessage._meta.db_table} AS rsm_t
+    JOIN {AlertGroup._meta.db_table} AS ag ON ag.id = rsm_t.alert_group_id
     JOIN {AlertReceiveChannel._meta.db_table} AS arc ON arc.id = ag.channel_id
     JOIN {Organization._meta.db_table} AS org ON org.id = arc.organization_id
-    JOIN {SlackChannel._meta.db_table} AS sc ON sc.slack_id = rsm._slack_channel_id
+    JOIN {SlackChannel._meta.db_table} AS sc ON sc.slack_id = rsm_t._slack_channel_id
                            AND sc.slack_team_identity_id = org.slack_team_identity_id
-    SET rsm.slack_channel_id = sc.id
-    WHERE rsm._slack_channel_id IS NOT NULL
-      AND org.slack_team_identity_id IS NOT NULL;
+        WHERE rsm_t._slack_channel_id IS NOT NULL
+      AND org.slack_team_identity_id IS NOT NULL
+    )
+
+
+    UPDATE {ResolutionNoteSlackMessage._meta.db_table} AS rsm
+    SET slack_channel_id = temp.id
+    FROM temp
+    WHERE rsm._slack_channel_id = temp._slack_channel_id
     """
 
     with schema_editor.connection.cursor() as cursor:
diff --git a/engine/apps/schedules/migrations/0019_auto_20241021_1735.py b/engine/apps/schedules/migrations/0019_auto_20241021_1735.py
index edc89366..518f16ed 100644
--- a/engine/apps/schedules/migrations/0019_auto_20241021_1735.py
+++ b/engine/apps/schedules/migrations/0019_auto_20241021_1735.py
@@ -14,13 +14,21 @@ def populate_slack_channel(apps, schema_editor):
     logger.info("Starting migration to populate slack_channel field.")
 
     sql = f"""
+    with temp as (
+        SELECT ocs_t.slack_channel_id as slack_channel_id, sc.id as id
+        FROM {OnCallSchedule._meta.db_table} AS ocs_t
+        JOIN {Organization._meta.db_table} AS org ON org.id = ocs_t.organization_id
+        JOIN {SlackChannel._meta.db_table} AS sc ON sc.slack_id = ocs_t.channel
+                            AND sc.slack_team_identity_id = org.slack_team_identity_id
+        WHERE ocs_t.channel IS NOT NULL
+        AND org.slack_team_identity_id IS NOT NULL
+    )
+
     UPDATE {OnCallSchedule._meta.db_table} AS ocs
-    JOIN {Organization._meta.db_table} AS org ON org.id = ocs.organization_id
-    JOIN {SlackChannel._meta.db_table} AS sc ON sc.slack_id = ocs.channel
-                         AND sc.slack_team_identity_id = org.slack_team_identity_id
-    SET ocs.slack_channel_id = sc.id
-    WHERE ocs.channel IS NOT NULL
-      AND org.slack_team_identity_id IS NOT NULL;
+    SET slack_channel_id = temp.id
+    FROM temp
+    WHERE ocs.slack_channel_id = temp.slack_channel_id
+    
     """
 
     with schema_editor.connection.cursor() as cursor:
diff --git a/engine/apps/user_management/migrations/0026_auto_20241017_1919.py b/engine/apps/user_management/migrations/0026_auto_20241017_1919.py
index df28b026..9006d0f8 100644
--- a/engine/apps/user_management/migrations/0026_auto_20241017_1919.py
+++ b/engine/apps/user_management/migrations/0026_auto_20241017_1919.py
@@ -14,12 +14,20 @@ def populate_default_slack_channel(apps, schema_editor):
     logger.info("Starting migration to populate default_slack_channel field.")
 
     sql = f"""
+    with temp as (
+        SELECT  org_t.default_slack_channel_id as default_slack_channel_id, sc.id as id
+        FROM {Organization._meta.db_table} AS org_t
+        JOIN {SlackChannel._meta.db_table} AS sc ON sc.slack_id = org_t.general_log_channel_id
+                            AND sc.slack_team_identity_id = org_t.slack_team_identity_id
+        WHERE org_t.general_log_channel_id IS NOT NULL
+        AND org_t.slack_team_identity_id IS NOT NULL
+    )
+
+
     UPDATE {Organization._meta.db_table} AS org
-    JOIN {SlackChannel._meta.db_table} AS sc ON sc.slack_id = org.general_log_channel_id
-                         AND sc.slack_team_identity_id = org.slack_team_identity_id
-    SET org.default_slack_channel_id = sc.id
-    WHERE org.general_log_channel_id IS NOT NULL
-      AND org.slack_team_identity_id IS NOT NULL;
+    SET default_slack_channel_id = temp.id
+    FROM temp
+    WHERE org.default_slack_channel_id = temp.default_slack_channel_id
     """
 
     with schema_editor.connection.cursor() as cursor:

[tarvip]

Migration from 1.12.0 to 1.13.1 worked fine. I guess it is best to skip 1.12.1.

EDIT: Also from 1.13.1 to 1.13.2. Probably upgrading directly to 1.13.2 will work as well.

[WoodyWoodsta]

I wasn't able to go past 1.12.1, instead, I had to step through the individual migrations and --fake the ones that were broken.

[chuchynz]

Migration from 1.12.0 to 1.13.1 worked fine. I guess it is best to skip 1.12.1.

EDIT: Also from 1.13.1 to 1.13.2. Probably upgrading directly to 1.13.2 will work as well.

This was the same experience for me.

[bpedersen2]

Check https://stackoverflow.com/a/7869611, the syntax in the migrations is mysql-only.

[joeyorlando]

hello! Yes, raw SQL was needed here 🙁 (as for reason's stated here, this sort of migration is not possible in a performant way using the ORM).

The quickest way around this is by using --fake on the affected migrations, not ideal, but will get you around this (note that these are data migrations related to Slack messages, so actions on older Slack messages may not fully work; but going forward, things will function as intended)

I see @mwheeler-ep already has a starting point for postgres/sqlite compliant SQL, if someone wants to open a PR to patch these migration files to work on those databases as well (you can do something very similar to this upcoming migration file), we can definitely take a look at that!