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 »
  • Using CiviCRM »
  • Using CiviContribute (Moderator: Donald Lobo) »
  • ORDER BY contact_a.sort_name' at line 5, 1064 Error
Pages: [1] 2

Author Topic: ORDER BY contact_a.sort_name' at line 5, 1064 Error  (Read 4822 times)

joommaster

  • Guest
ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 23, 2007, 11:13:44 am
We are getting this database error upon completing a contribution. The contribution goes fine, the emails go out, we just don't make it to the thank you page.

Here's the error:

Sorry. A non-recoverable error has occurred.

Database Error Code: 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 ( 1 ) ORDER BY contact_a.sort_name' at line 5, 1064

Return to CiviCRM menu.

Error Details:


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

       
Code: [Select]
=> -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT contact_a.id as contact_id, civicrm_individual.id as individual_id, civicrm_individual.first_name as `first_name`, civicrm_individual.last_name as `last_name`, `1-location`.id as `1-location_id`, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.street_address as `1-street_address`, `1-address`.supplemental_address_1 as `1-supplemental_address_1`, `1-address`.city as `1-city`, `1-state_province`.id as `1-state_province_id`, `1-state_province`.abbreviation as `1-state_province`, `1-address`.postal_code as `1-postal_code`  FROM civicrm_contact contact_a LEFT JOIN civicrm_individual ON (contact_a.id = civicrm_individual.contact_id) 
LEFT JOIN civicrm_location `1-location` ON (`1-location`.entity_table = 'civicrm_contact' AND `1-location`.entity_id = contact_a.id AND `1-location`.is_primary = 1 ) 
LEFT JOIN civicrm_location_type `1-location_type` ON (`1-location`.location_type_id = `1-location_type`.id ) 
LEFT JOIN civicrm_address `1-address` ON (`1-address`.location_id = `1-location`.id) 
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = 22 AND  civicrm_contribution.id =  )  AND  ( 1 )   ORDER BY contact_a.sort_name   [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  ( 1 )   ORDER BY contact_a.sort_name' at line 5]
    [type] => DB_Error
    [user_info] => SELECT contact_a.id as contact_id, civicrm_individual.id as individual_id, civicrm_individual.first_name as `first_name`, civicrm_individual.last_name as `last_name`, `1-location`.id as `1-location_id`, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.street_address as `1-street_address`, `1-address`.supplemental_address_1 as `1-supplemental_address_1`, `1-address`.city as `1-city`, `1-state_province`.id as `1-state_province_id`, `1-state_province`.abbreviation as `1-state_province`, `1-address`.postal_code as `1-postal_code`  FROM civicrm_contact contact_a LEFT JOIN civicrm_individual ON (contact_a.id = civicrm_individual.contact_id) 
LEFT JOIN civicrm_location `1-location` ON (`1-location`.entity_table = 'civicrm_contact' AND `1-location`.entity_id = contact_a.id AND `1-location`.is_primary = 1 ) 
LEFT JOIN civicrm_location_type `1-location_type` ON (`1-location`.location_type_id = `1-location_type`.id ) 
LEFT JOIN civicrm_address `1-address` ON (`1-address`.location_id = `1-location`.id) 
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = 22 AND  civicrm_contribution.id =  )  AND  ( 1 )   ORDER BY contact_a.sort_name   [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  ( 1 )   ORDER BY contact_a.sort_name' at line 5]
    [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 contact_id, civicrm_individual.id as individual_id, civicrm_individual.first_name as `first_name`, civicrm_individual.last_name as `last_name`, `1-location`.id as `1-location_id`, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.street_address as `1-street_address`, `1-address`.supplemental_address_1 as `1-supplemental_address_1`, `1-address`.city as `1-city`, `1-state_province`.id as `1-state_province_id`, `1-state_province`.abbreviation as `1-state_province`, `1-address`.postal_code as `1-postal_code`  FROM civicrm_contact contact_a LEFT JOIN civicrm_individual ON (contact_a.id = civicrm_individual.contact_id) 
LEFT JOIN civicrm_location `1-location` ON (`1-location`.entity_table = 'civicrm_contact' AND `1-location`.entity_id = contact_a.id AND `1-location`.is_primary = 1 ) 
LEFT JOIN civicrm_location_type `1-location_type` ON (`1-location`.location_type_id = `1-location_type`.id ) 
LEFT JOIN civicrm_address `1-address` ON (`1-address`.location_id = `1-location`.id) 
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = 22 AND  civicrm_contribution.id =  )  AND  ( 1 )   ORDER BY contact_a.sort_name   [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  ( 1 )   ORDER BY contact_a.sort_name' at line 5]"]
)

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: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 25, 2007, 04:29:04 pm

1.can u try to reproduce this issue on demo.civicrm.org/drupal

2. can u enable debugging and backtrace and see where this specific error is being thrown. Its thrown because of an unknown contribution id and would be good to find out where and why

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

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: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 25, 2007, 04:31:32 pm

I assume this is also authorize.net transaction processor. This is a contributed payment processor and as such we might not be able to help debug this completely. I've asked marshall to take a look at whats happening with the null transaction id etc. Something seems to be messed up with the transaction flow

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

joommaster

  • Guest
Re: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 25, 2007, 08:27:07 pm
I could not reproduce the error on the Joomla demo. What lines am I looking at specifically in the Smarty log?

Here's my installation info:


MYSQL 5.0.24
PHP 5.2.2
Joomla! 1.0.12
CiviCRM 1.7


Here's the backtrace:

Thanks for the help...


backTrace


site.org/administrator/components/com_civicrm/civicrm/CRM/Core/Error.php, backtrace, 147
, handle,
site.org/administrator/components/com_civicrm/civicrm/packages/PEAR.php, call_user_func, 901
site.org/administrator/components/com_civicrm/civicrm/packages/DB.php, PEAR_Error, 888
site.org/administrator/components/com_civicrm/civicrm/packages/PEAR.php, DB_Error, 563
site.org/administrator/components/com_civicrm/civicrm/packages/DB/common.php, raiseError, 1849
site.org/administrator/components/com_civicrm/civicrm/packages/DB/mysql.php, raiseError, 894
site.org/administrator/components/com_civicrm/civicrm/packages/DB/mysql.php, mysqlRaiseError, 324
site.org/administrator/components/com_civicrm/civicrm/packages/DB/common.php, simpleQuery, 1162
site.org/administrator/components/com_civicrm/civicrm/packages/DB/DataObject.php, query, 2344
site.org/administrator/components/com_civicrm/civicrm/packages/DB/DataObject.php, _query, 1566
site.org/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php, query, 659
site.org/administrator/components/com_civicrm/civicrm/CRM/Contact/BAO/Query.php, executeQuery, 2567
site.org/administrator/components/com_civicrm/civicrm/CRM/Core/BAO/UFGroup.php, searchQuery, 616
site.org/administrator/components/com_civicrm/civicrm/CRM/Contribute/BAO/ContributionPage.php, getValues, 133
site.org/administrator/components/com_civicrm/civicrm/CRM/Contribute/BAO/ContributionPage.php, buildCustomDisplay, 92
site.org/administrator/components/com_civicrm/civicrm/CRM/Member/BAO/Membership.php, sendMail, 991
site.org/administrator/components/com_civicrm/civicrm/CRM/Contribute/Form/Contribution/Confirm.php, postProcessMembership, 344
site.org/administrator/components/com_civicrm/civicrm/CRM/Core/StateMachine.php, postProcess, 166
site.org/administrator/components/com_civicrm/civicrm/CRM/Core/QuickForm/Action/Next.php, perform, 63
site.org/administrator/components/com_civicrm/civicrm/packages/HTML/QuickForm/Controller.php, perform, 195
site.org/administrator/components/com_civicrm/civicrm/packages/HTML/QuickForm/Page.php, handle, 95
site.org/administrator/components/com_civicrm/civicrm/CRM/Core/Controller.php, handle, 213
site.org/administrator/components/com_civicrm/civicrm/CRM/Contribute/Invoke.php, run, 129
site.org/administrator/components/com_civicrm/civicrm/CRM/Core/Component.php(171) : eval()'d code, main, 1
site.org/administrator/components/com_civicrm/civicrm/CRM/Core/Component.php, eval, 171
site.org/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php, invoke, 130
site.org/components/com_civicrm/civicrm.php, invoke, 62
site.org/components/com_civicrm/civicrm.php, civicrm_invoke, 19
site.org/index.php, require_once, 240


 Sorry. A non-recoverable error has occurred.

Database Error Code: 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 ( 1 ) ORDER BY contact_a.sort_name' at line 5, 1064

Return to CiviCRM menu.

Error Details:


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

       
Code: [Select]
=> -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT contact_a.id as contact_id, civicrm_individual.id as individual_id, civicrm_individual.first_name as `first_name`, civicrm_individual.last_name as `last_name`, `1-location`.id as `1-location_id`, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.street_address as `1-street_address`, `1-address`.supplemental_address_1 as `1-supplemental_address_1`, `1-address`.city as `1-city`, `1-state_province`.id as `1-state_province_id`, `1-state_province`.abbreviation as `1-state_province`, `1-address`.postal_code as `1-postal_code`  FROM civicrm_contact contact_a LEFT JOIN civicrm_individual ON (contact_a.id = civicrm_individual.contact_id) 
LEFT JOIN civicrm_location `1-location` ON (`1-location`.entity_table = 'civicrm_contact' AND `1-location`.entity_id = contact_a.id AND `1-location`.is_primary = 1 ) 
LEFT JOIN civicrm_location_type `1-location_type` ON (`1-location`.location_type_id = `1-location_type`.id ) 
LEFT JOIN civicrm_address `1-address` ON (`1-address`.location_id = `1-location`.id) 
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = 27 AND  civicrm_contribution.id =  )  AND  ( 1 )   ORDER BY contact_a.sort_name   [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  ( 1 )   ORDER BY contact_a.sort_name' at line 5]
    [type] => DB_Error
    [user_info] => SELECT contact_a.id as contact_id, civicrm_individual.id as individual_id, civicrm_individual.first_name as `first_name`, civicrm_individual.last_name as `last_name`, `1-location`.id as `1-location_id`, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.street_address as `1-street_address`, `1-address`.supplemental_address_1 as `1-supplemental_address_1`, `1-address`.city as `1-city`, `1-state_province`.id as `1-state_province_id`, `1-state_province`.abbreviation as `1-state_province`, `1-address`.postal_code as `1-postal_code`  FROM civicrm_contact contact_a LEFT JOIN civicrm_individual ON (contact_a.id = civicrm_individual.contact_id) 
LEFT JOIN civicrm_location `1-location` ON (`1-location`.entity_table = 'civicrm_contact' AND `1-location`.entity_id = contact_a.id AND `1-location`.is_primary = 1 ) 
LEFT JOIN civicrm_location_type `1-location_type` ON (`1-location`.location_type_id = `1-location_type`.id ) 
LEFT JOIN civicrm_address `1-address` ON (`1-address`.location_id = `1-location`.id) 
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = 27 AND  civicrm_contribution.id =  )  AND  ( 1 )   ORDER BY contact_a.sort_name   [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  ( 1 )   ORDER BY contact_a.sort_name' at line 5]
    [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 contact_id, civicrm_individual.id as individual_id, civicrm_individual.first_name as `first_name`, civicrm_individual.last_name as `last_name`, `1-location`.id as `1-location_id`, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-address`.id as `1-address_id`, `1-address`.street_address as `1-street_address`, `1-address`.supplemental_address_1 as `1-supplemental_address_1`, `1-address`.city as `1-city`, `1-state_province`.id as `1-state_province_id`, `1-state_province`.abbreviation as `1-state_province`, `1-address`.postal_code as `1-postal_code`  FROM civicrm_contact contact_a LEFT JOIN civicrm_individual ON (contact_a.id = civicrm_individual.contact_id) 
LEFT JOIN civicrm_location `1-location` ON (`1-location`.entity_table = 'civicrm_contact' AND `1-location`.entity_id = contact_a.id AND `1-location`.is_primary = 1 ) 
LEFT JOIN civicrm_location_type `1-location_type` ON (`1-location`.location_type_id = `1-location_type`.id ) 
LEFT JOIN civicrm_address `1-address` ON (`1-address`.location_id = `1-location`.id) 
LEFT JOIN civicrm_state_province `1-state_province` ON `1-state_province`.id = `1-address`.state_province_id  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = 27 AND  civicrm_contribution.id =  )  AND  ( 1 )   ORDER BY contact_a.sort_name   [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  ( 1 )   ORDER BY contact_a.sort_name' at line 5]"]
)

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: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 25, 2007, 08:51:18 pm

I'm pretty sure this is the same error as:

http://issues.civicrm.org/jira/browse/CRM-2017?page=com.cenqua.fisheye.jira:fisheye-tabpanel

can u apply this patch:

http://fisheye.civicrm.org/viewrep/CiviCRM/branches/v1.8/CRM/Member/BAO/Membership.php?r1=10061&r2=10064

and see if that 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

joommaster

  • Guest
Re: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 26, 2007, 10:26:09 am
The second link that has the patch does not seem to work. Thanks for the continued help.

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: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 26, 2007, 10:32:40 am

Can u try now

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

joommaster

  • Guest
Re: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 26, 2007, 10:36:14 am
Yes it works fine now. I confess that I need some directions of where and how to apply the patch to the code. Thanks.

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: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 26, 2007, 10:41:38 am

1. see what file the patch is for (in this case: CRM/Member/BAO/Membership.php)

2. edit the file on your server and make the required change

3. a '-' means the line was deleted, a '+' means to add that line

for more information check a 'diff' or 'patch' man page on the web

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

joommaster

  • Guest
Re: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 27, 2007, 08:28:54 am
I'm trying to apply the patch now but the link is not working again. Thanks.

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 27, 2007, 11:06:45 am
Sorry - we've been having some stability problems with the Fisheye application that we use to get in-browser snapshots of changes to code files. I'll try to get it restarted sometime today.
Protect your investment in CiviCRM by  becoming a Member!

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 27, 2007, 11:25:00 am
It's running again now. But in case you have problems loading it, the change is on one line.

In CRM/Member/BAO/Membership.php:

Code: [Select]
// line 895
// change this
self::getContributionPageId( $membership->id ) );

// to this
$contribution[$index]->id );
Protect your investment in CiviCRM by  becoming a Member!

joommaster

  • Guest
Re: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 27, 2007, 01:51:08 pm
I put in the recommended code. It was at line 991. No change. Still the same error as listed above.

Sorry. A non-recoverable error has occurred.

Database Error Code: 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 ( 1 ) ORDER BY contact_a.sort_name' at line 5, 1064

See backtrace above...

Any other ideas?

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: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 27, 2007, 05:11:20 pm

i've run out of ideas on how to potentially fix it.

my only suggestion would be to switch payment processors and see if that fixes it. We have not had similar reports like this with other folks (and i think a fair number of folks are using it)

alternatively you could potentially hite someone from the professional services list to help you debug it (depending on your budget etc)

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

joommaster

  • Guest
Re: ORDER BY contact_a.sort_name' at line 5, 1064 Error
June 29, 2007, 12:23:30 pm
I don't think it's the processor at all because it worked before. We delete a few test duplicates and then it seemed to crash into this error. Would deleting contributions make this happen? Also, is there a file that we can upload that will reset the data but maintain the basic architecture of civi --- the forms etc.? Thanks.
Pages: [1] 2
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviContribute (Moderator: Donald Lobo) »
  • ORDER BY contact_a.sort_name' at line 5, 1064 Error

This forum was archived on 2017-11-26.