Author Topic: Profile data does not save after upgrade to 2.2 (Read 1429 times)

menkh · July 09, 2009, 06:42:34 pm

Hello-

I am in the process of building a member-driven site where the primary purpose is to promote and register attendees for an annual retreat. I had finished the site originally using Drupal 6.x (currently 6.13) and CiviCRM 2.1.8. After tiring of the security upgrade alerts, I decided to upgrade to 2.2 (which in the end I had to install fresh and reconstitute the data in phpMyAdmin). I am using separate profiles on the Drupal account registration page to collect name, address, and phone information to CiviCRM (see sample photo).

Under 2.1, everything worked as it should, but now under 2.2, none of the contact data from the registration form saves (photo below). After searching around on this board, I tried the following:

1. Set smart groups cache to 5 min.
2. Deleted sites/default/files/civicrm (the old template files), which the new installation rebuilt
3. Reset menu cache.
4. Made sure no mixed field types in profiles.

Still no luck. However, when I re-entered the missing contact data in the newly created test user, it threw an array error:

Code: [Select]

Sorry. A non-recoverable error has occurred.

DB Error: syntax error

Database Error Code: 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 ') LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.na' at line 6, 1064


Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    {code] => -2
    {message] => DB Error: syntax error
    {mode] => 16
    {debug_info] => SELECT DISTINCT(contact_a.id) as contact_id, individual_prefix.value as individual_prefix_id, individual_prefix.label as individual_prefix, individual_suffix.value as individual_suffix_id, individual_suffix.label as individual_suffix, contact_a.custom_greeting as custom_greeting, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, contact_a.nick_name  as `nick_name`, contact_a.preferred_communication_method  as `preferred_communication_method`, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.street_address as `1-street_address`, `1-address`.supplemental_address_1 as `1-supplemental_address_1`, `1-address`.supplemental_address_2 as `1-supplemental_address_2`, `1-address`.city as `1-city`, `1-state_province`.id as `1-state_province_id`, `1-state_province`.abbreviation as `1-state_province`, `1-address`.postal_code as `1-postal_code`, `1-country`.id as `1-country_id`, `1-country`.name as `1-country`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`  FROM civicrm_contact contact_a 
LEFT JOIN civicrm_address `1-address` ON (`1-address`.contact_id = contact_a.id AND `1-address`.is_primary = 1)  
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  
LEFT JOIN civicrm_country `1-country` ON `1-country`.id = `1-address`.country_id  
LEFT JOIN civicrm_location_type `1-location_type` ON (  ( `1-address`.location_type_id = `1-location_type`.id )  )  
LEFT JOIN civicrm_location_type `Home-location_type` ON (  )  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix') LEFT JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id )  LEFT JOIN civicrm_option_group option_group_suffix ON (option_group_suffix.name = 'individual_suffix') LEFT JOIN civicrm_option_value individual_suffix ON (contact_a.suffix_id = individual_suffix.value AND option_group_suffix.id = individual_suffix.option_group_id )  WHERE  ( contact_a.id = 155 )  AND  ( 1 )   GROUP BY contact_a.id   [nativecode=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 ')  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.na' at line 6]
    [type] => DB_Error
    [user_info] => SELECT DISTINCT(contact_a.id) as contact_id, individual_prefix.value as individual_prefix_id, individual_prefix.label as individual_prefix, individual_suffix.value as individual_suffix_id, individual_suffix.label as individual_suffix, contact_a.custom_greeting as custom_greeting, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, contact_a.nick_name  as `nick_name`, contact_a.preferred_communication_method  as `preferred_communication_method`, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.street_address as `1-street_address`, `1-address`.supplemental_address_1 as `1-supplemental_address_1`, `1-address`.supplemental_address_2 as `1-supplemental_address_2`, `1-address`.city as `1-city`, `1-state_province`.id as `1-state_province_id`, `1-state_province`.abbreviation as `1-state_province`, `1-address`.postal_code as `1-postal_code`, `1-country`.id as `1-country_id`, `1-country`.name as `1-country`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`  FROM civicrm_contact contact_a 
