SQLSTATE[42000]: Syntax error or access violation

[DT20C]

Hello there,

I am updating the version of chevereto to 3.20 and I am encountering the following error.

Update failed​

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 read all the documentation, but I can not solve it.

My database is already updated to InnoDB.

Any ideas?

Thanks

 

[Rodolfo]

When updating from older releases using old databases (as MariaDB 10.1) you require to re-build all the application indexes.

 

[DT20C]

Hello Rodolfo,

And how I do it? I have not seen how in the documentation.

Thanks in advance.

 

[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));

 

[DT20C]

Hello Rodolfo,

I have done the 2 SQL queries and they finished successfully, but it shows the following error:

Update failed​

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.

 

[DT20C]

¿Alguien puede ayudarme?

 

[Rodolfo]

This is normal to expect when updating from old database servers. I'm afraid that your database requires to rebuild the conflicting indexes. Sadly, we don't have all the cases covered and in your case we require to inspect your web server directly.

You may send web server access to support@chevereto.com linking back to this topic.

Kindly note that this will take a lot of time before getting a resolution. Thanks for your patience.

 

[Rodolfo]

Hola, esto es normal de esperar cuando se actualiza desde gestores de base de datos tan antiguos. Me temo que necesito revisar esto directamente en tu instalación. Lamentablemente, no tenemos aún cubiertos todos los casos referentes a este conflicto.

Puedes enviar accesso a tu servidor a support@chevereto.com agregando un link a este tema para referencia.

Ten en cuenta que esto podría llevar bastante tiempo para resolverse. Gracias por tu paciencia.

 

[Rodolfo]

Dear @DT20C,

Unfortunately, this ticket has more than 14 days without a reply or feedback from you. We will now consider this ticket abandoned and its status is now closed.

Please, don't hesitate to create a new ticket if this matter is still causing you trouble. If this is already solved you can ignore this message.