Using Official MySQL to Postgresql Guide Resulting in Failure Due to Text vs JSONB Formatting about mattermost HOT 10 OPEN

cc @isacikgoz @agnivade

from mattermost.

@dkeenleyside these are warnings, data should've been migrated anyway. Can you check that?

from mattermost.

I've been learning how to navigate the pgsql cli interface and looked at some of this last night. While the data appears to be present, I suspect there are some formatting issues. Would the data arrive in the correct type (i.e., the target column does not change data type), or will the target table columns need to be manually formatted to the correct type following this?

The key area I need help with is the users section of user management. This displays an error, and will list no users, my thought is there is something specific which needs to be changed, or data which needs to be removed and replaced with some form of default. Can I check a specific log to see where that area would be tripping up?
[this would be helpful here.]

For some strange reason, when switching to the PostgreSQL db for testing, I noted that Boards and playbooks spontaneously came to life... weirdness. So, mainly concentrating on tidying up bad things, and making sure the membership panel works correctly.

from mattermost.

Since pgLoader doesn't create any tables anf if you created the postgres tables with morph, you should be good in terms of column types etc.

If you followed the guide here: https://docs.mattermost.com/deploy/postgres-migration.html#migrate-the-data it should've correctly migrate those data types. There are necessary type casting rules there. In fact you can use https://docs.mattermost.com/deploy/postgres-migration.html#compare-the-data section to conduct a comparison.

So what's the error you are getting regarding to users table? Can you share that?

from mattermost.

Morph was used for preparation, so we're good there.

I have a question regarding that comparison tool in its Github instance. Once I have an answer, I'll be able to use it. I've spent today familiarising myself with the PostgreSQL and Mattermost cli tools.

Since your last message I've experimented with the Mattermost CLI tool as a workaround, I can access the user list, and activate/deactivate/manipulate users using the Mattermost cli tool; at home learning its usage these last couple of hours.

Now, that error you're asking about, there's a very good reason why I'm asking if there are logs I can check:

It's not terribly informative; this is where a popup debug window would be invaluable, as end-users could send you reports with the details of the error - this is a helpful hint.

My current thought is it is drawing on user data which has not migrated correctly, and might need purging, or reformatting. Alternatively, perhaps there is some statistical information it cannot access, and this produces the error, meaning user table-related matters might be a bit of a Red Herring.

from mattermost.

Ah, I was asking errors/logs from the pgLoader output, not from the application. Because we need to be sure if everything successfully migrated or not. I think running application (w/o having a clear idea of whether the data is correct) would not help us. Once you get the chance, can you re-run the migration and share the logs?

PS, to suppress a warning while migrating the schema of Mattermost version v9.6, you can add the following casting type to your migartion.load file:

CAST
     column ChannelBookmarks.Type to "channel_bookmark_type" drop typemod

This would help reducing unnecessary warnings during the migration.

from mattermost.

These are the original migration logs, I'm currently running the Postgresql DB live, mainly testing.
migration.log
migration-posts.log

The posts were migrated separately using the earlier described loader.

So, I'd only be interested in the areas I need to look at specifically, and the data subsets I need to copy across; I only need to load across any faulty bits, or adjust manually.

If I get a decent answer on the dbcmp usage, I'll be able to run it, but it has basically no documentation that allows me to determine the correct formatting of the required DSN fields.

from mattermost.

Okay so from the logs, migration seems to be successful. There are no unexpected errors.

2024-04-09T02:13:01.756011Z ERROR Database error 23505: duplicate key value violates unique constraint "db_migrations_pkey"
DETAIL: Key (version)=(1) already exists.
CONTEXT: COPY db_migrations, line 1
2024-04-09T02:13:02.152014Z ERROR Database error 23505: duplicate key value violates unique constraint "systems_pkey"
DETAIL: Key (name)=(CRTChannelMembershipCountsMigrationComplete) already exists.
CONTEXT: COPY systems, line 18
2024-04-09T02:13:02.228014Z ERROR PostgreSQL warning: word is too long to be indexed
Words longer than 2047 characters are ignored.

These are the errors that we can ignore for now.

Also checking the migration-posts.log file, the migration seems to be successful.

And for the dbcmp, you can use the same DSN values/formatting you were using for Mattermost configuration. We can include that into the documentation. Please keep us posted if you try so. Note that dbcmp wouldn't give granular details if there is a diff, it will only check if the values are equal or not. If there is a diff between databases you'll need to manually check that.

from mattermost.

Okay, we're getting somewhere, example using redactions:

user@mattermost:~/go/bin$ sudo ./dbcmp --source "mmuser:password@tcp(localhost:3306)/mattermost" --target "postgres://mmuser:password@localhost:5432/mattermostdb" --exclude="db_migrations,ir_,focalboard,systems"

Producing:

Database values differ. Tables: Posts, RecentSearches, TeamMembers, ChannelMembers, Roles, Users, ChannelMemberHistory, Bots, Audits, Status, LinkMetadata, Jobs, Teams, FileInfo, UploadSessions, Preferences, Reactions, Sessions, SidebarCategories, Channels, Threads, ProductNoticeViewState

Some changes are expected, such as posts, team members, channel members, and users.

Now I'll need to whip out some tools and have a closer look; which of those do you believe would have the problematic effects on the user member management console?

from mattermost.

@dkeenleyside All of these tables may differ in values if the application started to work after the migration. This comparison should've been done right after the migration w/o application starts again. Is that the case? Otherwise all tables probably will change.

I think we should also see the server logs, do you see any errors/warnings?

from mattermost.