SpamAssassin + non-ASCII characters = MySQL encoding crash (Incorrect string value)

[PhiGi87]

SpamAssassin + non-ASCII characters = MySQL encoding crash

Environment

• Postal 3.3.5
• MariaDB 11.3
• SpamAssassin 4.0.1 (spamd on 127.0.0.1:783)
• Debian 12 / German locale

Problem 1: MySQL crashes with Incorrect string value

After enabling SpamAssassin in postal.yml, every time a spam check hits a rule whose description contains non-ASCII characters (German umlauts like ä → byte 0xE4), the worker dies with:

Mysql2::Error: Incorrect string value: '\xE4lt HT...' for column `postal-server-3`.`spam_checks`.`description` at row 6

The message gets stuck in held state and is retried endlessly.

What I traced: SpamAssassin/spamd sends its rule descriptions encoded as Latin-1/ISO-8859-1, not UTF-8. But Postal's MySQL2 client connects as utf8 (= utf8mb3 in MySQL-speak). So when the worker tries to insert the raw 0xE4 byte into a utf8mb4 column over a utf8mb3 connection, MySQL (rightly) rejects it as invalid UTF-8.

Things I tried that didn't work:

• Changing the column charset to latin1 — nope, fails on real UTF-8 multibyte later
• SET PERSIST character_set_connection = utf8mb4 on the DB — doesn't help, the bytes are still Latin-1
• Making it a BLOB column — stores fine, but then Problem 2 happens

Problem 2: Web UI crashes when description is BLOB

If I work around Problem 1 by changing spam_checks.description to BLOB, the spam_checks page in the web UI throws:

ActionView::Template::Error: incompatible character encodings: UTF-8 and BINARY (ASCII-8BIT)
app/views/messages/spam_checks.html.haml:31

The HAML view joins a BINARY string with UTF-8 template content, Ruby says no.

My current hotfix (works but ugly):

# messages_controller.rb
@s = @message.spam_checks.sort_by { |s| s["score"] }.reverse.each { |s|
  s["description"] = s["description"].to_s.force_encoding("UTF-8").encode("UTF-8", invalid: :replace, undef: :replace)
}

# spam_checks.html.haml line 31
%p.spamCheckList__description= spam_check['description'].to_s.force_encoding('UTF-8').encode('UTF-8', invalid: :replace, undef: :replace)

What I think the proper fix would be

For Problem 1: The SpamAssassin result handler should convert Latin-1 to UTF-8 before inserting:

desc = spam_result[:description]
if desc && !desc.valid_encoding?
  desc = desc.force_encoding('ISO-8859-1').encode('UTF-8')
end

Somewhere around where spam_checks rows are created from spamd responses.

For Problem 2: Either fix Problem 1 (web UI works fine with TEXT column) or add the encoding normalization in MessagesController#spam_checks for robustness.

Affected files (my guess from reading the stack trace)

• app/models/message.rb or wherever spamd results get persisted
• app/views/messages/spam_checks.html.haml:31

[willpower232]

I'm surprised no one has raised this sooner but perhaps something else got updated in the middle and things are a bit stricter now.

Thing have been utf8mb4/utf8mb4_unicode_ci since 752558f and #391 so it has been around for a while.