Suitecrm: After upgrade from 7.10.4 to 7.10.5 database failure

[noimang]

Issue

Tried running the upgrade from 7.10.4 to 7.10.5 last night as keen to see if it fixed an email problem we were having. Everything appeared to run fine, and I was able to login after completion (having cleared browser cache to be sure). When I go to an account however and click on contact name the box that comes up is messed up in terms of layout, with a warning at the end saying "Database failure, Please refer to suitecrm.log for details." This same warning also shows if I go into a contact record, with the top part of the record looking fine down as far as the tabs for All, Sales, Marketing, Support etc... the tabs are there and then nothing below them except a line of text with the same "Database Failure,...." warning.

Expected Behavior

My suitecrm.log
Mysqli_query failed.
Wed May 23 17:44:17 2018 [22288][37830756-77e3-0058-c1da-5aa108419b45][FATAL] Error retrieving Lead list: Query Failed: (SELECT fp_events.id , fp_events.name , fp_events.date_modified , fp_events.assigned_user_id , 'fp_events_leads_1' panel_name FROM fp_events INNER JOIN fp_events_leads_1_c ON leads.id=fp_events_leads_1_c.fp_events_leads_1leads_idb AND fp_events_leads_1_c.fp_events_leads_1fp_events_ida='89f5df77-ffd7-f297-6be1-5af86bec6c61' AND fp_events_leads_1_c.deleted=0

where ( fp_events.assigned_user_id ='37830756-77e3-0058-c1da-5aa108419b45' ) AND fp_events.deleted=0) ORDER BY fp_events.id asc LIMIT 0,11: MySQL error 1054: Unknown column 'leads.id' in 'on clause'
Wed May 23 17:44:17 2018 [22288][37830756-77e3-0058-c1da-5aa108419b45][FATAL] Exception handling in /home/admin/web/sms.cncsofts.com/public_html/include/MVC/Controller/SugarController.php:402
Wed May 23 17:44:17 2018 [22288][37830756-77e3-0058-c1da-5aa108419b45][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.

Actual Behavior

I could not open detailview

Possible Fix

Steps to Reproduce

1. Download 7.10.x to 7.10.5 upgrade version
2. Upgrade my SuiteCRM 7.10.4
3. It done

Context

After upgrade to 7.10.5 I could not open detailview of somes modules, system error that the database failure

Your Environment

• SuiteCRM Version used: 7.10.4, 7.10.3, 7.10.5
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)):Chrome 61
• Environment name and version (e.g. MySQL, PHP 7): MySQL, PHP 5.6
• Operating System and version (e.g Ubuntu 16.04):

[pgorod]

I've seen a similar error yesterday, also on 7.10.5, with a missing contacts.id field... 😕
https://suitecrm.com/suitecrm/forum/installation-upgrade-help/19170-database-failure-problem-after-upgrade-from-7-10-4-to-7-10-5

[PedroErnst]

Hello @noimang

Thanks for reporting this.

I haven't been able to reproduce this, did a clean install of 7.10.4 and upgraded to 7.10.5 via the upgrade package.

Do you have any customizations which might be interfering or are you running a standard out of the box SCRM installation?
Was 7.10.4 the first version you installed, or was it in turn upgraded from a previous version?

[pgorod]

@PedroErnst from the Forum thread we have some additional information.

The specific line of code generating the error (there were 4 possibilities, now we know which one it is) is this:

data/SugarBean.php:1507: $result = $this->db->query($count_query, true, "Error running count query for $this->object_name List: ");

The query itself has obvious problems: it mentions a field in the ON clause which belongs to a table that was not mentioned in either the FROM clause, or right after the INNER JOIN clause.

So one of these two needs to be done:

1. The table must be referred before the field can be used.
2. We don't want to use that field, we want to use a different one from the tables already mentioned.

So even if you can't reproduce, you might be able to debug the query creation, and try to see which part of the code was changed recently to cause this regression. You can also see how that same query is build in 7.10.4, so you can see the difference.

I hope this helps... this is definitely _the_ bug in 7.10.5 that's having a larger impact out there.

[PedroErnst]

Thanks @pgorod, I've had a read through the forum thread.

A priori I don't see any obvious recent changes to the code around, above or below that line. At least not between 7.10.4 and 7.10.5.

In both cases where we have the output of the logs, there are custom modules involved, and subpanels. More information here would be helpful, in order to be able to reproduce the issue.

What kind of relationship do the custom modules have to the module where the issue appears?
Were they created in studio?
Do they have only subpanels on this module, or are there others? If yes, does the issue appear as well on other modules?

As much info as possible on these customizations will help.

[pgorod]

In the top of the Forum thread there is this query with error:

SELECT count(*) c 
FROM fp_events 
INNER JOIN fp_events_contacts_c 
ON contacts.id=fp_events_contacts_c.fp_events_contactscontacts_idb 
AND fp_events_contacts_c.fp_events_contactsfp_events_ida='*******-****-****-****-***********' 
AND fp_events_contacts_c.deleted=0
where fp_events.deleted=0

This gives error on contacts.id field.

I don't think those are custom modules - or at least they are "out-of-the-box" custom modules. I have these tables in my test install which is (sort of) clean. At least I don't think I created any custom modules on it.

[PedroErnst]

Good point. Do you also get the error in your test install?

[PedroErnst]

I've managed to reproduce this error, and not just on an upgraded instance but anywhere.

Working on it.

[pgorod]

Ok, I am just upgrading my test system to 7.10.5, hadn't done that yet. How do you make the error happen? On which screen is it?

P.S. - I got the following messages when upgrading from 7.10.4:

    themes/SuiteP/themedef.php => custom/themes/SuiteP/themedef.php
    modules/Home/dashlets.php => custom/modules/Home/dashlets.php
    modules/Accounts/language/en_us.lang.php => custom/modules/Accounts/language/en_us.lang.php
    modules/AOS_Contracts/language/en_us.lang.php => custom/modules/AOS_Contracts/language/en_us.lang.php
    modules/AOS_Contracts/metadata/detailviewdefs.php => custom/modules/AOS_Contracts/metadata/detailviewdefs.php
    modules/AOS_Contracts/metadata/editviewdefs.php => custom/modules/AOS_Contracts/metadata/editviewdefs.php
    modules/Documents/metadata/editviewdefs.php => custom/modules/Documents/metadata/editviewdefs.php
    modules/Contacts/language/en_us.lang.php => custom/modules/Contacts/language/en_us.lang.php
    modules/FP_events/language/en_us.lang.php => custom/modules/FP_events/language/en_us.lang.php
    modules/FP_events/metadata/detailviewdefs.php => custom/modules/FP_events/metadata/detailviewdefs.php
    modules/FP_events/metadata/editviewdefs.php => custom/modules/FP_events/metadata/editviewdefs.php

I don't remember editing anything on fp_events module, but maybe I did. I am suspecting these messages, though. I might have some outdated stuff breaking something else in the new release.

[PedroErnst]

Opening the events subpanel on contacts detailview will produce the error.

[PedroErnst]

Seems like a missing pair of braces was causing this issue in the case of the Contacts->Events subpanel.

Maybe someone can confirm that the other cases are also fixed by this?

[pgorod]

Cool 😎 Fixes it for me.

I'll ask on the forum thread for other people to try it.

[Glen206]

I can verify that following this hotfix brought back access to all my submenus I was have a DB error with.

Thanks everybody for this fix!

[noimang]

@PedroErnst With new install I do not have this problem.

My system upgraded from 7.10.2 to 7.10.4 and then I upgrade from 7.10.4 to 7.10.5