MySQL查询在NaviCat中有效，但在SugarCRM中无效

Question

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 1

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.