Bleve index fails with "converting NULL to string is unsupported"

[Mayeu]

Summary

Bleve indexing fails on our E10 instances with the following error:

Unable to get the posts batch for indexing. — sql: Scan error on column index 18, name "teamid": converting NULL to string is unsupported

Steps to reproduce

1. Update Mattermost to 5.24.2
2. Activate Bleve
3. Save the configuration
4. Start indexing

Thought I'm pretty sure this is highly dependent of the data in the server

Expected behavior

I expect Bleve indexing to succeed.

Observed behavior (that appears unintentional)

Bleve indexing return the following error in the UI:

Unable to get the posts batch for indexing. — sql: Scan error on column index 18, name "teamid": converting NULL to string is unsupported

The server logs have similar error (but way more):

Jun 27 10:24:45 connect mattermost[24635]: {"level":"info","ts":1593246285.3541756,"caller":"indexer/indexing_job.go:124","msg":"Worker: Indexing job claimed by worker","workername":"BleveIndexer","job_id":"pht3pcp8t3bx8epqydd483oxfc"}
Jun 27 10:24:54 connect mattermost[24635]: {"level":"warn","ts":1593246294.2366376,"caller":"indexer/indexing_job.go:289","msg":"Failed to get posts batch for indexing. Retrying.","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}
Jun 27 10:25:09 connect mattermost[24635]: {"level":"warn","ts":1593246309.245132,"caller":"indexer/indexing_job.go:289","msg":"Failed to get posts batch for indexing. Retrying.","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}
Jun 27 10:25:24 connect mattermost[24635]: {"level":"warn","ts":1593246324.2523487,"caller":"indexer/indexing_job.go:289","msg":"Failed to get posts batch for indexing. Retrying.","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}
Jun 27 10:25:39 connect mattermost[24635]: {"level":"warn","ts":1593246339.2577672,"caller":"indexer/indexing_job.go:289","msg":"Failed to get posts batch for indexing. Retrying.","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}
Jun 27 10:25:54 connect mattermost[24635]: {"level":"warn","ts":1593246354.2641833,"caller":"indexer/indexing_job.go:289","msg":"Failed to get posts batch for indexing. Retrying.","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}
Jun 27 10:26:09 connect mattermost[24635]: {"level":"warn","ts":1593246369.2703948,"caller":"indexer/indexing_job.go:289","msg":"Failed to get posts batch for indexing. Retrying.","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}
Jun 27 10:26:24 connect mattermost[24635]: {"level":"warn","ts":1593246384.2785707,"caller":"indexer/indexing_job.go:289","msg":"Failed to get posts batch for indexing. Retrying.","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}
Jun 27 10:26:39 connect mattermost[24635]: {"level":"warn","ts":1593246399.2848582,"caller":"indexer/indexing_job.go:289","msg":"Failed to get posts batch for indexing. Retrying.","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}
Jun 27 10:26:54 connect mattermost[24635]: {"level":"warn","ts":1593246414.2921576,"caller":"indexer/indexing_job.go:289","msg":"Failed to get posts batch for indexing. Retrying.","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}
Jun 27 10:27:09 connect mattermost[24635]: {"level":"warn","ts":1593246429.2996347,"caller":"indexer/indexing_job.go:289","msg":"Failed to get posts batch for indexing. Retrying.","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}
Jun 27 10:27:24 connect mattermost[24635]: {"level":"error","ts":1593246444.3076475,"caller":"indexer/indexing_job.go:234","msg":"Worker: Failed to index batch for job","workername":"BleveIndexer","job_id":"pht3pcp8t3bx8epqydd483oxfc","error":"SqlPostStore.GetPostContext: Unable to get the posts batch for indexing., sql: Scan error on column index 18, name \"teamid\": converting NULL to string is unsupported"}

Possible fixes

No idea.

[agnivade]

/cc @mgdelacroix

[kingfisher77]

same here

[shieldsjared]

Also receiving this error, in 5.26

[shieldsjared]

Ironically, I'm also getting this error when attempting to use elasticsearch... Digging now to see if I can figure out what's going on here... (E20 instance)

[ethervoid]

Please check what we have done with @shieldsjared probably you're having the same issue

[Mayeu]

Sorry for the delay here, but I did not have time previously to focus on this issue.

Anyway, it was a similar case that the one describes in #15357.

After having checked the messages manually (I had only 62 in my case) and taking a backup of my database, I did run the following:

DELETE FROM Posts
WHERE id IN ( SELECT p.id
            FROM Posts p
            LEFT JOIN Channels c
            ON p.ChannelId = c.Id
             WHERE p.ChannelId IS NULL
                    OR c.Id is NULL
                     OR p.ChannelId = '');

(Don't run that blindly! Be sure to understand what's going on)

Indexing has been running for the past 2h now, but before it was failing after 3 minutes. So I guess we can say this is solved.