Calls: post-migration issues

[zap51]

Hello,
I was able to run other pgloader files successfully but though pgloader succeeded for calls, Mattermost server reports the errors. This is postgresql-14.13.

pgloader:

# pgloader calls.load
2024-08-20T05:03:51.012001Z LOG pgloader version "3.6.10~devel"
2024-08-20T05:03:51.132003Z LOG Migrating from #<MYSQL-CONNECTION mysql://mmuser@localhost:3306/mattermost {1006C69853}>
2024-08-20T05:03:51.132003Z LOG Migrating into #<PGSQL-CONNECTION pgsql://mmuser@localhost:5432/mattermost {1006C69A53}>
2024-08-20T05:03:51.536011Z LOG report summary reset
                    table name     errors       rows      bytes      total time
------------------------------  ---------  ---------  ---------  --------------
                   before load          0          1                     0.028s
               fetch meta data          0         14                     0.072s
                Create Schemas          0          0                     0.000s
              Create SQL Types          0          0                     0.004s
                 Create tables          0         10                     0.024s
                Set Table OIDs          0          5                     0.008s
------------------------------  ---------  ---------  ---------  --------------
              mattermost.calls          0         49    10.9 kB          0.080s
     mattermost.calls_channels          0          9     0.3 kB          0.040s
         mattermost.calls_jobs          0          0                     0.044s
mattermost.db_migrations_calls          0          4     0.1 kB          0.032s
     mattermost.calls_sessions          0          0                     0.024s
------------------------------  ---------  ---------  ---------  --------------
       COPY Threads Completion          0          8                     0.080s
                Create Indexes          0          9                     0.036s
        Index Build Completion          0          9                     0.052s
               Reset Sequences          0          0                     0.040s
                  Primary Keys          0          5                     0.000s
           Create Foreign Keys          0          0                     0.000s
               Create Triggers          0          0                     0.000s
               Set Search Path          0          1                     0.004s
              Install Comments          0          0                     0.000s
                    after load          0          3                     0.028s
------------------------------  ---------  ---------  ---------  --------------
             Total import time          ✓         62    11.3 kB          0.240s

And Mattermost server reports the below:

Aug 20 05:04:13 mm-sbx mattermost[15271]: {"timestamp":"2024-08-20 05:04:13.639 Z","level":"error","msg":"failed to get all calls channels","caller":"app/plugin_api.go:1011","plugin_id":"com.mattermost.calls","origin":"main.(*Plugin).handleGetAllCallChannelStates api.go:169","err":"failed to get calls channels: sql: Scan error on column index 2, name \"props\": unsupported source type string"}
Aug 20 05:09:40 mm-sbx mattermost[15271]: {"timestamp":"2024-08-20 05:09:40.050 Z","level":"error","msg":"public error message: An internal error has occurred. Check app server logs for details.; internal details: failed to get stats from store: failed to get average call participants: pq: function jsonb_array_length(text) does not exist","caller":"app/plugin_api.go:1011","plugin_id":"com.mattermost.calls","origin":"main.(*Plugin).handleErrorWithCode error_handler.go:27"}

As a result of this, System Statistics does not display any information on Calls plugin.

Regards,
Jayanth

[agnivade]

cc @streamer45

[streamer45]

@zap51 Could you show the current schema for the calls table?

\d+ calls

[zap51]

@streamer45

mattermost# \d+ public."calls";
                                                     Table "public.calls"
    Column    |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id           | character varying(26)  |           | not null |         | extended |             |              | 
 channelid    | character varying(26)  |           |          |         | extended |             |              | 
 startat      | bigint                 |           |          |         | plain    |             |              | 
 endat        | bigint                 |           |          |         | plain    |             |              | 
 createat     | bigint                 |           |          |         | plain    |             |              | 
 deleteat     | bigint                 |           |          |         | plain    |             |              | 
 title        | character varying(256) |           |          |         | extended |             |              | 
 postid       | character varying(26)  |           |          |         | extended |             |              | 
 threadid     | character varying(26)  |           |          |         | extended |             |              | 
 ownerid      | character varying(26)  |           |          |         | extended |             |              | 
 participants | text                   |           | not null |         | extended |             |              | 
 stats        | text                   |           | not null |         | extended |             |              | 
 props        | text                   |           | not null |         | extended |             |              | 
Indexes:
    "idx_24766_primary" PRIMARY KEY, btree (id)
    "idx_calls_channel_id" btree (channelid)
    "idx_calls_end_at" btree (endat)
Access method: heap

[streamer45]

@zap51 Right, somehow the JSON based columns (stats , props and participants) were not converted correctly. Wondering if there were JSON on the MySQL version to start with 🤔

Anyhow, to fix this now, you'd have to convert them to what's expected by the app:

ALTER TABLE calls ALTER COLUMN participants TYPE jsonb USING participants::jsonb;
ALTER TABLE calls ALTER COLUMN props TYPE jsonb USING props::jsonb;
ALTER TABLE calls ALTER COLUMN stats TYPE jsonb USING stats::jsonb;

