3

EDIT: This question turned out to be, in practical terms, something different than I originally thought I was asking. I am changing the title so that others will find the answer more easily in the future.

I've got CiviCRM 5.10.4 running on Drupal 7.66, and one particular function ("Subscriber Donor Report") is blowing up on an underlying SQL error. I turned on debugging, and following is what I see. Looks like a hard-core internals problem that I could never deal with on my own. Incidentally, this problem has persisted across several updates of Civi (since 5.4 maybe?) and also Drupal (at least since 7.60). Is there any hope? Could this possibly be caused by some weird data in the db? I wouldn't normally think so. Thanks.

Database Error Code: The used SELECT statements have a different number of columns, 1222
Additional Details:
Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
(SELECT * FROM civicrm_tmp_e_dflt_0441f87ccf495b6b8c955d3a4407fde6)
UNION ALL
(SELECT * FROM civireport_contribution_detail_temp2) [nativecode=1222 ** The used SELECT statements have a different number of columns]
    [type] => DB_Error
    [user_info] => CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
(SELECT * FROM civicrm_tmp_e_dflt_0441f87ccf495b6b8c955d3a4407fde6)
UNION ALL
(SELECT * FROM civireport_contribution_detail_temp2) [nativecode=1222 ** The used SELECT statements have a different number of columns]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
(SELECT * FROM civicrm_tmp_e_dflt_0441f87ccf495b6b8c955d3a4407fde6)
UNION ALL
(SELECT * FROM civireport_contribution_detail_temp2) [nativecode=1222 ** The used SELECT statements have a different number of columns]"]
)

#0 <path>/sites/all/modules/civicrm/CRM/Core/Error.php(190): CRM_Core_Error::backtrace()
#1 <path>/sites/all/modules/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 <path>/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
#3 <path>/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
#4 <path>/sites/all/modules/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", "DB_Error", TRUE)
#5 <path>/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 <path>/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", "1222 ** The used SELECT statements have a different number of columns")
#7 <path>/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 <path>/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
#9 <path>/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
#10 <path>/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
#11 <path>/sites/all/modules/civicrm/CRM/Core/DAO.php(438): DB_DataObject->query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
#12 <path>/sites/all/modules/civicrm/CRM/Core/DAO.php(1413): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", TRUE)
#13 <path>/sites/all/modules/civicrm/CRM/Report/Form.php(3710): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...", (Array:0))
#14 <path>/sites/all/modules/civicrm/CRM/Report/Form/Contribute/Detail.php(568): CRM_Report_Form->executeReportQuery("CREATE TEMPORARY TABLE civireport_contribution_detail_temp3  DEFAULT CHARACTE...")
#15 <path>/sites/all/modules/civicrm/CRM/Report/Form.php(2816): CRM_Report_Form_Contribute_Detail->beginPostProcessCommon()
#16 <path>/sites/all/modules/civicrm/CRM/Report/Form.php(3473): CRM_Report_Form->beginPostProcess()
#17 <path>/sites/all/modules/civicrm/CRM/Report/Form.php(886): CRM_Report_Form->postProcess()
#18 <path>/sites/all/modules/civicrm/CRM/Core/Form.php(585): CRM_Report_Form->preProcess()
#19 <path>/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Display.php(92): CRM_Core_Form->buildForm()
#20 <path>/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Display->perform(Object(CRM_Report_Form_Contribute_Detail), "display")
#21 <path>/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Report_Form_Contribute_Detail), "display")
#22 <path>/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("display")
#23 <path>/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(113): CRM_Core_Controller->run()
#24 <path>/sites/all/modules/civicrm/CRM/Report/Page/Instance.php(89): CRM_Utils_Wrapper->run("CRM_Report_Form_Contribute_Detail", NULL, (Array:0))
#25 <path>/sites/all/modules/civicrm/CRM/Core/Invoke.php(309): CRM_Report_Page_Instance->run((Array:4), NULL)
#26 <path>/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:14))
#27 <path>/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#28 <path>/sites/all/modules/civicrm/drupal/civicrm.module(445): CRM_Core_Invoke::invoke((Array:4))
#29 <path>/includes/menu.inc(527): civicrm_invoke("report", "instance", "4")
#30 <path>/index.php(21): menu_execute_active_handler()
#31 {main}
Improve this question
1
  • 2
    First thing I'd try is recreating that contribution report from scratch and see if that fixes it.
    – Demerit
    Commented Apr 17, 2019 at 0:00

2 Answers 2

Reset to default
3

Although it's not ultimately an answer to why the code is blowing up, the simplest solution to my problem was found in the report's settings. Under the Sorting tab, the report was set to sort on Contribution Status. This is what caused the blow up. If I set it to sort on another column, like Contact Name, the report runs fine.

So now I have another, hopefully much simpler, problem: I cannot resave the report with the new sort setting. Really? You can't change the options in a report definition and then save them? I looked in the (apparently old) docs, which show clearly an Update Report button, which no longer exists. I also found this, which presumably was correct at the time, but didn't remain in the UI long. I don't have a Save item under my Actions menu. This is, at best, terrible UX. Can someone enlighten me? Or is the answer implicit in the above comment: reports are write-only, and if you need to make a change, you redo it from scratch?

Improve this answer
7
  • He he that isn't what I meant in my comment I've just found sometimes that's the cleanest. As to why there is no Save action under the Actions menu I'm not sure - it seems like there should be. I can see it on dmaster.demo.civicrm.org.
    – Demerit
    Commented Apr 24, 2019 at 23:04
  • Hey thanks for the pointer to dmaster... I was not aware of that, I now I can see that the Save option is there - for some of the people, some of the time ;=P I went looking for some sort of global setting saying "don't allow modification of reports" or such, and didn't find it. And I tried Create New Report From Template. I can set up the report options the same as my broken report, and even sort on Contribution Status, but... I can't save it.
    – Kotodharma
    Commented Apr 24, 2019 at 23:18
  • 1
    Check drupal permissions in the drupal admin pages - I can't remember off the top if there's one that controls this.
    – Demerit
    Commented Apr 24, 2019 at 23:19
  • 1
    It might be "CiviReport: save Report Criteria", at SITE/admin/people/permissions
    – Demerit
    Commented Apr 24, 2019 at 23:21
  • 2
    I've just run into this as well - it seems to have arrived with 5.7.6 update which is surprising. Specifically, the contribution/detail report now gives the error noted above when sorting by contribution status. So aside from the specific advice of changing the sort order, this would seem to me a bug that should be reported as an issue.
    – Alan Dixon
    Commented Jun 4, 2019 at 15:07
3

Check Drupal permissions at http://site/admin/people/permissions. It might be "CiviReport: save Report Criteria".

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.