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

gmarsh · 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

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 · 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 · 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 · 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

jamie · 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 · 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

CiviCRM Community Forums (archive)

News:

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

gmarsh

DB Error: Unknown error after upgrading to 4.2.1

ken

Re: DB Error: Unknown error after upgrading to 4.2.1

gmarsh

Re: DB Error: Unknown error after upgrading to 4.2.1

Donald Lobo

Re: DB Error: Unknown error after upgrading to 4.2.1

jamie

Re: DB Error: Unknown error after upgrading to 4.2.1

gmarsh

Re: DB Error: Unknown error after upgrading to 4.2.1