starred/SuiteCRM-SuiteCRM

Fork 0

mirror of https://github.com/SuiteCRM/SuiteCRM.git synced 2026-03-02 19:16:58 -05:00

Elasticsearch failing to index cases #4983

New issue

Open

opened 2026-02-20 16:27:19 -05:00 by deekerman · 9 comments

deekerman commented

2026-02-20 16:27:19 -05:00

Owner

Originally created by @cyb456 on GitHub (Mar 17, 2023).

Issue

I have a relate field to a custom module made in studio. This is stored in cases_cstm.mymodule_id_c

I get this error when trying to do a full index. After examing the query in suitecrm.log the join table is missing the cases_cstm join. After manually changing the query in phpmyadmin to join cases_cstm the query does not error.

Mon Jan 29 11:42:01 2024 [87555][-none-][FATAL] Mysqli_query failed.
Mon Jan 29 11:42:01 2024 [87555][-none-][FATAL] Error retrieving Case list:  Query Failed:  SELECT 
  cases.*, 
  jt0.user_name modified_by_name, 
  jt0.created_by modified_by_name_owner, 
  'Users' modified_by_name_mod, 
  jt1.user_name created_by_name, 
  jt1.created_by created_by_name_owner, 
  'Users' created_by_name_mod, 
  jt2.user_name assigned_user_name, 
  jt2.created_by assigned_user_name_owner, 
  'Users' assigned_user_name_mod, 
  accounts.name account_name, 
  accounts.assigned_user_id account_name_owner, 
  'Accounts' account_name_mod, 
  jt4.name primary_unit_c, 
  LTRIM(
    RTRIM(
      CONCAT(
        IFNULL(jt5.first_name, ''), 
        ' ', 
        IFNULL(jt5.last_name, '')
      )
    )
  ) primary_contact_c 
FROM 
  cases 
  LEFT JOIN users jt0 ON cases.modified_user_id = jt0.id 
  AND jt0.deleted = 0 
  AND jt0.deleted = 0 
  LEFT JOIN users jt1 ON cases.created_by = jt1.id 
  AND jt1.deleted = 0 
  AND jt1.deleted = 0 
  LEFT JOIN users jt2 ON cases.assigned_user_id = jt2.id 
  AND jt2.deleted = 0 
  AND jt2.deleted = 0 
  LEFT JOIN accounts accounts ON cases.account_id = accounts.id 
  AND accounts.deleted = 0 
  AND accounts.deleted = 0 
  LEFT JOIN cyb_units jt4 ON cases_cstm.cyb_units_id_c = jt4.id 
  AND jt4.deleted = 0 
  LEFT JOIN contacts jt5 ON cases_cstm.contact_id_c = jt5.id 
  AND jt5.deleted = 0 
where 
  cases.deleted = 0 
ORDER BY 
  cases.id 
LIMIT 
  1000, 1000: MySQL error 1054: Unknown column 'cases_cstm.cyb_units_id_c' in 'on clause'
Mon Jan 29 11:42:01 2024 [87555][-none-][ERROR] [ElasticSearchIndexer] Failed to index module Cases! Exception details follow
Mon Jan 29 11:42:01 2024 [87555][-none-][ERROR] [ElasticSearchIndexer] Exception: Database failure. Please refer to suitecrm.log for details. in /var/www/website/include/utils.php:1990
Stack trace:
#0 /var/www/website/include/database/DBManager.php(354): sugar_die()
#1 /var/www/website/include/database/DBManager.php(329): DBManager->registerError()
#2 /var/www/website/include/database/MysqliManager.php(185): DBManager->checkError()
#3 /var/www/website/include/database/MysqlManager.php(285): MysqliManager->query()
#4 /var/www/website/data/SugarBean.php(4287): MysqlManager->limitQuery()
#5 /var/www/website/data/SugarBean.php(3532): SugarBean->process_list_query()
#6 /var/www/website/lib/Search/ElasticSearch/ElasticSearchModuleDataPuller.php(168): SugarBean->get_list()
#7 /var/www/website/lib/Search/ElasticSearch/ElasticSearchIndexer.php(213): SuiteCRM\Search\ElasticSearch\ElasticSearchModuleDataPuller->pullNextBatch()
#8 /var/www/website/lib/Search/ElasticSearch/ElasticSearchIndexer.php(147): SuiteCRM\Search\ElasticSearch\ElasticSearchIndexer->indexModule()
#9 /var/www/website/lib/Search/ElasticSearch/ElasticSearchIndexer.php(589): SuiteCRM\Search\ElasticSearch\ElasticSearchIndexer->index()
#10 /var/www/website/lib/Robo/Plugin/Commands/ElasticSearchCommands.php(121): SuiteCRM\Search\ElasticSearch\ElasticSearchIndexer::repairElasticsearchIndex()
#11 [internal function]: SuiteCRM\Robo\Plugin\Commands\ElasticSearchCommands->elasticIndex()
#12 /var/www/website/vendor/consolidation/annotated-command/src/CommandProcessor.php(257): call_user_func_array()
#13 /var/www/website/vendor/consolidation/annotated-command/src/CommandProcessor.php(212): Consolidation\AnnotatedCommand\CommandProcessor->runCommandCallback()
#14 /var/www/website/vendor/consolidation/annotated-command/src/CommandProcessor.php(176): Consolidation\AnnotatedCommand\CommandProcessor->validateRunAndAlter()
#15 /var/www/website/vendor/consolidation/annotated-command/src/AnnotatedCommand.php(390): Consolidation\AnnotatedCommand\CommandProcessor->process()
#16 /var/www/website/vendor/symfony/console/Command/Command.php(298): Consolidation\AnnotatedCommand\AnnotatedCommand->execute()
#17 /var/www/website/vendor/symfony/console/Application.php(1058): Symfony\Component\Console\Command\Command->run()
#18 /var/www/website/vendor/symfony/console/Application.php(301): Symfony\Component\Console\Application->doRunCommand()
#19 /var/www/website/vendor/symfony/console/Application.php(171): Symfony\Component\Console\Application->doRun()
#20 /var/www/website/vendor/consolidation/robo/src/Runner.php(282): Symfony\Component\Console\Application->run()
#21 /var/www/website/vendor/consolidation/robo/src/Runner.php(158): Robo\Runner->run()
#22 /var/www/website/vendor/consolidation/robo/robo(48): Robo\Runner->execute()
#23 /var/www/website/vendor/bin/robo(115): include('/var/www/ts-sui...')
#24 {main}

