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) »
  • <solved>update civicrm_financial_trxn causes upgrade from 4.3.5 to 4.3.6 to fail
Pages: [1]

Author Topic: <solved>update civicrm_financial_trxn causes upgrade from 4.3.5 to 4.3.6 to fail  (Read 730 times)

SarahG (FountainTribe)

  • Ask me questions
  • ****
  • Posts: 782
  • Karma: 29
  • CiviCRM version: 4.4.7
  • CMS version: Drupal 6, Drupal 7
  • MySQL version: 5.5
  • PHP version: 5.3
<solved>update civicrm_financial_trxn causes upgrade from 4.3.5 to 4.3.6 to fail
October 30, 2013, 09:55:14 pm
The SQL statement: UPDATE civicrm_financial_trxn SET to_financial_account_id = 1860, payment_processor_id = WHERE id IN (67034,67423,68510)

is failing with an SQL syntax error of a missing value after the last =
 I investigated the records in question and all 3 are installments of an automated recurring contribution entered via the back-office.   The stack trace for the error is:

[Error: Update financial_account_id in financial_trxn table]
Error Field   Error Value
Type   DB_Error
Code   -2
Message   DB Error: syntax error
Mode   16
UserInfo   UPDATE civicrm_financial_trxn SET to_financial_account_id = 1860, payment_processor_id = WHERE id IN (67034,67423,68510)

 [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 'WHERE id IN (67034,67423,68510)' at line 2]

UPDATE civicrm_financial_trxn SET to_financial_account_id = 1860, payment_processor_id = WHERE id IN (67034,67423,68510)



DebugInfo   UPDATE civicrm_financial_trxn SET to_financial_account_id = 1860, payment_processor_id = WHERE id IN (67034,67423,68510) [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 'WHERE id IN (67034,67423,68510)' at line 2]
PEAR_Exception: DB Error: syntax error in unknown on line unknown
- DB_Error: DB Error: syntax error in unknown on line unknown
Exception trace
#   Function   Location
0   CRM_Core_Error::exceptionHandler(Object(DB_Error))   unknown:unknown
1   call_user_func(Array, Object(DB_Error))   /home/nsteorg/public_html/sites/all/modules/civicrm/packages/PEAR.php:931
2   PEAR_Error->PEAR_Error('DB Error: syntax…', -2, 16, Array, 'UPDATE civicrm_f…')   /home/nsteorg/public_html/sites/all/modules/civicrm/packages/DB.php:969
3   DB_Error->DB_Error(-2, 16, Array, 'UPDATE civicrm_f…')   /home/nsteorg/public_html/sites/all/modules/civicrm/packages/PEAR.php:564
4   PEAR->raiseError(null, -2, null, null, 'UPDATE civicrm_f…', 'DB_Error', true)   /home/nsteorg/public_html/sites/all/modules/civicrm/packages/DB/common.php:1905
5   DB_common->raiseError(-2, null, null, null, '1064 ** You have…')   /home/nsteorg/public_html/sites/all/modules/civicrm/packages/DB/mysql.php:898
6   DB_mysql->mysqlRaiseError()   /home/nsteorg/public_html/sites/all/modules/civicrm/packages/DB/mysql.php:327
7   DB_mysql->simpleQuery('UPDATE civicrm_f…')   /home/nsteorg/public_html/sites/all/modules/civicrm/packages/DB/common.php:1216
8   DB_common->query('UPDATE civicrm_f…')   /home/nsteorg/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php:2421
9   DB_DataObject->_query('UPDATE civicrm_f…')   /home/nsteorg/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php:1613
10   DB_DataObject->query('UPDATE civicrm_f…')   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php:155
11   CRM_Core_DAO->query('UPDATE civicrm_f…', true)   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php:917
12   CRM_Core_DAO::executeQuery('UPDATE civicrm_f…')   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Upgrade/Incremental/php/FourThree.php:950
13   CRM_Upgrade_Incremental_php_FourThree::updateFinancialTrxnData(Object(CRM_Queue_TaskContext), '4.3.6')   unknown:unknown
14   call_user_func_array(Array, Array)   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Queue/Task.php:79
15   CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Queue/Runner.php:186
16   CRM_Queue_Runner->runNext(true)   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:44
17   {closure}()   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Queue/ErrorPolicy.php:80
18   CRM_Queue_ErrorPolicy->call(Object(Closure))   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:47
19   CRM_Queue_Page_AJAX::runNext(Array)   unknown:unknown
20   call_user_func(Array, Array)   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:258
21   CRM_Core_Invoke::runItem(Array)   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:70
22   CRM_Core_Invoke::_invoke(Array)   /home/nsteorg/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:52
23   CRM_Core_Invoke::invoke(Array)   /home/nsteorg/public_html/sites/all/modules/civicrm/drupal/civicrm.module:354
24   civicrm_invoke('upgrade', 'queue', 'ajax', 'runNext')   unknown:unknown
25   call_user_func_array('civicrm_invoke', Array)   /home/nsteorg/public_html/includes/menu.inc:348
26   menu_execute_active_handler()   /home/nsteorg/public_html/index.php:18
27   {main}


« Last Edit: November 11, 2013, 02:13:32 pm by epg »
Did I help you? Please donate to the Civi-Make-It-Happen campaign  CiviCRM for mobile devices! 

SarahG (FountainTribe)

  • Ask me questions
  • ****
  • Posts: 782
  • Karma: 29
  • CiviCRM version: 4.4.7
  • CMS version: Drupal 6, Drupal 7
  • MySQL version: 5.5
  • PHP version: 5.3
Re: update to civicrm_financial_trxn causes upgrade from 4.3.5 to 4.3.6 to fail
November 11, 2013, 02:12:58 pm
I found and fixed the issue in my database.  I had to run the SQL:

SELECT cc.id contribution_id, cc.contribution_recur_id, cft.payment_processor_id,
cft.id financial_trxn_id, cfi.entity_table, cft.from_financial_account_id, cft.to_financial_account_id
FROM `civicrm_contribution` cc
LEFT JOIN civicrm_entity_financial_trxn ceft ON ceft.entity_id = cc.id
LEFT JOIN civicrm_financial_trxn cft ON cft.id = ceft.financial_trxn_id
LEFT JOIN civicrm_entity_financial_trxn ceft1 ON ceft1.financial_trxn_id = ceft.financial_trxn_id
LEFT JOIN civicrm_financial_item cfi ON cfi.id = ceft1.entity_id
WHERE ceft.entity_table = 'civicrm_contribution'  AND cc.contribution_recur_id IS NOT NULL
AND ceft1.entity_table = 'civicrm_financial_item' AND cft.id IS NOT NULL AND cft.payment_instrument_id = 1
ORDER BY cft.id

Which showed me that a number of records had a NULL for the payment processor ID. I updated those NULLs to the ID of my payment processor, then re-ran the upgrade to version 4.3.6 which then worked as expected.
Did I help you? Please donate to the Civi-Make-It-Happen campaign  CiviCRM for mobile devices! 
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • <solved>update civicrm_financial_trxn causes upgrade from 4.3.5 to 4.3.6 to fail

This forum was archived on 2017-11-26.