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.
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
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.
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.
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ā¦
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.
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.
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 ?