Changeset ::20110825-1000-creating-providers-for-persons-from-encounter::raff fails

k.joseph · April 13, 2016, 10:23am

I have made upgrades in the past from 1.6.x to 1.11.x which were successful, however for one of the most recent upgrades i am now working on, i have fallen into

Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set liquibase-update-to-latest.xml::20110825-1000-migrating-providers-to-encounter-provider::raff:

which is as a result of the above failing Changeset;

github.com

openmrs/openmrs-core/blob/1.11.5/api/src/main/resources/liquibase-update-to-latest.xml#L6022


				select distinct person.person_id, user.system_id, 1, CURRENT_TIMESTAMP, user.retired, user.retired_by, user.date_retired, user.retire_reason, CONCAT('prov', SUBSTRING(person.uuid, 5))
				from person as person
					inner join encounter as encounter on encounter.provider_id = person.person_id
					left join (select max(users.user_id) as user_id, users.system_id, users.person_id, users.retired, users.retired_by, users.date_retired, users.retire_reason 
							from users as users group by users.person_id) as user 
						on person.person_id = user.person_id
				where user.user_id is not null and encounter.provider_id not in (select person_id from provider);
        </sql>
	</changeSet>
    
    <changeSet id="20110825-1000-migrating-providers-to-encounter-provider" author="raff" dbms="mysql">
    	<preConditions onFail="MARK_RAN">
			<columnExists tableName="encounter" columnName="provider_id"/>
		</preConditions>
		<comment>Migrating providers from the encounter table to the encounter_provider table</comment>
		<sql>			
			insert into encounter_provider(provider_id, encounter_id, encounter_role_id, creator, date_created, voided, uuid) 
				select distinct provider.provider_id, encounter.encounter_id, encounter_role.encounter_role_id, 
					encounter.creator, encounter.date_created, encounter.voided, encounter.uuid
				from encounter as encounter 
					inner join encounter_role as encounter_role

At first i thought it was my MySQL version (5.6, provided by MariaDB 10.0) but found out it is not. I am manually editing this SQL through MySQL client to use insert ignore to avoid checking for duplicates, since it fails in this manner even when there was no entries in encounter_provider after which i manually delete provider_id column from encounter table to skip these two consecutive change-sets.

Is this a personal problem or within the changeset @raff?

dkayiwa · April 13, 2016, 10:28am

You need to provide more of the stack trace.

k.joseph · April 13, 2016, 10:29am

http://pastebin.com/9pJsEyNL

dkayiwa · April 13, 2016, 6:16pm

As a way of troubleshooting, execute the select statement alone without the insert and locate the duplicate records. This will help refine the next steps.

k.joseph · April 13, 2016, 6:39pm

Wondering why distinct keyword can’t handle this!!! i couldn’t see such duplicates easily when i was troubleshooting this so i skipped them with insert ignore

lluismf · April 13, 2016, 9:18pm

It needs an additional correlated subquery to ensure the rows to insert do not exist already. The distinct clause simply removes duplicates from the select.

insert into encounter_provider(provider_id, encounter_id, encounter_role_id, creator, date_created, voided, uuid) 
select distinct provider.provider_id, encounter.encounter_id, encounter_role.encounter_role_id, 
	  encounter.creator, encounter.date_created, encounter.voided, encounter.uuid
from encounter as encounter inner join encounter_role as encounter_role 
	on encounter_role.uuid = "a0b03050-c99b-11e0-9572-0800200c9a66"
	inner join provider as provider on encounter.provider_id = provider.person_id
where encounter.encounter_id not in (select encounter_id from encounter_provider) AND
                   NOT EXISTS (SELECT 1 from encounter_provider ep
                               WHERE  ep.provider_id = provider.provider_id AND
                                      ep.encounter_id = encounter.encounter_id AND
                                      ep.encounter_role_id = encounter_role.encounter_role_id )