We're Hiring!

Mattermost Discussion Forums

Upgrading server from 5.39 to 6.0.2 fails

Summary
Upgrading Mattermost server from 5.39 to 6.0.2 fails

Steps to reproduce
Ubuntu 20.04.3 LTS
PostgreSQL 10.17
Mattermost 5.39

Upgrade following the documentation fails:

{"timestamp":"2021-11-09 10:57:43.933 +01:00","level":"fatal","msg":"Failed to alter column type. It is likely you have invalid JSON values in the column. Please fix the values manually and run the migration again.","caller":"sqlstore/store.go:860","error":"pq: default for column \"notifyprops\" cannot be cast automatically to type jsonb","tableName":"ChannelMembers","columnName":"NotifyProps"}

I have seen several similar posts concerning database schema alteration problems, but I haven´t found a solution that works for me. Any ideas?

Regards
Markus

cc @streamer45 @isacikgoz on this

you will need to the notes that the provided

Customers upgrading from releases older than v5.35 following our recommended upgrade process may encounter the following error during the upgrade to v6.0:

Failed to alter column type. It is likely you have invalid JSON values in the column. Please fix the values manually and run the migration again.",“caller”:“sqlstore/store.go:854”,“error”:"pq: unsupported Unicode escape sequence

To assist with troubleshooting, you can enable SqlSettings.Trace to narrow down what table and column are causing issues during the upgrade. The following queries change the columns to JSONB format in PostgreSQL. Run these against your v5.39 development database to find out which table and column has Unicode issues:

ALTER TABLE posts ALTER COLUMN props TYPE jsonb USING props::jsonb;
ALTER TABLE channelmembers ALTER COLUMN notifyprops TYPE jsonb USING notifyprops::jsonb;
ALTER TABLE jobs ALTER COLUMN data TYPE jsonb USING data::jsonb;
ALTER TABLE linkmetadata ALTER COLUMN data TYPE jsonb USING data::jsonb;
ALTER TABLE sessions ALTER COLUMN props TYPE jsonb USING props::jsonb;
ALTER TABLE threads ALTER COLUMN participants TYPE jsonb USING participants::jsonb;
ALTER TABLE users ALTER COLUMN props TYPE jsonb USING props::jsonb;
ALTER TABLE users ALTER COLUMN notifyprops TYPE jsonb USING notifyprops::jsonb;
ALTER TABLE users ALTER COLUMN timezone TYPE jsonb USING timezone::jsonb;

Once you’ve identified the table being affected, verify how many invalid occurrences of u0000 you have using the following SELECT query:

SELECT COUNT(*) FROM TableName WHERE ColumnName LIKE '%\u0000%';

Then select and fix the rows accordingly. If you prefer, you can also fix all occurrences at once in a given table or column using the following UPDATE query:

UPDATE TableName SET ColumnName = regexp_replace(ColumnName, '\\u0000', '', 'g') WHERE ColumnName LIKE '%\u0000%';
1 Like

Thanks for helping, @Gavin

I tried the following (on database table channelmembers):

ALTER TABLE channelmembers ALTER COLUMN notifyprops TYPE jsonb USING notifyprops::jsonb;

Output:

ERROR: default for column "notifyprops" cannot be cast automatically to type jsonb

SELECT COUNT(*) FROM channelmembers WHERE notifyprops LIKE '%\u0000%';
results

 count 
-------
     0
(1 row)

What does this mean? No affected rows?

After that, I tried to update the table

UPDATE channelmembers SET notifyprops = regexp_replace(notifyprops, '\\u0000', '', 'g') WHERE notifyprops LIKE '%\u0000%';

which leads to UPDATE 0, but \d channelmembers shows notifyprops is still of type character varying(2000):

                                Table "public.channelmembers"
      Column      |          Type           | Collation | Nullable |         Default         
------------------+-------------------------+-----------+----------+-------------------------
 channelid        | character varying(26)   |           | not null | 
 userid           | character varying(26)   |           | not null | 
 roles            | character varying(64)   |           |          | 
 lastviewedat     | bigint                  |           |          | 
 msgcount         | bigint                  |           |          | 
 mentioncount     | bigint                  |           |          | 
 lastupdateat     | bigint                  |           |          | 
 notifyprops      | character varying(2000) |           |          | '{}'::character varying
 schemeuser       | boolean                 |           |          | 
 schemeadmin      | boolean                 |           |          | 
 schemeguest      | boolean                 |           |          | 
 mentioncountroot | bigint                  |           |          | 0
 msgcountroot     | bigint                  |           |          | 0
Indexes:
    "channelmembers_pkey" PRIMARY KEY, btree (channelid, userid)
    "idx_channelmembers_user_id" btree (userid)

Sorry, I´m no database expert, maybe my question sounds stupid.