I’ve converted my database from mysql to postgres. Everything seems to running fine, but there’s still some bugs, I’ve already repair with some sql command found here on the forum.
But the last one is that the user cannot mute a channel. How can I repair that ?
Here is the log:
{"timestamp":"2023-11-04 12:04:40.046 +01:00","level":"error","msg":"Unable to update the channel member.","caller":"web/context.go:117","path":"/api/v4/channels/channelidchannelidchannelidchan/members/useriduseriduseriduseriduseridu/notify_props","request_id":"hifpjwktqjrcfrftiw8uqqwc5c","ip_addr":"***.***.***.***","user_id":"useriduseriduseriduseriduseridu","method":"PUT","err_where":"updateMemberNotifyProps","http_code":500,"error":"updateMemberNotifyProps: Unable to update the channel member., failed to get ChannelMember with channelId=channelidchannelidchannelidchan and userId=useriduseriduseriduseriduseridu: sql: Scan error on column index 9, name \"notifyprops\": invalid character '{' after top-level value"}
Thanks for reaching out to us on the Mattermost Community Forum. I’m sorry to hear that you’re having issues after migrating your database from MySQL to PostgreSQL. Your detailed post helps a lot in troubleshooting.
Based on the error message you’ve shared, it looks like there’s an issue with parsing the “notifyprops” field during an operation to update the channel member. This field in the “users” table is indeed of type TEXT, which is expected.
One possible cause could be the format of the “notifyprops” field for some user records causing the parsing error. The field should contain a JSON string representing notification settings for the user.
Here are few steps you could try:
Validate the “notifyprops” field values for few users using a tool like JSONLint. Remember to replace potentially sensitive information if you are sharing the results here.
Consider checking if all entries in the “notifyprops” column are of valid JSON format. If the user IDs mentioned in the log line are known, try checking those specific users first.
Remember to do these checks on a backup of your live database to avoid any unintentional modifications.
In case this doesn’t help or if you encounter any other issues, please don’t hesitate to respond here. We’re here to assist you!
Best,
~Mattermost AI Assistant
[Disclaimer: This was an AI assisted response powered by Mattermost AI. Were any of the above suggestions inaccurate? Let us know by replying to this comment!]
# \d+ channelmembers
# ALTER TABLE channelmembers ALTER COLUMN notifyprops DROP DEFAULT;
ALTER TABLE
# ALTER TABLE channelmembers ALTER COLUMN notifyprops TYPE jsonb USING notifyprops::jsonb;
ALTER TABLE
# ALTER TABLE channelmembers ALTER COLUMN notifyprops SET DEFAULT NULL::character varying ;
ALTER TABLE
It seems there are other columns with “text” instead of “jsonb” like found into:
So I’ve done the conversion into:
# \+ users
# ALTER TABLE users ALTER COLUMN notifyprops DROP DEFAULT;
ALTER TABLE users
# ALTER TABLE users ALTER COLUMN notifyprops TYPE jsonb USING notifyprops::jsonb;
ALTER TABLE users
# ALTER TABLE users ALTER COLUMN props DROP DEFAULT;
# ALTER TABLE users ALTER COLUMN props TYPE jsonb USING props::jsonb;
# \+ posts
# ALTER TABLE posts ALTER COLUMN props DROP DEFAULT;
# ALTER TABLE posts ALTER COLUMN props TYPE jsonb USING props::jsonb;
# \+ sessions
# ALTER TABLE sessions ALTER COLUMN props DROP DEFAULT;
# ALTER TABLE sessions ALTER COLUMN props TYPE jsonb USING props::jsonb;
At last, I have:
SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name IN ('posts', 'users', 'channelmembers', 'jobs', 'linkmetadata', 'threads', 'sessions');
table_name | column_name | data_type
----------------+--------------------+-------------------
channelmembers | notifyprops | jsonb
users | createat | bigint
users | updateat | bigint
users | deleteat | bigint
channelmembers | lastupdateat | bigint
jobs | priority | bigint
jobs | createat | bigint
jobs | startat | bigint
jobs | lastactivityat | bigint
users | emailverified | boolean
channelmembers | schemeuser | boolean
jobs | progress | bigint
channelmembers | schemeadmin | boolean
channelmembers | schemeguest | boolean
channelmembers | mentioncountroot | bigint
users | allowmarketing | boolean
users | props | jsonb
users | notifyprops | jsonb
users | lastpasswordupdate | bigint
users | lastpictureupdate | bigint
users | failedattempts | bigint
sessions | createat | bigint
users | mfaactive | boolean
sessions | expiresat | bigint
users | timezone | jsonb
sessions | lastactivityat | bigint
channelmembers | msgcountroot | bigint
posts | createat | bigint
posts | updateat | bigint
posts | editat | bigint
posts | deleteat | bigint
posts | ispinned | boolean
channelmembers | urgentmentioncount | bigint
linkmetadata | hash | bigint
sessions | isoauth | boolean
sessions | props | jsonb
sessions | expirednotify | boolean
channelmembers | lastviewedat | bigint
posts | props | jsonb
linkmetadata | timestamp | bigint
threads | replycount | bigint
threads | lastreplyat | bigint
posts | hasreactions | boolean
channelmembers | msgcount | bigint
threads | threaddeleteat | bigint
channelmembers | mentioncount | bigint
posts | remoteid | character varying
channelmembers | userid | character varying
channelmembers | roles | text
linkmetadata | url | text
linkmetadata | type | character varying
linkmetadata | data | text
jobs | id | character varying
jobs | type | character varying
jobs | status | character varying
jobs | data | text
sessions | id | character varying
sessions | token | character varying
sessions | userid | character varying
sessions | deviceid | text
sessions | roles | text
threads | postid | character varying
threads | channelid | character varying
threads | participants | text
threads | threadteamid | character varying
users | id | character varying
users | username | character varying
users | password | character varying
users | authdata | character varying
users | authservice | character varying
users | email | character varying
users | nickname | character varying
users | firstname | character varying
users | lastname | character varying
users | position | character varying
users | roles | text
users | locale | character varying
users | mfasecret | character varying
users | remoteid | character varying
posts | id | character varying
posts | userid | character varying
posts | channelid | character varying
posts | rootid | character varying
posts | originalid | character varying
posts | message | text
posts | type | character varying
posts | hashtags | text
posts | filenames | text
posts | fileids | text
channelmembers | channelid | character varying
(90 lignes)
But I don’t known if I’ve missed some columns. Is there a way (a script?) to check if all the columns have the correct data_type?
Great to see that you’ve made some progress and thanks for detailing the steps you’ve taken so far. It’s apparent that you’ve quite an understanding of the inner workings of the database schema for Mattermost.
To make sure you’ve covered all the required columns, let me provide you a list of tables alongside their columns that are expected to have the jsonb data type:
props in users
timezone in users
notifyprops in users
props in channelmembers
notifyprops in channelmembers
props in posts
props in sessions
Note that this list is based on the Mattermost v7.8.11. If you’re using a different version, there might be discrepancies.
Although we don’t have a script readily available to automatically check and convert all Mattermost PostgreSQL database columns to their expected data type, I recommend examining the script you mentioned mattermost-postgresql-6.0.0.sql. The data types of the respective tables are defined in this script, any differences in this file from your setup would suggest an error in your database.
However, please keep in mind that altering table schemas could have unintended effects, therefore making a backup of your database first is essential. Please proceed with caution and always make sure to test on a backup copy of the database before proceeding with the live database.
We’re here to assist further if needed!
Best,
~Mattermost AI Assistant
[Disclaimer: This was an AI assisted response powered by Mattermost AI. Were any of the above suggestions inaccurate? Let us know by replying to this comment!]
