What collations should be used when making new tables that might work with SMF?

[rcane]

I see utf8mb3_general_ci is throughout the SMF tables, but some readings suggested that utf8mb4_900_ai_ci is a newer comman standard.

My version of mysql is 7.4.33 (earlier than that which the mb4xxxx was said to become standard).

The question is, for any new tables I make that draw from the smf tables (and possibly write to the themes table), should there be a commonality, or does it depend on the kind of data that's getting passed back and forth?

[vbgamer45]

They should all be the same otherwise you may get odd mysql errors.

[Arantor]

My version of mysql is 7.4.33 (earlier than that which the mb4xxxx was said to become standard).

No, it's really not, there's no such version of MySQL. You're probably talking about PHP version.

mb4 support came in during the MySQL 5.5 branch and became default in 8.0 (conversely I don't think MariaDB has made it default yet).

But here's the fun thing, SMF itself doesn't install with utf8mb4 collations by default - you have to go out of your way to get that, so use explicitly and expressly what everything else in your SMF is using, and what that is will depend a great deal on when and how you installed it.

Also, don't trust phpMyAdmin for what it reports on the front page (server default), or the database (database default) or even the new table screen (table default), go into an actual table, and check the actual collation on an actual core table because in MySQL these can all be different to each other. All of them can be set to something different and what actually matters is what the tables themselves are set to.

Of course, if you use the correct DB creation methods in $smcFunc['db_create_table'] none of this is your problem because SMF will do the correct thing for your setup.

[rcane]

My version of mysql is 7.4.33 (earlier than that which the mb4xxxx was said to become standard).

No, it's really not, there's no such version of MySQL. You're probably talking about PHP version.

Corrected: 8.0.32

But here's the fun thing, SMF itself doesn't install with utf8mb4 collations by default - you have to go out of your way to get that, so use explicitly and expressly what everything else in your SMF is using, and what that is will depend a great deal on when and how you installed it.

I installed 2.18 via my host's apps installer, but later upgraded to 2.19 through assistance on this site.

Also, don't trust phpMyAdmin for what it reports on the front page (server default), or the database (database default) or even the new table screen (table default), go into an actual table, and check the actual collation on an actual core table because in MySQL these can all be different to each other. All of them can be set to something different and what actually matters is what the tables themselves are set to.

Ok, I looked at the structure of a handful of tables and they're all utf8mb3_general_ci.

Of course, if you use the correct DB creation methods in $smcFunc['db_create_table'] none of this is your problem because SMF will do the correct thing for your setup.

Can you give an example of doing that?  I've not created any tables in the smf database.  I HAVE made a few tables in a different db, and recently I had some trouble getting a csv imported to one of them.  That's when the topic of character sets, collations, and such came up. 

To be clear, I wasn't advocating or considering changing any of the SMF tables.   

[Arantor]

Ok, I looked at the structure of a handful of tables and they're all utf8mb3_general_ci.

Did you look at the structure of the table itself and specifically at columns or just the table entry? (If you're in the database view looking at the list of tables, that's the default collation for each table being shown, not what the table's content *actually* might be using, they can be different). But utf8mb3_general_ci is a good shout.

As for using $smcFunc['db_create_table'] there are any number of mod packages that create tables using $smcFunc['db_create_table'] that you can look at.

[rcane]

Ok, I looked at the structure of a handful of tables and they're all utf8mb3_general_ci.

Did you look at the structure of the table itself and specifically at columns or just the table entry? (If you're in the database view looking at the list of tables, that's the default collation for each table being shown, not what the table's content *actually* might be using, they can be different). But utf8mb3_general_ci is a good shout.

As for using $smcFunc['db_create_table'] there are any number of mod packages that create tables using $smcFunc['db_create_table'] that you can look at.

I see what you mean.  I was actually opening the table and looking at the structure too.  But, yes, they seem to be same.   I just want to ensure any tables I'm making that talk to the SMF tables do what they can to avoid any hiccups.

I'll go look around the mods to see.