Update error SQL

[BMWracing]

▶ Reproduction steps

1. Update to the last version (3.20.5 -> 3.20.8)
2. Update failed -> SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes
   
   😢 Unexpected result

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

📃 Error log message

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

UPDATE chv_settings SET setting_value = 1 WHERE setting_name = 'maintenance'; ALTER TABLE chv_settings MODIFY setting_name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL; ALTER TABLE chv_deletions MODIFY deleted_content_ip varchar(255) NOT NULL; 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; 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"; INSERT INTO chv_settings (setting_name, setting_value, setting_default, setting_typeset) VALUES ('enable_uploads_url', '0', '0', 'bool'); UPDATE chv_settings SET setting_value = "3.20.8" WHERE setting_name = "chevereto_version_installed"; UPDATE chv_settings SET setting_value = 0 WHERE setting_name = "maintenance";

 

[BMWracing]

When trying the query manualy in the database this seems to be the problem:

ALTER TABLE chv_users MODIFY user_username varchar(255) NOT NULL;

Specified key was too long; max key length is 1000 bytes

innodb_large_prefix

ON

 

[BMWracing]

Are the most recent versions of Chevereto not supporting Mysql versions 5.7?

 

[BMWracing]

Update: I updated from MYSQL 5.7 to 8.0. Updated php etc. All websites are working including the DB's except Chevereto. Error code 500 now when opening the site.

Can you help me debug / solve this issue?

edit:

[Tue Jul 06 11:40:36.322468 2021] [proxy_fcgi:error] [pid 1045:tid 140665832806144] [client xxx] AH01071: Got error 'PHP message: \nAw, snap! Internal Server Error [debug @ error_log] - https://chevereto.com/go/debug\n\n** errorId #a497b7f98a5f52f1 **\n>> PDOException [HY000]: SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.\nAt /lib/G/classes/class.db.php:155\n\nStack trace:\n#0

 

[BMWracing]

For the record I have updated all my tables to InnoDB

 

[Rodolfo]

Kindly provide the error for a497b7f98a5f52f1

 

[BMWracing]

Hi Rodolfo, Thanks for the quick reply. The site is currently offline so I hope we can solve this asap.

I cannot give you the output of that error anymore as the site is now asking for the database and is showing the installer. I cannot restore a backup of the database as the existing one cannot be deleted (to be replaced):

mysql> DROP DATABASE imgup_fotoupload;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

When manualy deleting the tables in the database - I was able to delete everything except for these two:


These two are both giving this error when trying to delete.



Any idea's on how to resolve this?

 

[Rodolfo]

Any idea's on how to resolve this?

No, I don't have any clue as it seems that you followed too many actions out of your comfort zone and the status of the installation is unknown

This is normal to happen if you perform actions not required, this was easily fixable as change the database table storage engine as is instructed in the update error message (yellow box). But you went to another direction, sorry about that. We will seek to improve such alert.

Unfortunately I can't assists you further, consider to purchase extra support if you want me to spend all the morning fixing it.

You could try a backup and simply alter the table storage engine. Hope you got a backup!

 

[BMWracing]

Ok thanks - is it possible to change the database URL / location? I'm going to restore the backup to another server and point to that temporary to keep everyhing online. How do I update this as it's now giving a 500 error?

 

[Rodolfo]

What is needed to do now is to debug to determine what's exactly the malfunction affecting your system so we can quickly assists you further. Kindly note that you will require to understand our error reporting and follow our debug guide.

Is this something you feel comfortable doing by yourself? If so, let us know your findings.

 

[BMWracing]

Hi Rodolfo, I've temporary hosted the DB on another server and connected the webserver to this. The site is back online and provides me some more time to troubleshoot.
Shall I re-open the ticket once I'm back on the same server? Currently the SQL server does not let me drop the old database.. so need to fix that first (any tips are welcome as it keeps giving me this error)

 

[Rodolfo]

The issue is quite simple to fix, simply change all the tables to InnoDB. Note that as any ALTER TABLE query this may take longer in huge databases and you may want to run each statement with caution.

ALTER TABLE chv_confirmations ENGINE = InnoDB;
ALTER TABLE chv_follows ENGINE = InnoDB;
ALTER TABLE chv_categories ENGINE = InnoDB;
ALTER TABLE chv_redirects ENGINE = InnoDB;
ALTER TABLE chv_users ENGINE = InnoDB;
ALTER TABLE chv_queues ENGINE = InnoDB;
ALTER TABLE chv_stats ENGINE = InnoDB;
ALTER TABLE chv_imports ENGINE = InnoDB;
ALTER TABLE chv_importing ENGINE = InnoDB;
ALTER TABLE chv_storage_apis ENGINE = InnoDB;
ALTER TABLE chv_pages ENGINE = InnoDB;
ALTER TABLE chv_logins ENGINE = InnoDB;
ALTER TABLE chv_ip_bans ENGINE = InnoDB;
ALTER TABLE chv_images ENGINE = InnoDB;
ALTER TABLE chv_assets ENGINE = InnoDB;
ALTER TABLE chv_albums ENGINE = InnoDB;
ALTER TABLE chv_notifications ENGINE = InnoDB;
ALTER TABLE chv_storages ENGINE = InnoDB;
ALTER TABLE chv_likes ENGINE = InnoDB;
ALTER TABLE chv_settings ENGINE = InnoDB;
ALTER TABLE chv_deletions ENGINE = InnoDB;
ALTER TABLE chv_locks ENGINE = InnoDB;
ALTER TABLE chv_requests ENGINE = InnoDB;

 

[Rodolfo]

Dear @BMWracing,

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.