Incorrect string value / for column `piler`.`sph_index`.`to` at row 1' (errno: 1366)

[Jan]

Hi.

We have some problems while importing Mail to a new installation.

‌

root@SRV-MAILARCHIV-NEU:/tmp# pilerimport -e /IMPORT/321718.eml
processing: /IMPORT/321718.eml
failed to import: /IMPORT/321718.eml (id: 5000000061c1e1e60a65627400563baa6cf7)

Dec 21 15:17:00 SRV-MAILARCHIV-NEU pilerimport[791551]: ERROR: 5000000061c1e1e60a65627400563baa6cf7: mysql_stmt_execute() 'Incorrect string value: '\xZ6se wX...' for column `piler`.`sph_index`.`to` at row 1' (errno: 1366)
Dec 21 15:17:00 SRV-MAILARCHIV-NEU pilerimport[791551]: ERROR: 5000000061c1e1e60a65627400563baa6cf7 failed to store index data for id=1762325, sql_errno=1366
Dec 21 15:17:00 SRV-MAILARCHIV-NEU pilerimport[791551]: ERROR: 5000000061c1e1e60a65627400563baa6cf7: rollback sql stmt=DELETE FROM sph_index WHERE id=1762325
Dec 21 15:17:00 SRV-MAILARCHIV-NEU pilerimport[791551]: ERROR: 5000000061c1e1e60a65627400563baa6cf7: rollback sql stmt=DELETE FROM rcpt WHERE id=1762325
Dec 21 15:17:00 SRV-MAILARCHIV-NEU pilerimport[791551]: ERROR: 5000000061c1e1e60a65627400563baa6cf7: rollback sql stmt=DELETE FROM metadata WHERE id=1762325
Dec 21 15:17:00 SRV-MAILARCHIV-NEU pilerimport[791551]: ERROR: 5000000061c1e1e60a65627400563baa6cf7: rollback sql stmt=DELETE FROM attachment WHERE piler_id='5000000061c1e1e60a65627400563baa6cf7'

root@SRV-MAILARCHIV-NEU:/tmp# piler -v
1.3.11 build 1001

Maria DB : 10.3.32

We found the error in:
To: "MASKED Zr�se" [e-mail]
-->
(>system reads sign in utf-8 which should be iso))

Mail was
exported from piler 1.2.0-master build 935
imported to piler 1.3.11 build 1001

the old system was en_US UTF-8
the new system is de_DE UTF-8

we made sure that everything runs in utf8mb4 and checked even for mysql

/etc/mysql/mariadb.conf.d/50-server.cnf:104:character-set-server = utf8mb4
/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf:8:default-character-set = utf8mb4
/etc/mysql/mariadb.conf.d/50-client.cnf:8:default-character-set = utf8mb4

we found that if we convert the mail to UTF-8 using iconv the mail gets imported correctly but not displayed correctly using webinterface

any advise here?

[Janos SUTO]

Fix the `to` column to text(8192) default null;

See https://bitbucket.org/jsuto/piler/src/4bd447b105c095abfe458f58325f3e73e7a73b8f/util/db-mysql.sql#lines-12

[Jan]

i already set this to longtext because of some other problem which is the current setting.

shouldnt this be enough?

‌

‌

MariaDB [piler]> describe sph_index;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| id               | bigint(20)       | NO   | PRI | NULL    |       |
| from             | tinyblob         | YES  |     | NULL    |       |
| to               | longtext         | YES  |     | NULL    |       |
| fromdomain       | char(255)        | YES  |     | NULL    |       |
| todomain         | text             | YES  |     | NULL    |       |
| subject          | blob             | YES  |     | NULL    |       |
| arrived          | int(10) unsigned | NO   |     | NULL    |       |
| sent             | int(10) unsigned | NO   |     | NULL    |       |
| body             | mediumblob       | YES  |     | NULL    |       |
| size             | int(11)          | YES  |     | 0       |       |
| direction        | int(11)          | YES  |     | 0       |       |
| folder           | int(11)          | YES  |     | 0       |       |
| attachments      | int(11)          | YES  |     | 0       |       |
| attachment_types | text             | YES  |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+

‌

[Janos SUTO]

Can show or send me the full headers of this email?

[Jan]

Hi. Can i send this in private to you somehow?

[Janos SUTO]

Yes. See my email in piler -V output.

[Jan]

i sent this by email

[Janos SUTO]

Thank you. You were right. Please fix the `to` column to be “blob(8192) default null”, and it should be ok. The To: header line is clearly malformed as any non-ascii char must be encoded.

[Janos SUTO]

Also checkout this commit, it fixes other columns in the sph_index table: https://bitbucket.org/jsuto/piler/commits/f1cc01b81f00633c25054a79779e6fabd22a9bba

[Jan]

Thank you. This fixed this type of error

alter table piler.sph_index change column `to` `to` blob(8192) default null;
alter table piler.sph_index change column `fromdomain` `fromdomain` tinyblob default null;
alter table piler.sph_index change column `todomain` `todomain` blob(512) default null;

‌

I got another one but will open another issue for that