Update failed

[Kevinterra]

Hello, I have updated from version 3.18 to 3.20.16. The following error occurred:

The update process failed. Here is the error returned:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

If you update from an older version update your MyISAM tables to InnoDB table storage engine and try again.

If you have altered your database you will need to manually perform this update.

I have updated the tables manually from MyISAM to InnoDB afterwards.

Now when I go to the /install page I get the same error. A manual update did not help.

The dashboard says Chevereto version 3.20.16. Database version 3.18.0.

best regards
Kevin

 

[Kevinterra]

Does no one have any ideas? Can't use the website since then.

 

[Rodolfo]

Hi @Kevinterra

This is a known issue which happens when you upgrade from MySQL 5 to 8.

👉 https://chevereto.com/community/thr...x-error-or-access-violation.13828/#post-69543

 

[Kevinterra]

Hey @Rodolfo . Thanks for the reply, unfortunately I do not have access to the site.

You do not have permission to view this page or perform this action.

 

[Rodolfo]

Try running these:

DROP INDEX ip_ban_ip ON chv_ip_bans;
DROP INDEX page_internal ON chv_pages;
ALTER TABLE `chv_users` DROP INDEX `username`;
ALTER TABLE `chv_users` DROP INDEX `email`;
DROP INDEX importing_path ON chv_importing;

Then:

ALTER TABLE `chv_users` ADD UNIQUE KEY `username` (`user_username`(191));
ALTER TABLE `chv_users` ADD UNIQUE KEY `email` (`user_email`(191));
ALTER TABLE `chv_pages` ADD UNIQUE KEY `page_internal` (`page_internal`(191));

 

[Kevinterra]

I have run the queries. Now when I try an update the following error message appears:

Update failed


The update process failed. Here is the error returned:


Problems executing the MySQL update query. It is recommended that you issue the above SQL queries manually.


If you update from an older version update your MyISAM tables to InnoDB table storage engine and try again.


If you have altered your database you will need to manually perform this update.

Wenn ich die MySQL Abfragen per Hand eingebe, kommt folgende Fehlermeldung.

MySQL meldet:

#1071 - Schlüssel ist zu lang. Die maximale Schlüssellänge beträgt 767

 

[Rodolfo]

Show the alleged queries, please don't omit details.

 

[Kevinterra]

I had run the following queries:

