# Upgrading server from 5.39 to 6.0.2 fails **URL:** \ **Category:** Troubleshooting\ **Created:** [November 9, 2021, 10:18am UTC](https://forum.mattermost.com/t/upgrading-server-from-5-39-to-6-0-2-fails/12504 "2021-11-09T10:18:04Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![elpatron68](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/elpatron68/32/151_2.png) [@elpatron68](https://forum.mattermost.com/u/elpatron68)\ **Post date:** [November 9, 2021, 10:18am UTC](https://forum.mattermost.com/t/upgrading-server-from-5-39-to-6-0-2-fails/12504/1 "2021-11-09T10:18:04Z")
**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](https://docs.mattermost.com/upgrade/upgrading-mattermost-server.html) 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 ---
**Author:** ![amy.blais](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/amy.blais/32/7481_2.png) [@amy.blais](https://forum.mattermost.com/u/amy.blais)\ **Post date:** [November 9, 2021, 2:05pm UTC](https://forum.mattermost.com/t/upgrading-server-from-5-39-to-6-0-2-fails/12504/2 "2021-11-09T14:05:12Z")
cc @streamer45 @isacikgoz on this ---
**Author:** ![Gavin](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/gavin/32/5107_2.png) [@Gavin](https://forum.mattermost.com/u/Gavin)\ **Post date:** [November 9, 2021, 8:50pm UTC](https://forum.mattermost.com/t/upgrading-server-from-5-39-to-6-0-2-fails/12504/3 "2021-11-09T20:50:23Z")
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: ```auto 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: ```auto 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: ```auto UPDATE TableName SET ColumnName = regexp_replace(ColumnName, '\\u0000', '', 'g') WHERE ColumnName LIKE '%\u0000%'; ``` ---
**Author:** ![elpatron68](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/elpatron68/32/151_2.png) [@elpatron68](https://forum.mattermost.com/u/elpatron68)\ **Post date:** [November 10, 2021, 2:00pm UTC](https://forum.mattermost.com/t/upgrading-server-from-5-39-to-6-0-2-fails/12504/4 "2021-11-10T14:00:30Z")
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 ```auto 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)`: ```auto 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. ---
**Author:** ![elpatron68](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/elpatron68/32/151_2.png) [@elpatron68](https://forum.mattermost.com/u/elpatron68)\ **Post date:** [January 20, 2022, 2:09pm UTC](https://forum.mattermost.com/t/upgrading-server-from-5-39-to-6-0-2-fails/12504/5 "2022-01-20T14:09:23Z")
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: ```auto { "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 ---
**Author:** ![amy.blais](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/amy.blais/32/7481_2.png) [@amy.blais](https://forum.mattermost.com/u/amy.blais)\ **Post date:** [January 20, 2022, 3:43pm UTC](https://forum.mattermost.com/t/upgrading-server-from-5-39-to-6-0-2-fails/12504/6 "2022-01-20T15:43:03Z")
cc @agnivade on this. ---
**Author:** ![agnivade](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agnivade/32/3846_2.png) [@agnivade](https://forum.mattermost.com/u/agnivade)\ **Post date:** [January 21, 2022, 7:20am UTC](https://forum.mattermost.com/t/upgrading-server-from-5-39-to-6-0-2-fails/12504/7 "2022-01-21T07:20:57Z")
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. ---
**Author:** ![elpatron68](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/elpatron68/32/151_2.png) [@elpatron68](https://forum.mattermost.com/u/elpatron68)\ **Post date:** [January 21, 2022, 8:43am UTC](https://forum.mattermost.com/t/upgrading-server-from-5-39-to-6-0-2-fails/12504/8 "2022-01-21T08:43:34Z")
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.