Josh Whitlow Josh Whitlow - 5 months ago 13
SQL Question

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

Answer

My previous answer appeared to work, but didn't actually work.

The real solution had to do with using after_save logic hook, versus using a before_save logic hook.

What I didn't know was that in before_save logic hooks, when you fire a SQL query, it was going back over it and firing an Update Statement again, but this time with blank values where I was trying to Update values.

Changing this to an after_save immediately solved the issue.

Comments