postalserver / postalserver/postal
SpamAssassin + non-ASCII characters = MySQL encoding crash (Incorrect string value)
- Dominant language
- Ruby
- Stars
- 16.8k
- Forks
- 1.3k
- Avg merge
- 13d 7h
- Merged PRs (30d)
- 2
Description
## 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):**
```ruby
# 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:
```ruby
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`
Contributor guide
Research direction
Start by tracing where SpamAssassin responses are handled and persisted, likely in app/models/message.rb as the report suggests; inspect how the description's encoding is handled before insertion. Then review app/views/messages/spam_checks.html.haml around line 31 and the associated controller path. Verify that non-ASCII descriptions can be stored and displayed without a worker or page error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mariadb, mysql, ruby
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100