CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • Errors on upgrade
Pages: [1]

Author Topic: Errors on upgrade  (Read 1795 times)

David L

  • I post occasionally
  • **
  • Posts: 55
  • Karma: 2
Errors on upgrade
August 20, 2010, 02:46:28 pm
I upgraded from 3.1.4 to 3.2

I am getting the errors like this all over the place:
DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
Error Details
Database Error Code: Operand should contain 1 column(s), 1241

For example, I as uid1 can view contacts without a problem, but when other users try to view a contact they get that message. Or when even I as uid1 try to send a mailing I get that message.

For demo purposes, here is an example of the full message when trying to view a contact. The details in the message constantly change depending on the function being attempted:


Code: [Select]
DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
Error Details
Database Error Code: Operand should contain 1 column(s), 1241
Additional Details:

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

    [code] => a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => SELECT count(DISTINCT civicrm_group_contact.id) FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)  LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id  LEFT JOIN civicrm_group ON civicrm_group.id =  civicrm_group_contact.group_id  LEFT JOIN civicrm_subscription_history
                                   ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id
                                  AND civicrm_group_contact.group_id   =  civicrm_subscription_history.group_id LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id   LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-21,23,20` ON ( `civicrm_entity_tag-21,23,20`.entity_id = contact_a.id  AND
                        `civicrm_entity_tag-21,23,20`.entity_table = 'civicrm_contact' )   LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender') LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)  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 = 17487 AND civicrm_group.is_active = 1  AND  (  ( civicrm_group_contact.group_id IN (34, 2, 38, 6, 18, 37, 22, 31, 26, 27, 7, 8, 9, 32, 33, 17, 15, 16, 23, 30, 19, 21, 10, 25, 11, 36, 14, 12, 28, 13, 35, 24 ) AND civicrm_group_contact.status = 'Added' )  OR  ( contact_a.contact_type IN ('Individual') AND `civicrm_entity_tag-21,23,20`.tag_id = (21,23,20) )  )   [nativecode=1241 ** Operand should contain 1 column(s)]
    [type] => DB_Error
    [user_info] => SELECT count(DISTINCT civicrm_group_contact.id) FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)  LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id  LEFT JOIN civicrm_group ON civicrm_group.id =  civicrm_group_contact.group_id  LEFT JOIN civicrm_subscription_history
                                   ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id
                                  AND civicrm_group_contact.group_id   =  civicrm_subscription_history.group_id LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id   LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-21,23,20` ON ( `civicrm_entity_tag-21,23,20`.entity_id = contact_a.id  AND
                        `civicrm_entity_tag-21,23,20`.entity_table = 'civicrm_contact' )   LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender') LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)  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 = 17487 AND civicrm_group.is_active = 1  AND  (  ( civicrm_group_contact.group_id IN (34, 2, 38, 6, 18, 37, 22, 31, 26, 27, 7, 8, 9, 32, 33, 17, 15, 16, 23, 30, 19, 21, 10, 25, 11, 36, 14, 12, 28, 13, 35, 24 ) AND civicrm_group_contact.status = 'Added' )  OR  ( contact_a.contact_type IN ('Individual') AND `civicrm_entity_tag-21,23,20`.tag_id = (21,23,20) )  )   [nativecode=1241 ** Operand should contain 1 column(s)]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT count(DISTINCT civicrm_group_contact.id) FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)  LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id  LEFT JOIN civicrm_group ON civicrm_group.id =  civicrm_group_contact.group_id  LEFT JOIN civicrm_subscription_history
                                   ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id
                                  AND civicrm_group_contact.group_id   =  civicrm_subscription_history.group_id LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id   LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-21,23,20` ON ( `civicrm_entity_tag-21,23,20`.entity_id = contact_a.id  AND
                        `civicrm_entity_tag-21,23,20`.entity_table = 'civicrm_contact' )   LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender') LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)  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 = 17487 AND civicrm_group.is_active = 1  AND  (  ( civicrm_group_contact.group_id IN (34, 2, 38, 6, 18, 37, 22, 31, 26, 27, 7, 8, 9, 32, 33, 17, 15, 16, 23, 30, 19, 21, 10, 25, 11, 36, 14, 12, 28, 13, 35, 24 ) AND civicrm_group_contact.status = 'Added' )  OR  ( contact_a.contact_type IN ('Individual') AND `civicrm_entity_tag-21,23,20`.tag_id = (21,23,20) )  )   [nativecode=1241 ** Operand should contain 1 column(s)]"]
)
[/code]

David L

  • I post occasionally
  • **
  • Posts: 55
  • Karma: 2
Re: Errors on upgrade
August 20, 2010, 03:54:15 pm
Update: This error only happens to users who have ACL permission for Edit > All Groups.

Users with Edit permissions on only one group have no problem. But users with permissions on all groups get errors whatever they do.


Side question: Is it possible to downgrade from 3.2.1 to 3.2, being that I already ran update.php? If yes, what would the process be?

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Errors on upgrade
August 21, 2010, 11:38:38 am

you can downgrade by going back to your 3.2.0 db

can you try to reproduce this issue on demo?

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

David L

  • I post occasionally
  • **
  • Posts: 55
  • Karma: 2
Re: Errors on upgrade
August 21, 2010, 08:56:40 pm
I could not reproduce this on the sandbox. Then again the sandbox only has 200 records in it.

In any event, I tried version 3.2 but that gave me the same problem, so I rolled everything back to 3.1.4

The 3.2 release is quite new, so I'll wait to see if anyone reports similar trouble so we can figure out what is going on.

Thanks!

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: Errors on upgrade
August 22, 2010, 12:35:53 am
This last bit is the error in your SQL

( contact_a.contact_type IN ('Individual') AND `civicrm_entity_tag-21,23,20`.tag_id = (21,23,20) )  )   

I would try truncating every table that has the word 'cache' in it and see it that helps. Or else re-creating the group you have based on those tags
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute

dschafer

  • I post occasionally
  • **
  • Posts: 93
  • Karma: 3
    • Backoffice Thinking
  • CiviCRM version: 3.2.3 - 4.4.4
  • CMS version: Drupal 6.x, 7.x, Wordpress
  • MySQL version: 5.1.x - 5.5.x
  • PHP version: 5.2.x - 5.4.x
Re: Errors on upgrade
September 01, 2010, 03:14:46 pm
We have this problem as well after upgrading form 3.0.3 to 3.2.1

The issue appears to be with all smart groups that employ multiple tag criteria and were created with Advance Search rather that search builder.

Have not found a way to correct other than creating a new group. You cannot edit the criteria of the existing group.

-- Dave

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Errors on upgrade
September 01, 2010, 03:20:12 pm

hey dave:

can u create a similar search in 3.2.x and then compare the db column values between the 3.0.x version and 3.2.x version

i dont know what changes were made to tags in 3.2, but i do know that there were a few major upgrades to the tags code which might cause this issue

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
Re: Errors on upgrade
November 30, 2010, 10:25:36 pm
I'm getting a similar problem. It is related to smart groups based on searches created using the Custom Searches... Include / Exclude Contacts in a Group / Tag

I resolved this by recreating the smart groups from scratch.

Quote
Dec 01 15:09:06  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
           
  • => CRM_Core_Error
    • [1] => handle
            )

       
Code: [Select]
=> a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] =>  INSERT IGNORE INTO Xg_civicrm_temp_custom_0fe504e5c6fd84ed56ed27c28d97ed93(contact_id)
SELECT contact_a.id
 FROM civicrm_contact contact_a  LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-7,17,8` ON ( `civicrm_entity_tag-7,17,8`.entity_id = contact_a.id  AND
                        `civicrm_entity_tag-7,17,8`.entity_table = 'civicrm_contact' ) 
