postalserver / postalserver/postal

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

Open
#3,620 1 comment 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.