DB Error: no such field when running a report

[michael-chaplin-gmcvo]

When running a gift aid report get: DB Error: no such field
Civicrm 4.6.36
uk.co.compucorp.civicrm.giftaid 3.0.1

Backtrace:

#0 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(182): CRM_Core_Error::backtrace()
#1 internal function: CRM_Core_Error::handle(Object(DB_Error))
#2 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/DB.php(976): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "SELECT SQL_CALC_FOUND_ROWS entity_batch_civireport.batch_id as civicrm_entity...")
#4 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "SELECT SQL_CALC_FOUND_ROWS entity_batch_civireport.batch_id as civicrm_entity...")
#5 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "SELECT SQL_CALC_FOUND_ROWS entity_batch_civireport.batch_id as civicrm_entity...", "DB_Error", TRUE)
#6 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/DB/mysql.php(895): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column 'value_gift_aid_submission_civireport.amount' in 'wher...")
#7 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
#8 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT SQL_CALC_FOUND_ROWS entity_batch_civireport.batch_id as civicrm_entity...")
#9 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(2442): DB_common->query("SELECT SQL_CALC_FOUND_ROWS entity_batch_civireport.batch_id as civicrm_entity...")
#10 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(1634): DB_DataObject->_query("SELECT SQL_CALC_FOUND_ROWS entity_batch_civireport.batch_id as civicrm_entity...")
#11 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(321): DB_DataObject->query("SELECT SQL_CALC_FOUND_ROWS entity_batch_civireport.batch_id as civicrm_entity...")
#12 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(1181): CRM_Core_DAO->query("SELECT SQL_CALC_FOUND_ROWS entity_batch_civireport.batch_id as civicrm_entity...", TRUE)
#13 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Report/Form.php(2814): CRM_Core_DAO::executeQuery("SELECT SQL_CALC_FOUND_ROWS entity_batch_civireport.batch_id as civicrm_entity...")
#14 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Report/Form.php(3289): CRM_Report_Form->buildRows("SELECT SQL_CALC_FOUND_ROWS entity_batch_civireport.batch_id as civicrm_entity...", (Array:0))
#15 /home/XXXXXXX/public_html/sites/default/files/civicrm/extensions/uk.co.compucorp.civicrm.giftaid-civi4.5/CRM/Civigiftaid/Report/Form/Contribute/GiftAid.php(350): CRM_Report_Form->postProcess()
#16 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Report/Form.php(753): CRM_Civigiftaid_Report_Form_Contribute_GiftAid->postProcess()
#17 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Core/Form.php(455): CRM_Report_Form->preProcess()
#18 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Display.php(94): CRM_Core_Form->buildForm()
#19 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Display->perform(Object(CRM_Civigiftaid_Report_Form_Contribute_GiftAid), "display")
#20 /home/XXXXXXX/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Civigiftaid_Report_Form_Contribute_GiftAid), "display")
#21 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Core/Controller.php(353): HTML_QuickForm_Page->handle("display")
#22 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(115): CRM_Core_Controller->run()
#23 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Report/Page/Instance.php(101): CRM_Utils_Wrapper->run("CRM_Civigiftaid_Report_Form_Contribute_GiftAid", NULL, NULL)
#24 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(312): CRM_Report_Page_Instance->run((Array:4), NULL)
#25 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:14))
#26 /home/XXXXXXX/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:4))
#27 /home/XXXXXXX/public_html/sites/all/modules/civicrm/drupal/civicrm.module(482): CRM_Core_Invoke::invoke((Array:4))
#28 internal function: civicrm_invoke("report", "instance", "388")
#29 /home/XXXXXXX/public_html/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:3))
#30 /home/XXXXXXX/public_html/index.php(21): menu_execute_active_handler()
#31 {main}

The column 'value_gift_aid_submission_civireport.amount' doesn't exist in the db. It should be 'value_gift_aid_submission.amount'

[michael-chaplin-gmcvo]

Bump

[guanhuan]

Hi @mikantchap, thanks for reporting this.

Is this is new GiftAid installation or were you previously on a lower version of GiftAid?

[michael-chaplin-gmcvo]

Previously on a lower version of GiftAid. Got the error so updated to 3.0.1 - still have the error.

[michael-chaplin-gmcvo]

Is it worth simply re-naming the 'value_gift_aid_submission_civireport' table to 'value_gift_aid_submission' ?

[guanhuan]

@mikantchap what was the version you are on previously that works for you? Or you started with version 3 and it never worked on your installation?

[michael-chaplin-gmcvo]

The customer says the reports used to work. I have upgraded so I'm not sure what the previous version was (although it was broken at that point anyway).

The extension folder is uk.co.compucorp.civicrm.giftaid-civi4.5 if that's any help?

[michael-chaplin-gmcvo]

Ok so does this extension create tables? If so, what name(s)?

If it doesn't create tables, can we uninstall it without losing data?

And then do a clean install (get a new report template) and create new reports from the (newly installed) template?

[guanhuan]

Hi @mikantchap,

Sounds like your customer were on an earlier alpha/ beta version.

This extension does install tables in order to keep the GiftAid related data.

All extensions (normally) should remove all tables they added upon uninstallation. Although GiftAid does not remove all tables but we would not advise uninstallation as a safe option.

[michael-chaplin-gmcvo]

Hi So what's the fix?

I tried renaming the table

civicrm_value_gift_aid_submission
to
civicrm_value_gift_aid_submission_civireport

but am still getting the above error, even though the named column now exists.

Any ideas? Thanks Mike

[guanhuan]

Hi Mike,

value_gift_aid_submission_civireport is not a schema that the GiftAid extension creates. Also we haven't had any similar issue on our standard installation and client sites.

This is probably a client specific issue. We would be happy to help but we may need to assign some developer resource to investigate this and there may be some costs involved.

[michael-chaplin-gmcvo]

Hi

The extension provides a template "Gift Aid Report".

The extension is an up to date version.

If I try to create a new report from this template, it errors. The problem when previewing the report is

Unknown column 'value_gift_aid_submission_civireport.amount

Surely the problem is in the extension?

Mike

[guanhuan]

Hi Mike,

Not sure if this is directly related to the issue you are facing but I want to mention that there is no automated upgrade script for GiftAid version 2 to upgrade to GiftAid version 3 since there is a audit method change between this two version which will require some manual checks.

You can find more information about the changes about "3.0" in readme.

Sounds like you might be on a alpha/ beta version earlier also so that could be a cause. As I mentioned we have not faced any similar issue before but if you could reproduce this on a clean install, we would assign some resource to look into this asap.

Best

[michael-chaplin-gmcvo]

Hi Guanhuan

I re-instated version 2.2 and was able to create new reports.

So there's no upgrade path from 2.2 to 3.01?

I get an invitation to upgrade to 3.01 on the extension page - I guess this is where the problems started.

cheers Mike

[guanhuan]

Hi Mike,

since there is a audit method change between this two version which will require some manual checks.

There is no automated process for upgrading 2.2 to 3.0.1.

I get an invitation to upgrade to 3.01 on the extension page - I guess this is where the problems started.

That does sound like a problem. We will conduct some investigations and see if there is a way we can work around this notice or notify users. Thanks for mentioning this.