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

[Jan]

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

Jan 5 09:58:59 SRV-MAILARCHIV-NEU pilerimport[9073]: ERROR: 5000000061d55ddb2d0c8dfc00d36c100d79: mysql_stmt_execute() 'Incorrect string value: '\x84c.sto...' for column piler.rcpt.to at row 1' (errno: 1366)

‌

Jan 5 10:16:50 SRV-MAILARCHIV-NEU pilerimport[9642]: ERROR: 5000000061d5620a00af0a8c00e8da331671: mysql_stmt_execute() 'Incorrect string value: '\x84c.sto...' for column piler.rcpt.to at row 1' (errno: 1366)
Jan 5 10:16:50 SRV-MAILARCHIV-NEU pilerimport[9642]: ERROR: 5000000061d5620a00af0a8c00e8da331671: failed to add '�[e-mail]�' for id=1815872 to rcpt table, sql_errno=1366

‌

We already hat do modify to and todomain to varchar 512 because of some “monster header mails“

MariaDB [piler]> describe rcpt;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id       | bigint(20) unsigned | NO   | MUL | NULL    |       |
| to       | varchar(512)        | YES  | MUL | NULL    |       |
| todomain | varchar(512)        | YES  | MUL | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.002 sec)

‌

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

‌

so it might be the same issue like https://bitbucket.org/jsuto/piler/issues/1220/incorrect-string-value-for-column-piler

but the fix changing this to blob is not possible:

‌

MariaDB [(none)]> ALTER TABLE piler.rcpt MODIFY `to` blob(8192);
ERROR 1170 (42000): BLOB/TEXT column 'to' used in key specification without a key length

‌

[Janos SUTO]

Try dropping the index, then modify the table, and finally recreate the index.

[Jan]

This doesnt work:

drop index rcpt_idx on rcpt;
drop index rcpt_idx2 on rcpt;
drop index rcpt_idx3 on rcpt;

ALTER TABLE piler.rcpt MODIFY to blob(8192);

‌

MariaDB [piler]> create index rcpt_idx2 on rcpt(to);
ERROR 1170 (42000): BLOB/TEXT column 'to' used in key specification without a key length

‌

‌

‌

[Janos SUTO]

OK. How about blob(512)?

[Jan]

nope. ERROR 1170 (42000) at line 27: BLOB/TEXT column 'to' used in key specification without a key length

but i digged deeper and found out what is happening:

MariaDB [piler]> create table `rcpt` (
    ->    `id` bigint unsigned not null,
    ->    `to` blob(512) not null,
    ->    `todomain` varchar(512) not null,
    ->    unique(`id`,`to`)
    -> ) Engine=InnoDB;
ERROR 1170 (42000): BLOB/TEXT column 'to' used in key specification without a key length

---> For indexes on BLOB and TEXT columns, you must specify an index prefix length.

so right would be:

create table if not exists `rcpt` (
   `id` bigint unsigned not null,
   `to` blob(512) not null,
   `todomain` varchar(512) not null,
   unique(`id`,`to`(512))
) Engine=InnoDB;

then it works but im unsure about creating this index on blob 512 and the needed performance

‌

after that i had the same with todomain column and piler.metadata.message_id

so i first fixed
the whole rcpt table - it looks like this now:

create table if not exists `rcpt` (
   `id` bigint unsigned not null,
   `to` blob(512) not null,
   `todomain` blob(512) not null,
   unique(`id`,`to`(512))
) Engine=InnoDB;


create index `rcpt_idx` on `rcpt`(`id`);
create index `rcpt_idx2` on `rcpt`(`to`(512));
create index `rcpt_idx3` on `rcpt`(`todomain`(512));

now i get all these problem mails imported

‌

in message id something similar happened:

Jan 6 12:20:21 SRV-MAILARCHIV-NEU pilerimport[31573]: ERROR: 5000000061d6d07f23605fa400279d0e6838: mysql_stmt_execute() 'Incorrect string value: '\xF6ser>' for column piler.metadata.message_id at row 1' (errno: 1366)

pilertest shows:

