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) »
  • Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups
Pages: [1]

Author Topic: Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups  (Read 2036 times)

bryancn

  • I’m new here
  • *
  • Posts: 16
  • Karma: 0
Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups
June 02, 2011, 09:30:36 pm
After upgrading a Joomla site from 3.3.5 to 4.0.2, seems to have invalidated most (but not all) of my smart groups.

Code: [Select]
Jun 03 15:38:03  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT contact_a.id as 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  WHERE  (  (  AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0)     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 = 15 )  UNION
SELECT contact_id as id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 15  [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 'AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0' at line 1]
    [type] => DB_Error
    [user_info] => SELECT contact_a.id as 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  WHERE  (  (  AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0)     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 = 15 )  UNION
SELECT contact_id as id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 15  [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 'AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT contact_a.id as 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  WHERE  (  (  AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0)     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 = 15 )  UNION
SELECT contact_id as id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 15  [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 'AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0' at line 1]"]
)


Jun 03 15:38:03  [info] $backTrace = /var/www/web9/web/administrator/components/com_civicrm/civicrm/CRM/Core/Error.php, backtrace, 205
, handle,
/var/www/web9/web/administrator/components/com_civicrm/civicrm/packages/PEAR.php, call_user_func, 931
/var/www/web9/web/administrator/components/com_civicrm/civicrm/packages/DB.php, PEAR_Error, 968
/var/www/web9/web/administrator/components/com_civicrm/civicrm/packages/PEAR.php, DB_Error, 564
/var/www/web9/web/administrator/components/com_civicrm/civicrm/packages/DB/common.php, raiseError, 1903
/var/www/web9/web/administrator/components/com_civicrm/civicrm/packages/DB/mysql.php, raiseError, 898
/var/www/web9/web/administrator/components/com_civicrm/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/web9/web/administrator/components/com_civicrm/civicrm/packages/DB/common.php, simpleQuery, 1216
/var/www/web9/web/administrator/components/com_civicrm/civicrm/packages/DB/DataObject.php, query, 2420
/var/www/web9/web/administrator/components/com_civicrm/civicrm/packages/DB/DataObject.php, _query, 1606
/var/www/web9/web/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php, query, 145
/var/www/web9/web/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php, query, 872
/var/www/web9/web/administrator/components/com_civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php, executeQuery, 313
/var/www/web9/web/administrator/components/com_civicrm/civicrm/CRM/Mailing/BAO/Mailing.php, load, 305
/var/www/web9/web/administrator/components/com_civicrm/civicrm/CRM/Mailing/BAO/Mailing.php, getRecipients, 112
/var/www/web9/web/administrator/components/com_civicrm/civicrm/CRM/Mailing/BAO/Mailing.php, getRecipientsCount, 1664
/var/www/web9/web/administrator/components/com_civicrm/civicrm/CRM/Mailing/Page/Report.php, report, 97
/var/www/web9/web/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php, run, 223
/var/www/web9/web/administrator/components/com_civicrm/admin.civicrm.php, invoke, 85
/var/www/web9/web/administrator/components/com_civicrm/admin.civicrm.php, civicrm_invoke, 45
/var/www/web9/web/libraries/joomla/application/component/helper.php, require_once, 162
/var/www/web9/web/administrator/includes/application.php, renderComponent, 136
/var/www/web9/web/administrator/index.php, dispatch, 67
/var/www/web9/web/administrator/index2.php, include, 14

What these have in common is that they select on state_province. For example:
Code: [Select]
(  ( LOWER(civicrm_state_province.name) = 'auckland' AND contact_a.contact_type IN ('Individual') )  OR  ( LOWER(civicrm_state_province.name) = 'bay of plenty' AND contact_a.contact_type IN ('Individual') )  OR  ( LOWER(civicrm_state_province.name) = 'waikato' AND contact_a.contact_type IN ('Individual') )  OR  ( LOWER(civicrm_state_province.name) = 'northland' AND contact_a.contact_type IN ('Individual') )  ) Any clues on how I might repair them?

bryancn

  • I’m new here
  • *
  • Posts: 16
  • Karma: 0
Re: Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups
June 06, 2011, 01:02:20 am
More precisely:

ANY 'Search Builder' search employing the 'State' field now fails with a fatal error as above, whereas an 'Advanced Search' using the same value on the State field succeeds.

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: Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups
June 06, 2011, 07:42:49 am

can u please file an issue and link to this forum post

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

