Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Major
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.4.3
-
Fix Version/s: 4.4.4
-
Component/s: None
-
Labels:None
Description
The following diff shows the missing fields (in some cases, just added comments) in an upgraded instance.
— update.report_instance.sql 2013-12-17 13:30:13.000000000 -0500
+++ install.report_instance.sql 2013-12-17 13:33:05.000000000 -0500
@@ -3,9 +3,11 @@
`domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this instance for',
`title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Report Instance Title.',
`report_id` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'FK to civicrm_option_value for the report template',
+ `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'when combined with report_id/template uniquely identifies the instance',
+ `args` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'arguments that are passed in the url when invoking the instance',
`description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Report Instance description.',
`permission` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'permission required to be able to run this instance',
- `grouprole` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL,
+ `grouprole` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'role required to be able to run this instance',
`form_values` text COLLATE utf8_unicode_ci COMMENT 'Submitted form values for this report',
`is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this entry active?',
`email_subject` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Subject of email',
@@ -14,8 +16,8 @@
`header` text COLLATE utf8_unicode_ci COMMENT 'comma-separated list of email addresses to send the report to',
`footer` text COLLATE utf8_unicode_ci COMMENT 'comma-separated list of email addresses to send the report to',
`navigation_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to navigation ID', - `is_reserved` tinyint(4) DEFAULT '0',
`drilldown_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to instance ID drilldown to',
+ `is_reserved` tinyint(4) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `FK_civicrm_report_instance_domain_id` (`domain_id`),
KEY `FK_civicrm_report_instance_navigation_id` (`navigation_id`),
@@ -23,4 +25,4 @@
CONSTRAINT `FK_civicrm_report_instance_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`),
CONSTRAINT `FK_civicrm_report_instance_drilldown_id` FOREIGN KEY (`drilldown_id`) REFERENCES `civicrm_report_instance` (`id`) ON DELETE SET NULL,
CONSTRAINT `FK_civicrm_report_instance_navigation_id` FOREIGN KEY (`navigation_id`) REFERENCES `civicrm_navigation` (`id`) ON DELETE SET NULL
Attachments
Activity
Michael - I did a quick experiment trying to run part of the query above w/in our framework and it looks like our DAO doesn't support the EXECUTE stmt; syntax.
I'm not sure that we use the 'name' and 'args' columns at all, and they were added to new installs in 3.2 but never added for upgrades so lots of sites don't have them. Did this come up because one of your sites threw an error related to the missing columns or ???
In any case, I think the best approach for the two potentially missing columns would be to use the pattern we've used in the past to check whether a column exists and add it conditionally. Refer to CRM/Upgrade/Incremental/php/FourTwo.php lines 872-881 for a good example.
The COMMENT fix could be handled by adding your ALTER TABLE statement to 4.4.4.mysql.tpl
Would be great if you could add put together a PR for this against 4.4 (if this causes actual errors) OR against master (if this is strictly for consistency).
DGG: I think I found this when installing CiviVolunteer, and yes it threw an error. We probably thought that those fields were required and aren't actually using them.
Does that say PR against master or 4.4 to you?
PR against 4.4 - https://github.com/civicrm/civicrm-core/pull/2364
Replacement PR at https://github.com/civicrm/civicrm-core/pull/2369
– /*** add civicrm_report_instance.name ***/
SELECT count
INTO @exist FROM information_schema.columns
WHERE table_schema = database() AND table_name = 'civicrm_report_instance' AND COLUMN_NAME = 'name'
;
set @query = IF(@exist <= 0, 'ALTER TABLE civicrm_report_instance ADD `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT \'when combined with report_id/template uniquely identifies the instance\'',
'select \'Column Exists\'');
prepare stmt from @query;
EXECUTE stmt;
– /*** add civicrm_report_instance.args ***/
SELECT count
INTO @exist FROM information_schema.columns
WHERE table_schema = database() AND table_name = 'civicrm_report_instance' AND COLUMN_NAME = 'args'
;
set @query = IF(@exist <= 0,
'ALTER TABLE civicrm_report_instance ADD `args` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT \'arguments that are passed in the url when invoking the instance\'',
'select \'Column Exists\'');
prepare stmt from @query;
EXECUTE stmt;
– /*** update comment for civicrm_report_instance.grouprole ***/
ALTER TABLE civicrm_report_instance MODIFY grouprole varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'role required to be able to run this instance';