Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sqlite to postgres migration fails #214

Closed
Nothing4You opened this issue Jul 30, 2020 · 6 comments
Closed

sqlite to postgres migration fails #214

Nothing4You opened this issue Jul 30, 2020 · 6 comments
Labels
bug Something isn't working

Comments

@Nothing4You
Copy link

Nothing4You commented Jul 30, 2020 •
edited
Loading

mautrix-whatsapp 0.1.3+dev.6cb55f39 (Jul 30 2020, 19:26:53)

fresh db, created with the same settings that are recommended for synapse:

CREATE DATABASE matrix_mautrix_whatsapp
 ENCODING 'UTF8'
 LC_COLLATE='C'
 LC_CTYPE='C'
 template=template0
 OWNER matrix_mautrix_whatsapp;
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Database currently on v18, latest: v18
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Database currently on v0, latest: v18
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v1: Initial schema
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v2: Add ON DELETE CASCADE to message table
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v3: Add timestamp column to messages
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v4: Add last_connection column to users
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v5: Update message content to new protocol version. This may take a while.
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v6: Add columns to store custom puppet info
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v7: Add user-portal mapping table
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v8: Add columns to store avatar MXC URIs
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v9: Add columns to store portal in filtering community meta
[18:19:51 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v10: Move state store to main DB
[18:19:52 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v11: Add columns to store full member info in state store
[18:19:52 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v12: Adjust the length of column topic in portal
[18:19:52 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v13: Add encryption status to portal table
[18:19:52 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v14: Add crypto store to database
[18:19:52 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v15: Add outbound group sessions to database
[18:19:52 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v16: Add enable_presence column for puppets
[18:19:52 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v17: Add account_id to crypto store
[18:19:52 30.07.2020] [Database/Upgrade/INFO] Upgrading database to v18: Add enable_receipts column for puppets
Migrating portal: found 66 rows of data, migrating in 2 batches: ## -- done with 65 rows inserted
Migrating user: found 1 rows of data, migrating in 1 batches: # -- done with 1 rows inserted
Migrating puppet: found 214 rows of data, migrating in 4 batches: #### -- done with 211 rows inserted
Migrating user_portal: found 66 rows of data, migrating in 1 batches: panic: pq: insert or update on table "user_portal" violates foreign key constraint "user_portal_portal_jid_fkey"

goroutine 1 [running]:
maunium.net/go/mautrix-whatsapp/database.migrateTable(0xc0000792c0, 0xc0000794f0, 0xd9f9ab, 0xb, 0xc0002a7ac0, 0x4, 0x4, 0x0, 0x0)
	/home/mautrix-whatsapp/mautrix-whatsapp/database/migrate.go:76 +0xde8
maunium.net/go/mautrix-whatsapp/database.Migrate(0xc0000792c0, 0xc0000794f0)
	/home/mautrix-whatsapp/mautrix-whatsapp/database/migrate.go:104 +0x564
main.(*Bridge).MigrateDatabase(0xc00011bee0)
	/home/mautrix-whatsapp/mautrix-whatsapp/main.go:121 +0x364
main.(*Bridge).Main(0xc00011bee0)
	/home/mautrix-whatsapp/mautrix-whatsapp/main.go:397 +0x4b
main.main()
	/home/mautrix-whatsapp/mautrix-whatsapp/main.go:439 +0x116
@tulir tulir added the bug Something isn't working label Jul 30, 2020
@tulir tulir mentioned this issue Aug 1, 2020
Closed
@Pecadis
Copy link

Pecadis commented Oct 22, 2020 •
edited
Loading

I am running into the same issue. Could it be solved, when you actually change the order of the migration? This way it should avoid the issue with the non existing fk reference because the pk is there first.

@Pecadis
Copy link

Pecadis commented Nov 25, 2020 •
edited
Loading

by today, it looks like another migration issue has occured. Namely

Migrating portal: found 156 rows of data, migrating in 3 batches: panic: UNIQUE constraint failed: portal.jid, portal.receiver

mautrix-whatsapp# ./mautrix-whatsapp -c /etc/mautrix-whatsapp/config.yaml --migrate-db sqlite3 /e
tc/mautrix-whatsapp/mautrix-whatsapp.db
[15:23:01 25.11.2020] [Database/Upgrade/INFO] Database currently on v20, latest: v20                                                                                                                                                         [15:23:01 25.11.2020] [Database/Upgrade/INFO] Database currently on v20, latest: v20                                                                                                                                                         Migrating portal: found 156 rows of data, migrating in 3 batches: panic: UNIQUE constraint failed: portal.jid, portal.receiver                                                                                                                                                                                                                                                                                                                                                            goroutine 1 [running]:
maunium.net/go/mautrix-whatsapp/database.migrateTable(0xc00007b360, 0xc00007b590, 0xd996b5, 0x6, 0xc00016fb70, 0x8, 0x8, 0xc000010020, 0xc00016fa48)
        /etc/mautrix-whatsapp/source/database/migrate.go:79 +0xde7
maunium.net/go/mautrix-whatsapp/database.Migrate(0xc00007b360, 0xc00007b590)
        /etc/mautrix-whatsapp/source/database/migrate.go:95 +0x17d
main.(*Bridge).MigrateDatabase(0xc000117930)
        /etc/mautrix-whatsapp/source/main.go:121 +0x35d
main.(*Bridge).Main(0xc000117930)
        /etc/mautrix-whatsapp/source/main.go:400 +0x4b
main.main()
        /etc/mautrix-whatsapp/source/main.go:442 +0x116

oddly enough, doing the migration "from hand" looks good so far for the first 3 tables portal, user and puppet.

was using the latest build

mautrix-whatsapp# ./mautrix-whatsapp --version
mautrix-whatsapp 0.1.4+dev.unknown

@Craeckie
Copy link

Since a recent update I'm getting a different error:

> mautrix-whatsapp -c /data/config.yaml --migrate-db sqlite3 /data/mautrix-whatsapp.db
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0xc0 pc=0xa5a520]

goroutine 1 [running]:
maunium.net/go/mautrix-whatsapp/database.New(0x7fffa363df12, 0x7, 0x7fffa363df1a, 0x19, 0x0, 0x0, 0xdf9, 0xd7c280, 0xc0002bc000)
        /builds/tulir/mautrix-whatsapp/database/database.go:53 +0xc0
main.(*Bridge).MigrateDatabase(0xc0002941a0)
        /builds/tulir/mautrix-whatsapp/main.go:99 +0x8d
main.(*Bridge).Main(0xc0002941a0)
        /builds/tulir/mautrix-whatsapp/main.go:400 +0x4b
main.main()
        /builds/tulir/mautrix-whatsapp/main.go:442 +0x116

Version: mautrix-whatsapp 0.1.5+dev.95f64879 (Jan 25 2021, 19:02:14)

Maybe caused by #260?

@Pecadis
Copy link

Pecadis commented Feb 21, 2021

Hi, i was trying to migrate the db again with a friend of mine and we found two issues with it.

  1. using booleans in sqlite: When importing e.g. the "portal" table, without changing anything, the message
ERROR:  column "encrypted" is of type boolean but expression is of type integer

comes up. When taking a look into the expression the last column for "encrypted" has the "0" or "1" in it. by changing it to "true" or "false" the statement is going through without any noticable issues.
i have identified the following tables to have booleans so far.

  • user_portal
  • puppet
  • portal
  1. in the messages table, the conversion of the "content" colums from bytea type to the postgres equivalent doesn't seem to work properly, unfortunately, i couldn't dig deeper into that but it is giving the following message
ERROR:  column "content" is of type bytea but expression is of type bit
LINE 1: ...XXXXXXXXXXXX', '[email protected]', X'7B22636F...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

If it would only point 1, i could have imported the whole db manually, but i am not sure about the conversion of the binary formats from one to another without risking to corrupt the Informations.

It would be great to get the migration up and running because i don't want to loose the whole chat-histories and portals which has already been created up to now.

@Ma27
Copy link

Ma27 commented Oct 3, 2021

I guess the problem from the OP is related to the fact that 214 rows exist in portal, but only 211 were migrated. I'm currently trying to figure out what went wrong there.

@Nothing4You
Copy link
Author

I can't reproduce this anymore with my current database from current git master.

@github-actions github-actions bot locked as resolved and limited conversation to collaborators Sep 24, 2024
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
bug Something isn't working
Milestone
No milestone
Development

No branches or pull requests
5 participants
@Nothing4You @tulir @Craeckie @Pecadis @Ma27