"Specified key was too long; max key length is 767 bytes" Error when Upgrading

[snapetom]

I'm trying to upgrade from 3.20.0 to 3.20.10 and am getting the "Specified key was too long" error for some ALTER TABLE statements, but not others. This is despite using the required versions of PHP 7.4 (7.4.10) and MariaDB 10 and the tables being InnoDB. The upgrade failed and told me to run SQL statmentes manually. These are the results for the ALTER TABLE statements.

MariaDB [chevereto]> ALTER TABLE `xxx_pages` MODIFY `page_internal` varchar(255) DEFAULT NULL;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MariaDB [chevereto]> ALTER TABLE `xxx_ip_bans` MODIFY `ip_ban_ip` varchar(255) NOT NULL;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MariaDB [chevereto]> ALTER TABLE `xxx_users` MODIFY `user_email` varchar(255) DEFAULT NULL;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MariaDB [chevereto]> ALTER TABLE `xxx_users` MODIFY `user_username` varchar(255) DEFAULT NULL;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

However, the following ALTER statements do work:

ALTER TABLE `xxx_users` MODIFY `user_registration_ip` varchar(255) NOT NULL;
ALTER TABLE `xxx_users` MODIFY `user_image_expiration` varchar(255) NOT NULL;
ALTER TABLE `xxx_deletions` MODIFY `deleted_content_ip` varchar(255) NOT NULL;

All tables are already using InnoDB.

|
MariaDB [chevereto]> show create table xxx_pages\G
*************************** 1. row ***************************
       Table: xxx_pages
Create Table: CREATE TABLE `xxx_pages` (
  `page_id` bigint(32) NOT NULL AUTO_INCREMENT,
  `page_url_key` varchar(32) DEFAULT NULL,
  `page_type` enum('internal','link') NOT NULL DEFAULT 'internal',
  `page_file_path` varchar(255) DEFAULT NULL,
  `page_link_url` mediumtext,
  `page_icon` varchar(255) DEFAULT NULL,
  `page_title` varchar(255) NOT NULL,
  `page_description` mediumtext,
  `page_keywords` mediumtext,
  `page_is_active` tinyint(1) NOT NULL DEFAULT '1',
  `page_is_link_visible` tinyint(1) NOT NULL DEFAULT '1',
  `page_attr_target` enum('_self','_blank') DEFAULT '_self',
  `page_attr_rel` varchar(255) DEFAULT NULL,
  `page_sort_display` int(11) DEFAULT NULL,
  `page_internal` varchar(191) DEFAULT NULL,
  `page_code` text,
  PRIMARY KEY (`page_id`),
  UNIQUE KEY `page_internal` (`page_internal`),
  KEY `page_url_key` (`page_url_key`),
  KEY `page_type` (`page_type`),
  KEY `page_is_active` (`page_is_active`),
  KEY `page_is_link_visible` (`page_is_link_visible`),
  KEY `page_sort_display` (`page_sort_display`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

MariaDB [chevereto]> show create table xxx_deletions\G
*************************** 1. row ***************************
       Table: xxx_deletions
Create Table: CREATE TABLE `xxx_deletions` (
  `deleted_id` bigint(32) NOT NULL AUTO_INCREMENT,
  `deleted_date_gmt` datetime NOT NULL,
  `deleted_content_id` bigint(32) NOT NULL,
  `deleted_content_date_gmt` datetime NOT NULL,
  `deleted_content_user_id` bigint(32) DEFAULT NULL,
  `deleted_content_ip` varchar(255) NOT NULL,
  `deleted_content_md5` varchar(32) DEFAULT NULL,
  `deleted_content_original_filename` varchar(255) DEFAULT NULL,
  `deleted_content_views` bigint(32) NOT NULL DEFAULT '0',
  `deleted_content_likes` bigint(32) NOT NULL DEFAULT '0',
  PRIMARY KEY (`deleted_id`),
  KEY `deleted_content_id` (`deleted_content_id`),
  KEY `deleted_content_user_id` (`deleted_content_user_id`),
  KEY `deleted_content_ip` (`deleted_content_ip`(191)),
  KEY `deleted_content_md5` (`deleted_content_md5`),
  KEY `deleted_content_views` (`deleted_content_views`),
  KEY `deleted_content_likes` (`deleted_content_likes`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4
|

Versions:

MariaDB [chevereto]> select @@version as version \G
*************************** 1. row ***************************
version: 10.1.48-MariaDB-0ubuntu0.18.04.1

PHP:




Any ideas on how to upgrade the DB?

 

[snapetom]

For shits and giggles, I decided to upgrade to Maria 10.3. This solved the issue and my database upgrade completed. The instructions I followed are here: https://mariadb.com/docs/deploy/upgrade-community-server-cs103-ubuntu18/

 

[ashkir]

your ini needs to be updated to support maradb

 

[snapetom]

your ini needs to be updated to support maradb

my.ini? If MariaDB 10.1 doesn't take care of upgrading that, Chevereto users should be warned.

 

[ashkir]

my.ini? If MariaDB 10.1 doesn't take care of upgrading that, Chevereto users should be warned.

It's not a Chevereto issue, it's a server set up issue.

I use MariaDB just fine and I have one of the largest Chevereto websites.