WHERE   ( `civicrm_entity_tag-7,17,8`.tag_id = (7,17,8) )  AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 185 AND civicrm_group_contact.status = 'Removed') [nativecode=1241 ** Operand should contain 1 column(s)]
    [type] => DB_Error
    [user_info] =>  INSERT IGNORE INTO Xg_civicrm_temp_custom_0fe504e5c6fd84ed56ed27c28d97ed93(contact_id)
SELECT contact_a.id
 FROM civicrm_contact contact_a  LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-7,17,8` ON ( `civicrm_entity_tag-7,17,8`.entity_id = contact_a.id  AND
                        `civicrm_entity_tag-7,17,8`.entity_table = 'civicrm_contact' ) 
WHERE   ( `civicrm_entity_tag-7,17,8`.tag_id = (7,17,8) )  AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 185 AND civicrm_group_contact.status = 'Removed') [nativecode=1241 ** Operand should contain 1 column(s)]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info=" INSERT IGNORE INTO Xg_civicrm_temp_custom_0fe504e5c6fd84ed56ed27c28d97ed93(contact_id)
SELECT contact_a.id
 FROM civicrm_contact contact_a  LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-7,17,8` ON ( `civicrm_entity_tag-7,17,8`.entity_id = contact_a.id  AND
                        `civicrm_entity_tag-7,17,8`.entity_table = 'civicrm_contact' ) 
