UTF8 on MariaDB

[charlottezweb]

I upgraded a site tonight from 2.0.17 to 2.1.4 using the large upgrade package.

The upgrader did it's normal thing without any errors that I noticed however I'm getting cron.php errors logged by SMF for guests now hitting that URL.  I see lots of posts for this around 2022 but may be missing something newer.

Code Select Expand

: Incorrect string value: '\xE0\xAE\x9A\xE0\xAE\xBF...' for column 'value' at row 1
/cron.php
/Sources/Subs.php (Line 529)
Long story short, I tried the fixes in several threads about converting the db in SMF, etc. but in phpMyAdmin, my collation is still latin_swedish_ci even though I thought the upgrader converted to utf8. 

So that led me to some posts on changing the collation in phpMyAdmin -- Here's where I'm stuck.  I'm running MariaDB and the collation options don't offer UTF8_general_ci like I see mentioned in most posts here.  There's tons of other "utf" options just not plain UTF8_. 

I just tried utf8mb4_general_ci after reading what to use that's newer than UTF8 but that failed on the smf_messages table due to some sort of length exceeded error.

Plain UTF8 is listed in phpmyadmin when I import a database and I tried that approach too (dropping all tables and importing it again with utf8 but it remains in latin_swedish according to phpMyAdmin. 

Worth mentioning - I have quite a few other SMF forums on this same server.  Some of them I upgraded in the past month or two and never got this cron.php error like tonight.

Am I missing something simple, hopefully? 

**UPDATE**
Is it possible the collation isn't the cause of the issue?

If I backtrace the error in SMF, it says:

URL of page causing the error
https://www.website.com/index.phphttps://www.WEBSITE.com/cron.php

(where I replaced "WEBSITE", for example)  Notice there's no space as if that's a single url.

Is this some sort of redirect problem or some reason why it seems to be calling the same URL twice?

Cheers,
Jason

[Kindred]

Well, the collation is definitely wrong,  it needs to be updated to utf8... I think mb3 is correct for 2.1.x

[shawnb61]

On newer DBs, the collation is reported as UTF8MB3_general_ci, instead of UTF8_general_ci.  At the moment, the two are synonymous.  (This will change in the future, but that's another topic for another day...)

A proper utf8 conversion will result in:
- All tables being either utf8_general_ci or utf8mb3_general_ci
- The value of $db_character_set in your Settings.php file should be: 'utf8'
- The value of the 'global_character_set' entry in your smf_settings table should be: 'UTF-8'

Take these steps & you'll be good.  This is true for 2.0 & 2.1.

[charlottezweb]

Thank you both!  I'll try again tomorrow and will update this thread afterwards.

Cheers,
Jason

[charlottezweb]

Thank you both!  I'll try again tomorrow and will update this thread afterwards.

I went ahead and adjusted the collation to UTF8MB3_general_ci about 20 mins ago.  The other things were already correct or were corrected by this:

- The value of $db_character_set in your Settings.php file should be: 'utf8'
- The value of the 'global_character_set' entry in your smf_settings table should be: 'UTF-8'

So far nothing new has logged (I'll mark this thread as resolved for now) but I'll check back tomorrow just in case.

THANK YOU!
-Jason