[SOLVED] We couldn't get the posts for the channel: invalid character '\n' in string literal

Troubleshooting
1

I recently migrated our database from PSQL to MySQL and from 3.7 to 3.9. When I’m trying to load older messages from a particular channel, Mattermost says “We couldn’t get the posts for the channel”.

Looking into logs, the error message, there is a error 500 with the following log line: We couldn’t get the posts for the channel [details: channelId=r1uk98s4spnci8ra8jrkusw18winvalid character ‘\n’ in string literal

I’ve tried to look for that \n in the database but without luck so far. Does anyone have any clue about where that \n might be?

2

Hi @magicknot,

Thanks for your feedback!

To clarify, did you use the upgrade guide here?

If your issue still persists after following this guide, please let us know?

3

Yes I did follow that guide.

4

Thanks @magicknot,

I’ll ask one of our devs whether they have any suggestions / help for your issue and post back here once I hear from them :slight_smile:

5

Thanks @magicknot for the report, a few questions,

  1. At what point did you notice that you could no longer load more messages? After migrating the database, or after upgrading to 3.9?
  2. Were you able to successfully load the channel before the migration?
  3. Would you have any idea if there is some difference between that particular channel compared to the others?
6

@jasonblais sorry for the late response. For some reason I missed the notification about your reply.

  1. I noticed it after migrating the database;
  2. Yes, I was.
  3. One of those channels was mainly used to receive notifications from travis and GitHub, the other one were used normally (as in, chatting and sharing links).

If you prefer, I’m on pre release mattermost, so feel free to ping me if you want :slight_smile:

7

Hi @magicknot,

Just checking whether this issue still exists after upgrading to the latest version of Mattermost? I see your original post was from May and there have been a couple of releases since then…

8

Hi @lindy65,

Yes, I’ve updated to the latest version and I still cannot load all the messages. I can navigate through the channel but there are messages missing. In one of the channels, none of the messages are loading.

9

Thanks for confirming @magicknot,

It’s a holiday in Canada and the US today but when the devs are back tomorrow, I’ll ask them to help troubleshoot.

1 Like
10

Are you able to login into your MySQL db and check some of those message giving you error?

If so, could you see if they literally have \n in the message and replace with a ENTER and see if that particular message stops giving you error?

If so you might need to run a query to convert all messages containing that.

11

@prixone I did the following

select * from Posts where ChannelId like "ACTUAL CHANNEL ID IN LOGS" and Message like '\\\\n';

and got an empty set.

Btw, I failed to mention this on the first post, but I’m using 10.1.23-MariaDB.

12

Well I don’t think it would be \\\\n but just \\n, the thing is, you want to query the database by the post id, so you can view its content to potentially identify what could be wrong in it.

If you had to run a query to update it, it would be something like:

UPDATE `Posts` SET `Message` = REPLACE(`Message`,'\\n', CHAR(10)) WHERE `ChannelId` = 'id';

CHAR(10) is a line feed.

Also, since you’re using LIKE, you would need to use % at the begin and end of it, since you do not know where the literal string is located at, inside the message:

SELECT * FROM Posts WHERE ChannelId = 'id' AND Message LIKE '%\\n%';
13

Thanks for the reply @prixone!

Yes, you were right about the select. And running it I have results, but none of those results have a \n in there.

Also, running the update doesn’t affect any row.

For example, here is one of the messages returned by that select:

+-----------------------------+
| Message                     |
+-----------------------------+
| rcm has joined the channel. |
+-----------------------------+
14

I’ve run some tests on my own mm, \\n will get legit new lines, \\\\n and \\\n will get me the literal text \n, which for me was common to see on the header update messages none affected my channels.

If you have notepad++ the easiest way is to copy the message into it and enable view symbol to verify if u notice anything weird.

Try with an extra \:

SELECT * FROM Posts WHERE ChannelId = 'id' AND Message LIKE '%\\\n%';

Alternatively could you try:

SELECT * FROM Posts WHERE ChannelId = 'id' AND Message LIKE '%\\\"%';
15

Those queries doesn’t return any result :confused:

16

Well the only other thing that comes to mind would be the way you converted it from PSQL to MYSQL that could have broken something be either with the encoding or method used.

Without actual data to test on, I don’t think there is much more I could help you with, sorry.

Maybe some one else had the issue or have some other ideas.

@magicknot was kind enough to take the time and send me some sample data to reproduce the issue. With the sample data I have identified the issue was indeed literal new lines \\n and running the following update I was able to solve the issue on my local test server where I reproduce it with his sample data:

UPDATE `Posts` SET `Props` = REPLACE(`Props`, CHAR(10), CHAR(10)+CHAR(13)) WHERE `ChannelId` = 'channel id here'
1 Like
17

Thanks a lot for your time and help @prixone! :smiley:

2 Likes