Upgrading server from 5.39 to 6.0.2 fails

Troubleshooting
1

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

2

cc @streamer45 @isacikgoz on this

3

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
4

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.

5

More than two months later, I tried to upgrade from 5.39 to the fresh version 6.30. Hoped, that something has changed in the meantime, but it still fails:

{
   "timestamp":"2022-01-20 14:47:37.689 +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:932",
   "error":"pq: default for column \"notifyprops\" cannot be cast automatically to type jsonb",
   "tableName":"ChannelMembers",
   "columnName":"NotifyProps"
}

I would really appreciate any help here, I have no idea how this can be solved.

Thanks in advance
Markus

6

cc @agnivade on this.

7

Hey @elpatron68 - as you can see from the error message, it says “default for column cannot be cast automatically”.

If we look at your schema, we can see that the default value is '{}'::character varying. Therefore, it is failing to change the column type to json.

The fix is to run alter table channelmembers alter column notifyprops drop default; and then run the migration again.

I am not sure how your db got into that state, as we don’t set the default by our side.

1 Like
8

Hi @agnivade , that helped, runs like a charm. Thank you so much! My Mattermost instance is quite old, started with the first Beta versions and received countless updated from then. Maybe that´s the reason for this issue.