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