Collation conversion

4 years 8 months ago - 4 years 8 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
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    |

Please Log in to join the conversation.

4 years 8 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 to join the conversation.

4 years 8 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 to join the conversation.

4 years 8 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.


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 to join the conversation.

4 years 8 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 to join the conversation.

4 years 8 months ago - 4 years 8 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...

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.177 seconds

Facebook Twitter LinkedIn