Liquibase error when upgrading the module

If in upgrading the reporting module you get a liquibase error, then you probably have the problem that the constraint in the database were saved with ?'s in it. To see if that's the case go into your database and type the following

SELECT  * FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU WHERE   KCU.TABLE_SCHEMA = database() AND     KCU.TABLE_NAME = 'reporting_report_design';

 

If you see ? in any of the names of the constraints then you have the problem.  If you don't see any ?, then skip to part B

A. Here's the solution.

 

Before anything, make a backup of your database in case something happens

1. mysqldump -uUSER -p DBNAME > dbBackup.sql

where USER is your mysql database user and DBNAME is the database name, probably openmrs

 

Then fix the spaces and ? in the reporting_report_design table by doing the following

1. mysqldump -uUSER -p DBNAME reporting_report_design > reportTable.sql

where USER is your mysql database user and DBNAME is the database name, probably openmrs

 

2. Edit the reportTable.sql file with a text editor such as nano or notepad. Change only the following lines, otherwise you might lose your data

  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_id?for?reporting_report_design` FOREIGN KEY (`report_definition_id`) REFERENCES `serialized_object` (`serialized_object_id`),

  CONSTRAINT `retired_by?for?reporting_report_design` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`)

 

to the following

  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_id_for_reporting_report_design` FOREIGN KEY (`report_definition_id`) REFERENCES `serialized_object` (`serialized_object_id`),

  CONSTRAINT `retired_by_for_reporting_report_design` FOREIGN KEY (`retired_by`) REFERENCES `users` (`user_id`)

 

4.Put the information back into your database

mysql -uUSER -p DBNAME < reportTable.sql

 

B. Removing foreign key constraints

Now that the table is working fine, drop the restrictions that are causing the error. You may need to change the order of these statements.

alter table reporting_report_design drop foreign key report_definition_id_for_reporting_report_design;

alter table reporting_report_design drop key `report_definition_id for reporting_report_design`;

 

 

Now restart the module and it should work.