Database Failure and Upgrade Skipping to Commit Upgrade Without Patch Uploaded

[indieben2]

Suite CRM Version: 7.11.21

Good Morning,

I am having issues since I have migrated a SuiteCRM install from one hosting service to another.

I may be wrong but the issues seem to be surrounding items not being found vendor/google/apiclient-services/autoload.php on line 21 and the wider vender directory.

I’ve been back through the install pre-requisites in an attempt to make sure a) permissions are correct, b) PHP modules are enabled, I couldn’t find all of them but I made sure the vast majority were enabled, which they are.

I can log in. However, when i go to the upgrade wizard (in an attempt to upgrade to the latest version of 7.x, before I can even upload the patch, the upgrade wizard already skips to “commit upgrade”.

A quick rebuild and repair does some of the work, produces a script, when I try and run that script, I get “database failure” - here is the script:

/* Table : users /
/COLUMNS/
/ INDEXES */
/*INDEX MISMATCH WITH DATABASE - idx_user_name - ROW idx_user_name index [0] => ‘user_name’ [1] => ‘is_group’ [2] => ‘status’ [3] => ‘last_name (30)’ [4] => ‘first_name (30)’ [5] => ‘id’ /
/ VARDEF - idx_user_name - ROW idx_user_name index [0] => ‘user_name’ [1] => ‘is_group’ [2] => ‘status’ [3] => ‘last_name’ [4] => ‘first_name’ [5] => ‘id’ /
ALTER TABLE users DROP INDEX idx_user_name, ADD INDEX idx_user_name (user_name,is_group,status,last_name,first_name,id);
/ Table : aos_pdf_templates */
/COLUMNS/
/*MISMATCH WITH DATABASE - pdfheader - ROW [name] => ‘pdfheader’ [type] => ‘text’ /
/ VARDEF - pdfheader - ROW[required] => ‘’ [name] => ‘pdfheader’ [vname] => ‘LBL_HEADER’ [type] => ‘longtext’ [massupdate] => ‘0’ [comments] => ‘’ [help] => ‘’ [importable] => ‘true’ [duplicate_merge] => ‘disabled’ [duplicate_merge_dom_value] => ‘0’ [audited] => ‘’ [reportable] => ‘1’ [size] => ‘20’ [studio] => ‘visible’ [rows] => ‘4’ [cols] => ‘20’ [dbType] => ‘longtext’ [len] => ‘’ */
/*MISMATCH WITH DATABASE - pdffooter - ROW [name] => ‘pdffooter’ [type] => ‘text’ /
/ VARDEF - pdffooter - ROW[required] => ‘’ [name] => ‘pdffooter’ [vname] => ‘LBL_FOOTER’ [type] => ‘longtext’ [massupdate] => ‘0’ [comments] => ‘’ [help] => ‘’ [importable] => ‘true’ [duplicate_merge] => ‘disabled’ [duplicate_merge_dom_value] => ‘0’ [audited] => ‘’ [reportable] => ‘1’ [size] => ‘20’ [studio] => ‘visible’ [rows] => ‘4’ [cols] => ‘20’ [dbType] => ‘longtext’ [len] => ‘’ /
/ INDEXES /
ALTER TABLE aos_pdf_templates modify COLUMN pdfheader longtext NULL , modify COLUMN pdffooter longtext NULL ;
/ Table : users_password_link /
/COLUMNS/
/MISSING IN DATABASE - user_id - ROW/
/ INDEXES */
ALTER TABLE users_password_link add COLUMN user_id varchar(36) NULL ;

In the config file, username and password obscured, this is the db script:

‘db_host_name’ => ‘localhost’,
‘db_host_instance’ => ‘SQLEXPRESS’,
‘db_user_name’ => ‘REMOVED’,
‘db_password’ => ‘REMOVED’,
‘db_name’ => ‘REMOVED’,
‘db_type’ => ‘mysql’,
‘db_port’ => ‘’,
‘db_manager’ => ‘MysqliManager’,

In the suitecrm.log file:

Sun Jan 2 06:08:58 2022 [2624521][1][FATAL] Mysqli_query failed.
Sun Jan 2 06:08:58 2022 [2624521][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
Sun Jan 2 06:08:58 2022 [2624521][1][FATAL] Exception handling in /home/csg/public_html/workflow/contactcentre/include/MVC/Controller/SugarController.php:409
Sun Jan 2 06:08:58 2022 [2624521][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.

Hoping somebody might be able to shed some light on this.

Thanks and Kind Regards

[pgr]

The critical part of your log messages is this: error 1071: Specified key was too long; max key length is 1000 bytes

With that, you’ll find a few similar threads here in the forums, like this one:

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

Thanks @shad at first I couldn’t change the field length in Studio because I would get database failure when trying to change the value. So I FIRST changed the column length in the Database. Then I would still get database failure on rebuild because the DB didn’t match SuiteCRM vardefs. So… magic trick… First change the DB length, THEN change them in SuiteCRM Studio to match. THEN repair and rebuild. Now all tables synced!!! Thanks for the help.

That has a few workarounds, and a link to a helpful video.

But the easiest suggestion is to upgrade MySQL from 5.6 to 5.7

[indieben2]

Morning PGR and thanks for the reply,

I’ve had a chat with the host but they have said that the server is running Maria DB 10.5 so would you still say the linked workarounds would be the best route?

Thanks

[indieben2]

I’ve just had some further information here from the very helpful webhosts:

"I’ve taken a look into this and it does appear to be a scripting issue at hand. The commit.php file is reading from an array of variables stored in the $_SESSION.

I’ve added a line to the top of the file that prints the whole session variable.

If you look closely the keys such as current_db_version are undefined/missing meaning the script cannot complete.

Unfortunately, these values are set prior to this file being loaded and are not set by the server itself these are set by the script."

[pgr]

I don’t know how to solve that one…

[indieben2]

Hello pgr,

Yeah all as i’ve used is the SuiteCRM upgrade patches over the years - it seems that the last patch has created some sort of issue.

On closer inspection, I notice that some tables within the database are MyISAM and others are InnoDB. Is this ever normal from a SuiteCRM perspective?

Thanks.

[pgr]

@indieben2

On closer inspection, I notice that some tables within the database are MyISAM and others are InnoDB. Is this ever normal from a SuiteCRM perspective?

That doesn’t sound right, no. You could have different databases in the same server with different modes, but within the suitecrm database I would expect everything should be the same (InnoDB in my case)