mysql_stmt_execute error: Incorrect string value (errno: 1366) and status=error

[Ozan H]

Hello,

I think I got a big problem. I am trying to use mailpiler for 2 weeks. I noticed from the syslog that some emails have not been added to the mysql database probably because of unicode things. Although the piler's mysql table columns are unicode aware it gives this error below. I tried to change the related column type (table=attachment, column=name) shown below to utf8mb4 but it did not help. Here is logs. How can I fix this and how can I reprocess those failed emails to be inserted to the database?

#!

login as: root
root@192.168.1.60's password:
Linux piler 3.2.0-4-amd64 #1 SMP Debian 3.2.63-2+deb7u2 x86_64
Last login: Fri Jan  9 08:46:41 2015 from 192.168.1.81
root@piler:~# cat /var/log/syslog | grep 4000000054afd4643297782c0031a9741309
Jan  9 15:15:06 mailarchive piler[10699]: 4000000054afd4643297782c0031a9741309: mysql_stmt_execute error: *Incorrect string value: '\xDC\xC7\xDCKPA...' for column 'name' at row 1* (errno: 1366)
Jan  9 15:15:06 mailarchive piler[10699]: 4000000054afd4643297782c0031a9741309: from=[e-mail], size=8957/1704, attachments=1, reference=, message-id=<[e-mail]>, retention=0, delay=0.07, delays=0.07/0.00/0.00/0.00/0.00/0.00, status=error
root@piler:~#

[Janos SUTO]

What piler version is this? Can you send me such a problematic message in a zip file? (See my address in piler -V)

[Ozan H]

Sorry here is the version info and the link to download the syslogs with error

http://download.kpa.com.tr//data/public/436db7.php

#!

root@piler:~# piler -V
piler 1.1.0, build 884, Janos SUTO <[e-mail]>

Build Date: Tue Aug 19 09:14:15 CEST 2014
ldd version: ldd (Debian EGLIBC 2.13-38+deb7u3) 2.13
gcc version: gcc version 4.7.2 (Debian 4.7.2-5)
Configure command: ./configure --localstatedir=/var --enable-starttls --enable-tcpwrappers --with-database=mysql

[Janos SUTO]

OK, I tried to import it, and I succeeded (using build 896, but your version also should handle it properly).

Please show me the output of the following query:

show create database piler;

[Ozan H]

Finally I found the root cause of the problem. It is because of my MySQL v5.6. I tested this situation with MySQL v5.5 and the latest master branch, everything works fine. So, one should not upgrade mysql until there is a solution for this.

In addition to this, In piler documents mysql support should be limited to MySQL v5.1-v5.5

Note: At sph_index table with MySQL 5.6, *Incorrect string value (errno: 1366) appears for the column 'body'. However, It is weird that, at metadata table, column 'subject' (varchar) accepts unicodes from piler.

[Janos SUTO]

Thanks for digging for the resolution. Note that metadata.subject and sph_index.body have somewhat different types. Perhaps it's the reason, not sure.