MySQL query works in NaviCat but not in SugarCRM

If any additional information is needed that would help you solve the problem, just let me know.

I'm running SugarCRM 6.5.20 CE

I have a logic hook that fires for a custom module and when I go to check the log, the query shows an execution time and appears to run just fine, but the database doesn't actually get updated.

I took this same query right out of the sugarcrm.log file and then ran the query in Navicat, and... it updated fine with no problems.

I have already tried

  • Some / None / All Backticks around column / table names
  • Making sure I wasn't using any reserved words

So at this point I'm wanting to chalk it up to a MySQL version issue possibly. I'm running MySQL version 5.5.49-cll.

UPDATE `my_database`.`p_policies_cstm` 
LEFT OUTER JOIN r_raises_p_policies_1_c ON p_policies_cstm
.id_c = r_raises_p_policies_1_c.r_raises_p_policies_1p_policies_idb
LEFT OUTER JOIN r_raises_cstm ON r_raises_cstm
.id_c = r_raises_p_policies_1_c.r_raises_p_policies_1r_raises_ida
SET factor_c
= '1.00', client_ppp_c = '1,529,987.76'
WHERE r_raises_p_policies_1_c
.r_raises_p_policies_1p_policies_idb = 'e1570120-56e0-5d75-8ab7-574f2ef83a5b'

Can someone possibly help think of why it would throw no error but not actually update the database?

Here is also the log file where it fired:

Wed Jun  1 20:30:06 2016 [26589][1][INFO] Query:UPDATE my_database.p_policies_cstm 
LEFT OUTER JOIN r_raises_p_policies_1_c ON p_policies_cstm
.id_c = r_raises_p_policies_1_c.r_raises_p_policies_1p_policies_idb
LEFT OUTER JOIN r_raises_cstm ON r_raises_cstm
.id_c = r_raises_p_policies_1_c.r_raises_p_policies_1r_raises_ida
SET factor_c
= '1.00', client_ppp_c = '1,529,987.76'
WHERE r_raises_p_policies_1_c
.r_raises_p_policies_1p_policies_idb = 'e1570120-56e0-5d75-8ab7-574f2ef83a5b'
Wed Jun  1 20:30:06 2016 [26589][1][INFO] Query Execution Time:0.000363111495972
Wed Jun  1 20:30:06 2016 [26589][1][INFO] Get One: |SELECT id_c FROM p_policies_cstm WHERE id_c = 'e1570120-56e0-5d75-8ab7-574f2ef83a5b'|
Wed Jun  1 20:30:06 2016 [26589][1][DEBUG] Limit Query:SELECT id_c FROM p_policies_cstm WHERE id_c = 'e1570120-56e0-5d75-8ab7-574f2ef83a5b' Start: 0 count: 1
Wed Jun  1 20:30:06 2016 [26589][1][INFO] Query:SELECT id_c FROM p_policies_cstm WHERE id_c = 'e1570120-56e0-5d75-8ab7-574f2ef83a5b' LIMIT 0,1
Wed Jun  1 20:30:06 2016 [26589][1][INFO] Query Execution Time:0.000181913375854

Then lastly here is the function within the logic hook:

function setNewValues($bean, $factor, $purchasePrice){

  $GLOBALS['db']->query("UPDATE `aaigloba_crm`.`p_policies_cstm`

  LEFT OUTER JOIN `r_raises_p_policies_1_c` ON `p_policies_cstm`.`id_c` = `r_raises_p_policies_1_c`.`r_raises_p_policies_1p_policies_idb`

  LEFT OUTER JOIN `r_raises_cstm` ON `r_raises_cstm`.`id_c` = `r_raises_p_policies_1_c`.`r_raises_p_policies_1r_raises_ida`

  SET `factor_c` = '" . $factor . "', `client_ppp_c` = '" . $purchasePrice . "'

  WHERE `r_raises_p_policies_1_c`.`r_raises_p_policies_1p_policies_idb` = '" . $bean->id . "'");

  return true;

  } //end setNewValues function

Parents Reply Children