Database error 1366

[icubex]

We get the following error message when entering a text that contains "𝗳𝗿𝗲𝗲" in the Compose Message field.

Database error 1366 while doing query Incorrect string value: '\xF0\x9D\x97\xB3\xF0\x9D...' for column infusion_list.phplist_messagedata.data at row 1

[michield]

Really? that's odd. phpList definitely doesn't filter on "free" or something. Looks more like a Multibyte string problem. Are your database tables using the correct collation?

[icubex]

What collation should that be ? And shouldn't that have been setup by the PHPList installer ?

[michield]

Yes, it defaults it: https://github.com/phpList/phplist3/blob/main/public_html/lists/admin/mysqli.inc#L405

Looks similar to this https://stackoverflow.com/questions/34165523/error-1366-hy000-incorrect-string-value-xf0-x9f-x98-x9c-for-column-comme

[icubex]

Yes it's similar to the SO entry. Our PHPList database uses some Latin variety character set and collation - not sure how that happened. Anyway, apparently it should be using UTF8 variety character set and collation to handle the "𝗳𝗿𝗲𝗲" character sequence, correct ? Is there a way to switch the database to UTF8 ?

[michield]

It should initialise in UTF-8, but you can try to go to system -> convert to UTF8

[icubex]

It says "The DB was already converted to UTF-8 on 2015-01-12 21:23". I previously ran the query "SHOW COLLATION LIKE 'latin%';" on the PHPList database which resulted in lots of latin variety collations so I thought the database was a latin variety but actually it's easy to see it's a UTF8 variety (utf8mb3_general_ci). So now it's not clear to me why the database was not able to handle the "𝗳𝗿𝗲𝗲" character sequence.

[michield]

Yes, very strange. Can you post the exact string you entered in phpList (I presume in the subject or body)? Then I can try to replicate it.

[icubex]

The string is "𝗳𝗿𝗲𝗲". FYI, I created it using an online website for creating bold text without HTML.

[michield]

Ah, I think you may need to post the URL to create that, because I think Github has sanitised the text. It must be some unicode trick. I wonder if you need some utf8mb variant instead.

[icubex]

It was a website like https://lingojam.com/BoldTextGenerator.
The database already uses utf8mb3_general_ci, or do you mean it should use another utf8mb variant ?

[michield]

Can you check if #1001 fixes it for you?