Collation conversion

activha
OFFLINE
Posts: 2334
Thanks: 117
Karma: 13

5 years 4 months ago - 5 years 4 months ago #313155 by activha

Collation conversion was created by activha

Hello

We tried to convert all of our database tables to utf8mb4_unicode_ci and got an error returned for the following tables which could not be changed and wanted to keep utf8_general_ci .
Can you tell me if that's correct and we need to keep utf8_general_ci or if we can force the changes to utf8mb4_unicode_ci collation ?

Also is it safe to change ALL CB and CB plugins tables from MyIsam to INNODB ?

Thanks

Code:

DB  | yq0g1_cbsubs_donations                    | utf8_general_ci    |
| DB  | yq0g1_cbsubs_gateway_accounts             | utf8_general_ci    |
| DB  | yq0g1_cbsubs_history                      | utf8_general_ci    |
| DB  | DB yq0g1_cbsubs_notifications                | utf8_general_ci    |
| DB  | yq0g1_cbsubs_payments                     | utf8_general_ci    |
| DB  | yq0g1_comprofiler_plug_pulogger           | utf8_general_ci    |
| DB  | yq0g1_comprofiler_plugin                  | utf8_general_ci    |
| DB  | yq0g1_comprofiler_plugin_packages_tokens  | utf8mb4_general_ci |
| DB  | yq0g1_comprofiler_plugin_privacy          | utf8_general_ci    |
| DB  | yq0g1_comprofiler_provinces               | utf8_general_ci    |

Last edit: 5 years 4 months ago by activha.

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48541
Thanks: 8290
Karma: 1445

5 years 4 months ago #313158 by krileon

Replied by krileon on topic Collation conversion

We tried to convert all of our database tables to utf8mb4_unicode_ci and got an error returned for the following tables which could not be changed and wanted to keep utf8_general_ci .

What error did you get? Those tables should be whatever your default collation is. The new default for Joomla is utf8mb4_unicode_ci so all tables and columns should have their collation set to that.

Can you tell me if that's correct and we need to keep utf8_general_ci or if we can force the changes to utf8mb4_unicode_ci collation ?

It's typically not a good idea to have mixed collations as table joins will fail if 2 different collations try to compare to one another in the query.

Also is it safe to change ALL CB and CB plugins tables from MyIsam to INNODB ?

Yes.

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in or Create an account to join the conversation.

activha
OFFLINE
Posts: 2334
Thanks: 117
Karma: 13

5 years 4 months ago #313179 by activha

Replied by activha on topic Collation conversion

We got some problems while converting from MYISAM to INNODB

On the two tables _cbsubs_payment_baskets and _cbsubs_promotions we got the error : $Index column size too large. The maximum column size is 767 bytes

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48541
Thanks: 8290
Karma: 1445

5 years 4 months ago #313185 by krileon

Replied by krileon on topic Collation conversion

Probably old indexes. Delete the indexes, convert the table, then reinstall CBSubs for it to create its indexes again.

Please Log in or Create an account to join the conversation.

activha
OFFLINE
Posts: 2334
Thanks: 117
Karma: 13

5 years 4 months ago #313276 by activha

Replied by activha on topic Collation conversion

This is very useful to convert MYISAM tables to INNODB tables

Index length

We just had to change the length of some indexes to solve the issue.

Hope it may help someone else

The following user(s) said Thank You: krileon

Please Log in or Create an account to join the conversation.

mikerotec
OFFLINE
Posts: 346
Thanks: 28
Karma: 2

5 years 4 months ago - 5 years 4 months ago #313319 by mikerotec

Replied by mikerotec on topic Collation conversion

Using the 'Admin Tools" extension to convert all tables to utf8mb4_general_ci ...

for reasons unknown, after the process completes 'successfully' - some (but not all!) CBSubs tables are still set to utf8_general_ci.

these are the ones that did not convert:

JOM_cbsubs_payments
JOM_cbsubs_notifications
JOM_cbsubs_history

Wondering why this might be, and how I should proceed to get every table in conformity...

Last edit: 5 years 4 months ago by mikerotec.

Please Log in or Create an account to join the conversation.

Moderators: beat, nant, krileon