Installation/upgrade fails on MySQL 5.5 - Index column size too large

[lpellissier]

Hi,

I can't upgrade RC 1.4.11 to 1.5.0 :

$ ./bin/installto.sh /var/www/roundcube-dev/public_html
Running update script at target...
Executing database schema update.
Updating database schema (2020020100)... [OK]
Updating database schema (2020020101)... [FAILED]
ERROR: Error in DDL upgrade 2020020101: [1709] Index column size too large. The maximum column size is 767 bytes.
All done.

I use CentOS Linux release 7.9.2009 (Core) with MariaDB 5.5.68. As mentioned in UPGRADING doc file I've added these two lines and restarted mariadb service :

[server]
innodb_large_prefix=true
innodb_file_format=Barracuda

I've then cloned again my RoundCube production server to the dev and upgrade it again. The issue is the same.

Regards

[alecpl]

I don't know, try innodb_large_prefix = ON.

[diegocortassa]

I can confirm the update fails on Centos 7.

mysql -V
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1

This happens because there is no way to default to ROW_FORMAT=DYNAMIC in maria DB 5.5.
innodb_default_row_format is not recognized.

I added ROW_FORMAT=DYNAMIC to the CREATE TABLE in SQL/mysql/2020091000.sql and the upgrade worked smoothly:

CREATE TABLE `collected_addresses` (
 `address_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
 `name` varchar(255) NOT NULL DEFAULT '',
 `email` varchar(255) NOT NULL,
 `user_id` int(10) UNSIGNED NOT NULL,
 `type` int(10) UNSIGNED NOT NULL,
 PRIMARY KEY(`address_id`),
 CONSTRAINT `user_id_fk_collected_addresses` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 UNIQUE INDEX `user_email_collected_addresses_index` (`user_id`, `type`, `email`)
) ROW_FORMAT=DYNAMIC /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;

Just made a PR #8250 in case you want to merge it...

[alecpl]

I'd need confirmation that it fixes @lpellissier's issue.

[jameswill]

Hi,

I am also getting the same error on fresh installation of 1.5.0 version. I am using PHP 5.5 and Mysql 5.5 with following settings in mysql configuration file.

innodb_large_prefix=true/ON/1
innodb_file_format=Barracuda

I also tried setting "innodb_file_per_table=true/ON/1" but the error persists.

[alecpl]

@jameswill did you try adding ROW_FORMAT=DYNAMIC in every create table command in mysql.initial.sql file?

[alecpl]

On https://mariadb.com/kb/en/innodb-dynamic-row-format/ I've found two notes:

1. In MariaDB 10.1 and before, the DYNAMIC row format is also only supported if the table is in a file per-table tablespace. Therefore, the innodb_file_per_table system variable must be set to ON to use this row format in those versions.
2. In MariaDB 10.2 and before, the innodb_large_prefix system variable is used to configure the maximum index prefix length. In these versions, if innodb_large_prefix is set to ON, then the maximum prefix length is 3072 bytes.

So, if we force all tables to be ROW_FORMAT=DYNAMIC in our sql files, we should be fine, as long as these mysql options are set properly.

[jameswill]

@jameswill did you try adding ROW_FORMAT=DYNAMIC in every create table command in mysql.initial.sql file?

Hi,

Manually changing create table query by adding ROW_FORMAT=DYNAMIC in mysql.initial.sql file resolves the issue. However can you please change in the Roundcube core file because many people are facing this issue and changing file manually isn't feasible option for everyone.

Also to work this follwing lines must set in mysql configuration file.

innodb_large_prefix=true/ON/1
innodb_file_format=Barracuda
innodb_file_per_table=true/ON/1

This should also needs to be added in installation notes. i.e INSTALL file

[Fonant]

Add this one to the above three mysql INI config lines:

innodb_default_row_format = dynamic

[alecpl]

Fixed.

[jameswill]

Fixed.

HI,

Is there any ETA for 1.5.1 release with the fix ?

[alecpl]

I think it will be in November, most likely the second half.

[XegerrI]

Thank you for your help and sorry for the duplicate post - I looked but missed it. Adding the following Server Variables to the bottom of my.cnf solved this.

[mysqld]
innodb_large_prefix=true
innodb_file_format=Barracuda
innodb_file_per_table=true
innodb_default_row_format=dynamic

[lpellissier]

For me (CentOS 7, Mariadb 5.5) the solution to update 1.4.11 to 1.5.2 was :

1/ Add to my.cnf :

innodb_large_prefix=true
innodb_file_format=Barracuda
innodb_file_per_table=true

2/ Alter some Mysql tables :

ALTER TABLE `cache` ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `cache_index` ADD CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `cache_thread` ADD CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `cache_messages` ADD CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `contactgroups` ADD CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dictionary` ADD CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `filestore` ADD CONSTRAINT `user_id_fk_filestore` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `identities` ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `searches` ADD CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `session` ROW_FORMAT=DYNAMIC;
ALTER TABLE `users` ROW_FORMAT=DYNAMIC;
ALTER TABLE `cache` ROW_FORMAT=DYNAMIC;
ALTER TABLE `cache_shared` ROW_FORMAT=DYNAMIC;
ALTER TABLE `cache_index` ROW_FORMAT=DYNAMIC;
ALTER TABLE `cache_thread` ROW_FORMAT=DYNAMIC;
ALTER TABLE `cache_messages` ROW_FORMAT=DYNAMIC;
ALTER TABLE `contacts` ROW_FORMAT=DYNAMIC;
ALTER TABLE `contactgroups` ROW_FORMAT=DYNAMIC;
ALTER TABLE `contactgroupmembers` ROW_FORMAT=DYNAMIC;
ALTER TABLE `identities` ROW_FORMAT=DYNAMIC;
ALTER TABLE `dictionary` ROW_FORMAT=DYNAMIC;
ALTER TABLE `searches` ROW_FORMAT=DYNAMIC;
ALTER TABLE `filestore` ROW_FORMAT=DYNAMIC;
ALTER TABLE `system` ROW_FORMAT=DYNAMIC;

3/ Finally :

./roundcubemail-1.5.2/bin/installto.sh /site/roundcube-dev/public_html