message-id: 314838591780876542181@xxxxxxxxxhK�ser / 6b395e326fd7b497121d19ce5a98fc87648a6ba6ba753fd285585f4b427e32db

which is the reason of this.

‌

so i have 3 final questions:

• Why was the old piler able to handle these? ist this because we used mysql 5.5.46 there?

  • when i take this (ARCHIVDUMP-20211220//329215.eml) EMail and try to import this to the old piler with pilerimport simply no error happens

• We found some “Vcard“ eml files and testfiles as eml in our export which doesnt have a correct header but came from old archives pilerexport

  • is it possible that in piler 1.2.0-master build 935 there everyting got imported with pilerimport even without correct header?

• what to do with piler.metadata.message_id ? there is the same problem with an index on it (metadata_idx2) so i could fix the column to blob and recreate the index with length

‌

isnt this a general problem when storing things to db?

wouldnt this be always possible when an MTA delivers non-rfc mail via smtp to piler?

[Janos SUTO]

The current mysql variant might be the reason. On MariaDB-1:10.5.8+maria~focal the below schema works properly (notice there’s no size component in the “unique” part):

create table if not exists `rcpt2` (
   `id` bigint unsigned not null,
   `to` blob(512) not null,
   `todomain` blob(512) not null,
   unique(`id`,`to`)
) Engine=InnoDB;

The message-id is malformed in your example. All non-ascii character must be encoded properly. Older mysql versions were not that picky about invalid utf8 sequences.

Anyway, if you fixed the message_id column then you should be fine.

[Jan]

hi thank yo

root@SRV-MAILARCHIV-NEU:~# mysql --version
mysql Ver 15.1 Distrib 10.3.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

‌

here this doesnt work…

MariaDB [piler]> create table if not exists `rcpt2` (
    ->    `id` bigint unsigned not null,
    ->    `to` blob(512) not null,
    ->    `todomain` blob(512) not null,
    ->    unique(`id`,`to`)
    -> ) Engine=InnoDB;
ERROR 1170 (42000): BLOB/TEXT column 'to' used in key specification without a key length

‌

so maybe you could write something about in your faq….

‌

can you say something to my other questions:

‌

We found some “Vcard“ eml files and testfiles as eml in our export which doesnt have a correct header but came from old archives pilerexport

• is it possible that in piler 1.2.0-master build 935 there everyting got imported with pilerimport even without correct header?

‌

im unsure about creating this index on blob 512 and the needed performance

[Janos SUTO]

Well, if you needed to specify the key length, then we have no other choice.

Regarding that vcard files, pilerimport does its best to import whatever file you provide it, because it thinks you want to import that file anyway. The ingested data, however, depends on what it can extract from it.

[Jan]

ok thanks.

‌

i had another problem with retained in metadata

Jan 6 14:37:42 SRV-MAILARCHIV-NEU pilerimport[33075]: ERROR: 5000000061d6f0b030d46f5400f1adc3fd1a: mysql_stmt_execute() 'Out of range value for column 'retained' at row 1' (errno: 1264)

it is defined as retained int unsigned not null,

when i look to retained there are values like 4101105235

int is limited to 2147483647

so i had to

‌

alter table piler.metadata change column retained retained bigint unsigned not null;

‌

do you have any idea how this can happen?

i defined default_retention_days=36500 in config

[Janos SUTO]

Well, if you want to preserve all emails, then it’s much easier simply not running the purging script. Anyway a signed int limited to 2147483647 but an unsigned int is twice as large.

‌

MariaDB [piler]> create table a(a int default null, b int unsigned default null);
Query OK, 0 rows affected (0.015 sec)

MariaDB [piler]> insert into a (a,b) values(2147483647, 2147483647);
Query OK, 1 row affected (0.004 sec)

MariaDB [piler]> insert into a (a,b) values(4101105235, 4101105235);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

MariaDB [piler]> insert into a (a,b) values(4, 4101105235);
Query OK, 1 row affected (0.004 sec)

MariaDB [piler]> select * from a;
+------------+------------+
| a          | b          |
+------------+------------+
| 2147483647 | 2147483647 |
|          4 | 4101105235 |
+------------+------------+
2 rows in set (0.001 sec)

‌