CiviCRM Community Forums (archive)

I have just upgraded from 4.2.9 to 4.3.0 under Drupal 6. The upgrade stated it was successful but membership renewals no longer work and I get the error shown in the attachment. I have created a second contribution page exactly as the first one and still get the same error (I realise that if the contribution page is exactly the same then what would I expect, but I was trying to see if it was caused by the upgrade process).

The contribution page includes a profile and I have not yet been able to check what happens when there is no profile on the contribution page.

Where can I see what the actual error is rather than the stuff displayed in the attached image?

Cheers
Martin Fuggle

I have just upgraded from 4.2.9 to 4.3.0 under Drupal 6. The upgrade stated it was successful but membership renewals no longer work and I get the error shown in the attachment. I have created a second contribution page exactly as the first one and still get the same error (I realise that if the contribution page is exactly the same then what would I expect, but I was trying to see if it was caused by the upgrade process).

The contribution page includes a profile and I have not yet been able to check what happens when there is no profile on the contribution page.

Where can I see what the actual error is rather than the stuff displayed in the attached image?

Cheers
Martin Fuggle

Check the CiviCRM log under your Drupal home directory - sites/default/files/civicrm/ConfigAndLog (replace "default" with a site name if needed).

Thanks for this Dave. 

I have attached a screenshot of the New Contribution I was experimenting with.

Here is the error message in CiviCRM:

Sorry but we are not able to provide this at the moment.
DB Error: constraint violation

And here is the error message in the dblog:

$Fatal Error Details = Array ( [callback] => Array (

• => CRM_Core_Error [1] => handle ) => -3 [message] => DB Error: constraint violation [mode] => 16 [debug_info] => INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , total_amount , currency , trxn_id , status_id , payment_instrument_id , check_number ) VALUES ( 19 , 20130417084650 , 2 , 'GBP' , NULL , 1 , 1 , NULL ) [nativecode=1048 ** Column 'trxn_id' cannot be null] [type] => DB_Error [user_info] => INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , total_amount , currency , trxn_id , status_id , payment_instrument_id , check_number ) VALUES ( 19 , 20130417084650 , 2 , 'GBP' , NULL , 1 , 1 , NULL ) [nativecode=1048 ** Column 'trxn_id' cannot be null] [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , total_amount , currency , trxn_id , status_id , payment_instrument_id , check_number ) VALUES ( 19 , 20130417084650 , 2 , 'GBP' , NULL , 1 , 1 , NULL ) [nativecode=1048 ** Column 'trxn_id' cannot be null]"] )

Here's the backtrace too:

$backTrace = #0 /sites/all/modules/civicrm/CRM/Core/Error.php(196): CRM_Core_Error::backtrace("backTrace", TRUE) #1 [internal function](): CRM_Core_Error::handle(Object(DB_Error)) #2 /sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error)) #3 /sites/all/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: constraint violation", -3, 16, (Array:2), "INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , tot...") #4 /sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-3, 16, (Array:2), "INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , tot...") #5 /sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -3, NULL, NULL, "INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , tot...", "DB_Error", TRUE) #6 /sites/all/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-3, NULL, NULL, NULL, "1048 ** Column 'trxn_id' cannot be null") #7 /sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError() #8 /sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , tot...") #9 /sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , tot...") #10 /sites/all/modules/civicrm/packages/DB/DataObject.php(1055): DB_DataObject->_query("INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , tot...") #11 /sites/all/modules/civicrm/CRM/Core/DAO.php(287): DB_DataObject->insert() #12 /sites/all/modules/civicrm/CRM/Core/BAO/FinancialTrxn.php(61): CRM_Core_DAO->save() #13 /sites/all/modules/civicrm/CRM/Contribute/BAO/Contribution.php(2772): CRM_Core_BAO_FinancialTrxn::create((Array:11)) #14 /sites/all/modules/civicrm/CRM/Contribute/BAO/Contribution.php(178): CRM_Contribute_BAO_Contribution::recordFinancialAccounts((Array:37), (Array:1)) #15 /sites/all/modules/civicrm/CRM/Contribute/BAO/Contribution.php(266): CRM_Contribute_BAO_Contribution::add((Array:37), (Array:1)) #16 /sites/all/modules/civicrm/CRM/Contribute/Form/Contribution.php(1235): CRM_Contribute_BAO_Contribution::create((Array:37), (Array:1)) #17 /sites/all/modules/civicrm/CRM/Core/Form.php(246): CRM_Contribute_Form_Contribution->postProcess() #18 /sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php(151): CRM_Core_Form->mainProcess() #19 /sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php(128): CRM_Core_QuickForm_Action_Upload->realPerform(Object(CRM_Contribute_Form_Contribution), "upload") #20 /sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Upload->perform(Object(CRM_Contribute_Form_Contribution), "upload") #21 /sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contribute_Form_Contribution), "upload") #22 /sites/all/modules/civicrm/CRM/Core/Controller.php(316): HTML_QuickForm_Page->handle("upload") #23 /sites/all/modules/civicrm/CRM/Contribute/Page/Tab.php(302): CRM_Core_Controller->run() #24 /sites/all/modules/civicrm/CRM/Contribute/Page/Tab.php(351): CRM_Contribute_Page_Tab->edit() #25 /sites/all/modules/civicrm/CRM/Core/Invoke.php(292): CRM_Contribute_Page_Tab->run((Array:3), NULL) #26 /sites/all/modules/civicrm/CRM/Core/Invoke.php(70): CRM_Core_Invoke::runItem((Array:15)) #27 /sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3)) #28 /sites/all/modules/civicrm/drupal/civicrm.module(436): CRM_Core_Invoke::invoke((Array:3)) #29 [internal function](): civicrm_invoke("contribute", "add") #30 /includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:2)) #31 /index.php(21): menu_execute_active_handler() #32 {main}