WHERE   ( `civicrm_entity_tag-7,17,8`.tag_id = (7,17,8) )  AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 185 AND civicrm_group_contact.status = 'Removed') [nativecode=1241 ** Operand should contain 1 column(s)]"]
)


Dec 01 15:09:06  [info] $backTrace = /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 189
, handle,
/var/www/citybibleforum/sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 931
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 971
/var/www/citybibleforum/sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 564
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/DataObject.php, query, 2411
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/DataObject.php, _query, 1597
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 145
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 867
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Contact/Form/Search/Custom/Group.php, executeQuery, 266
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Contact/BAO/SearchCustom.php, from, 121
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Contact/BAO/SavedSearch.php, fromWhereEmail, 177
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Mailing/BAO/Mailing.php, fromWhereEmail, 307
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Mailing/BAO/Mailing.php, getRecipients, 123
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Mailing/Form/Group.php, getRecipientsCount, 387
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Form.php, postProcess, 250
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/StateMachine.php, mainProcess, 167
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php, perform, 64
/var/www/citybibleforum/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
/var/www/citybibleforum/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Controller.php, handle, 279
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Invoke.php, run, 219
/var/www/citybibleforum/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 351
, civicrm_invoke,
/var/www/citybibleforum/includes/menu.inc, call_user_func_array, 348
/var/www/citybibleforum/index.php, menu_execute_active_handler, 18
[/quote]

mdatcher

  • I’m new here
  • *
  • Posts: 21
  • Karma: 2
  • CiviCRM version: 4.5.1
  • CMS version: Drupal 7.31
  • MySQL version: 5.5.37
  • PHP version: 5.3.28
Re: Errors on upgrade
January 10, 2011, 07:24:18 am
I have run into exactly the same problem. I am using a smart group based on an advanced search. The error crops up when someone attempts to use a profile to search within the smart group using more than one tag. I deleted and recreated the smart group to no avail. Is there a problem with searching by multiple tags that we all need to know about? I am able to reproduce this error in a matter of minutes on the demo site. Thanks.
« Last Edit: January 10, 2011, 07:43:06 am by mdatcher »

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Errors on upgrade
January 10, 2011, 02:12:35 pm

This seems like a different issue and is potentially a bug.

Can you please start a new topic and give steps to reproduce the bug.

If you are a coder, would be great if you can take  a look at the query and figure out what needs to be fixed

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

mdatcher

  • I’m new here
  • *
  • Posts: 21
  • Karma: 2
  • CiviCRM version: 4.5.1
  • CMS version: Drupal 7.31
  • MySQL version: 5.5.37
  • PHP version: 5.3.28
Re: Errors on upgrade
January 13, 2011, 12:28:56 pm
I opened CRM-7373. I downloaded and tested the included file and it worked perfectly. That was a very quick response.
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • Errors on upgrade

This forum was archived on 2017-11-26.