search

mautrix / whatsapp

A Matrix-WhatsApp puppeting bridge
https://maunium.net/go/mautrix-whatsapp
GNU Affero General Public License v3.0
1.29k stars 175 forks source link

Failed to migrate legacy database error="FOREIGN KEY constraint failed" #725

Closed freaktechnik closed 1 month ago

freaktechnik commented 1 month ago

Bridge isn't starting, failing with the same error every time.

Oct  4, 2024 12:02:49 INF Initializing bridge built_at=2024-10-03T11:39:51Z go_version=go1.23.2 name=mautrix-whatsapp version=0.11.0+dev.a58f6a91
Oct  4, 2024 12:02:49 DBG Initializing database connection
Oct  4, 2024 12:02:51 INF Detected legacy database, migrating... action="migrate legacy db"
Oct  4, 2024 12:02:51 FTL Failed to migrate legacy database error="FOREIGN KEY constraint failed" db_section=main

Edit: identical logs with version=0.11.0+dev.1f454a82.

progecon commented 1 month ago

I am facing the same issue, the mautrix container keeps restarting

Glotzon commented 1 month ago

Same isssue with my instance:

  1. ``synapse-whatsapp-1 | Oct 6, 2024 08:40:47 INF Initializing bridge built_at=2024-10-04T11:43:44Z go_version=go1.23.2 name=mautrix-whatsapp version=0.11.0+dev.1f454a82
  2. synapse-whatsapp-1 | Oct 6, 2024 08:40:47 DBG Initializing database connection
  3. synapse-whatsapp-1 | Oct 6, 2024 08:40:47 WRN Using SQLite without _txlock=immediate is not recommended fixed_uri_example=file:mautrix-whatsapp.db?_txlock=immediate
  4. synapse-whatsapp-1 | Oct 6, 2024 08:40:47 INF Detected legacy database, migrating... action="migrate legacy db"
  5. synapse-whatsapp-1 | Oct 6, 2024 08:40:47 FTL Failed to migrate legacy database error="FOREIGN KEY constraint failed" db_section=main
  6. synapse-whatsapp-1 exited with code 15``>
progecon commented 1 month ago

@tulir Could you please explain as to why this issue was closed?

freaktechnik commented 1 month ago

Sadly:

Oct 10, 2024 14:51:49 INF Initializing bridge built_at=2024-10-10T12:22:48Z go_version=go1.23.2 name=mautrix-whatsapp version=0.11.0+dev.f758401b
Oct 10, 2024 14:51:49 DBG Initializing database connection
Oct 10, 2024 14:51:49 INF Detected legacy database, migrating... action="migrate legacy db"
Oct 10, 2024 14:51:49 FTL Failed to migrate legacy database error="FOREIGN KEY constraint failed" db_section=main
freaktechnik commented 1 month ago

Still failing:

Oct 16, 2024 14:06:41 INF Initializing bridge built_at=2024-10-16T11:08:42Z go_version=go1.23.2 name=mautrix-whatsapp version=0.11.0
Oct 16, 2024 14:06:41 DBG Initializing database connection
Oct 16, 2024 14:06:41 INF Detected legacy database, migrating... action="migrate legacy db"
Oct 16, 2024 14:06:41 FTL Failed to migrate legacy database error="FOREIGN KEY constraint failed" db_section=main
freaktechnik commented 1 month ago

From trying to apply the migration to a local copy of the sqlite database (first doing the alter tables from https://github.com/mautrix/whatsapp/blob/main/cmd/mautrix-whatsapp/legacymigrate.go, then adding the new tables from https://github.com/mautrix/go/blob/main/bridgev2/database/upgrades/00-latest.sql and then running https://github.com/mautrix/whatsapp/blob/main/cmd/mautrix-whatsapp/legacymigrate.sql) it seems that it's failing on the user_portal insert. I think the problem are entries in user_portal that have no matching jid in portal_old/portal_jid in portal. Adding AND EXISTS(SELECT 1 FROM portal WHERE user_portal_old.portal_jid=portal.id) to the INSERT INTO user_portal query makes it succeed locally.

freaktechnik commented 1 month ago

This si fixed for me as of 220dc219b8f8cc74c39ff42521cf2675188899c9