Expected Behavior

no error

Actual Behavior

error

Possible Fix

detailed above

Steps to Reproduce

add a one way relate field to cases
try to do an elasticsearch robo index or full index

Context

Your Environment

SuiteCRM Version used: 7.14.2
Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): chromium
Environment name and version (e.g. MySQL, PHP 7): php 7.4 mariadb
Operating System and version (e.g Ubuntu 16.04): ubuntu 20.04

Originally created by @cyb456 on GitHub (Mar 17, 2023).     #### Issue   I have a relate field to a custom module made in studio. This is stored in cases_cstm.mymodule_id_c I get this error when trying to do a full index. After examing the query in suitecrm.log the join table is missing the cases_cstm join. After manually changing the query in phpmyadmin to join cases_cstm the query does not error. ``` Mon Jan 29 11:42:01 2024 [87555][-none-][FATAL] Mysqli_query failed. Mon Jan 29 11:42:01 2024 [87555][-none-][FATAL] Error retrieving Case list: Query Failed: SELECT cases.*, jt0.user_name modified_by_name, jt0.created_by modified_by_name_owner, 'Users' modified_by_name_mod, jt1.user_name created_by_name, jt1.created_by created_by_name_owner, 'Users' created_by_name_mod, jt2.user_name assigned_user_name, jt2.created_by assigned_user_name_owner, 'Users' assigned_user_name_mod, accounts.name account_name, accounts.assigned_user_id account_name_owner, 'Accounts' account_name_mod, jt4.name primary_unit_c, LTRIM( RTRIM( CONCAT( IFNULL(jt5.first_name, ''), ' ', IFNULL(jt5.last_name, '') ) ) ) primary_contact_c FROM cases LEFT JOIN users jt0 ON cases.modified_user_id = jt0.id AND jt0.deleted = 0 AND jt0.deleted = 0 LEFT JOIN users jt1 ON cases.created_by = jt1.id AND jt1.deleted = 0 AND jt1.deleted = 0 LEFT JOIN users jt2 ON cases.assigned_user_id = jt2.id AND jt2.deleted = 0 AND jt2.deleted = 0 LEFT JOIN accounts accounts ON cases.account_id = accounts.id AND accounts.deleted = 0 AND accounts.deleted = 0 LEFT JOIN cyb_units jt4 ON cases_cstm.cyb_units_id_c = jt4.id AND jt4.deleted = 0 LEFT JOIN contacts jt5 ON cases_cstm.contact_id_c = jt5.id AND jt5.deleted = 0 where cases.deleted = 0 ORDER BY cases.id LIMIT 1000, 1000: MySQL error 1054: Unknown column 'cases_cstm.cyb_units_id_c' in 'on clause' Mon Jan 29 11:42:01 2024 [87555][-none-][ERROR] [ElasticSearchIndexer] Failed to index module Cases! Exception details follow Mon Jan 29 11:42:01 2024 [87555][-none-][ERROR] [ElasticSearchIndexer] Exception: Database failure. Please refer to suitecrm.log for details. in /var/www/website/include/utils.php:1990 Stack trace: #0 /var/www/website/include/database/DBManager.php(354): sugar_die() #1 /var/www/website/include/database/DBManager.php(329): DBManager->registerError() #2 /var/www/website/include/database/MysqliManager.php(185): DBManager->checkError() #3 /var/www/website/include/database/MysqlManager.php(285): MysqliManager->query() #4 /var/www/website/data/SugarBean.php(4287): MysqlManager->limitQuery() #5 /var/www/website/data/SugarBean.php(3532): SugarBean->process_list_query() #6 /var/www/website/lib/Search/ElasticSearch/ElasticSearchModuleDataPuller.php(168): SugarBean->get_list() #7 /var/www/website/lib/Search/ElasticSearch/ElasticSearchIndexer.php(213): SuiteCRM\Search\ElasticSearch\ElasticSearchModuleDataPuller->pullNextBatch() #8 /var/www/website/lib/Search/ElasticSearch/ElasticSearchIndexer.php(147): SuiteCRM\Search\ElasticSearch\ElasticSearchIndexer->indexModule() #9 /var/www/website/lib/Search/ElasticSearch/ElasticSearchIndexer.php(589): SuiteCRM\Search\ElasticSearch\ElasticSearchIndexer->index() #10 /var/www/website/lib/Robo/Plugin/Commands/ElasticSearchCommands.php(121): SuiteCRM\Search\ElasticSearch\ElasticSearchIndexer::repairElasticsearchIndex() #11 [internal function]: SuiteCRM\Robo\Plugin\Commands\ElasticSearchCommands->elasticIndex() #12 /var/www/website/vendor/consolidation/annotated-command/src/CommandProcessor.php(257): call_user_func_array() #13 /var/www/website/vendor/consolidation/annotated-command/src/CommandProcessor.php(212): Consolidation\AnnotatedCommand\CommandProcessor->runCommandCallback() #14 /var/www/website/vendor/consolidation/annotated-command/src/CommandProcessor.php(176): Consolidation\AnnotatedCommand\CommandProcessor->validateRunAndAlter() #15 /var/www/website/vendor/consolidation/annotated-command/src/AnnotatedCommand.php(390): Consolidation\AnnotatedCommand\CommandProcessor->process() #16 /var/www/website/vendor/symfony/console/Command/Command.php(298): Consolidation\AnnotatedCommand\AnnotatedCommand->execute() #17 /var/www/website/vendor/symfony/console/Application.php(1058): Symfony\Component\Console\Command\Command->run() #18 /var/www/website/vendor/symfony/console/Application.php(301): Symfony\Component\Console\Application->doRunCommand() #19 /var/www/website/vendor/symfony/console/Application.php(171): Symfony\Component\Console\Application->doRun() #20 /var/www/website/vendor/consolidation/robo/src/Runner.php(282): Symfony\Component\Console\Application->run() #21 /var/www/website/vendor/consolidation/robo/src/Runner.php(158): Robo\Runner->run() #22 /var/www/website/vendor/consolidation/robo/robo(48): Robo\Runner->execute() #23 /var/www/website/vendor/bin/robo(115): include('/var/www/ts-sui...') #24 {main} ``` #### Expected Behavior  no error #### Actual Behavior   error #### Possible Fix  detailed above #### Steps to Reproduce   1. add a one way relate field to cases 2. try to do an elasticsearch robo index or full index 3. 4. #### Context   #### Your Environment  * SuiteCRM Version used: 7.14.2 * Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): chromium * Environment name and version (e.g. MySQL, PHP 7): php 7.4 mariadb * Operating System and version (e.g Ubuntu 16.04): ubuntu 20.04

