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) »
  • DB Error: Unknown error after upgrading to 4.2.1
Pages: [1]

Author Topic: DB Error: Unknown error after upgrading to 4.2.1  (Read 1349 times)

gmarsh

  • I’m new here
  • *
  • Posts: 17
  • Karma: 0
  • CiviCRM version: 4.4.2
  • CMS version: Joomla 2.5.14
  • MySQL version: 5.5.32
  • PHP version: 5.3.26
DB Error: Unknown error after upgrading to 4.2.1
September 24, 2012, 07:07:19 pm
Had a smooth upgrade from 4.1.5 to 4.2.1 on a test site.  All administrative menus, records, etc. came up.  Until I got to a directory listing.  This directory listing has a custom template, but I've disabled the template, cleared the templates cache and get the same result.  Here's the error code:


Database Error Code: Column 'contact_id' in group statement is ambiguous, 1052
Additional Details:
Array
(
    [callback] => Array
        (
           
  • => CRM_Core_Error
  • [1] => handle
            )

       
Code: [Select]
=> -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (SELECT 24 as group_id,  contact_a.id as contact_id  FROM civicrm_contact contact_a INNER JOIN Ig_civicrm_temp_custom_74985c490cc587c8ecf65b6a8a920a93 temptableIg ON (contact_a.id = temptableIg.contact_id) LEFT JOIN Xg_civicrm_temp_custom_74985c490cc587c8ecf65b6a8a920a93 temptableXg ON (contact_a.id = temptableXg.contact_id) LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) )  WHERE  (temptableIg.contact_id IS NOT NULL) AND temptableXg.contact_id IS NULL AND contact_a.is_deleted = 0  GROUP BY contact_id AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.status = 'Removed'
                              AND   civicrm_group_contact.group_id = 24 ) ); [nativecode=1052 ** Column 'contact_id' in group statement is ambiguous]
    [type] => DB_Error
    [user_info] => INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (SELECT 24 as group_id,  contact_a.id as contact_id  FROM civicrm_contact contact_a INNER JOIN Ig_civicrm_temp_custom_74985c490cc587c8ecf65b6a8a920a93 temptableIg ON (contact_a.id = temptableIg.contact_id) LEFT JOIN Xg_civicrm_temp_custom_74985c490cc587c8ecf65b6a8a920a93 temptableXg ON (contact_a.id = temptableXg.contact_id) LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) )  WHERE  (temptableIg.contact_id IS NOT NULL) AND temptableXg.contact_id IS NULL AND contact_a.is_deleted = 0  GROUP BY contact_id AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.status = 'Removed'
                              AND   civicrm_group_contact.group_id = 24 ) ); [nativecode=1052 ** Column 'contact_id' in group statement is ambiguous]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (SELECT 24 as group_id,  contact_a.id as contact_id  FROM civicrm_contact contact_a INNER JOIN Ig_civicrm_temp_custom_74985c490cc587c8ecf65b6a8a920a93 temptableIg ON (contact_a.id = temptableIg.contact_id) LEFT JOIN Xg_civicrm_temp_custom_74985c490cc587c8ecf65b6a8a920a93 temptableXg ON (contact_a.id = temptableXg.contact_id) LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) )  WHERE  (temptableIg.contact_id IS NOT NULL) AND temptableXg.contact_id IS NULL AND contact_a.is_deleted = 0  GROUP BY contact_id AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.status = 'Removed'
                              AND   civicrm_group_contact.group_id = 24 ) ); [nativecode=1052 ** Column 'contact_id' in group statement is ambiguous]"]
)

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: DB Error: Unknown error after upgrading to 4.2.1
September 24, 2012, 09:58:29 pm
@gmarsh,

I had a look at the code that generates this SQL, and I think that work on CRM-10848 might fix that issue for you.

See the issue at http://issues.civicrm.org/jira/browse/CRM-10848 and note that the backtrace provided is similar to yours.

See the code change at https://fisheye2.atlassian.com/viewrep/CiviCRM/branches/v4.2/CRM/Contact/Form/Search/Custom/Group.php?r1=42237&r2=42435&u=3 and note that it changes the SQL that bothers you.

BTW, what do you mean by 'directory listing'?

ken

gmarsh

  • I’m new here
  • *
  • Posts: 17
  • Karma: 0
  • CiviCRM version: 4.4.2
  • CMS version: Joomla 2.5.14
  • MySQL version: 5.5.32
  • PHP version: 5.3.26
Re: DB Error: Unknown error after upgrading to 4.2.1
September 25, 2012, 06:16:19 am
Thanks for the investigation, Ken!

The 'directory listing' is a forced listing of a profile search form.  The list of contacts to be displayed are a Smart Group of active members chosen through an Include/Exclude Search.  Only members can view the listing, so ACL may be involved, though all other member-only information shows up fine.

Your notes got me part of the way to a solution.  I looked at CRM-10848 and made the code change provided at https://fisheye2.atlassian.com/viewrep/CiviCRM/branches/v4.2/CRM/Contact/Form/Search/Custom/Group.php?r1=42237&r2=42435&u=3

Instead of the DB Error, the first entry of the Smart Group is now displayed correctly, but no additional entries.  If I edit the search criteria to find a name known to be in the Smart Group, Civi responds with "no match."

I again disabled/enabled the custom template file.  The look of the listing changed as expected, but no additional entries showed up.

Gary

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: DB Error: Unknown error after upgrading to 4.2.1
September 25, 2012, 07:54:31 am

i made a few more fixes and commits to that issue

can you take a look and see if the new commits fixes the issue

thanx

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

jamie

  • I post occasionally
  • **
  • Posts: 95
  • Karma: 6
Re: DB Error: Unknown error after upgrading to 4.2.1
September 25, 2012, 01:47:47 pm
I had a similar problem. After upgrading to 4.2.1 (from 4.1.5) one of our reports crapped out with the exact same sql errors.

I applied this fix (as suggested):

https://fisheye2.atlassian.com/viewrep/CiviCRM/branches/v4.2/CRM/Contact/Form/Search/Custom/Group.php?r1=42237&r2=42435

And now it works like a charm.

Thanks!

gmarsh

  • I’m new here
  • *
  • Posts: 17
  • Karma: 0
  • CiviCRM version: 4.4.2
  • CMS version: Joomla 2.5.14
  • MySQL version: 5.5.32
  • PHP version: 5.3.26
Re: DB Error: Unknown error after upgrading to 4.2.1
September 26, 2012, 10:13:36 am
I tried each of the revisions of group.php (42435, 42516 & 42523) to see what their changes were to both front and back end listings. 

The listings were of 2 smart groups: Active Mbrs [IN>(new,current, grace)] | MbrShipDir [Include/Exclude search.  Include Active Mbrs Group | Exclude Cancelled Group]

42435 - Active Mbrs/OK, MembershipDir/OK | Frontend - one member listed
42516 - Active Mbrs/OK, MembershipDir/OK | Frontend - one member listed
42523 - Active Mbrs/OK, MembershipDir/one person | Frontend - one member listed

When I changed the group in the profile used for the frontend membership directory listing to Active Mbrs, the frontend listing was correct. So it appears that the listing problem (both front and back end) is coming from the Include/Exclude search, but the last revision affected both front and backend. 

With the last revision I tried several variants of the Include/Exclude search in the admin.  I consistently got only one contact listed (the lowest CID number) when several should have been listed.

Hope this helps the troubleshooting!
Gary

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • DB Error: Unknown error after upgrading to 4.2.1

This forum was archived on 2017-11-26.