Open zap51 opened 3 months ago
cc @streamer45
@zap51 Could you show the current schema for the calls
table?
\d+ calls
@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
@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 :thinking:
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
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 Right, somehow the JSON based columns (
stats
,props
andparticipants
) 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 https://github.com/mattermost/migration-assist/issues/17#issuecomment-2295739800
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!
Ah, that makes sense. Thanks for checking!
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
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.
@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.
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
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.
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.
@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.
@isacikgoz thanks for the info. I'll give this a try today.
@zap51 - Just checking on this. Have you been able to make any progress here?
Hello, I was able to run other
pgloader
files successfully but thoughpgloader
succeeded for calls, Mattermost server reports the errors. This is postgresql-14.13.pgloader:
And Mattermost server reports the below:
As a result of this, System Statistics does not display any information on Calls plugin.
Regards, Jayanth