table charset issue (mysql_upgradeTableToUTF8mb4()) for CMSB upgrade to 3.15
3 posts by 3 authors in: Forums > CMS Builder
Last Post: December 27, 2018 (RSS)
By Deborah - December 23, 2018
Hi, All.
Just sharing an error I encountered upgrading from 3.13 to 3.15.
I received a similar error set for three (not all) of the tables in the install.
----------------------------
MySQL Error: Specified key was too long; max key length is 1000 bytes. MySQL Query:
ALTER TABLE `cms_articles` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY `title` varchar(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' ,
MODIFY `author` varchar(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' ,
MODIFY `content` mediumtext CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' ,
MODIFY `external_website_url` mediumtext CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' ,
MODIFY `external_website_name` mediumtext CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'
TIP: Add &deferCharsetUpgrade=1 to URL to temporarily skip this upgrade and then remove and re-add any indexes in the field editor for the fields listed above.
- in mysql_functions.php on line 397 by __mysql_upgradeTableToUTF8mb4()
/home/zzzweb/public_html/cmszzz/lib/common.php (line 1280)
----------------------------
There were a couple of similar posts prior to version 3.15, but it seems the field editor fix in 3.15 would have taken care of this. I'm guessing this error tripped because MySQL Indexing was set to 'Create column index' for a couple of the fields in each of the tables.
I wasn't clear on the "Tip" instructions, so I manually updated the column types in the database to utf8mb4_unicode for the three tables and all seems well. I'll do the same if this happens again, but wanted to post this for others.
~ Deborah
Thanks, Deborah.
Best for the holidays
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
By Dave - December 27, 2018
Hi Deborah,
Hmm, thanks for reporting that. Yea, there's an issue where when mysql indexes get created they have a max length, and then when we upgrade to utf8mb4 it actually requires more "bytes" to store each character, so we need to shorten those indexes. We had a fix in the last release but it looks like it might not have been triggered in this case.
If it keeps coming up or is getting in your way let me know and we can look further into it. It definitely shouldn't occur for any new sites, though, as we're now starting with utf8mb4 as the default charset.
Happy Holidays! :-)
interactivetools.com