Upgrade not possible. --> Database failure. Please refer to suitecrm.log for details. 🤔

PowerQuest · 30 September 2021 23:04

Well I loaded the upgrade wizard and clicked next to fire up the upgrade process and then this happens:

Thu Sep 30 21:48:56 2021 [22056][1][FATAL] Mysqli_query failed.
Thu Sep 30 21:48:56 2021 [22056][1][FATAL]  Query Failed: ALTER TABLE users   DROP INDEX idx_user_name,  ADD INDEX idx_user_name (user_name,is_group,status,last_name,first_name,id): MySQL error 1071: Specified key was too long; max key length is 1000 bytes
Thu Sep 30 21:48:56 2021 [22056][1][FATAL] Exception handling in /home/public_html/include/MVC/Controller/SugarController.php:409
Thu Sep 30 21:48:56 2021 [22056][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.
Thu Sep 30 21:49:41 2021 [22578][1][FATAL] Mysqli_query failed.
Thu Sep 30 21:49:41 2021 [22578][1][FATAL]  Query Failed: ALTER TABLE users   DROP INDEX idx_user_name,  ADD INDEX idx_user_name (user_name,is_group,status,last_name,first_name,id): MySQL error 1071: Specified key was too long; max key length is 1000 bytes
Thu Sep 30 21:49:41 2021 [22578][1][FATAL] Exception handling in /home/public_html/include/MVC/Controller/SugarController.php:409
Thu Sep 30 21:49:41 2021 [22578][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.
Thu Sep 30 21:50:34 2021 [23220][1][FATAL] Mysqli_query failed.
Thu Sep 30 21:50:34 2021 [23220][1][FATAL] Executing repair query:  Query Failed: ALTER TABLE users   DROP INDEX idx_user_name,  ADD INDEX idx_user_name (user_name,is_group,status,last_name,first_name,id): MySQL error 1071: Specified key was too long; max key length is 1000 bytes
Thu Sep 30 21:50:34 2021 [23220][1][FATAL] Exception handling in /home/public_html/include/MVC/Controller/SugarController.php:409
Thu Sep 30 21:50:34 2021 [23220][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.
Thu Sep 30 21:55:56 2021 [25911][1][FATAL] Mysqli_query failed.
Thu Sep 30 21:55:56 2021 [25911][1][FATAL] Executing repair query:  Query Failed: ALTER TABLE users   DROP INDEX idx_user_name,  ADD INDEX idx_user_name (user_name,is_group,status,last_name,first_name,id): MySQL error 1071: Specified key was too long; max key length is 1000 bytes
Thu Sep 30 21:55:56 2021 [25911][1][FATAL] Exception handling in /home/public_html/include/MVC/Controller/SugarController.php:409
Thu Sep 30 21:55:56 2021 [25911][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.

To be honestly I do not know how to fix this. Something is completely wrong here. I can login to the CRM and do all kinds of normal things (like viewing creating new leads, records, emails etc.) as the system acts normally here. But the attempting to run a repair or the upgrade wizard goes totally down the drain.

Please kindly advice.
kind regards
PowerQuest

malcek · 1 October 2021 13:30

Same here, just try to upgrade and get same error
Fri Oct 1 13:10:11 2021 [906429][1][FATAL] Mysqli_query failed.
Fri Oct 1 13:10:11 2021 [906429][1][FATAL] Query Failed: ALTER TABLE users DROP INDEX idx_user_name, ADD INDEX idx_user_name (user_name,is_group,status,last_name,first_name,id): MySQL error 1071: Specified key was too long; max key length is 1000 bytes
Fri Oct 1 13:10:11 2021 [906429][1][FATAL] Exception handling in /home/veleproda/public_html/sugar/include/MVC/Controller/SugarController.php:409

pgr · 1 October 2021 15:11

I’ve seen this issue often here in the Forums, try searching for solutions here. I don’t understand the issue fully, so I don’t feel ready to help out directly; but I just wanted to say that it’s a known issue.

PowerQuest · 1 October 2021 15:56

Thanks @pgr
Hm well then someone her ein the community must know the answer to the issue I hope?

Kind regards
PowerQuest

PowerQuest · 4 October 2021 17:02

Hmmmm…

I must admit i have no idea on how to fix this. i been searching but so far I don’t get my head around this issue.

Kind regards
PowerQuest

pgr · 4 October 2021 18:41

Have you seen this?

github.com/salesagility/SuiteCRM

Impossible to install SuiteCRM 7.11.20

opened 02:16PM - 24 Jun 21 UTC

InfoLibre

Type:Bug Priority:Critical Area: Installation Area: Databases

Installation (and update too) of SuiteCRM 7.11.20 fails : ![Capture d’écran d…u 2021-06-24 16-12-21](https://user-images.githubusercontent.com/3955499/123278166-03319300-d507-11eb-93ee-baab2aa8efbb.png) Only 9 tables are created : ![Capture d’écran du 2021-06-24 16-13-12](https://user-images.githubusercontent.com/3955499/123278292-1b091700-d507-11eb-9a96-01de7851d43a.png) It seems it's a problem with MyISAM tables ? (see https://community.suitecrm.com/t/database-failure-during-update-from-7-10-30-to-7-10-31/80358/4) I can't set innoDB on the server (I'm not the proprietary of the server). Is it possible to add this choice in the installation interface ? **suitecrm.log :** ``` Thu Jun 24 16:08:19 2021 [1335610][-none-][FATAL] Mysqli_query failed. Thu Jun 24 16:08:19 2021 [1335610][-none-][FATAL] Error creating table: users: Query Failed: CREATE TABLE users (`id` char(36) NOT NULL ,`user_name` varchar(60) NULL ,`user_hash` varchar(255) NULL ,`system_generated_password` bool NULL ,`pwd_last_changed` datetime NULL ,`authenticate_id` varchar(100) NULL ,`sugar_login` bool DEFAULT '1' NULL ,`first_name` varchar(255) NULL ,`last_name` varchar(255) NULL ,`is_admin` bool DEFAULT '0' NULL ,`external_auth_only` bool DEFAULT '0' NULL ,`receive_notifications` bool DEFAULT '1' NULL ,`description` text NULL ,`date_entered` datetime NULL ,`date_modified` datetime NULL ,`modified_user_id` char(36) NULL ,`created_by` char(36) NULL ,`title` varchar(50) NULL ,`photo` varchar(255) NULL ,`department` varchar(50) NULL ,`phone_home` varchar(50) NULL ,`phone_mobile` varchar(50) NULL ,`phone_work` varchar(50) NULL ,`phone_other` varchar(50) NULL ,`phone_fax` varchar(50) NULL ,`status` varchar(100) NULL ,`address_street` varchar(150) NULL ,`address_city` varchar(100) NULL ,`address_state` varchar(100) NULL ,`address_country` varchar(100) NULL ,`address_postalcode` varchar(20) NULL ,`deleted` bool NULL ,`portal_only` bool DEFAULT '0' NULL ,`show_on_employees` bool DEFAULT '1' NULL ,`employee_status` varchar(100) NULL ,`messenger_id` varchar(100) NULL ,`messenger_type` varchar(100) NULL ,`reports_to_id` char(36) NULL ,`is_group` bool NULL ,`factor_auth` bool NULL ,`factor_auth_interface` varchar(255) NULL , PRIMARY KEY (id), KEY idx_user_name (user_name, is_group, status, last_name, first_name, id)) CHARACTER SET utf8 COLLATE utf8_general_ci: MySQL error 1071: Specified key was too long; max key length is 1000 bytes ```

PowerQuest · 5 October 2021 07:21

No thanks, I’ll check it.

Kind regards
PowerQuest

PowerQuest · 5 October 2021 07:41

Hi @pgr
I cannot see anything mentioning the issue itself which is the DB/SQL error:
MySQL error 1071: Specified key was too long; max key length is 1000 bytes
Which is preventing me from doing the upgrade.

Kind regards
PowerQuest

pgr · 5 October 2021 10:11

That is the exact error they report, scroll right on the section called “suitecrm.log” of the Issue

PowerQuest · 5 October 2021 10:32

Right I’ll check it again.

However I found this Youtube video a few days ago and the hosting company where we have our VPS servers at applied that fix:

It actually worked. Well sort of. I was able to actually run the upgrade and it only failed in the absolutely the last step of the upgrade process.

Before it would not even load the upgrade wizard. It just said “Database error. Refer to the suitecrm.log”.

Error in the last step was:
Tue Oct 5 10:22:06 2021 [7339][1][FATAL] Query Failed: ALTER TABLE users DROP INDEX idx_user_name, ADD INDEX idx_user_name (user_name,is_group,status,last_name,first_name,id): MySQL error 1071: Specified key was too long; max key length is 1000 bytes
Tue Oct 5 10:22:06 2021 [7339][1][FATAL] Exception handling in /home/public_html/public_html/include/MVC/Controller/SugarController.php:409
Tue Oct 5 10:22:06 2021 [7339][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.

The DB is still complaining over 1000 characters…

I’ll re-check that thread, thanks @pgr

Kind regards
PowerQuest

PowerQuest · 5 October 2021 10:36

I checked it and they just list the same error:

MySQL error 1071: Specified key was too long; max key length is 1000 bytes ?
But I cannot see any suggestion for a fix?

Kind regards
PowerQuest

pgr · 5 October 2021 11:03

You’re right, it’s just the issue, no PR with solution yet.

I don’t know if you can try that suggestion of setting MySQL as “InnoDB”. But other than that, I don’t know what else to suggest.

PowerQuest · 5 October 2021 11:22

Well look on the YouTube video solution that my hosting company applied. It did fix something as it made the upgrade possible.

Kind regards
PowerQuest

shad · 7 October 2021 18:25

Hello,

To resolve this issue you need to change some of the column definitions on the users table. With this number of columns in the index it is easy to go over the 1000 byte limit. Especially if you have switched to MB4.

A good suggestion is find out the maximum length you currently have with these queries. You don’t really have much control of the other columns.
SELECT MAX(LENGTH(first_name)) FROM users;
SELECT MAX(LENGTH(last_name)) FROM users;
SELECT MAX(LENGTH(user_name)) FROM users;

With those values compare what your current length for these varchar columns are and make needed adjustments with a bit of wiggle room. first_name in particular is usually 255 so you may only need to change that.

Once complete you can test by running the index query manually or just try the upgrade again.

PowerQuest · 7 October 2021 19:18

So there is no way to change /override the 1000 byte limit in MariaDB/MySQL?

Kind regards
PowerQuest

shad · 7 October 2021 20:44

@PowerQuest To the best of my knowledge no. At least not for innodb.

pstevens · 7 October 2021 20:54

Hey @PowerQuest I just upgraded to LTS 7.10.33, the upgrade succeeded but with a DB error exactly the same as you and a repair and rebuild generates the same error. I’m trying to follow the thread, but did you solve this? If not I’m following the thread.

PS - I tried to change the field length for first name and last name as suggested, but can’t save, get the database error.

PowerQuest · 7 October 2021 21:37

Well my host run that fix from the YouTube video I posted here too. After that I was able to access the upgrade wizard and then run the upgrade. Although it failed on the very last step. But the system is after that upgraded to the newest version.

Also they added this comment in the support convo i had with them as they were digging/analyzing the issue further on one of our VPS servers where we have suitecrm installed on:

Ah ok I can see that the change has not taken effect. This is because it uses the wrong engine of 220 rows in the tables.

MariaDB [(none)]> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'XC_DB02' and ENGINE = 'myISAM';
+ ----------------------------------------------- + - ------- +
| TABLE_NAME | ENGINE |
+ ----------------------------------------------- + - ------- +
| accounts | MyISAM |
| accounts_audit | MyISAM |
| accounts_bugs | MyISAM |
| accounts_cases | MyISAM |
| accounts_contacts | MyISAM |
| accounts_cstm | MyISAM |
| accounts_opportunities | MyISAM |
| acl_actions | MyISAM |
| acl_roles | MyISAM |
| acl_roles_actions | MyISAM |
It should use InnoDB instead.
5:04 pm
MariaDB [(none)]> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'XC_DB02' and ENGINE = 'myISAM';
+ ----------------------------------------------- + - ------- +
| TABLE_NAME | ENGINE |
+ ----------------------------------------------- + - ------- +
| accounts | MyISAM |
| accounts_audit | MyISAM |
| accounts_bugs | MyISAM |
| accounts_cases | MyISAM |
| accounts_contacts | MyISAM |
| accounts_cstm | MyISAM |
| accounts_opportunities | MyISAM |
| acl_actions | MyISAM |
| acl_roles | MyISAM |
| acl_roles_actions | MyISAM |
Perhaps this can be read more easily. But I do not have exactly a smart way to do it on 220 rows at once. This can be done in phpmyadmin.

This should in theory solve the issue.

I haven’t personally have time to explore that last part that my host suggest.
Maybe this could help too @pstevens ?

shad · 7 October 2021 21:48

What are the lengths of these columns in the database currently.

ckangwei · 8 October 2021 04:29

Are you using utf8mb for those fields? I suppose a normal utf8 field should not exceed the 1000 limit