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
Have you seen this?
No thanks, I’ll check it.
Kind regards
PowerQuest
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
That is the exact error they report, scroll right on the section called “suitecrm.log” of the Issue
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
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
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.
1 Like
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
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.
1 Like
So there is no way to change /override the 1000 byte limit in MariaDB/MySQL?
Kind regards
PowerQuest
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 ?
What are the lengths of these columns in the database currently.
Are you using utf8mb for those fields? I suppose a normal utf8 field should not exceed the 1000 limit
Hey @shad I’m using utf8_general_ci. However, I’m unclear on what the 1000 byte limit applies to. I have no fields anywhere near that:
Username =60
user_hash = 255
first _name 255
last_name = 255
is_group=1
id= 36
Status = 100
Is it the sum of all these that can’t be over 1000?
Hello @pstevens
The 1000 byte limit is the stored amount of data. What that translates to in terms of actual character length is a bit variable depending on some database settings.
I think it is 3-4 bytes per character so the total length of a all characters in the index is maybe 250. Technically only part of the column can be indexed but that gets weird sometimes.
Most commonly we really don’t need much more than 20-30 characters for either first or last names.
I also believe status is either Active or Inactive so you could likely save some space there.
So basically, I go in the DB and shorten up those field lengths? (careful not to truncate anything)?
Exactly. That’s what I had to do.
There are some sample queries in this thread to check what your current max lengths are
1 Like