[PARTIAL SOLVE] Survey: can't interview people or see survey dashboard in 4.4.0

[ken]

[This problem is partly due to 2 columns not being added to civicrm_report_instance in an earlier upgrade. The incremental upgrade SQL doesn't include these columns.]

[There is a residual issue in that the SQL doesn't use domain_id, so multisites may have issues.]

When I go to ...
    CiviCRM > Campaigns > Conduct Survey > Interview Respondents
... I get an error. The logs say ...

Nov 01 10:56:13  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [ 0 ] => CRM_Core_Error
            [ 1 ] => handle
        )

    [ code ] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [nativecode=1054 ** Unknown column 'name' in 'where clause']
    [type] => DB_Error
    [user_info] => SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [nativecode=1054 ** Unknown column 'name' in 'where clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [nativecode=1054 ** Unknown column 'name' in 'where clause']"])


Nov 01 10:56:13  [info] $backTrace = #0 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/Error.php(197): CRM_Core_Error::backtrace("backTrace",
TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/xxx/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /var/www/xxx/sites/all/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [n...")
#4 /var/www/xxx/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [n...")
#5 /var/www/xxx/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [n...", "DB_Error", TRUE)
#6 /var/www/xxx/sites/all/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column 'name' in 'where clause'")
#7 /var/www/xxx/sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /var/www/xxx/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16'")
#9 /var/www/xxx/sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16'")
#10 /var/www/xxx/sites/all/modules/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16'")
#11 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/DAO.php(158): DB_DataObject->query("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16'")
#12 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/DAO.php(947): CRM_Core_DAO->query("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16'", TRUE)
#13 /var/www/xxx/sites/all/modules/civicrm/CRM/Campaign/BAO/Survey.php(879): CRM_Core_DAO::singleValueQuery("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = %1", (Array:1))
#14 /var/www/xxx/sites/all/modules/civicrm/CRM/Campaign/Form/Task/Interview.php(237): CRM_Campaign_BAO_Survey::getReportID("16")
#15 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/Form.php(351): CRM_Campaign_Form_Task_Interview->preProcess()
#16 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Display.php(93): CRM_Core_Form->buildForm()
#17 /var/www/xxx/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Display->perform(Object(CRM_Campaign_Form_Task_Interview), "display")
#18 /var/www/xxx/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Campaign_Form_Task_Interview), "display")
#19 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/Controller.php(345): HTML_QuickForm_Page->handle("display")
#20 /var/www/xxx/sites/all/modules/civicrm/CRM/Campaign/Page/Vote.php(65): CRM_Core_Controller->run()
#21 /var/www/xxx/sites/all/modules/civicrm/CRM/Campaign/Page/Vote.php(81): CRM_Campaign_Page_Vote->interview()
#22 /var/www/xxx/sites/all/modules/civicrm/CRM/Campaign/Page/Vote.php(96): CRM_Campaign_Page_Vote->browse()
#23 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/Invoke.php(323): CRM_Campaign_Page_Vote->run((Array:3), NULL)
#24 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/Invoke.php(72): CRM_Core_Invoke::runItem((Array:14))
#25 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#26 /var/www/xxx/sites/all/modules/civicrm/drupal/civicrm.module(436): CRM_Core_Invoke::invoke((Array:3))
#27 [internal function](): civicrm_invoke("campaign", "vote")
#28 /var/www/xxx/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:2))
#29 /var/www/xxx/index.php(21): menu_execute_active_handler()
#30 {main}

I get a similar error when I view ...
    CiviCRM > Campaigns > Dashboard > Surveys
... and the logs contain ...

Nov 01 14:16:44  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [ 0 ] => CRM_Core_Error
            [ 1 ] => handle
        )

    [ code ] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [nativecode=1054 ** Unknown column 'name' in 'where clause']
    [type] => DB_Error
    [user_info] => SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [nativecode=1054 ** Unknown column 'name' in 'where clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [nativecode=1054 ** Unknown column 'name' in 'where clause']"])


Nov 01 14:16:44  [info] $backTrace = #0 /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Error.php(197): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/xxx/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /var/www/xxx/sites/all/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [n...")
#4 /var/www/xxx/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [n...")
#5 /var/www/xxx/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16' [n...", "DB_Error", TRUE)
#6 /var/www/xxx/sites/all/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column 'name' in 'where clause'")
#7 /var/www/xxx/sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /var/www/xxx/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16'")
#9 /var/www/xxx/sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16'")
#10 /var/www/xxx/sites/all/modules/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16'")
#11 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/DAO.php(158): DB_DataObject->query("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16'")
#12 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/DAO.php(947): CRM_Core_DAO->query("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = 'survey_16'", TRUE)
#13 /var/www/xxx/sites/all/modules/civicrm/CRM/Campaign/BAO/Survey.php(879): CRM_Core_DAO::singleValueQuery("SELECT MAX(id) as id FROM civicrm_report_instance WHERE name = %1", (Array:1))
#14 /var/www/xxx/sites/all/modules/civicrm/CRM/Campaign/BAO/Survey.php(805): CRM_Campaign_BAO_Survey::getReportID(16)
#15 /var/www/xxx/sites/all/modules/civicrm/CRM/Campaign/Page/DashBoard.php(331): CRM_Campaign_BAO_Survey::buildPermissionLinks(16, TRUE, "more")
#16 /var/www/xxx/sites/all/modules/civicrm/CRM/Campaign/Page/AJAX.php(729): CRM_Campaign_Page_DashBoard::getSurveySummary((Array:4))
#17 [internal function](): CRM_Campaign_Page_AJAX::surveyList((Array:42))
#18 /var/www/xxx/sites/all/modules/civicrm/CRM/Utils/REST.php(329): call_user_func((Array:2), (Array:42))
#19 /var/www/xxx/sites/all/modules/civicrm/CRM/Utils/REST.php(580): CRM_Utils_REST::process((Array:1), FALSE)
#20 [internal function](): CRM_Utils_REST::ajax((Array:3))
#21 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/Invoke.php(289): call_user_func((Array:2), (Array:3))
#22 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/Invoke.php(72): CRM_Core_Invoke::runItem((Array:12))
#23 /var/www/xxx/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#24 /var/www/xxx/sites/all/modules/civicrm/drupal/civicrm.module(436): CRM_Core_Invoke::invoke((Array:3))
#25 [internal function](): civicrm_invoke("ajax", "rest")
#26 /var/www/xxx/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:2))
#27 /var/www/xxx/index.php(21): menu_execute_active_handler()
#28 {main}

[ken]

Some observations ...

• CRM_Campaign_BAO_Survey::getReportID() is being called with the Survey's id
• It is being called WITHOUT the Domain Id. This is sure to cause a problem for multi-sites

[ken]

BTW, I upgraded from 4.2.7 to 4.4.0 and my database has no 'name' column in the 'civicrm_report_instance' table.

I suspect the table needs to be updated in the /CRM/Upgrade/Incremental scripts. And the domain issue needs sorting.

[ken]

It turns out that I'm missing 2 columns in civicrm_report_instance. I guess that in an earlier upgrade, the SQL to add those columns was missing. The following SQL fixes my issue...

Code: [Select]

ALTER TABLE `civicrm_report_instance`
     ADD `name` varchar(255)    COMMENT 'when combined with report_id/template uniquely identifies the instance' AFTER `report_id`,
     ADD `args` varchar(255)    COMMENT 'arguments that are passed in the url when invoking the instance' AFTER `name`;