Elasticsearch failing to index cases

cyb456 commented 1 year ago

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

cyb456 commented 1 year ago

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 8 months ago

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.

chris001 commented 8 months ago

@JanSiero Any suggestions?

pgorod commented 8 months ago

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.

cyb456 commented 8 months ago

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 8 months ago

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

chris001 commented 8 months ago

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

cyb456 commented 8 months ago

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

chris001 commented 8 months ago

Done! Issue #10350

salesagility / SuiteCRM