deekerman added the

labels

2026-02-20 16:27:19 -05:00

deekerman commented

2026-02-20 16:27:21 -05:00

Author

Owner

@cyb456 commented on GitHub (Mar 17, 2023):

I was able to hacky fix it by adding
if($this->object_name == 'Case'){ return true; }
to SugarBean.php in hasCustomFields()

For some reason hasCustomFields() is blank/false for Cases when running the elasticsearch index

@cyb456 commented on GitHub (Mar 17, 2023): I was able to hacky fix it by adding if($this->object_name == 'Case'){ return true; } to SugarBean.php in hasCustomFields() For some reason hasCustomFields() is blank/false for Cases when running the elasticsearch index

deekerman commented

2026-02-20 16:27:21 -05:00

Author

Owner

@cyb456 commented on GitHub (Jan 29, 2024):

I am again asking if someone could look into this, still occuring after pulling in the changes from https://github.com/salesagility/SuiteCRM/pull/10275

the root issue is $GLOBALS['dictionary']['Case']['custom_fields'] seems to always be false when run from robo or cron(command line)

So if I have custom field in my cases module, it breaks the indexing.

@cyb456 commented on GitHub (Jan 29, 2024): I am again asking if someone could look into this, still occuring after pulling in the changes from https://github.com/salesagility/SuiteCRM/pull/10275 the root issue is $GLOBALS['dictionary']['Case']['custom_fields'] seems to always be false when run from robo or cron(command line) So if I have custom field in my cases module, it breaks the indexing.

