Problem
User getting the Unable to save channel error when trying to create a new channel / DM. For example:
Environment
- Mattermost Server 5.28.0
- Database migration was done from MySQL to PostgreSQL in the past
Diagnosis
The following error is observed in the mattermost.log when the issue is reproduced. For example:
{"level":"error","ts":XXXXXXXXXX.XXXXXXXXXX,"caller":"app/diagnostics.go:952","msg":"SqlTeamStore.GroupSyncedTeamCount: select error, pq: invalid input syntax for integer: \"true\""},
...
{"level":"error","ts":XXXXXXXXXX.XXXXXXXXXX,"caller":"app/diagnostics.go:957","msg":"SqlChannelStore.GroupSyncedChannelCount: select error, pq: invalid input syntax for integer: \"true\""},
...
{"level":"error","ts":XXXXXXXXXX.XXXXXXXXXX,"caller":"mlog/log.go:190","msg":"Unable to save channel.","path":"/api/v4/channels","request_id":"XXXXXXXXXX","ip_addr":"XXX.XXX.X.XXX","user_id":"XXXXXXXXXX","method":"POST","err_where":"CreateChannel","http_code":500,"err_details":"save_channel: id=XXXXXXXXXX: pq: invalid input syntax for integer: \"false\""}
Checking the Channels table schema shows the following result:
mattermost=# \d Channels
Table "public.channels"
Column | Type | Modifiers
------------------+------------------------+-----------
id | character varying(26) | not null
createat | bigint |
updateat | bigint |
deleteat | bigint |
teamid | character varying(26) |
type | character varying(1) |
displayname | character varying(64) |
name | character varying(64) |
header | text |
purpose | character varying(250) |
lastpostat | bigint |
totalmsgcount | bigint |
extraupdateat | bigint |
creatorid | character varying(26) |
schemeid | character varying(26) |
groupconstrained | smallint |
Cause
The problem here stems from the groupconstrained column which in MySQL is a tinyint while in PostgreSQL supposed to be set to boolean .
Based on the example provided above, smallint was used instead which most likely happened during the database migration.
Resolution
Run the following command to cast smallint to int and then to boolean to fix the problem:
ALTER TABLE channels ALTER COLUMN groupconstrained TYPE bool USING groupconstrained::int::bool;