Unable to delete own posts (`mattermost failed to update Posts: pq: function jsonb_set(text, unknown, unknown) does not exist`)

Troubleshooting
1

Summary
All users cannot delete their own posts.

Steps to reproduce
We’ve got the problems in 8.1.0 and 9.0.0

  1. Publish a post
  2. Delete the post (with delete or delete all characters + save)
  3. Confirm delete
  4. Reload Mattermost app

Expected behaviour
The post should be deleted.

Observed behaviour

  • Mattermost Desktop App + web ui: the post disappears but reappears after reload or channel switching.
  • Mattermost iOS App
    • No error message with the delete function
    • If I edit the message, delete all characters and save, I’ve got the message »A problem occurred while editing this message. Please try again.«

In the javascript console I’ve got a 500 for https://our.mattermost.example.com/api/v4/posts/5cpws7wotpy8dxxnepazmxtpza with the following detailed error failed to update Posts: pq: function jsonb_set(text, unknown, unknown) does not exist

In the server mattermost.log I’ve got the same message:

{"timestamp":"2023-09-27 09:00:06.363 +02:00","level":"error","msg":"Unable to delete the post.","caller":"web/context.go:117","path":"/api/v4/posts/k3ss75r1rirq9xxwkwwkbtrsue","request_id":"y5on8oknk7futetbte51ii9zda","ip_addr":"87.79.xx.xx","user_id":"577e365riprofda4ps5y14rwea","method":"DELETE","err_where":"DeletePost","http_code":500,"error":"DeletePost: Unable to delete the post., failed to update Posts: pq: function jsonb_set(text, unknown, unknown) does not exist"}

I’m unable to find any solutions for this problem with google.

We’ve migrated our database before upgrading to version 8 from MySQL to postgresql with pgLoader.

We’ve already fixed some wrong smallint types (= converted to boolean) in teammembers and channelmembers.

2

cc @isacikgoz (random text because post needs to be 20 chars minimum)

3

Seems like a schema mismatch issue, @dombn can you post you contents of db_migrations table and also the schema (DDL) for the posts table?

4

Thank you for your answer

public.db_migrations

version name
1 create_teams
2 create_team_members
3 create_cluster_discovery
4 create_command_webhooks
5 create_compliances
6 create_emojis
7 create_user_groups
8 create_group_members
9 create_group_teams
10 create_group_channels
11 create_link_metadata
12 create_commands
13 create_incoming_webhooks
14 create_outgoing_webhooks
15 create_systems
16 create_reactions
17 create_roles
18 create_schemes
19 create_licenses
20 create_posts
21 create_product_notice_view_state
22 create_sessions
23 create_terms_of_service
24 create_audits
25 create_oauth_access_data
26 create_preferences
27 create_status
28 create_tokens
29 create_bots
30 create_user_access_tokens
31 create_remote_clusters
32 create_sharedchannels
33 create_sidebar_channels
34 create_oauthauthdata
35 create_sharedchannelattachments
36 create_sharedchannelusers
37 create_sharedchannelremotes
38 create_jobs
39 create_channel_member_history
40 create_sidebar_categories
41 create_upload_sessions
42 create_threads
43 thread_memberships
44 create_user_terms_of_service
45 create_plugin_key_value_store
46 create_users
47 create_file_info
48 create_oauth_apps
49 create_channels
50 create_channelmembers
51 create_msg_root_count
52 create_public_channels
53 create_retention_policies
54 create_crt_channelmembership_count
55 create_crt_thread_count_and_unreads
56 upgrade_channels_v6.0
57 upgrade_command_webhooks_v6.0
58 upgrade_channelmembers_v6.0
59 upgrade_users_v6.0
60 upgrade_jobs_v6.0
61 upgrade_link_metadata_v6.0
62 upgrade_sessions_v6.0
63 upgrade_threads_v6.0
64 upgrade_status_v6.0
65 upgrade_groupchannels_v6.0
66 upgrade_posts_v6.0
67 upgrade_channelmembers_v6.1
68 upgrade_teammembers_v6.1
69 upgrade_jobs_v6.1
70 upgrade_cte_v6.1
71 upgrade_sessions_v6.1
72 upgrade_schemes_v6.3
73 upgrade_plugin_key_value_store_v6.3
74 upgrade_users_v6.3
75 alter_upload_sessions_index
76 upgrade_lastrootpostat
77 upgrade_users_v6.5
78 create_oauth_mattermost_app_id
79 usergroups_displayname_index
80 posts_createat_id
81 threads_deleteat
82 upgrade_oauth_mattermost_app_id
83 threads_threaddeleteat
84 recent_searches
85 fileinfo_add_archived_column
86 add_cloud_limits_archived
87 sidebar_categories_index
88 remaining_migrations
89 add-channelid-to-reaction
90 create_enums
91 create_post_reminder
92 add_createat_to_teamembers
93 notify_admin
94 threads_teamid
95 remove_posts_parentid
96 threads_threadteamid
97 create_posts_priority
98 create_post_acknowledgements
99 create_drafts
100 add_draft_priority_column
101 create_true_up_review_history
102 posts_originalid_index
103 add_sentat_to_notifyadmin
104 upgrade_notifyadmin
105 remove_tokens
106 fileinfo_channelid
107 threadmemberships_cleanup
108 remove_orphaned_oauth_preferences
109 create_persistent_notifications
111 update_vacuuming

public.posts

CREATE TABLE IF NOT EXISTS public.posts
(
    id character varying(26) COLLATE pg_catalog."default" NOT NULL,
    createat bigint,
    updateat bigint,
    editat bigint,
    deleteat bigint,
    ispinned boolean,
    userid character varying(26) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    channelid character varying(26) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    rootid character varying(26) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    originalid character varying(26) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    message character varying(65535) COLLATE pg_catalog."default",
    type character varying(26) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    props text COLLATE pg_catalog."default",
    hashtags text COLLATE pg_catalog."default",
    filenames text COLLATE pg_catalog."default",
    fileids text COLLATE pg_catalog."default",
    hasreactions boolean,
    remoteid character varying(26) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    CONSTRAINT idx_22752_primary PRIMARY KEY (id)
)
5

Thank you for the info @dombn I think the correct DDL is as following:

CREATE TABLE IF NOT EXISTS  public.posts (
    id character varying(26) NOT NULL,
    createat bigint,
    updateat bigint,
    deleteat bigint,
    userid character varying(26),
    channelid character varying(26),
    rootid character varying(26),
    originalid character varying(26),
    message character varying(65535),
    type character varying(26),
    props jsonb,
    hashtags character varying(1000),
    filenames character varying(4000),
    fileids character varying(300),
    hasreactions boolean,
    editat bigint,
    ispinned boolean,
    remoteid character varying(26)
)

Where props is a jsonb, hashtags, filenames and fileids are size constrained varchars.

Did you follow the guidelines here? Migration guidelines from MySQL to PostgreSQL — Mattermost documentation

1 Like
6

Thank you very much. I’ve able to »repair« our database scheme and we can delete again.

No, I hadn’t found the official guideline (I’ve migrated at the beginning of July 2023). I’ve used:

I’ll use your tools from the new guideline to compare the »official« scheme with our database.

1 Like
7

I see, glad to hear you were able to recover. Feel free to run some schema comparisons and let us know if we can help further.

1 Like