Suitecrm: Database failure when filtering on relate field and exporting

[dursuntosun]

Issue

When i add custom Target list filter to Target module, it is working good. But when i select all records from bulk action menu and export them, there is an error which is Database failure. But when i select records of first page and exports, it is working good.

Expected Behavior

It should be working as expected.

Actual Behavior

Possible Fix

Steps to Reproduce

1. Add custom target list filter to Targets module
   Add the file:
   custom/Extension/modules/Prospects/Ext/Vardefs/targetlistfilter.php
   with content:
   <?php $dictionary['Prospect']['fields']['prospect_list_name'] = array ( 'name' => 'prospect_list_name', 'rname' => 'name', 'id_name' => 'prospect_list_id', 'vname' => 'LBL_PROSPECTLIST_NAME', 'type' => 'relate', 'link' => 'prospect_lists', 'table' => 'prospect_lists', 'isnull' => 'true', 'module' => 'ProspectLists', 'dbType' => 'char', 'len' => '255', 'source' => 'non-db', 'duplicate_merge'=> 'disabled', );
   Add the file:
   custom/Extension/modules/Prospects/Ext/Language/en_us.targetlistfilter.php
   with content:
   <?php $mod_strings['LBL_PROSPECTLIST_NAME'] = 'Target List:';

Afterwards, do a Admin / Repair / Quick Repair and Rebuild

1. Open Studio, module Contacts / Layouts / Filter
   The filter field Target List is now available in the Hidden list and can be added to the Quick Filter and the Advance Filter layout.
2. Open Target module and filter with "Target List" field
   

1. Select all results
   

2. Then export them via Bulk Action Menu
   
   6.See result
   

3. Suitecrm log;
   `Mon Dec 7 17:52:06 2020 [30995][e433d2f0-c23e-3ad2-bd4d-5ee727929421][FATAL] Mysqli_query failed.
   Mon Dec 7 17:52:06 2020 [30995][e433d2f0-c23e-3ad2-bd4d-5ee727929421][FATAL] Error exporting Prospects:
   .SELECT
   prospects.,
   email_addresses.email_address email_address,
   ‘’ email_addresses_non_primary, users.user_name as assigned_user_name ,prospects_cstm.jjwg_maps_geocode_status_c,prospects_cstm.jjwg_maps_lng_c,prospects_cstm.jjwg_maps_lat_c,prospects_cstm.target_tag_c,prospects_cstm.jjwg_maps_address_c FROM prospects LEFT JOIN users
   ON prospects.assigned_user_id=users.id LEFT JOIN email_addr_bean_rel on prospects.id = email_addr_bean_rel.bean_id and email_addr_bean_rel.bean_module = ‘Prospects’ and email_addr_bean_rel.deleted = 0 and email_addr_bean_rel.primary_address = 1 LEFT JOIN email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id LEFT JOIN prospects_cstm ON prospects.id = prospects_cstm.id_c WHERE ((prospect_list_name like ‘15 ŞUBAT 2018 KONFERANS KATILIMCI LİSTESİ%’)) AND prospects.deleted=0 Query Failed: SELECT
   prospects.,
   email_addresses.email_address email_address,
   ‘’ email_addresses_non_primary, users.user_name as assigned_user_name ,prospects_cstm.jjwg_maps_geocode_status_c,prospects_cstm.jjwg_maps_lng_c,prospects_cstm.jjwg_maps_lat_c,prospects_cstm.target_tag_c,prospects_cstm.jjwg_maps_address_c FROM prospects LEFT JOIN users
   ON prospects.assigned_user_id=users.id LEFT JOIN email_addr_bean_rel on prospects.id = email_addr_bean_rel.bean_id and email_addr_bean_rel.bean_module = ‘Prospects’ and email_addr_bean_rel.deleted = 0 and email_addr_bean_rel.primary_address = 1 LEFT JOIN email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id LEFT JOIN prospects_cstm ON prospects.id = prospects_cstm.id_c WHERE ((prospect_list_name like ‘15 ŞUBAT 2018 KONFERANS KATILIMCI LİSTESİ%’)) AND prospects.deleted=0 : MySQL error 1054: Unknown column ‘prospect_list_name’ in ‘where clause’
   Mon Dec 7 17:52:06 2020 [30995][e433d2f0-c23e-3ad2-bd4d-5ee727929421][FATAL] Exception handling in /var/www/sucrm/crm/include/MVC/Controller/SugarController.php:400
   Mon Dec 7 17:52:06 2020 [30995][e433d2f0-c23e-3ad2-bd4d-5ee727929421][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.
   Mon Dec 7 17:52:06 2020 [30995][e433d2f0-c23e-3ad2-bd4d-5ee727929421][FATAL] backtrace:
   

   0 /var/www/sucrm/crm/include/database/DBManager.php(353): sugar_die(‘Database failur…’)

1 /var/www/sucrm/crm/include/database/DBManager.php(328): DBManager->registerError(‘Error exporting…’, ‘Error exporting…’, true)

2 /var/www/sucrm/crm/include/database/MysqliManager.php(179): DBManager->checkError(‘Error exporting…’, true)

3 /var/www/sucrm/crm/include/export_utils.php(194): MysqliManager->query(‘SELECTntttttpro…’, true, ‘Error exporting…’)

4 /var/www/sucrm/crm/export.php(69): export(‘Prospects’)

5 /var/www/sucrm/crm/include/MVC/Controller/SugarController.php(1020): require_once(’/var/www/sucrm/…’)

6 /var/www/sucrm/crm/include/MVC/Controller/SugarController.php(468): SugarController->handleEntryPoint()

7 /var/www/sucrm/crm/include/MVC/Controller/SugarController.php(373): SugarController->process()

8 /var/www/sucrm/crm/include/MVC/SugarApplication.php(113): SugarController->execute()

9 /var/www/sucrm/crm/index.php(52): SugarApplication->execute()

10 {main}`

