Problem
Message export is not properly completing the export and appears stuck. This knowledge base article uses MySQL. If you use PostgreSQL or another database, you may need to modify the commands to what matches in PostgreSQL.
Things to Know
- Batch export will ignore the ‘start’ timestamp in the config if there is a successful job in the jobs table for ‘message_export’.
- It’s not suggested to attempt an export all posts from the beginning on larger systems.
- Lowering the batch size can speed up the overall export process, depending on your system.
Troubleshooting
-
Set the log level to debug.
- When the batch export starts, you’ll see ‘starting batch export’ and ‘found posts to export’, along with a starting timestamp.
-
Run the query for message export and see how long this takes to run for your batch size. The query is at the bottom of this article.
- However long it took to run your batch size select is approximately what you need per batch. Divide your total posts/batch size to see how many batches. Multiply this by the time. This is approximately how much time your system will need.
Restarting your Message Export job manually
If the job, got cancelled because of a timeout or the cancel failed, try restarting the job. This can be fixed by manually setting the job status to pending.
- Find the cancelled job in the DB.
-
SELECT * FROM Jobs WHERE type='message_export' ORDER BY CreateAt DESC LIMIT 5;
-
- If the job shows up from that query that has the `cancel_requested` or `canceled`status , copy the ID.
- Try to restart the job.
-
UPDATE Jobs SET Status='pending' where id='YOUR_ID';
-
- Check in the System Console, if the job successfully runs now. It might take a long time for the job to run.
Fixing your Message Export
If the above troubleshooting steps did not resolve the export issue, you can clear the export data and start fresh by following the below steps.
- Clear the jobs table of all `message_export` jobs by running the following command: delete from Jobs where Type = ‘message_export’ Once you do this, the jobs table (and System Console) should no longer show any jobs.
- Set the config.json to have your export timestamp. You may also need to work on the batch size if you notice it taking too long.
- Restart Mattermost.
- Manually run batch sync in the System Console.
-
On your MySQL, you can run `show processlist` to see the processes that are running. You should see the select statement from the troubleshooting above.
- You can also check the logs. It will take a little for this job to move from `pending` to `in_progress` in the jobs table.
- To check the progress of the job, check the jobs table by running
SELECT * FROM Jobs WHERE type='message_export' ORDER BY CreateAt DESC;
-
- The data & progress columns are what’s important to look at.
Message Export Query
Replace the Posts.UpdateAt timestamp with one that matches what you've configured in the config.json.
Replace the LIMIT 10000 with what your batch size is configured for in the config.json.
SELECT
Posts.Id AS PostId,
Posts.CreateAt AS PostCreateAt,
Posts.UpdateAt AS PostUpdateAt,
Posts.DeleteAt AS PostDeleteAt,
Posts.Message AS PostMessage,
Posts.Type AS PostType,
Posts.Props AS PostProps,
Posts.OriginalId AS PostOriginalId,
Posts.RootId AS PostRootId,
Posts.FileIds AS PostFileIds,
Teams.Id AS TeamId,
Teams.Name AS TeamName,
Teams.DisplayName AS TeamDisplayName,
Channels.Id AS ChannelId,
CASE
WHEN Channels.Type = 'D' THEN 'Direct Message'
WHEN Channels.Type = 'G' THEN 'Group Message'
ELSE Channels.DisplayName
END AS ChannelDisplayName,
Channels.Name AS ChannelName,
Channels.Type AS ChannelType,
Users.Id AS UserId,
Users.Email AS UserEmail,
Users.Username,
Bots.UserId IS NOT NULL AS IsBot
FROM
Posts
LEFT OUTER JOIN Channels ON Posts.ChannelId = Channels.Id
LEFT OUTER JOIN Teams ON Channels.TeamId = Teams.Id
LEFT OUTER JOIN Users ON Posts.UserId = Users.Id
LEFT JOIN Bots ON Bots.UserId = Posts.UserId
WHERE
Posts.UpdateAt > REPLACE_ME_WITH_A_UNIX_TIMESTAMP AND
Posts.Type NOT LIKE 'system_%'
ORDER BY PostUpdateAt
LIMIT 10000
Comments
Article is closed for comments.