LEFT JOIN civicrm_address `1-address` ON (`1-address`.contact_id = contact_a.id AND `1-address`.is_primary = 1)  
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  
LEFT JOIN civicrm_country `1-country` ON `1-country`.id = `1-address`.country_id  
LEFT JOIN civicrm_location_type `1-location_type` ON (  ( `1-address`.location_type_id = `1-location_type`.id )  )  
LEFT JOIN civicrm_location_type `Home-location_type` ON (  )  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix') LEFT JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id )  LEFT JOIN civicrm_option_group option_group_suffix ON (option_group_suffix.name = 'individual_suffix') LEFT JOIN civicrm_option_value individual_suffix ON (contact_a.suffix_id = individual_suffix.value AND option_group_suffix.id = individual_suffix.option_group_id )  WHERE  ( contact_a.id = 155 )  AND  ( 1 )   GROUP BY contact_a.id   [nativecode=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 ')  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.na' at line 6]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT(contact_a.id) as contact_id, individual_prefix.value as individual_prefix_id, individual_prefix.label as individual_prefix, individual_suffix.value as individual_suffix_id, individual_suffix.label as individual_suffix, contact_a.custom_greeting as custom_greeting, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, contact_a.nick_name  as `nick_name`, contact_a.preferred_communication_method  as `preferred_communication_method`, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.street_address as `1-street_address`, `1-address`.supplemental_address_1 as `1-supplemental_address_1`, `1-address`.supplemental_address_2 as `1-supplemental_address_2`, `1-address`.city as `1-city`, `1-state_province`.id as `1-state_province_id`, `1-state_province`.abbreviation as `1-state_province`, `1-address`.postal_code as `1-postal_code`, `1-country`.id as `1-country_id`, `1-country`.name as `1-country`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`  FROM civicrm_contact contact_a 
LEFT JOIN civicrm_address `1-address` ON (`1-address`.contact_id = contact_a.id AND `1-address`.is_primary = 1)  
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  
LEFT JOIN civicrm_country `1-country` ON `1-country`.id = `1-address`.country_id  
LEFT JOIN civicrm_location_type `1-location_type` ON (  ( `1-address`.location_type_id = `1-location_type`.id )  )  
LEFT JOIN civicrm_location_type `Home-location_type` ON (  )  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix') LEFT JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id )  LEFT JOIN civicrm_option_group option_group_suffix ON (option_group_suffix.name = 'individual_suffix') LEFT JOIN civicrm_option_value individual_suffix ON (contact_a.suffix_id = individual_suffix.value AND option_group_suffix.id = individual_suffix.option_group_id )  WHERE  ( contact_a.id = 155 )  AND  ( 1 )   GROUP BY contact_a.id   [nativecode=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 ')  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.na' at line 6]"]
)

This is the backtrace:

Code: [Select]

backTrace

sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 148
, handle, 
sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 912
sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 966
sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 574
sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
sites/all/modules/civicrm/packages/DB/DataObject.php, query, 2394
sites/all/modules/civicrm/packages/DB/DataObject.php, _query, 1587
sites/all/modules/civicrm/CRM/Core/DAO.php, query, 144
sites/all/modules/civicrm/CRM/Core/DAO.php, query, 861
sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, executeQuery, 3091
sites/all/modules/civicrm/CRM/Core/BAO/UFGroup.php, searchQuery, 645
sites/all/modules/civicrm/CRM/Core/BAO/UFGroup.php, getValues, 1869
sites/all/modules/civicrm/CRM/Profile/Form.php, checkFieldsEmptyValues, 642
sites/all/modules/civicrm/CRM/Profile/Form/Dynamic.php, postProcess, 127
sites/all/modules/civicrm/CRM/Core/Form.php, postProcess, 247
sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Submit.php, mainProcess, 77
sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
sites/all/modules/civicrm/CRM/Core/Controller.php, handle, 245
sites/all/modules/civicrm/CRM/Core/BAO/UFGroup.php, run, 561
sites/all/modules/civicrm/drupal/civicrm.module, getEditHTML, 572
sites/all/modules/civicrm/drupal/civicrm.module, civicrm_form_data, 447
, civicrm_user, 
includes/module.inc, call_user_func_array, 450
modules/user/user.module, module_invoke, 2471
modules/user/user.pages.inc, _user_forms, 250
, user_profile_form, 
includes/form.inc, call_user_func_array, 366
, drupal_retrieve_form, 
includes/form.inc, call_user_func_array, 102
modules/user/user.pages.inc, drupal_get_form, 235
, user_edit, 
includes/menu.inc, call_user_func_array, 348
index.php, menu_execute_active_handler, 18