ALTER TABLE `chv_ip_bans` MODIFY `ip_ban_ip` varchar(255) NOT NULL;
ALTER TABLE `chv_pages` MODIFY `page_internal` varchar(255) DEFAULT NULL;
ALTER TABLE `chv_users` MODIFY `user_username` varchar(255) NOT NULL;
ALTER TABLE `chv_users` MODIFY `user_email` varchar(255) DEFAULT NULL;
ALTER TABLE `chv_users` MODIFY `user_image_expiration` varchar(255) DEFAULT NULL;
ALTER TABLE `chv_users` MODIFY `user_registration_ip` varchar(255) NOT NULL;
DROP TABLE IF EXISTS `chv_assets`;
CREATE TABLE `chv_assets` (
 `asset_id` bigint(32) NOT NULL AUTO_INCREMENT,
 `asset_key` varchar(255) NOT NULL,
 `asset_md5` varchar(32) NOT NULL,
 `asset_filename` varchar(255) NOT NULL,
 `asset_file_path` varchar(255) NOT NULL,
 `asset_blob` blob,
 PRIMARY KEY (`asset_id`),
 UNIQUE KEY `key` (`asset_key`(191)) USING BTREE,
 KEY `md5` (`asset_md5`),
 KEY `filename` (`asset_filename`),
 KEY `file_path` (`asset_file_path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `chv_pages` ADD `page_code` text ;
UPDATE `chv_settings` SET `setting_value`="default/favicon.png" WHERE `setting_name`="favicon_image" AND `setting_value`="favicon.png";
 UPDATE `chv_settings` SET `setting_value`="default/logo.png" WHERE `setting_name`="logo_image" AND `setting_value`="logo.png";
 UPDATE `chv_settings` SET `setting_value`="default/logo.svg" WHERE `setting_name`="logo_vector" AND `setting_value`="logo.svg";
 UPDATE `chv_settings` SET `setting_value`="default/home_cover.jpg" WHERE `setting_name`="homepage_cover_image" AND `setting_value`="home_cover.jpg";
 UPDATE `chv_settings` SET `setting_value`="default/home_cover.jpg" WHERE `setting_name`="homepage_cover_image" AND `setting_value` IS NULL;
 UPDATE `chv_settings` SET `setting_value`="default/logo_homepage.png" WHERE `setting_name`="logo_image_homepage" AND `setting_value`="logo_homepage.png";
 UPDATE `chv_settings` SET `setting_value`="default/logo_homepage.svg" WHERE `setting_name`="logo_vector_homepage" AND `setting_value`="logo_homepage.svg";
UPDATE `chv_settings` SET `setting_value`="default/consent-screen_cover.jpg" WHERE `setting_name`="consent_screen_cover_image" AND `setting_value` IS NULL;
ALTER TABLE `chv_importing` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 ALTER TABLE `chv_imports` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
UPDATE `chv_settings` SET `setting_value`="default/watermark.png" WHERE `setting_name`="watermark_image" AND `setting_value`="watermark.png";
UPDATE `chv_settings` SET `setting_typeset`="string" WHERE `setting_name`="explore_albums_min_image_count";
UPDATE `chv_pages` SET `page_icon`="fas fa-landmark" WHERE `page_icon`="icon-text";
 UPDATE `chv_pages` SET `page_icon`="fas fa-lock" WHERE `page_icon`="icon-lock";
 UPDATE `chv_pages` SET `page_icon`="fas fa-at" WHERE `page_icon`="icon-mail";
ALTER TABLE `chv_ip_bans` ADD UNIQUE KEY `ip_ban_ip` (`ip_ban_ip`(191));
ALTER TABLE `chv_importing` ADD UNIQUE KEY `importing_path` (`importing_path`(191));
INSERT INTO `chv_settings` (setting_name, setting_value, setting_default, setting_typeset) VALUES ('enable_uploads_url', '0', '0', 'bool');

INSERT INTO `chv_settings` (setting_name, setting_value, setting_default, setting_typeset) VALUES ('chevereto_news', 'a:0:{}', 'a:0:{}', 'string');
INSERT INTO `chv_settings` (setting_name, setting_value, setting_default, setting_typeset) VALUES ('cron_last_ran', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'string');

DELETE FROM `chv_settings` WHERE `setting_name` = 'minify_enable';
UPDATE `chv_settings` SET `setting_value` = "3.20.16" WHERE `setting_name` = "chevereto_version_installed";

 

[Rodolfo]

🤔 And what happened?

 

[Kevinterra]

Fehler
SQL-Befehl: Kopieren


ALTER TABLE chv_pages MODIFY page_internal varchar(255) DEFAULT NULL;
MySQL meldet:

#1071 - Schlüssel ist zu lang. Die maximale Schlüssellänge beträgt 767

 

[Gpinpin]

Bonjour faire un test

Pour résoudre le problème, il faut donc pour chaque nouvelle version de MariaDB passer
par la console et changer les paramètres :
SET GLOBAL innodb_default_row_format='dynamic' ;

Hello do a test

To solve the problem, it is therefore necessary for each new version of MariaDB to pass
by the console and change the parameters:
SET GLOBAL innodb_default_row_format='dynamic';

 

[Kevinterra]

Nothing happend.

Update screen says:

 

[Rodolfo]

@Kevinterra

Get a console where to run MySQL queries and run the queries manually, just like the error message tells you to do it. When doing that, you will notice that it will fail at some query(ies) and that's the errors that we are expecting you to provide.

🥷 If you don't want to run these queries manually, neither follow the debug process you can purchase Extra Support and I fix the database for you in less than one hour.

 

[Kevinterra]

I did a server update from Debian 9 to Debian 10. This fixed the problem.