CiviCRM Community Forums (archive)

following suggestions in http://forum.civicrm.org/index.php/topic,4976.0.html, including link to successful joomla->standalone migration.

got to re-creation of initial openid user, went through fine to openid, but then throw the following rather peculiar error:

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 id  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id 
LEFT JOIN civicrm_value_1_farm_connection_details ON civicrm_value_1_farm_connection_details.entity_id = contact_a.id  WHERE  ( LOWER() = 'all' AND  civicrm_contribution.is_test = '0' AND civicrm_value_1_farm_connection_details.interested_in_volunteering_ = 'y' )  AND  ( 1 )     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 = 9 )  UNION
SELECT contact_id as id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 9  [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 ') = 'all' AND  civicrm_contribution.is_test = '0' AND civicrm_value_1_farm_conne' at line 2]
    [type] => DB_Error
    [user_info] => SELECT DISTINCT(contact_a.id) as id  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id 
LEFT JOIN civicrm_value_1_farm_connection_details ON civicrm_value_1_farm_connection_details.entity_id = contact_a.id  WHERE  ( LOWER() = 'all' AND  civicrm_contribution.is_test = '0' AND civicrm_value_1_farm_connection_details.interested_in_volunteering_ = 'y' )  AND  ( 1 )     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 = 9 )  UNION
SELECT contact_id as id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 9  [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 ') = 'all' AND  civicrm_contribution.is_test = '0' AND civicrm_value_1_farm_conne' at line 2]
    [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 id  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id 
LEFT JOIN civicrm_value_1_farm_connection_details ON civicrm_value_1_farm_connection_details.entity_id = contact_a.id  WHERE  ( LOWER() = 'all' AND  civicrm_contribution.is_test = '0' AND civicrm_value_1_farm_connection_details.interested_in_volunteering_ = 'y' )  AND  ( 1 )     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 = 9 )  UNION
SELECT contact_id as id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 9  [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 ') = 'all' AND  civicrm_contribution.is_test = '0' AND civicrm_value_1_farm_conne' at line 2]"]
)

Civicrm.Log gives:

May 17 23:35:31  [info] $backTrace = /var/www/civicrm/CRM/Core/Error.php, backtrace, 192
, handle,
/var/www/civicrm/packages/PEAR.php, call_user_func, 912
/var/www/civicrm/packages/DB.php, PEAR_Error, 966
/var/www/civicrm/packages/PEAR.php, DB_Error, 574
/var/www/civicrm/packages/DB/common.php, raiseError, 1903
/var/www/civicrm/packages/DB/mysql.php, raiseError, 898
/var/www/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/civicrm/packages/DB/common.php, simpleQuery, 1216
/var/www/civicrm/packages/DB/DataObject.php, query, 2394
/var/www/civicrm/packages/DB/DataObject.php, _query, 1587
/var/www/civicrm/CRM/Core/DAO.php, query, 144
/var/www/civicrm/CRM/Core/DAO.php, query, 861
/var/www/civicrm/CRM/Contact/BAO/GroupContactCache.php, executeQuery, 255
/var/www/civicrm/CRM/Contact/BAO/Query.php, load, 2039
/var/www/civicrm/CRM/Contact/BAO/Query.php, savedSearch, 1997
/var/www/civicrm/CRM/Contact/BAO/Query.php, group, 1066
/var/www/civicrm/CRM/Contact/BAO/Query.php, whereClauseSingle, 1205
/var/www/civicrm/CRM/Contact/BAO/Query.php, whereClause, 396
/var/www/civicrm/CRM/Contact/BAO/Query.php, initialize, 360
/var/www/civicrm/CRM/Contact/BAO/Query.php, __construct, 1625
/var/www/civicrm/CRM/Contact/BAO/Group.php, getWhereClause, 370
/var/www/civicrm/standalone/finish_auth.php, buildClause, 52
/var/www/civicrm/standalone/finish_auth.php, run, 90

this appears to be an error with LOWER() having nothing inside parens -- any idea what's causing this?

could 2.2.2 -> 2.2.3 be causing any problems?

thanks!
.b[/code]

thanks lobo...

no, this is immediately after authorizing the first openid account -- after all the civicrm_openid* and civicrm_uf* tables have been truncated.  note that the first script in the backtrace is "/var/www/civicrm/standalone/finish_auth.php, buildClause, 52".  fwiw, the first openid account is not created; it crashes before it does that.

i'm not sure why it's creating this big fancy search -- but it's part of the "first-time standalone install"  as it's inside
if ( $session->get('new_install') == true ) {

what i don't understand is why the LOWER() would be empty, and whether that's a bug in the code (where?) or somehow a result of an upgrade corruption as you say (which is possible, but i don't understand how that would affect the contents of the LOWER() -- assuming that is the problem).

thanks!
.b