MySQL error when re-saving an Opportunity

💬 General Discussion
1

I have a fresh unaltered install which starting with Sugar 6.5.20 then I uploaded the upgrade module 6.5.X to Suite 7.1.5. through the upgrade wizard and the install is running on…
Linux
Apache: 2.2.22
MySQL: 5.1.66-cll
Php: 5.3.16

I noticed that after creating a new opportunity either from converting a lead or directly from the opportunity module, if I try to edit that opportunity and save it again or just click the edit button and save it without changing anything, I get the following… “Database failure. Please refer to sugarcrm.log for details.”

Here is the error log…

Wed Feb 11 00:32:36 2015 [24580][1][FATAL] Query Failed: UPDATE aos_pdf_templates SET type = ‘AOS_Quotes’ WHERE type = ‘Quotes’: MySQL error 1146: Table ‘veritas9_grow15.aos_pdf_templates’ doesn’t exist
Wed Feb 11 00:32:36 2015 [24580][1][FATAL] Query Failed: UPDATE aos_pdf_templates SET type = ‘AOS_Invoices’ WHERE type = ‘Invoices’: MySQL error 1146: Table ‘veritas9_grow15.aos_pdf_templates’ doesn’t exist
Wed Feb 11 00:32:37 2015 [24580][1][FATAL] Query Failed: ALTER TABLE aor_fields DISABLE KEYS: MySQL error 1146: Table ‘veritas9_grow15.aor_fields’ doesn’t exist
Wed Feb 11 00:32:37 2015 [24580][1][FATAL] Query Failed: ALTER TABLE aor_fields CHANGE function field_function VARCHAR( 100 ): MySQL error 1146: Table ‘veritas9_grow15.aor_fields’ doesn’t exist
Wed Feb 11 00:32:37 2015 [24580][1][FATAL] Query Failed: ALTER TABLE aor_fields ENABLE KEYS: MySQL error 1146: Table ‘veritas9_grow15.aor_fields’ doesn’t exist
Wed Feb 11 00:32:37 2015 [24580][1][FATAL] MySQL error 1146: Table ‘veritas9_grow15.aor_fields’ doesn’t exist
Wed Feb 11 00:36:28 2015 [25160][1][FATAL] Error updating table: opportunities: Query Failed: UPDATE opportunities
SET name=‘tom, tom - credit program’,date_modified=‘2015-02-11 06:36:27’,modified_user_id=‘1’,created_by=‘1’,description=NULL,assigned_user_id=‘1’,opportunity_type=NULL,campaign_id=’’,lead_source=NULL,amount=247,amount_usdollar=247,currency_id=’-99’,date_closed=‘2015-02-13’,next_step=NULL,sales_stage=‘Closed Won’,probability=100,=NULL
WHERE opportunities.id = ‘9c51b892-1604-cf5c-0df8-54daf65f3afb’ AND deleted=0: MySQL error 1064: 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 ‘=NULL
WHERE opportunities.id = ‘9c51b892-1604-cf5c-0df8-54daf65f3afb’ AND’ at line 2
Wed Feb 11 00:41:55 2015 [26210][1][FATAL] Error updating table: opportunities: Query Failed: UPDATE opportunities
SET name=‘tom, tom - credit program’,date_modified=‘2015-02-11 06:41:54’,modified_user_id=‘1’,created_by=‘1’,description=NULL,assigned_user_id=‘1’,opportunity_type=NULL,campaign_id=’’,lead_source=NULL,amount=247,amount_usdollar=247,currency_id=’-99’,date_closed=‘2015-02-13’,next_step=NULL,sales_stage=‘Needs Analysis’,probability=25,=NULL
WHERE opportunities.id = ‘9c51b892-1604-cf5c-0df8-54daf65f3afb’ AND deleted=0: MySQL error 1064: 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 ‘=NULL
WHERE opportunities.id = ‘9c51b892-1604-cf5c-0df8-54daf65f3afb’ AND’ at line 2
Wed Feb 11 00:42:12 2015 [26316][1][FATAL] Error updating table: opportunities: Query Failed: UPDATE opportunities
SET name=‘tom, tom - credit program’,date_modified=‘2015-02-11 06:42:12’,modified_user_id=‘1’,created_by=‘1’,description=NULL,assigned_user_id=‘1’,opportunity_type=‘Existing Business’,campaign_id=’’,lead_source=‘Employee’,amount=247,amount_usdollar=247,currency_id=’-99’,date_closed=‘2015-02-13’,next_step=NULL,sales_stage=‘Perception Analysis’,probability=50,=NULL
WHERE opportunities.id = ‘9c51b892-1604-cf5c-0df8-54daf65f3afb’ AND deleted=0: MySQL error 1064: 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 ‘=NULL
WHERE opportunities.id = ‘9c51b892-1604-cf5c-0df8-54daf65f3afb’ AND’ at line 2
Wed Feb 11 00:43:59 2015 [26448][1][FATAL] Error updating table: opportunities: Query Failed: UPDATE opportunities
SET name=‘tom, tom - credit program’,date_modified=‘2015-02-11 06:43:59’,modified_user_id=‘1’,created_by=‘1’,description=NULL,assigned_user_id=‘1’,opportunity_type=NULL,campaign_id=’’,lead_source=NULL,amount=247,amount_usdollar=247,currency_id=’-99’,date_closed=‘2015-02-13’,next_step=NULL,sales_stage=‘Perception Analysis’,probability=50,=NULL
WHERE opportunities.id = ‘9c51b892-1604-cf5c-0df8-54daf65f3afb’ AND deleted=0: MySQL error 1064: 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 ‘=NULL
WHERE opportunities.id = ‘9c51b892-1604-cf5c-0df8-54daf65f3afb’ AND’ at line 2

2

do a quick repair and rebuild and make sure you execute the changes to the database

3

I did a quick repair and rebuild and this did not change the result when trying to save an edit to the Opportunity module.

You also mentioned to make sure to execute changes in the database. I’m not sure exactly what you mean by this. Can you explain in more detail so I can make sure this is done properly.

Thank you For your assistance.

4

Hi SuiteConvert,

When you run a Quick Repair & Rebuild, and it completes, scroll to the bottom of the page.

There will be queries to Execute.

Thanks,

Will.

5

Hi Will,

Thank you for your quick reply and further instruction.

When I ran the original quick repair and rebuild there were no database configurations to execute. I have provided a link to a video of exactly what is happening with the repair rebuild process and trying to save the opportunity along with the sugar log attached.

Link to video…
http://screencast.com/t/e8gdvnAF

6

Hi SuiteConvert,

Navigate to Admin -> Studio -> Opportunities -> Fields.

Are there any ‘null’ fields?

Thanks,

Will.

7

Yes, there is one null field In the opportunities module Showing in studio.

8

RESET that module from studio and created from fresh.

9

Thank’s Jayadeep.

I went into admin>Studio, Selected the Opportunity module and clicked on the rest button and selected all the optional variables to be reset. The following is the result of resetting the opportunity module…

Removed field jjwg_maps_address_c
Removed field jjwg_maps_geocode_status_c
Removed field jjwg_maps_lat_c
Removed field jjwg_maps_lng_c
Removed layout detailview.php
Cleared extensions for Opportunities
Complete!

After this, I went to a previously created opportunity and was able to edit and save the opportunity.

Thank you again for your assistance.