/cc @isacikgoz in case you have thoughts

[zap51]

Thanks for the hint, @streamer45. In my other instance which has Postgres from the start says jsonb

mattermost=# \d+ calls;
                                              Table "public.calls"
    Column    |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 id           | character varying(26)  |           | not null |         | extended |              | 
 channelid    | character varying(26)  |           |          |         | extended |              | 
 startat      | bigint                 |           |          |         | plain    |              | 
 endat        | bigint                 |           |          |         | plain    |              | 
 createat     | bigint                 |           |          |         | plain    |              | 
 deleteat     | bigint                 |           |          |         | plain    |              | 
 title        | character varying(256) |           |          |         | extended |              | 
 postid       | character varying(26)  |           |          |         | extended |              | 
 threadid     | character varying(26)  |           |          |         | extended |              | 
 ownerid      | character varying(26)  |           |          |         | extended |              | 
 participants | jsonb                  |           | not null |         | extended |              | 
 stats        | jsonb                  |           | not null |         | extended |              | 
 props        | jsonb                  |           | not null |         | extended |              | 
Indexes:
    "calls_pkey" PRIMARY KEY, btree (id)
    "idx_calls_channel_id" btree (channelid)
    "idx_calls_end_at" btree (endat)
Access method: heap

I'm converting that to jsonb as per your suggestion in some time and let you know.

[zap51]

@zap51 Right, somehow the JSON based columns (stats , props and participants) were not converted correctly. Wondering if there were JSON on the MySQL version to start with 🤔

@streamer45,
The source MySQL has longtext. Infact our existing prod DBMS is MariaDB and for the sake of this migration I had to take a mysqldump and import to mysql-8.0 server. Could this be the possible issue? Please see #17 (comment)

mysql> show columns from calls;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ID           | varchar(26)  | NO   | PRI | NULL    |       |
| ChannelID    | varchar(26)  | YES  | MUL | NULL    |       |
| StartAt      | bigint       | YES  |     | NULL    |       |
| EndAt        | bigint       | YES  | MUL | NULL    |       |
| CreateAt     | bigint       | YES  |     | NULL    |       |
| DeleteAt     | bigint       | YES  |     | NULL    |       |
| Title        | varchar(256) | YES  |     | NULL    |       |
| PostID       | varchar(26)  | YES  |     | NULL    |       |
| ThreadID     | varchar(26)  | YES  |     | NULL    |       |
| OwnerID      | varchar(26)  | YES  |     | NULL    |       |
| Participants | longtext     | NO   |     | NULL    |       |
| Stats        | longtext     | NO   |     | NULL    |       |
| Props        | longtext     | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

Thanks!

[streamer45]

Ah, that makes sense. Thanks for checking!

[zap51]

Thanks @streamer45. Looks like I'll have to be more careful while doing MariaDB to MySQL.
This instance was installed with MariaDB back in 2021, would be helpful if there are certain things which need focus from me when performing a restore using mysqldump.

Thanks

[zap51]

I better check the DB migrations and perform data type changes. Would you please suggest if the mysql column data type changes yield any undesirable results or any pointers for me to consider?

I'll rather attempt performing a fresh installation of MM with MySQL and compare the column data types of all tables against my MariaDB one.

[streamer45]

@zap51 I think in MySQL JSON is backward compatible with text based columns so it will probably work fine. But in Postgres that's obviously not the case since jsonb is a separate binary format.

MariaDB has not been supported by MM for a long time so I think a clean install could be a good solution.

[isacikgoz]

Actually there was a --full-schema-check flag for MySQL. It basically creates a brand new MySQL schema and does a comparison, you could've used that to ensure you had the right schema.

Maybe we should add the full-schema-check to the guide (I'm just inclined not to add it by default as it may create some confusion). But let me know if you think otherwise.

cc @streamer45 @zap51

[zap51]

Actually there was a --full-schema-check flag for MySQL. It basically creates a brand new MySQL schema and does a comparison, you could've used that to ensure you had the right schema.

Maybe we should add the full-schema-check to the guide (I'm just inclined not to add it by default as it may create some confusion). But let me know if you think otherwise.

cc @streamer45 @zap51

@isacikgoz thanks for the info. I'll give this a try today.

[streamer45]

Actually there was a --full-schema-check flag for MySQL. It basically creates a brand new MySQL schema and does a comparison, you could've used that to ensure you had the right schema.

Maybe we should add the full-schema-check to the guide (I'm just inclined not to add it by default as it may create some confusion). But let me know if you think otherwise.

@isacikgoz Why do you think it would be confusing? If we can make it happen in the background and print a warning or error, we could prevent a deeper problem later on when using the product. It's not a huge deal in the case of Calls, but it'd be great to find a workaround.

[isacikgoz]

@streamer45 Right, the confusion may come from interpreting the diff and being able to fix it. But all in all it should indicate the error earlier indeed.

@zap51 Just realized that I may rushed with my recommendation as --full-schema-check compares the Mattermost DB only but not custom plugin tables, so that check may not surface this specific case.