Upgrade Packate 7.10.x to 7.10.19 DB Error - CREATE INDEX last_name (30) fails

📦 Install & Upgrade Help
1

I am receiving this message during the last step of the upgrade process. It seems to me to be a syntax error where I think the intention is to only index using the first 30 chars of the last_name and first_name columns. I don’t see this is possible with SQL CREATE INDEX at all. My plan would be to remove the (30) from the sql in both spots before executing but those fields are 255 chars each making for large index columns. Anybody else run into this and, if so, what was your workaround?

sql from log message: CREATE INDEX idx_user_name ON users (user_name,is_group,status,last_name (30),first_name (30),id)

using SSMS and removing the (30) from each works fine and index is created which I would expect based on sql doc. I see nowhere you can specify a substring of col when creating index.

2

Check this post for the answer:

https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/27007-suitecrm-7-11-6-database-failure

Thanks,

AlxGr

3

Thank you for the suggestion AlxGr. I did check my …\modules\Users\vardefs.php and the (30) is in there (see attached). Are you suggesting I remove it from here and re-run the upgrade? That seems to make sense as it seems it is constructing the SQL to execute from here. Please advise and thank you.

4

You don’t need to run it again. Just run a Quick Repair and Rebuild and commit the changes to the DB at the bottom of the page

5

OK, I went ahead and did a QRR and when I attempted to commit changes I received same message (see attached). I did another QRR but this time before I committed the sql changes I manually adjusted the sql by removing the (30) in last_name and first_name and it was successful. I was guessing this would be the outcome as my test using SSMS worked in same fashion. Shall I remove the (30) and (30) from the …\modules\Users\vardefs.php for consistency? Perhaps this is an MS-SQL problem only?

6

I don’t have the exact response for that. I’ll recommend you to report your findings here: https://github.com/salesagility/SuiteCRM/pull/7510
That will help the developers to understand the issue better and try to find the solution.

Thanks,

AlxGr

7

Thanks for your insight AlxGr, your suggestions helped me work through it. I will go ahead and post my findings there to see if it can help others. My local upgrade and that on my development server both went OK and I’m testing now and all seems well. Thanks again.

8

I’m glad it worked.

Thanks,

AlxGr