Here are the results of the SQL queries:
show create table civicrm_contribution;

CREATE TABLE `civicrm_contribution` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution ID',
 `contact_id` int(10) unsigned NOT NULL COMMENT 'FK to Contact ID',
 `financial_type_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Financial Type for (total_amount - non_deductible_amount).',
 `contribution_page_id` int(10) unsigned DEFAULT NULL COMMENT 'The Contribution Page which triggered this contribution',
 `payment_instrument_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Payment Instrument',
 `receive_date` datetime DEFAULT NULL COMMENT 'when was gift received',
 `non_deductible_amount` decimal(20,2) DEFAULT '0.00' COMMENT 'Portion of total amount which is NOT tax deductible. Equal to total_amount for non-deductible contribution types.',
 `total_amount` decimal(20,2) NOT NULL COMMENT 'Total amount of this contribution. Use market value for non-monetary gifts.',
 `fee_amount` decimal(20,2) DEFAULT NULL COMMENT 'actual processor fee if known - may be 0.',
 `net_amount` decimal(20,2) DEFAULT NULL COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.',
 `trxn_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'unique transaction id. may be processor id, bank id + trans id, or account number + check number... depending on payment_method',
 `invoice_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'unique invoice id, system generated or passed in',
 `currency` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
 `cancel_date` datetime DEFAULT NULL COMMENT 'when was gift cancelled',
 `cancel_reason` text COLLATE utf8_unicode_ci,
 `receipt_date` datetime DEFAULT NULL COMMENT 'when (if) receipt was sent. populated automatically for online donations w/ automatic receipting',
 `thankyou_date` datetime DEFAULT NULL COMMENT 'when (if) was donor thanked',
 `source` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Origin of this Contribution.',
 `amount_level` text COLLATE utf8_unicode_ci,
 `contribution_recur_id` int(10) unsigned DEFAULT NULL COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each contribution made in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.',
 `honor_contact_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to contact ID',
 `is_test` tinyint(4) DEFAULT '0',
 `is_pay_later` tinyint(4) DEFAULT '0',
 `contribution_status_id` int(10) unsigned DEFAULT '1',
 `honor_type_id` int(10) unsigned DEFAULT NULL COMMENT 'Implicit FK to civicrm_option_value.',
 `address_id` int(10) unsigned DEFAULT NULL COMMENT 'Conditional foreign key to civicrm_address.id. We insert an address record for each contribution when we have associated billing name and address data.',
 `check_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `campaign_id` int(10) unsigned DEFAULT NULL COMMENT 'The campaign for which this contribution has been triggered.',
 PRIMARY KEY (`id`),
 UNIQUE KEY `UI_contrib_trxn_id` (`trxn_id`),
 UNIQUE KEY `UI_contrib_invoice_id` (`invoice_id`),
 KEY `UI_contrib_payment_instrument_id` (`payment_instrument_id`),
 KEY `index_contribution_status` (`contribution_status_id`),
 KEY `received_date` (`receive_date`),
 KEY `check_number` (`check_number`),
 KEY `FK_civicrm_contribution_contact_id` (`contact_id`),
 KEY `FK_civicrm_contribution_contribution_page_id` (`contribution_page_id`),
 KEY `FK_civicrm_contribution_contribution_recur_id` (`contribution_recur_id`),
 KEY `FK_civicrm_contribution_honor_contact_id` (`honor_contact_id`),
 KEY `FK_civicrm_contribution_address_id` (`address_id`),
 KEY `FK_civicrm_contribution_campaign_id` (`campaign_id`),
 KEY `FK_civicrm_contribution_financial_type_id` (`financial_type_id`),
 CONSTRAINT `FK_civicrm_contribution_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`),
 CONSTRAINT `FK_civicrm_contribution_address_id` FOREIGN KEY (`address_id`) REFERENCES `civicrm_address` (`id`) ON DELETE SET NULL,
 CONSTRAINT `FK_civicrm_contribution_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL,
 CONSTRAINT `FK_civicrm_contribution_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
 CONSTRAINT `FK_civicrm_contribution_contribution_page_id` FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page` (`id`) ON DELETE SET NULL,
 CONSTRAINT `FK_civicrm_contribution_contribution_recur_id` FOREIGN KEY (`contribution_recur_id`) REFERENCES `civicrm_contribution_recur` (`id`) ON DELETE SET NULL,
 CONSTRAINT `FK_civicrm_contribution_honor_contact_id` FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=11704 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

show create table civicrm_financial_trxn;

CREATE TABLE `civicrm_financial_trxn` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Gift ID',  `trxn_date` datetime DEFAULT NULL,  `total_amount` decimal(20,2) NOT NULL COMMENT 'amount of transaction',  `fee_amount` decimal(20,2) DEFAULT NULL COMMENT 'actual processor fee if known - may be 0.',  `net_amount` decimal(20,2) DEFAULT NULL COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.',  `currency` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',  `trxn_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,  `trxn_result_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'processor result code',  `from_financial_account_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to financial_account table.',  `to_financial_account_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to financial_financial_account table.',  `status_id` int(10) unsigned DEFAULT NULL,  `payment_instrument_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to payment_instrument option group values',  `check_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,  `payment_processor_id` int(10) unsigned DEFAULT NULL COMMENT 'Payment Processor for this contribution Page',  PRIMARY KEY (`id`),  KEY `UI_ftrxn_check_number` (`check_number`),  KEY `UI_ftrxn_payment_instrument_id` (`payment_instrument_id`),  KEY `FK_civicrm_financial_trxn_to_financial_account_id` (`to_financial_account_id`),  KEY `FK_civicrm_financial_trxn_from_financial_account_id` (`from_financial_account_id`),  KEY `FK_civicrm_financial_trxn_payment_processor_id` (`payment_processor_id`),  CONSTRAINT `FK_civicrm_financial_trxn_from_financial_account_id` FOREIGN KEY (`from_financial_account_id`) REFERENCES `civicrm_financial_account` (`id`),  CONSTRAINT `FK_civicrm_financial_trxn_payment_processor_id` FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor` (`id`) ON DELETE SET NULL,  CONSTRAINT `FK_civicrm_financial_trxn_to_financial_account_id` FOREIGN KEY (`to_financial_account_id`) REFERENCES `civicrm_financial_account` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=16527 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

SELECT VERSION();

5.1.63-0+squeeze1

SELECT @@GLOBAL.sql_mode;
This seems to be blank...

Dave, thanks for looking into this and reassuring me that it should be working.

In the end, I've recreated the database table from a copy and this now seems to work fine. The table must have just got damaged in the upgrade to 4.3.0

Stuart - really bizarre. Might be worth doing 'show create table' and 'describe' on that table before and after you 'fix' it - and doing a diff on the outputs to see if anything shows up.

Hi Martin,

Just done this myself again on updating to 4.3.3

I used phpmyadmin to create a copy of each of these 2 tables. Then I renamed them to swap the new copy with the old version. Simply creating the copy of each table somehow seemed to fix it.

Copying the old versions of these tables might work if there has been no new entries added in the meantime.

As I'm not into the development of Civi, so I don't know whether the table structure has changed between those two versions.

Hope that helps.

Stuart

Unfortunately that has not worked for me. Perhaps I need to open a new forum post to see if I can get this issue resolved.

Cheers
Martin Fuggle