Cannot update Contact. Error with SQL

Hi,

After going to SuiteCRM 7.1.0 I notice I cannot update my contacts. Each time I update a contact I get

Looking at sugarcrm.log I see


 Error updating table: contacts: Query Failed: UPDATE contacts
                                        SET date_modified=....
...,[b]campaign_id='',=NULL[/b],joomla_account_id=NULL...
 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,joomla_account_id=NULL,portal_account_disabled=0,portal_user_type='Single'' at line 2

The problem is a syntax error as highlighted in bold. How to fix? I have upgraded to SuiteCRM 7.1.2 and the issue has been the same.

Running on Ubuntu 12.04.4 LTS and MySQL 5.5.x

Hi Kenmanwong,

This looks like there is a field missing from the database, if you go to studio for contacts and view the field list chances are there is a line which just has null in it.

if this is the case you need to go to custom/Extension/modules/Contacts/Ext/Vardefs in this folder you will see all files relating to the fields in the list in studio one of these files is causing the issue you are seeing. you need to locate the file which does not relate to a field in the list and remove the file.

repair and rebuild and the null should be gone from studio which will fix the db error on save on contacts.

1 Like

Looking at studio, I indeed see a NULL in one of the Fields for Contacts. (see attached screen shot)

Looking at custom/Extension/modules/Contacts/Ext/Vardefs directory I notice t files that are just 44 KB in size

sugarfield_jjwg_maps_address_c.php
sugarfield_jjwg_maps_geocode_status_c.php
sugarfield_jjwg_maps_lat_c.php
sugarfield_jjwg_maps_lng_c.php

All four are the same content


<?php
 // created: 2014-01-20 12:22:30

 ?>

Are these four the culprit?

are these the only files in that folder ?

this shows that something is not syncing up with the fields and there is a file in that folder which is not in the list. this file needs to be removed what every one it is.

There are other files

 1606 Apr  2 16:41 aos_contactsVardefs.php
 2537 Apr  2 16:41 contacts_aop_case_updates.php
  286 Apr  2 16:41 fp_events_contacts_Contacts.php
  292 Apr  2 16:41 securitygroups_contactsVardefs.php
 2527 Apr  2 16:41 sugarfield_events_contact.php
  135 Apr  2 16:41 sugarfield_facebook_username_c.php
   44 Apr  2 16:41 sugarfield_jjwg_maps_address_c.php
   44 Apr  2 16:41 sugarfield_jjwg_maps_geocode_status_c.php
   44 Apr  2 16:41 sugarfield_jjwg_maps_lat_c.php
   44 Apr  2 16:41 sugarfield_jjwg_maps_lng_c.php

The other files look populated with proper fields.

they will do,

One of these fields is not in the mysql database which is cauing the issue. you need to check each of the file names and remove the sugarfield_ prefix and look for the field facebook_username_c and you need to do this for each one till you find one which does not exist in the field list in studio. Once you find one that is not in the field list remove it from the Vardefs folder. this will fix this issue.

Ian.

Thanks Ian.

Found file sugarfile_facebook_username_c.php containing

<?php
 // created: 2014-01-21 10:49:24
$dictionary['Contact']['fields']['facebook_username_c']['labelValue']='Facebook
Username';

 ?>

I remove all the sugarfield_* files from the Vardefs folder, went to Quick Repair again and now the NULL is no longer in the Contacts Field table. Now able to update my contacts like normal.

Thanks Ian for the assist