AnsweredAssumed Answered

SugarBean retrieve_by_string_fields broken for custom module

Question asked by Jon Sheppard on Jul 24, 2019
Latest reply on Jul 25, 2019 by Jon Sheppard

Hello everyone, 

 

We've got a custom module installed via Module Loader that is having a weird bug on a few client's instance. We call Sugar's built-in retrieve_by_string_fields() function similarly to the way we've done it in the past, but are getting a mySQL error as follows:

 

SugarCRM Version: 9.0.1
SugarCRM Edition: ENT

Full error: Array
(
[status] => error
[message] => An exception occurred while executing
              'SELECT FROM module_table_name WHERE () LIMIT 1':

You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near 'FROM sugarchimp_mc_list WHERE () LIMIT 1' at line 1
);

On the query, you will notice that the SELECT portion of the query is blank with two spaces. Sugar is just not filling it with any database fields.

 

I've outlined the process below to give you more context

 

Our scheduler job runs these lines: 

$foo = 'bar';
$bean = BeanFactory::newBean('CUSTOM_MODULE_NAME');
$bean->retrieve_by_string_fields(array('module_field' => $foo));

That calls Sugar's built-in retrieve_by_string_fields() function at:
data/SugarBean.php  -  line 6358

function retrieve_by_string_fields($fields_array, $encode=true, $deleted=true)
    {
        $query = new \SugarQuery();
        $query->from($this, ['add_deleted' => $deleted, 'team_security' => false]);
        $query->select('*');
        foreach ($fields_array as $field => $value) {
            $query->where()->equals($field, $value);
        }
        $query->limit(1);
        $results = $query->execute();
....        


 Digging into the SugarQuery's select function, you'll see that when it receives a '*', it parses through all of the module's vardef fields in the database and adds them. Those are vardef fields where source == 'db'  or  !isset($def['source']  or source  is a non-related custom field. That is here:  
include/SugarQuery/Builder/Field/Select.php  -  line 48

if ($this->field == '*') {
            // remove *
            $bean = $this->query->getFromBean();
            if (empty($this->moduleName)) {
                $this->moduleName = $bean->module_name;
            }
            foreach ($bean->field_defs AS $field => $def) {
                if (!isset($def['source'])
                    || $def['source'] == 'db'
                    || ($def['source'] == 'custom_fields' && !in_array($def['type'], $bean::$relateFieldTypes))
                ) {
                    $this->addToSelect("{$this->table}.{$field}");
                }
            }
            $this->markNonDb();
            return;
        }

It looks like there is an issue with the vardefs on our module that is causing Sugar not to see any fields matching that criteria. We have looked at the customer's mySQL schema as well as their vardefs from the Diagnostic Tools and everything looks to be in order. We do not add the source field for most of our vardefs, so they should be found there. 

 

To complicate things further, this package is working on Sugar 8 and Sugar 9 locally with no issues. 

 

Has anyone come across an issue like this? Any thoughts or things to try?  

 

EDIT: If any SugarCRM support personnel are reading this, Sugar support seems to have fixed this on one of the client instances (Case#: 345831). Can you please provide details about how this error was fixed so we can assist any other clients who might come across the issue? 

 

Best Regards,

Jon

Outcomes