Strangely, after the error, the data did save.

I am completely stumped on this one - it would be great to be able to get this information on registration, but could also set up collection afterward. However, I am trying to avoin the hassle of redoing the registration system.

Any ideas?
THANKS!

Donald Lobo · July 09, 2009, 09:27:02 pm

can u check your location types entry and see if u have an entry for the Home location type.

http://drupal.demo.civicrm.org/civicrm/admin/locationType?reset=1

lobo

menkh · July 10, 2009, 07:07:45 am

Yes, there are entries for 'HOME' and 'WORK' (they seem to be default values from the installation). Should these be changed?

Dave Greenberg · July 10, 2009, 11:44:33 am

Seems like there might be something wrong in one or more of the profile field configurations. Can you run the following SELECT query and paste the output here - substituting the ID of your profile for N:

SELECT * from civicrm_uf_field WHERE uf_group_id = N

menkh · July 10, 2009, 06:23:45 pm

Ran the queries for all profiles as you suggested. They all returned the fields normally, bit I did notice that every field in every profile (except for the Home Phone field) had a location type value of 'NULL'. Do all profile fields require a location type?

menkh · July 10, 2009, 06:46:40 pm

Whoops, forgot to attach the output:

'My Name' profile:

Code: [Select]

mysql> SELECT * from civicrm_uf_field WHERE uf_group_id = 3;
+----+-------------+-------------------+-----------+---------+-------------+--------+-----------+--------------------------+-------------+---------------+------------------+---------------+----------------+------------+
| id | uf_group_id | field_name        | is_active | is_view | is_required | weight | help_post | visibility               | in_selector | is_searchable | location_type_id | phone_type_id | label          | field_type |
+----+-------------+-------------------+-----------+---------+-------------+--------+-----------+--------------------------+-------------+---------------+------------------+---------------+----------------+------------+
| 38 |           3 | first_name        |         1 |       0 |           1 |      2 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | First Name     | Individual | 
| 39 |           3 | middle_name       |         1 |       0 |           0 |      3 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | Middle Name(s) | Individual | 
| 40 |           3 | last_name         |         1 |       0 |           1 |      4 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | Last Name      | Individual | 
| 41 |           3 | individual_suffix |         1 |       0 |           0 |      5 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | Suffix         | Individual | 
| 42 |           3 | individual_prefix |         1 |       0 |           0 |      1 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | Title          | Individual | 
+----+-------------+-------------------+-----------+---------+-------------+--------+-----------+--------------------------+-------------+---------------+------------------+---------------+----------------+------------+
5 rows in set (0.00 sec)

'My Address' Profile:

Code: [Select]

mysql> SELECT * from civicrm_uf_field WHERE uf_group_id = 11;
+-----+-------------+------------------------+-----------+---------+-------------+--------+-----------+--------------------------+-------------+---------------+------------------+---------------+-------------------------+------------+
| id  | uf_group_id | field_name             | is_active | is_view | is_required | weight | help_post | visibility               | in_selector | is_searchable | location_type_id | phone_type_id | label                   | field_type |
+-----+-------------+------------------------+-----------+---------+-------------+--------+-----------+--------------------------+-------------+---------------+------------------+---------------+-------------------------+------------+
| 184 |          11 | street_address         |         1 |       0 |           1 |      1 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | Street Address (Line 1) | Contact    | 
| 185 |          11 | supplemental_address_1 |         1 |       0 |           0 |      2 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | Street Address (Line 2) | Contact    | 
| 186 |          11 | supplemental_address_2 |         1 |       0 |           0 |      3 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | Street Address (Line 3) | Contact    | 
| 187 |          11 | city                   |         1 |       0 |           1 |      4 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | City                    | Contact    | 
| 188 |          11 | state_province         |         1 |       0 |           1 |      5 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | State                   | Contact    | 
| 189 |          11 | postal_code            |         1 |       0 |           1 |      6 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | Zip/Postal Code         | Contact    | 
| 190 |          11 | country                |         1 |       0 |           0 |      7 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | Country                 | Contact    | 
+-----+-------------+------------------------+-----------+---------+-------------+--------+-----------+--------------------------+-------------+---------------+------------------+---------------+-------------------------+------------+
7 rows in set (0.01 sec)

