Internal Server Error: /archives/api/mailman/archive OperationalError at /archives/api/mailman/archive (1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x8A\\x0A\\x0A...' for column `mailman3web`.`hyperkitty_email`.`content` at row 1")

Someone sent a short E-Mail with an Emoition in it. Is there something known about problems?
The mails was sent via mailman3, but not archived due to the error. I can bring more details, please let me know, if this is requested.
Internal Server Error:
/archives/api/mailman/archive
OperationalError at /archives/api/mailman/archive
(1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x8A\\x0A\\x0A...' for column mailman3web
.hyperkitty_email
.content
at row 1")
Hyperkitty 1.3.12 GNU Mailman 3.3.10 (Tom Sawyer) Python 3.11.2 (main, Apr 28 2025, 14:11:48) [GCC 12.2.0]
Now, every new mail triggers again this error.

On 9/9/25 13:45, Wikinaut wrote:
Someone sent a short E-Mail with an Emoition in it. Is there something known about problems?
Yes, the issue is your database is MySQL or MariaDB and your tables are not utf8mb4 so they don't accept 4-byte utf8 encodings. See the post at https://lists.mailman3.org/archives/list/mailman-users@mailman3.org/message/... for a way to fix this.
Now, every new mail triggers again this error.
Because the offending message is in the queue at /opt/mailman/mm/var/archives/hyperkitty/spool/ and it gets retried on every post. Setting the character set to utf8mb4 on the hyperkitty_email table will allow the retries to succeed.
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan

I changed the table hyperkitty_email.
The 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'mailman3web', 'USER': 'mailman3user', # TODO: Replace this with the password. 'PASSWORD': '****', 'HOST': 'localhost', 'PORT': '5432', # For MySQL/MariaDB, in addition to changing the 'ENGINE' setting, # uncomment the following to enable utf8 4-byte encodings. 'OPTIONS': { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'mailman3web', 'USER': 'mailman3user', # TODO: Replace this with the password. 'PASSWORD': '883AGfuadS7gg', 'HOST': 'localhost', 'PORT': '5432', # For MySQL/MariaDB, in addition to changing the 'ENGINE' setting, # uncomment the following to enable utf8 4-byte encodings. 'OPTIONS': { 'charset': 'utf8mb4', 'init_command': "SET sql_mode='STRICT_TRANS_TABLES'", } } }
'charset': 'utf8mb4',
is already present.
The Error message is still coming.
What do I have to fix next?

MariaDB [mailman3web]> SHOW FULL COLUMNS FROM hyperkitty_email; +-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+ | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | message_id | varchar(255) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | message_id_hash | varchar(255) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | subject | varchar(512) | utf8mb4_unicode_ci | YES | MUL | NULL | | select,insert,update,references | | | content | longtext | utf8mb4_unicode_ci | YES | | NULL | | select,insert,update,references | | | date | datetime(6) | NULL | NO | MUL | NULL | | select,insert,update,references | | | timezone | smallint(6) | NULL | NO | | NULL | | select,insert,update,references | | | in_reply_to | varchar(255) | utf8mb4_general_ci | YES | MUL | NULL | | select,insert,update,references | | | archived_date | datetime(6) | NULL | NO | MUL | NULL | | select,insert,update,references | | | thread_depth | int(11) | NULL | NO | | NULL | | select,insert,update,references | | | thread_order | int(11) | NULL | YES | MUL | NULL | | select,insert,update,references | | | mailinglist_id | int(11) | NULL | NO | MUL | NULL | | select,insert,update,references | | | parent_id | int(11) | NULL | YES | MUL | NULL | | select,insert,update,references | | | sender_id | varchar(255) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | thread_id | int(11) | NULL | NO | MUL | NULL | | select,insert,update,references | | | sender_name | varchar(255) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | | +-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
Looks to be okay, doesn't it?

Mark,
my database **was** already on utf8mb.
I checked with the server (with the problem) from which I moved to a new server, the sam issue. Server 1: SHOW FULL COLUMNS FROM hyperkitty_email; +-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+ | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | message_id | varchar(255) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | message_id_hash | varchar(255) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | subject | varchar(512) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | content | longtext | utf8mb4_general_ci | NO | | NULL | | select,insert,update,references | | | date | datetime(6) | NULL | NO | MUL | NULL | | select,insert,update,references | | | timezone | smallint(6) | NULL | NO | | NULL | | select,insert,update,references | | | in_reply_to | varchar(255) | utf8mb4_general_ci | YES | MUL | NULL | | select,insert,update,references | | | archived_date | datetime(6) | NULL | NO | MUL | NULL | | select,insert,update,references | | | thread_depth | int(11) | NULL | NO | | NULL | | select,insert,update,references | | | thread_order | int(11) | NULL | YES | MUL | NULL | | select,insert,update,references | | | mailinglist_id | int(11) | NULL | NO | MUL | NULL | | select,insert,update,references | | | parent_id | int(11) | NULL | YES | MUL | NULL | | select,insert,update,references | | | sender_id | varchar(255) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | thread_id | int(11) | NULL | NO | MUL | NULL | | select,insert,update,references | | | sender_name | varchar(255) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | | +-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+ 16 rows in set (0.004 sec)
This looks fine, too, doesn't it?
How can I fix the Internal Server Error? Lost.....

Please close this issue now.
The problem could be solved now. No idea, what was wrong.

On 9/9/25 14:47, Wikinaut wrote:
Mark,
my database **was** already on utf8mb.
Do you mean utf8mb4? I don't think there is a utf8mb encoding, but if there is it may be an alias for utf8mb3.
I checked with the server (with the problem) from which I moved to a new server, the sam issue. Server 1: SHOW FULL COLUMNS FROM hyperkitty_email; +-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+ | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | message_id | varchar(255) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | message_id_hash | varchar(255) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | subject | varchar(512) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | content | longtext | utf8mb4_general_ci | NO | | NULL | | select,insert,update,references | | | date | datetime(6) | NULL | NO | MUL | NULL | | select,insert,update,references | | | timezone | smallint(6) | NULL | NO | | NULL | | select,insert,update,references | | | in_reply_to | varchar(255) | utf8mb4_general_ci | YES | MUL | NULL | | select,insert,update,references | | | archived_date | datetime(6) | NULL | NO | MUL | NULL | | select,insert,update,references | | | thread_depth | int(11) | NULL | NO | | NULL | | select,insert,update,references | | | thread_order | int(11) | NULL | YES | MUL | NULL | | select,insert,update,references | | | mailinglist_id | int(11) | NULL | NO | MUL | NULL | | select,insert,update,references | | | parent_id | int(11) | NULL | YES | MUL | NULL | | select,insert,update,references | | | sender_id | varchar(255) | utf8mb4_general_ci | NO | MUL | NULL | | select,insert,update,references | | | thread_id | int(11) | NULL | NO | MUL | NULL | | select,insert,update,references | | | sender_name | varchar(255) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | | +-----------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+ 16 rows in set (0.004 sec)
This looks fine, too, doesn't it?
utf8mb4_general_ci is only a collation sequence. It is not the character set of the database.
The bottom line is if you are still getting the subject error it is because the character set of the database and/or the hyperkitty_email table is utf8mb3.
There several threads on this in the archives (search for utf8mb4) and at least one issue at https://gitlab.com/mailman/hyperkitty/-/issues/248 that have information about fixing this.
I don't know for sure about MariaDB, but for MySQL you can also add
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
to the MySQL configuration file.
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan

Dear Mark,
thank you very much for your hints. Every issue has now been sorted out and settled.
The problem was mainly caused by the long MX record TTL: I fixed the issue on the new(!) server successfully, but the mail was sent to the _unfixed_ old server due to the very high TTL time of the MX record!
Thus I was testing by mailing to the unfixed server for some hours, stupid, but true.
Happy end: I backported your suggested database fix to the old server and found that the utf8mb4 option was missing there - other than I said. I was really puzzled due to the MX time-to-lease issue and I *deeply* apologize for bringing some trouble here to the mailing list.
Hint for people who might have the same problem:
- This is good to have in /etc/mailman3/settings.py:
'OPTIONS': { 'charset': 'utf8mb4', 'init_command': "SET sql_mode='STRICT_TRANS_TABLES'", }
and
- The database fix is this one, please take note that the *longtext* verb is needed to make the command working on Server version: 10.11.14-MariaDB-0+deb12u2 Debian 12
ALTER TABLE hyperkitty_email DEFAULT CHARACTER SET utf8mb4, MODIFY subject varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY content longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
participants (2)
-
Mark Sapiro
-
Wikinaut