jerdavis

  • I’m new here
  • *
  • Posts: 3
  • Karma: 0
  • CiviCRM version: 4.3.3
  • CMS version: Drupal 7
  • MySQL version: 5.2
  • PHP version: 5.3
Re: Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups
July 08, 2011, 06:06:55 pm
Ran into this as well, the following changed corrected our issue.  The strings were not being quoted.


Index: modules/civicrm/CRM/Contact/BAO/Query.php
===================================================================
--- modules/civicrm/CRM/Contact/BAO/Query.php   (revision 47)
+++ modules/civicrm/CRM/Contact/BAO/Query.php   (working copy)
@@ -2931,10 +2931,9 @@
         }
 
         $stateClause =
-            'civicrm_state_province.id IN (' .
-            implode( ',', $value ) .
-            ')';
-
+            'civicrm_state_province.id IN ("' .
+            implode( '","', $value ) .
+            '")';
         $this->_tables['civicrm_state_province'] = 1;
         $this->_whereTables['civicrm_state_province'] = 1;

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: Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups
July 08, 2011, 09:41:58 pm

Can you please file an issue for this so we can fix it for 3.4.5 :)

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

kreynen

  • I post occasionally
  • **
  • Posts: 105
  • Karma: 8
Re: Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups
July 11, 2011, 01:11:56 pm
While that fix eliminated the fatal error, the queries didn't actually produce the expected results.  I reverted part of the stateProvince function to fix this.  I'm not sure what those changes were attempting to fix, but my Search Builder queries work again.

We're using Drupal as the CMS, but I don't think that makes a difference with this error.

Code: [Select]
  /**
     * where / qill clause for state/province AND country (if present)
     *
     * @return void
     * @access public
     */
    function stateProvince( &$values, $status = null )
    {
        list( $name, $op, $value, $grouping, $wildcard ) = $values;
       
        if (! is_array( $value ) ) {
            // force the state to be an array
            $value = array( $value );
        }
       
        $stateClause =
        'civicrm_state_province.id IN ("' .
         implode( '","', $value ) .
         '")';

        $this->_tables['civicrm_state_province'] = 1;
        $this->_whereTables['civicrm_state_province'] = 1;
           
        $stateProvince =& CRM_Core_PseudoConstant::stateProvince();
        $names = array( );
        foreach ( $value as $id ) {
            $names[] = $stateProvince[$id];
        }
           

        $countryValues = $this->getWhereValues( 'country', $grouping );
        list( $countryClause, $countryQill ) = $this->country( $countryValues, true );

        if ( $countryClause ) {
            $clause = ( $stateClause AND $countryClause );
        } else {
            $clause = $stateClause;
        }
       
        if (!$status) {
        $this->_qill[$grouping][] = ts('State/Province') . ' - ' . implode( ' ' . ts('or') . ' ', $names );
        } else {
        return implode( ' ' . ts('or') . ' ', $names );
        }
           
        return $this->restWhere( $values );
       
    }

I'm having a really had time following the how these issues are tracked.  There is an issue in Jira with a status of closed that points back to this thread...

http://issues.civicrm.org/jira/browse/CRM-8255

But this issue still exists in 3.44 and it doesn't look like @jerdavis's changes were implemented.

https://fisheye2.atlassian.com/browse/CiviCRM/branches/v3.4/CRM/Contact/BAO/Query.php?r=35033#to2924

Does the existing issues status need to be changed or do we need to open a new issue?

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: Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups
July 11, 2011, 09:58:51 pm

can you please create a new issue for jerdavis's case of search builder not working

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

jerdavis

  • I’m new here
  • *
  • Posts: 3
  • Karma: 0
  • CiviCRM version: 4.3.3
  • CMS version: Drupal 7
  • MySQL version: 5.2
  • PHP version: 5.3
Re: Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups
August 12, 2011, 01:05:00 pm
We're still having this issue in the latest version. For some reason the values being passed in for the IN query are not being quoted, and as a result are blowing up the query.

The generation of the clause is happening here:


 function stateProvince( &$values, $status = null )
    {
        list( $name, $op, $value, $grouping, $wildcard ) = $values;

        if (! is_array( $value ) ) {
            // force the state to be an array
            $value = array( $value );
        }

        $stateClause =
            'civicrm_state_province.id IN (' .
            implode( ',', $value ) .
            ')';

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • Upgrade 3.3.5 to 3.4.2 produces Fatal Error when using some smart groups

This forum was archived on 2017-11-26.