[#41994] - Changing collation of certain db fields away from utf8mb4_unicode_ci
- Closed
- 30 Sep 2023
- Medium
- Build: 4.3-dev
- # 41994
Problem identified
I just have a client with a database where the innodb_large_prefix option isn't enabled. Thus he cannot install SobiPro because some indexes are hitting a limit of 1000 bytes.
I realised I have indexes (in SobiPro) with VARCHAR fields and its collation set to utf8mb4_unicode_ci. Which not only doesn't make much sense but, I assume, affects also the performance of the DBMS.
Then I checked and see that we have similar issue in Joomla! as well.
Or is there any reason that, for example the field message_language_key in the table #_action_logs has collation set to utf8mb4_unicode_ci?
Proposed solution
Revise the database design and change particular fields, especially those included in indexes to for example ascii_general_ci
Open questions
joomla-cms-bot
- | Labels |
Added:
No Code Attached Yet
|
||
Nope. It just an assumption
In addition, Joomla does not have any influence on character set or collation of 3rd party extensions tables. The character set and collation is determined by the CREATE TABLE statement in the core SQL scripts.
If 3rd party devs specify default character set and collation for their tables in their CREATE TABLE statements, Joomla doesn't touch that, and that characters set and collation is used for that table as default, so all varchar and text columns will have that character set and collation, and they also can specify character set and collation for their tables individually for each column. As said, Joomla core does not touch that.
If 3rd party devs do not specify default character set and collation for their tables in their CREATE TABLE statements and do not specify character set and collation for their table columns, the database defaults are used, on which Joomla also doesn't have any influence. Joomla does NOT set or change the database default character set and collation of the database (so-called database character set and collation.
So the core does NOT keep you from using pure ASCII for your extension's database tables. As said before, it might lead to illegal mix of collations errors when being used in joins or unions, but that is standard MySQL behavior.
The only things which change character set and collation of 3rd party developers tables, i.e. noon core tables, are tools like e.g. Akeeba Admin tools, which allow to change character set and collation globally for all tables in the database.
So in my opinion this issue here should be closed as not a core issue.
More opinions are welcome.
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2023-09-30 11:17:28 |
| Closed_By | ⇒ | Radek-Suski |
I’m kinda speechless but ok.
It’s not like actually care much.
Cya
We have all tables on utf8mb4 _unicode_ci. Having some tables with different collations can cause “illegal mix of collations” errors when using them in a JOIN or UNION statement.
The problem with index sizes can be circumvented by using only the first n characters for the index like we do it in core.
Do you have any evidence for performance decrease caused by using utf8mb4 or is this just an unproven assumption?