deekerman commented

2026-02-20 16:27:21 -05:00

Author

Owner

@chris001 commented on GitHub (Jan 29, 2024):

@JanSiero Any suggestions?

@chris001 commented on GitHub (Jan 29, 2024): @JanSiero Any suggestions?

deekerman commented

2026-02-20 16:27:22 -05:00

Author

Owner

@pgorod commented on GitHub (Jan 30, 2024):

Can anyone get a stack trace at that point (function hasCustomFields), when running from the Scheduler or cron (the cases where it fails)? Knowing where it is being called from would likely provide clues about why it's failing.

Maybe it's as simple as ensuring there is an instantiation of the global variables before accessing them.

@pgorod commented on GitHub (Jan 30, 2024): Can anyone get a stack trace at that point (function hasCustomFields), when running from the Scheduler or cron (the cases where it fails)? Knowing where it is being called from would likely provide clues about why it's failing. Maybe it's as simple as ensuring there is an instantiation of the global variables before accessing them.

deekerman commented

2026-02-20 16:27:22 -05:00

Author

Owner

@cyb456 commented on GitHub (Jan 30, 2024):

Can anyone get a stack trace at that point (function hasCustomFields), when running from the Scheduler or cron (the cases where it fails)? Knowing where it is being called from would likely provide clues about why it's failing.

Maybe it's as simple as ensuring there is an instantiation of the global variables before accessing them.

There are multiple places within/called from the SugarBean->get_list() method where it checks that to make the query, what I essentially did to patch it for myself was replace

if (isset($this->custom_fields))
with
if (isset($this->custom_fields) || $this->object_name == "Case")
(along with inside the hasCustomFields() method from my first reply)

wherever I saw it, obviously this is not a real solution

@cyb456 commented on GitHub (Jan 30, 2024): > Can anyone get a stack trace at that point (function hasCustomFields), when running from the Scheduler or cron (the cases where it fails)? Knowing where it is being called from would likely provide clues about why it's failing. > > Maybe it's as simple as ensuring there is an instantiation of the global variables before accessing them. There are multiple places within/called from the SugarBean->get_list() method where it checks that to make the query, what I essentially did to patch it for myself was replace ```if (isset($this->custom_fields))``` with ```if (isset($this->custom_fields) || $this->object_name == "Case")``` (along with inside the hasCustomFields() method from my first reply) wherever I saw it, obviously this is not a real solution

deekerman commented

2026-02-20 16:27:23 -05:00

Author

Owner

@cyb456 commented on GitHub (Jan 30, 2024):

one other thing I noticed is LIMIT 1000, 1000, does that mean the first 0-1000 worked?

@cyb456 commented on GitHub (Jan 30, 2024): one other thing I noticed is LIMIT 1000, 1000, does that mean the first 0-1000 worked?

deekerman commented

2026-02-20 16:27:23 -05:00

Author

Owner

@chris001 commented on GitHub (Jan 30, 2024):

LIMIT is not compatible with Microsoft SQL Server or standard SQL, so it should be changed to be compatible with the standard SQL.
For example: github.com/salesagility/SuiteCRM@b39d9610f7/include/database/MssqlManager.php (L433)

@chris001 commented on GitHub (Jan 30, 2024): `LIMIT` is not compatible with Microsoft SQL Server or standard SQL, so it should be changed to be compatible with the standard SQL. For example: https://github.com/salesagility/SuiteCRM/blob/b39d9610f705807ce4b595c04031b001c2f0c819/include/database/MssqlManager.php#L433

deekerman commented

2026-02-20 16:27:23 -05:00

Author

Owner

@cyb456 commented on GitHub (Jan 31, 2024):

LIMIT is not compatible with Microsoft SQL Server or standard SQL, so it should be changed to be compatible with the standard SQL. For example:

github.com/salesagility/SuiteCRM@b39d9610f7/include/database/MssqlManager.php (L433)

That should probably be in a new issue then

@cyb456 commented on GitHub (Jan 31, 2024): > `LIMIT` is not compatible with Microsoft SQL Server or standard SQL, so it should be changed to be compatible with the standard SQL. For example: > > https://github.com/salesagility/SuiteCRM/blob/b39d9610f705807ce4b595c04031b001c2f0c819/include/database/MssqlManager.php#L433 That should probably be in a new issue then

deekerman commented

2026-02-20 16:27:23 -05:00

Author

Owner

@chris001 commented on GitHub (Jan 31, 2024):

Done! Issue #10350

@chris001 commented on GitHub (Jan 31, 2024): Done! Issue #10350