Context

Your Environment

• SuiteCRM Version used: 7.11.15
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Firefox 83 (64 bit)
• Environment name and version (e.g. MySQL, PHP 7): Php 7
• Operating System and version (e.g Ubuntu 16.04): Ubuntu 16.04

[johnM2401]

Hey,

I've had a look, and it seems the CRM will have slightly different behaviour, depending on whether it is exporting records from one page, or exporting records across multiple pages.

When exporting from one page, it seems to use the record's ID's in the "where" clause.

ie:

 where (contacts.id in ('c231ee34-b8f5-3fcc-2f82-5fc8eb8254f7','60519c74-9cf4-46d1-c69c-5fc8ebd72aec','1f60e8af-7018-34ef-cf67-5fc8eb5c59c5','496c4b81-f400-17c6-ca9a-5f490e06f4bf'))

When searching over multiple pages, it will use the fields as a filter.
In this case, it is trying to do:

WHERE ((prospect_list_name like ‘15 ŞUBAT 2018 KONFERANS KATILIMCI LİSTESİ%’))

It would be worth checking, but I would imagine the "prospect_list_name" column does not exist in the Database table for the customized module?
(ie, Targets/Contacts)

If not, that would explain why you are hitting this DB error.

When running the Quick Repair and Rebuild, were you prompted to Execute any SQL?
This _should_ appear if the Vardefs are fine, and the field is not in the DB, as this updates the Database with the necessary Columns/Values, to accommodate the new field.

If not, it might be worth creating this as a Relate field via Studio, unless there is a reason you need it to be custom code?

If custom code is needed, it might still be worth creating a Relate field via Studio, as you could inspect the auto-created Vardefs to see if anything needs to be added.

[dursuntosun]

Yes i know prospect_list_name doesn't exist in DB. But the Target list filter working well. I can exports first page good. And also if i select all records page by page, it is working good also and exports all records well.
If i make selection from selection menu and export them from bulk action menu, i couldn't export them. I couldn't understand behavior of selection?

When i create relate field, i couldn't see it on DB. Is it normal? I make Quick Repair and Rebuild.

I create relate field for Target module and add this field to Target module, it doesn't work also.

[dursuntosun]

@johnM2401 if you want i can give you access to my crm. You can see the issue.

[johnM2401]

Hey, Thanks for your response!

Hmm,
I've created a Relate field in Studio between Targets<->Target Lists, and it seems to create an item in the Database's prospects_cstm table.

It appears as though the column is named "prospectlist_id_c", rather than what i'd named the field.
(So the Relate field must take the target module, and use this to name the Column in the DB.)

Does this appear in your module's _cstm table?

If not, i'm not too sure..
It might be worth resetting your file/folder permissions and running another Quick Repair & Rebuild.

You also mentioned that "it doesnt work also"
Would you be able to clarify this?

Do you mean that exporting, while filtering on this new Relate field, gives you a Database Failure error?
Or, do you mean something else?

It seems to work for myself
I'm able to export All pages when filtering by this new Relate Field

(As an aside, it may be worth also bringing this to the community forums, as you will reach a wider audience with this, which could very well prove helpful )

[JanSiero]

Easier way to reproduce this issue (without the use of custom fields):

• Create Account "account1"
• Create Account "account2" and set the field "Member of" to "account1"
• Go into studio, add the field "Member of" to the quick filter of module "Accounts"
• Open the listview for Accounts (View Accounts)
• Open the quick filter and set the "Member of" value to "account1"
  -> The filter result should only show "account2"
• Click "Select All"
• Bulk Action -> Export

Error is caused by legacy code in the function "create_export_query". This function is derived from the base function SugarBean::create_export_query . In most cases, the derived functions can be deleted / commented out as it contains obsolete additional functionality that is already covered by the base function.

Derived functions are in the following files and should be checked. Either way, derived functions should call the base function SugarBean::create_export_query or SugarBean::create_new_list_query

include/SugarObjects/templates/company/Company.php
include/SugarObjects/templates/person/Person.php
modules/ProjectTask/ProjectTask.php
modules/ProspectLists/ProspectList.php
modules/Opportunities/Opportunity.php
modules/Notes/Note.php
modules/Project/Project.php
modules/Users/User.php
modules/Accounts/Account.php
modules/Contacts/Contact.php
modules/Tasks/Task.php
modules/Documents/Document.php
modules/EmailMan/EmailMan.php
modules/Bugs/Bug.php
modules/Emails/Email.php
modules/Calls/Call.php
modules/Meetings/Meeting.php
modules/Groups/Group.php
modules/Campaigns/Campaign.php
modules/Employees/Employee.php

[dursuntosun]

I comment out this function in the file
include/SugarObjects/templates/person/Person.php

Function
public function create_export_query($order_by, $where, $relate_link_join = '')

My problem is solved. I think this is a bug and should be important.
Thank you @JanSiero

[johnM2401]

Very much appreciated @JanSiero
I'm able to replicate quite easily with those steps.
I've marked this as a Critical Bug now, as it's a replicable DB Failure.

Also, thanks to @dursuntosun for your patience in this!

[JanSiero]

Hi @johnM2401 ,

I'll try to supply a pull request on short term.

I suggest to change the title of this issue to: "Database failure when filtering on relate field and exporting"