pilerimport: Data too long for column body

[Jose M. Albarran]

Hi,

I'm 1.2.0-master build 945 on Ubuntu 16.04.1 LTS

I have a PST file, converted to independent files with readpst -M -b mypstfile.pst

When I execute the pilerimport with -d option, I see a lot of errors in /var/log/mail.log

mysql_stmt_execute error: Data too long for column 'body' at row 1 (errno: 1406)

Also, I see (less) errors like: mysql_stmt_execute error: Incorrect string value: '\xF0\x9D\x91\x83\xF0\x9D...' for column 'body' at row 1 (errno: 1366)

[Janos SUTO]

Can you send me such an email to me to test?

[Jose M. Albarran]

Hi,

I'm trying to identify the failing e-mails, but it's hard for me. I'm doing a pilerimport -d where the folder contains several thousands e-mails, at the same time that I monitor the log with a tail -f , but I cannot identify the e-mail. How can identify it (from the error message) (I imagine that the bid number is an UUID, but I cannot locate neither Aug 19 11:04:16 irulan pilerimport[19910]: 4000000057b6cb993a77376c00af5718b4f7: mysql_stmt_execute error: Data too long for column 'body' at row 1 (errno: 1406)

[Janos SUTO]

Didn't pilerimport print a filename to stdout?

[Jose M. Albarran]

Hi, I have located one (of several thousands) of the conflictive e-mail, but already in the store folder. These ones are crypted (for example 4000000057b7f61f213a9d5c00663479a090.m).

How can I decrypt one, so can I attach it?

[Janos SUTO]

Run "pilerget 4000000057b7f61f213a9d5c00663479a090" to retrieve the email from the archive.

[Jose M. Albarran]

Hi,

I think I have workarounded it. I'm testing now.

I have executed: ALTER TABLE piler.sph_index CHANGE body body LONGTEXT CHARACTER SET utf8mb4, COLLATE = utf8mb4_unicode_ci;

And, yes, I have (a lot) of e-mail larger than 64Kb in body (long e-mail chains).

When the full reindex ends, I'll update this thread!

[Jose M. Albarran]

Hi,

I have just send you (by e-mail) one of the very long / conflictive e-mails.

[Janos SUTO]

OK, I'll check it soon. If the message body was bigger than 64 kB, it shouldn't be a problem, the rest is just not indexed.

[Janos SUTO]

Sorry that it took extremely long, anyway I can confirm that the current build properly handles such a long message on ubuntu xenial using mariadb.

I've also concluded that mariadb is the suggested and supported sql server on xenial (instead of mysql 5.7).