'My Phone Numbers' profile:

Code: [Select]

mysql> SELECT * from civicrm_uf_field WHERE uf_group_id = 12;
+-----+-------------+--------------------------------+-----------+---------+-------------+--------+-----------+--------------------------+-------------+---------------+------------------+---------------+--------------------------------+------------+
| id  | uf_group_id | field_name                     | is_active | is_view | is_required | weight | help_post | visibility               | in_selector | is_searchable | location_type_id | phone_type_id | label                          | field_type |
+-----+-------------+--------------------------------+-----------+---------+-------------+--------+-----------+--------------------------+-------------+---------------+------------------+---------------+--------------------------------+------------+
| 204 |          12 | phone                          |         1 |       0 |           0 |      1 |           | User and User Admin Only |           0 |             0 |                1 |             0 | Home Phone Number              | Contact    | 
| 205 |          12 | phone                          |         1 |       0 |           0 |      2 |           | User and User Admin Only |           0 |             0 |             NULL |             0 | Mobile Phone Number            | Contact    | 
| 206 |          12 | preferred_communication_method |         1 |       0 |           0 |      3 |           | User and User Admin Only |           0 |             0 |             NULL |          NULL | Preferred Communication Method | Contact    | 
+-----+-------------+--------------------------------+-----------+---------+-------------+--------+-----------+--------------------------+-------------+---------------+------------------+---------------+--------------------------------+------------+
3 rows in set (0.00 sec)

Dave Greenberg · July 13, 2009, 12:35:05 pm

The NULL location_type_id s/b ok - for "location related fields" (address, phone, email) - it signifies PRIMARY (1 = Home location type). However, the phone_type_id values for your 2 phone fields are not correct. Mobile phone_type_id s/b 2 not 0, and the regular (landline) phone s/b have phone_type_id = 1.

I don't know if this is part of the problem - but I would edit those two fields in the UI - and set the correct values (if not set) and save.

If that doesn't fix things - the next steps would be:
1. edit and save all the profile fields (might fix a db setting that we're not catching visually)
2. disable one profile at a time and see if removing one makes the save work on registration. There may be a bug here - so that would help narrow things down.

menkh · July 13, 2009, 08:49:51 pm

OK, I fixed the phone type ID's and followed your suggestions - tried each profile one-by-one, and created a new test user in each case. Still no luck... I took a close look at the database tables in phpMyAdmin, and a contact record is in fact being created, but all of the fields in civicrm_contact, save email address, are NULL. There were also no civicrm_address or civicrm_phone records created, nor were there any custom profile records. I checked the profile fields in uf_field, and the columns appear to be properly set - they are simply not writing the data to the exposed fields in the relevant tables. However, this only applies to the Drupal user registration form - once the data is re-entered from the (Drupal) user profile, the data now saves correctly. Perhaps a bug in linking CiviCRM custom profiles to the Drupal registration form?

Dave Greenberg · July 14, 2009, 07:55:39 am

Sounds like a bug. Can you please recreate on the drupal demo and then file and issue w/ steps to recreate. thx!

CiviCRM Community Forums (archive)

News:

Author Topic: Profile data does not save after upgrade to 2.2 (Read 1429 times)

menkh

Profile data does not save after upgrade to 2.2

Donald Lobo

Re: Profile data does not save after upgrade to 2.2

menkh

Re: Profile data does not save after upgrade to 2.2

Dave Greenberg

Re: Profile data does not save after upgrade to 2.2

menkh

Re: Profile data does not save after upgrade to 2.2

menkh

Re: Profile data does not save after upgrade to 2.2

Dave Greenberg

Re: Profile data does not save after upgrade to 2.2

menkh

Re: Profile data does not save after upgrade to 2.2

Dave Greenberg

Re: Profile data does not save after upgrade to 2.2