Interface just shows / search Mails from today

[Thomas Helmrich]

I recognized an issue today:

Default behaviour was if i hit search on the blank in put, it shows me all mails. Now it just shows me the mails from current day. Also if i use the search function, just mails from today (or no results) are shown.

If i login into admin, the last indexed and amount of emails seems good.

What could it be?

Thanks!

[Thomas Helmrich]

Found the entry in FAQ. Default sphinx wasn´t started. On reindex this appears:

failed to add to sph_index table: 446.eml

[Janos SUTO]

It's still the default behaviour, it hasn't changed. I suspect that your sphinx index data might have changed. Check if main1 index has older references as well:

$ mysql -h 127.0.0.1 -P9306
mysql> select * from main1;

and you should see values in id column from 1 to 20.

[Thomas Helmrich]

i see 20 rows, but no values from 1 to 20:

MySQL [(none)]> select * from main1;
+---------+------------+--------+-------------+
| id      | sent       | size   | attachments |
+---------+------------+--------+-------------+
| 2773482 | 1523916104 |  32238 |           0 |
| 2773483 | 1523916199 | 281524 |           1 |
| 2773484 | 1523916421 | 810910 |           1 |
| 2773485 | 1523916606 |   2996 |           0 |
| 2773486 | 1523917321 | 810628 |           1 |
| 2773487 | 1523917986 |   1629 |           0 |
| 2773488 | 1523917986 |   2005 |           0 |
| 2773489 | 1523918216 |  12749 |           0 |
| 2773490 | 1523918221 | 810909 |           1 |
| 2773491 | 1523918334 |   1907 |           0 |
| 2773492 | 1523918334 |   1554 |           0 |
| 2773493 | 1523919072 |  18606 |           0 |
| 2773494 | 1523919072 |  19055 |           0 |
| 2773495 | 1523919092 |  12756 |           0 |
| 2773496 | 1523919122 | 810667 |           1 |
| 2773497 | 1523919870 |  12754 |           0 |
| 2773498 | 1523919841 |   1712 |           0 |
| 2773499 | 1523919841 |   2091 |           0 |
| 2773500 | 1523920021 | 810667 |           1 |
| 2773501 | 1523920145 |   2067 |           0 |
+---------+------------+--------+-------------+

[Janos SUTO]

Something has initialized (=destroyed) your main index file by running 'indexer --all' command. You should find it, and disable it. When you have done it, then you have to reindex all your emails.

[Thomas Helmrich]

I had to rebuild piler (upgraded to stretch) and maybe the postinstall indexer did it? Have nothing other in my /usr /etc or /var where 'indexer --all" appears.

Does i reindex with the folliwing?

cd /tmp
reindex -a

It throws a lot of

failed to add to sph_index table: 655774.eml

[Janos SUTO]

Yes, that should do the reindexing. The 'failed to add' message indicates that it couldn't insert an entry to sph_index table. Probably because it already exists or a problem with the sql query. Check the mail logs if there's any mysql related error.

[Thomas Helmrich]

There are messages like this:

Apr 17 15:54:58 host reindex[22493]: ERROR: 40000000585bc12b033c839c00059f8d7258: mysql_stmt_execute() 'Incorrect string value: '\xFCr Cro...' for column 'body' at row 1' (errno: 1366)
Apr 17 15:54:58 host reindex[22493]: ERROR: 40000000585bc12b033c839c00059f8d7258 failed to store index data for id=8032, sql_errno=1366
Apr 17 15:54:58 host reindex[22493]: ERROR: 40000000585bc12c0673e8ac00346d3649d4: mysql_stmt_execute() 'Data too long for column 'body' at row 1' (errno: 1406)
Apr 17 15:54:58 host reindex[22493]: ERROR: 40000000585bc12c0673e8ac00346d3649d4 failed to store index data for id=8082, sql_errno=1406

Is this fixable?

[Janos SUTO]

\xFCr might be an invalid utf8 sequence. Assuming that sph_index table is set to use utf8mb4, you may need to alter the body column to be mediumblob.

[Thomas Helmrich]

But shouldn´t it be utf8mb4 ?

[Janos SUTO]

The 'body' column should be utf8mb4 and it should be mediumblob type.

[Thomas Helmrich]

Regarding Ticket #709 what is linked in upgrade guide it should be set:

ALTER TABLE piler.sph_index CHANGE body body TEXT CHARACTER SET utf8mb4, COLLATE = utf8mb4_unicode_ci;

Maybe that should be corrected?

[Thomas Helmrich]

So

ALTER TABLE  piler.sph_index MODIFY COLUMN body MEDIUMBLOB;

is the way to go?

[Janos SUTO]

probably, or perhaps

ALTER TABLE sph_index CHANGE COLUMN body body MEDIUMBLOB default NULL;

[Janos SUTO]

Hopefully this issue can be closed.

[Thomas Helmrich]

Yes - thanks!