Section error when dragging column order
7 posts by 2 authors in: Forums > CMS Builder
Last Post: March 27, 2018 (RSS)
By kitsguru - January 4, 2018
I am receiving the following in one section when I try to change the column order
Error: MySQL Error: Specified key was too long; max key length is 1000 bytes
- in editTable_functions.php on line 381 by _saveFieldOrder_updateColumnOrder()
Schema attached
By kitsguru - January 4, 2018
More Info:
I added a trigger_error to capture the sql generated by moving the column.
E_USER_NOTICE: ALTER TABLE `cmsb_custom_pages`
MODIFY COLUMN `permalink` varchar(255) FIRST,
MODIFY COLUMN `shadow_permalink` mediumtext FIRST,
MODIFY COLUMN `forms` mediumtext FIRST,
MODIFY COLUMN `font_icon` varchar(255) FIRST,
MODIFY COLUMN `meta_description` varchar(255) FIRST,
MODIFY COLUMN `embed_video` mediumtext FIRST,
MODIFY COLUMN `ads` mediumtext FIRST,
MODIFY COLUMN `side_note` mediumtext FIRST,
MODIFY COLUMN `content` mediumtext FIRST,
MODIFY COLUMN `subtitle` mediumtext FIRST,
MODIFY COLUMN `page_title` varchar(255) FIRST,
MODIFY COLUMN `name` varchar(255) FIRST,
MODIFY COLUMN `removeDate` datetime NOT NULL FIRST,
MODIFY COLUMN `publishDate` datetime NOT NULL FIRST,
MODIFY COLUMN `neverRemove` tinyint(1) unsigned NOT NULL FIRST,
MODIFY COLUMN `featured` tinyint(1) unsigned NOT NULL FIRST,
MODIFY COLUMN `is_heading_only` tinyint(1) unsigned NOT NULL FIRST,
MODIFY COLUMN `hide_in_nav_menu` tinyint(1) unsigned NOT NULL FIRST,
MODIFY COLUMN `hidden` tinyint(1) unsigned NOT NULL FIRST,
MODIFY COLUMN `breadcrumb` varchar(255) NOT NULL FIRST,
MODIFY COLUMN `parentNum` int(10) unsigned NOT NULL FIRST,
MODIFY COLUMN `depth` int(10) unsigned NOT NULL FIRST,
MODIFY COLUMN `lineage` varchar(255) NOT NULL FIRST,
MODIFY COLUMN `siblingOrder` int(10) unsigned NOT NULL FIRST,
MODIFY COLUMN `globalOrder` int(10) unsigned NOT NULL FIRST,
MODIFY COLUMN `updatedByUserNum` int(10) unsigned NOT NULL FIRST,
MODIFY COLUMN `updatedDate` datetime NOT NULL FIRST,
MODIFY COLUMN `createdByUserNum` int(10) unsigned NOT NULL FIRST,
MODIFY COLUMN `createdDate` datetime NOT NULL FIRST,
MODIFY COLUMN `num` int(10) unsigned auto_increment NOT NULL FIRST
/Volumes/J/@projects/jas/html/www/cmsb/lib/menus/database/editTable_functions.php (line 419)
https://jas.yaa.test/cmsb/admin.php
I then change the table ENGINE=InnoDB
and I was able to move the column order.
By Dave - January 5, 2018
Hi Jeff,
Ok, glad you got it working. If that comes up again or you'd like us to investigate it further just let me know.
Cheers!
interactivetools.com
By kitsguru - January 5, 2018
I think you will find this error coming up more frequently now that the database matches the ini column order.
There might be two solutions.
Change the table engine to innodb
OR
split the alter table command so its total length is less than 1000 characters.
By Dave - January 5, 2018
I think it's related to the key length of the columns, or maybe any indexes used on the table. I don't think it's related to the length of the query itself. We recently changed the default charset from utf8 to utfmb4 which supports more characters such as emojis and other languages. CMSB should automatically update your database to resolve that issue. If you run into it again on another server feel free to email me details to dave@interactivetools.com and I can debug it. (Don't post login details to the forum).
There's probably no harm in changing the table type but I wouldn't suggest it as a default because it's not addressing the underlying issue.
Cheers!
interactivetools.com
By kitsguru - January 5, 2018 - edited: January 6, 2018
[edited]
The max key length for myisam is 1000. The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes.
Since innodb is now the default and is better for overall performance with record locking instead of table locking, it might be worth moving in that direction.
See: https://www.mysql.com/why-mysql/presentations/myisam-2-innodb-why-and-how/
Also see: https://www.percona.com/blog/2016/10/11/mysql-8-0-end-myisam/
By kitsguru - March 27, 2018
To change a table to INNODB :
Alter Table cmsb_accounts ENGINE=INNODB;