Amavis - UTF8 storing of emojiis in msgs.subject

[James Palmer]

Hi all,

After enabling @storage_sql_dsn I'm seeing this error, which relates to the storing of subjects which contain emojiis.

Mar 4 10:36:35 luna amavis[26441]: (26441-04) (!)WARN save_info_final: sql exec: err=1366, HY000, DBD::mysql::st execute failed: Incorrect string value: '\\xF0\\x9F\\xA4\\x98 W...' for column 'subject' at row 1 at (eval 100) line 172.

I think I've correctly set up MySQL (below), however error still persists.


mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_name = "msgs" AND column_name = "subject";
+--------------------+
| character_set_name |
+--------------------+
| utf8mb4 |
+--------------------+
1 row in set (0.00 sec)

Ubuntu 16.04
amavisd-new-2.10.1 (20141025), Unicode aware, LC_ALL="C", LANG="en_US.UTF-8"

Does anybody have any fixes for this?

Thanks in advance!

[s***@gmail.com]

Post by James Palmer
Hi all,
Mar 4 10:36:35 luna amavis[26441]: (26441-04) (!)WARN save_info_final: sql exec: err=1366, HY000, DBD::mysql::st execute failed: Incorrect string value: '\\xF0\\x9F\\xA4\\x98 W...' for column 'subject' at row 1 at (eval 100) line 172.
I think I've correctly set up MySQL (below), however error still persists.
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_name = "msgs" AND column_name = "subject";
+--------------------+
| character_set_name |
+--------------------+
| utf8mb4 |
+--------------------+
1 row in set (0.00 sec)
Ubuntu 16.04
amavisd-new-2.10.1 (20141025), Unicode aware, LC_ALL="C", LANG="en_US.UTF-8"
Does anybody have any fixes for this?
Thanks in advance!

I've had the same problem. So I changed the data type of the field 'subject' from varchar to varbinary. Now the charset doesn't matter anymore and the sql insert works.