Database Failure problem after upgrade from 7.10.4 to 7.10.5

SuiteCRM Forum - English Language Installation & Upgrade Help
1

Hi All,

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.

I had the host reset the permissions thinking this might help, but it doesn’t seem to have solved anything. I have also done the usual repair and rebuild (in fact have run most of the options under repair) but to no avail.

In term of the suitecrm.log, the relevant entries around that time are:

Mon May 21 21:37:32 2018 [FATAL] Mysqli_query failed.

Mon May 21 21:37:32 2018 [FATAL] Error running count query for Contact List: Query Failed: ( 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 ): MySQL error 1054: Unknown column ‘contacts.id’ in ‘on clause’

Mon May 21 21:37:32 2018 [FATAL] Exception handling in //********//include/MVC/Controller/SugarController.php:402

Mon May 21 21:37:32 2018 [FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.

Mon May 21 21:37:32 2018 [FATAL] backtrace:
#0 ////include/database/DBManager.php(353): sugar_die(‘Database failur…’)
#1 ////include/database/DBManager.php(328): DBManager->registerError(‘Error running c…’, ‘Error running c…’, true)
#2 ////include/database/MysqliManager.php(177): DBManager->checkError(‘Error running c…’, true)
#3 ////data/SugarBean.php(1510): MysqliManager->query(’( SELECT count(…’, true, ‘Error running c…’)
#4 ////data/SugarBean.php(1260): SugarBean->_get_num_rows_in_query(’( SELECT count(…’, false)
#5 ////data/SugarBean.php(1018): SugarBean->process_union_list_query(Object(Contact), ‘(SELECT fp_even…’, ‘’, -1, 10, ‘’, Object(aSubPanel), ‘( SELECT count(…’, Array)
#6 ////include/ListView/ListView.php(1101): SugarBean::get_union_related_list(Object(Contact), ‘fp_events.id as…’, ‘asc’, ‘’, ‘’, -1, 10, ‘’, Object(aSubPanel))
#7 ////include/ListView/ListViewSubPanel.php(142): ListView->processUnionBeans(Object(Contact), Object(aSubPanel), ‘fp_events_conta…’)
#8 ////include/SubPanel/SubPanel.php(220): ListViewSubPanel->process_dynamic_listview(‘Contacts’, Object(Contact), Object(aSubPanel))
#9 ////include/SubPanel/SubPanelTiles.php(366): SubPanel->ProcessSubPanelListView(‘include/SubPane…’, Array)
#10 ////include/MVC/View/SugarView.php(1193): SubPanelTiles->display()
#11 ////include/MVC/View/SugarView.php(235): SugarView->_displaySubPanels()
#12 ////include/MVC/Controller/SugarController.php(432): SugarView->process()
#13 ////include/MVC/Controller/SugarController.php(375): SugarController->processView()
#14 ////include/MVC/SugarApplication.php(109): SugarController->execute()
#15 ////index.php(52): SugarApplication->execute()
#16 {main}

Unfortunately I am not a developer so a bit lost with this, and am hoping somebody can make a suggestion or two beyond what has already been tried.

I do have a backup but only wanted to use that as a last resort really.

Thanks a lot.

Andy

2

That is a very strange error indeed.

If you go in phpMyAdmin, in the contacts table, you do have a field called “id” there, don’t you?

You can also try adding this in config_override.php

$sugar_config['disable_count_query'] = true;

Just to see if that makes any difference.

I find too many instances of your error in the code, it would be nice to know which one is actually breaking in your case:

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

/var/www/html/include/database/DBManager.php:1348:            $result = $this->query($count_query, true, "Error running count query for $this->object_name List: ");

/var/www/html/soap/SoapPortalHelper.php:214:            $result = $sugar->db->query($count_query, true, "Error running count query for $sugar->object_name List: ");

Maybe you can try tweaking those error messages a bit so you can tell from the logs which one it is. Like adding “Error 1”, “Error 2”, etc., to them.

3

@Adnyg,

I;m getting the exact same errors that you posted as well. Were you able to come up with a solution or is this still open for you.

I looked @PGR’s suggestions and I can confirm that there is a database column for contact “id”. I also tried the disable count override but this did not affect anything thing.

I have a LAMP setup

MySQL 5.6
php 7.1

Has anyone else had this issue and/or have any suggestions.

4

Hi @pgr

I checked and the id field does indeed exist in the contacts table.

I have also added that line to the config_override.php and it makes no difference.

Sorry @SuiteConvert but no solution here yet, glad to know it’s not just me having this problem though.

I have attached the log file showing the last few entries in the hope it helps clarify the problem more.

After a few repair and rebuilds and clearing some of the Javascript files through admin / repair, things seem to be getting worse if anything, with many of the screens now bringing up no information at all. For example I can type an account name in the search box and it finds it ok, but if I click Accounts in the menu bar I just get the Sales Agility footer in the main area with no accounts listed at all.

Andy

5

@Andyg, is your Suitecrm version a conversion from sugarcrm?

We’ve had our crm for some time and it originally started as SugarCRM 6.5.X and have upgraded version by version eventually to 7.10.5,

Trying to see if we have any similarities in our systems.

6

Can you try running the count SQL query from phpMyAdmin and tell me if you get any error from there?

It seems this happening to more people, and for different queries:
https://github.com/salesagility/SuiteCRM/issues/5920

7

Yes, our’s started as SugarCRM some years back too and we then moved over to SuiteCRM. It was around the time SugarCRM made the big changes to the community edition and have run quite a few SuiteCRM upgrades since. The only major problem we have had with them has been fixed by resetting permissions each time.

In terms of environment we are running PHP 5.6.36 and MySQL version shows as 10.1.31-MariaDB

Andy

8

just ran this and the result was 240, having said that I might not have done it correctly. I ran the following in PHPMyAdmin, changing your_db to my database name:

SELECT count(table_name) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘YOUR_DB’

9

With that query you’re just counting how many tables you have in the database.

What I meant was, increase your log level to DEBUG, make the error happen, and grab the full query from the error. For example, above we see

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

That is what I want to try from phpMyAdmin.

10

Hi,

I have the same BD error after upgrade from 7.10.4 to 7.10.5

Thank you.

11

Hi @pgr

I had to restore the backup so the sales team here could operate (working on the live box) so back on 7.10.4 for now. I will try and rerun the upgrade later this evening though and run this query then. In the meantime if anybody else with the problem is able to run it on theirs, that would be great.

Thanks for your help trying to sort this by the way.

Andy

12

Hi All.

Same problem here… same BD error after upgrade from 7.10.4 to 7.10.5

13

Can you try changing the error messages slightly like I ask in this post?
https://suitecrm.com/suitecrm/forum/installation-upgrade-help/19170-database-failure-problem-after-upgrade-from-7-10-4-to-7-10-5#66396

I would like to know which of those areas of the code is failing.

The query is failing because the “contacts” table is not mentioned in the FROM clause, and it’s not mentioned in the JOIN clauses. But I don’t think the contacts table is even supposed to be in there.

@AndyG since you’ve restored your backup to the working version, can you find that same query in your logs, when it works, and paste it here? I would like to see the correct query so I can spot the difference. Thanks

14

Hi, I’m facing the same problem after upgrading from 7.10.2 to 7.10.5. But it only occurs on some SubPanels for me.
I looked at the logs and I noticed that the query has changed…

In version 7.10.2 the query looked like this:
Thu May 24 09:04:44 2018 [25693][1][INFO] Query:(
SELECT count(*) c
FROM prof_jobtype
INNER JOIN prof_jobtype_contacts_c
ON prof_jobtype.id=prof_jobtype_contacts_c.prof_jobtype_contactsprof_jobtype_ida
AND prof_jobtype_contacts_c.prof_jobtype_contactscontacts_idb=N’XXXXXXXX-8148-33b2-8af4-XXXXXXXXXXXX’
AND prof_jobtype_contacts_c.deleted=0
where prof_jobtype.deleted=0
)

In version 7.10.5 the same query has changed to:
Thu May 24 06:57:44 2018 [382][1][FATAL] The multi-part identifier “contacts.id” could not be bound.: (
SELECT count(*) c
FROM prof_jobtype
INNER JOIN prof_jobtype_contacts_c
ON contacts.id=prof_jobtype_contacts_c.prof_jobtype_contactscontacts_idb
AND prof_jobtype_contacts_c.prof_jobtype_contactsprof_jobtype_ida=N’XXXXXXXX-8148-33b2-8af4-XXXXXXXXXXXX’
AND prof_jobtype_contacts_c.deleted=0
where prof_jobtype.deleted=0
)

In this case, this is a self-made module called prof_jobtype.
We’re running this installation on a MSSQL server.
Using Docker I have tried to run it on PHP 7.0.27, there it ends with a “Database failure. Please refer to suitecrm.log for details.”.
In PHP 5.6.33 the message “SQL Error : The multi-part identifier “contacts.id” could not be bound.” comes up.

15

I’ve added Error 1-5 to them and in my case it is the first occurence:

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

The function ‘_get_num_rows_in_query’ in data/SugarBean.php.

16

Absolutely the same issues here, “DB failure” messages all over the shop after upgrading from 7.9.8 to 7.10.5 - looks like 7.10.5 is a real dog’s breakfast. I’m going to revert to my pre-upgrade VM snapshot and then try upgrading to 7.10.4

Mark.

17

Guys, please try this fix here

https://github.com/PedroErnst/SuiteCRM/commit/2b3839c08511a65056d91c246d6aff5af96964e7#diff-8d01ca067f1bcc78f40e5067f8b18c5d

and tell me if it works for you. Thanks!

3 Likes
18

Worked for me.
PHEW!!

19

@pgr sorry I didn’t get chance to run the query, been a bit swamped, but with any luck the link you provided fixes it. I’ll try and implement the fix tonight / tomorrow and will post the results either way.

Thanks

Andy

20

The fix mentioned does appear to work, I just ran the update and then edited that file as per the recommendations and so far all working ok. I will test a bit more later this evening though.

I did get a connection timeout during the update itself this time (but not last time), although it appears the upgrade did work. The final entries in the upgradeWizard.log following the update were as follows. If anybody could just confirm whether that is the final part of the upgrade process that would be appreciated for peace of mind though.

Thu, 24 May 2018 16:45:52 +0100 [UpgradeWizard] - modules/InboundEmail/InboundEmailTest.php
Thu, 24 May 2018 16:46:05 +0100 [UpgradeWizard] - Cleaning up the session. Goodbye.
Thu, 24 May 2018 16:46:05 +0100 [UpgradeWizard] - at unlinkUWTempFiles()
Thu, 24 May 2018 16:46:20 +0100 [UpgradeWizard] - finished!
Thu, 24 May 2018 16:46:20 +0100 [UpgradeWizard] - Cleaning up the session. Goodbye.
Thu, 24 May 2018 16:46:20 +0100 [UpgradeWizard] - resetting $_SESSION

Thanks a lot.

Andy