Reporting Tables Preventing Platform Upgrade

pascal · June 21, 2016, 1:44pm

@mseaton and other #reporting experts, I think the eSaude Community might have run into an edge case that is causing some issues when upgrading our Platform version.

This particular implementation was initially configured using Platform v1.9.2 and Reporting v0.7.6.

The issue we’re having is due to this foreign key constraint that references a UUID:

ALTER table `reporting_report_design` ADD CONSTRAINT `report_definition_uuid for reporting_report_design` FOREIGN KEY (`report_definition_uuid`) REFERENCES `serialized_object` (`uuid`)

It seems that we moved to liquibase in Reporting v0.7.7, and at the same time, the constraint was changed (back) to referencing the serialized_object_id column:

<addForeignKeyConstraint baseTableName="reporting_report_design" baseColumnNames="report_definition_id" constraintName="report_definition_id for reporting_report_design" referencedTableName="serialized_object" referencedColumnNames="serialized_object_id" deferrable="false" initiallyDeferred="false" />

I can’t find a liquibase changeset that should change my existing FK back to using serialized_object_id instead of uuid.

My pre-upgrade reporting_report_design tables looks like:

CREATE TABLE `reporting_report_design` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` char(38) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(1000) DEFAULT NULL,
  `renderer_type` varchar(255) NOT NULL,
  `properties` text,
  `creator` int(11) NOT NULL DEFAULT '0',
  `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `changed_by` int(11) DEFAULT NULL,
  `date_changed` datetime DEFAULT NULL,
  `retired` tinyint(1) NOT NULL DEFAULT '0',
  `retired_by` int(11) DEFAULT NULL,
  `date_retired` datetime DEFAULT NULL,
  `retire_reason` varchar(255) DEFAULT NULL,
  `report_definition_uuid` varchar(38) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `creator for reporting_report_design` (`creator`) USING BTREE,
  KEY `changed_by for reporting_report_design` (`changed_by`) USING BTREE,
  KEY `retired_by for reporting_report_design` (`retired_by`) USING BTREE,
  KEY `report_definition_uuid for reporting_report_design` (`report_definition_uuid`) USING BTREE,
  CONSTRAINT `reporting_report_design_ibfk_1` FOREIGN KEY (`changed_by`) REFERENCES `users` (`user_id`),
  CONSTRAINT `reporting_report_design_ibfk_2` FOREIGN KEY (`creator`) REFERENCES `users` (`user_id`),
  CONSTRAINT `reporting_report_design_ibfk_3` FOREIGN KEY (`report_definition_uuid`) REFERENCES `serialized_object` (`uuid`),
  CONSTRAINT `reporting_report_design_ibfk_4` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=86 DEFAULT CHARSET=utf8

First, I’m not quite sure how the constraints all have default names, since I can see that they are given explicit names in the sqldiff files and liquibase changesets.

Second, when I try to upgrade to Platform 1.11.6, it fails on this change:

<addNotNullConstraint tableName="serialized_object" columnName="uuid" columnDataType="char(38)" />

with:

Error executing SQL alter table reporting_report_design modify report_definition_uuid char(38) not null: Cannot change column 'report_definition_uuid': used in a foreign key constraint 'reporting_report_design_ibfk_3'

To work around this for now, we’re just deleting the reporting_report_design_ibfk_3 constraint, but I guess this could cause data integrity issues. Would it be better to set changset 20160202-1743 as MARK_RAN?

Both of the above workarounds seem less than ideal, since we could have many (100+) sites in the field with this issue, and we’d prefer not to ask our implementers to manually perform database operations if possible.

Any thoughts on this would be greatly appreciated .

mseaton · June 21, 2016, 8:35pm

Hey @pascal,

Happy to help brainstorm this. I’m curious to know how the database got in this state to begin with. I just created a new environment from scratch using reporting 0.7.6, built from the github tag, to see what the table structure looks like, and it looks like this:

show CREATE TABLE reporting_report_design;

CREATE TABLE `reporting_report_design` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` char(38) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(1000) DEFAULT NULL,
  `renderer_type` varchar(255) NOT NULL,
  `properties` text,
  `creator` int(11) NOT NULL DEFAULT '0',
  `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `changed_by` int(11) DEFAULT NULL,
  `date_changed` datetime DEFAULT NULL,
  `retired` tinyint(1) NOT NULL DEFAULT '0',
  `retired_by` int(11) DEFAULT NULL,
  `date_retired` datetime DEFAULT NULL,
  `retire_reason` varchar(255) DEFAULT NULL,
  `report_definition_uuid` varchar(38) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `creator for reporting_report_design` (`creator`),
  KEY `changed_by for reporting_report_design` (`changed_by`),
  KEY `retired_by for reporting_report_design` (`retired_by`),
  KEY `report_definition_uuid for reporting_report_design` (`report_definition_uuid`),
  CONSTRAINT `changed_by for reporting_report_design` FOREIGN KEY (`changed_by`) REFERENCES `users` (`user_id`),
  CONSTRAINT `creator for reporting_report_design` FOREIGN KEY (`creator`) REFERENCES `users` (`user_id`),
  CONSTRAINT `report_definition_uuid for reporting_report_design` FOREIGN KEY (`report_definition_uuid`) REFERENCES `serialized_object` (`uuid`),
  CONSTRAINT `retired_by for reporting_report_design` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Mike

pascal · June 22, 2016, 9:04am

Me too! The only ideas I have are:

The original MySQL version used didn’t support named constraints
The database was imported/exported using a flag or tool that ignored constraint names

The first option seems unlikely, but I’ll try to get more information from the implementation site.

Having a look at the first version of our database, it seems that the constraints are correctly named, so I’m a little stumped. I guess having incorrectly named constraints will have other effects, since the liquibase changesets all refer to the constraints by name.

Any ideas on how to recover from something like this @mseaton?

EDIT: @mseaton, if you upgrade the version in your new environment, does the FK that references serialized_object by uuid ever get updated to reference it by serialzed_object_id instead? If you try to upgrade to platform v1.11.6, do you see the same error that we do?

mseaton · June 22, 2016, 6:22pm

@pascal - I bumped up reporting to the latest version and am getting this error at startup which seems to confirm your findings:

SEVERE 6/22/16 2:18 PM:liquibase: Change Set liquibase.xml::reporting_report_design_3::mseaton failed. Error: Error executing SQL alter table reporting_report_design modify report_definition_uuid char(38) not null: Cannot change column ‘report_definition_uuid’: used in a foreign key constraint ‘report_definition_uuid for reporting_report_design’ liquibase.exception.DatabaseException: Error executing SQL alter table reporting_report_design modify report_definition_uuid char(38) not null: Cannot change column ‘report_definition_uuid’: used in a foreign key constraint ‘report_definition_uuid for reporting_report_design’

Looks like something we’ll need to ticket and fix…

Mike

mseaton · June 22, 2016, 6:24pm

@pascal, also, as for ideas as to how to clean this up - that seems tricky. Is there a way to specify that a module should start before another module? I think @darius was trying to add something like this in at some point. If there is, you could use liquibase in a small custom module to clean things up before the reporting module tries to start.

Mike

pascal · July 19, 2016, 10:44am

Thanks for your support @mseaton. The key naming issue is definitely something we need to resolve on our end, very likely with a custom module that we’ll need to install prior to upgrading.

I’ve created the following ticket to address the upgrade issue we’ve confirmed above: