search

salesagility / SuiteCRM

SuiteCRM - Open source CRM for the world
https://www.suitecrm.com
GNU Affero General Public License v3.0
4.55k stars 2.1k forks source link

Database failure when filtering on relate field and exporting #8989

Open dtosun61 opened 3 years ago

dtosun61 commented 3 years ago

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 image

  3. Select all results image

  4. Then export them via Bulk Action Menu image 6.See result image

  5. 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(‘SELECT\n\t\t\t\t\tpro…’, 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

johnM2401 commented 3 years ago

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.

dtosun61 commented 3 years ago

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. image

dtosun61 commented 3 years ago

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

johnM2401 commented 3 years ago

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.)

image

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 commented 3 years ago

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

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

dtosun61 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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"

stlgaits commented 2 years ago

Hi, any updates regarding this bug & PR ?

holdusback commented 2 years ago

I have this exact same error in the lastest version : 7.12.3

I filter account with a relative field in account. So it's a relative field from account to account : accounts_accounts_2

Here is the log from the where statement of the SQL request :

where ((accounts.account_type in ('Beneficiaire') ) AND ( accounts_accounts_2_name like 'XXX XXXX%')) AND ( accounts.deleted IS NULL OR accounts.deleted=0 ): MySQL error 1054: Unknown column 'accounts_accounts_2_name' in 'where clause'

SinergiaCRM commented 2 years ago

What are the updates on this issue? Are the latest referenced commits becoming a PR?

Thanks

SinergiaCRM commented 2 years ago

We analyzed the issue with detail and we thought it might help here. These are the results:

Action Filter 1:N Filter Related field Filter 1:1
Export OK OK KO
MassUpdate OK OK OK
Assign Security Group KO KO KO
Print PDF KO OK OK
Delete OK OK OK

We would like to emphasize the "Assign Security Group" and the "Print PDF" actions, that weren't mentioned in this issue

samus-aran commented 2 years ago

@jack7anderson7 Just a quick mention :wave:

SinergiaCRM commented 1 year ago

Hi,

We consider this a very important Issue that it's still around.

Although not sure this commit was suppose to solve this issue: https://github.com/jack7anderson7/SuiteCRM/commit/3c34e9cdc54fff30213f2b0c40bac2d838a7d63d

Any updates? @clemente-raposo @johnM2401 @jack7anderson7

Thanks

JanSiero commented 1 year ago

Hi, the mentioned commit does not solve the issue. To resolve this, the method create_new_list_query needs a serious rewriting and afterwards extensive testing, see my comment:

https://github.com/salesagility/SuiteCRM/pull/9006#issuecomment-793021347