← Back to team overview

dhis2-devs team mailing list archive

Re: Schema missing many unique constraints

 

I know that openmrs makes use of liquibase for managing database diffs
: http://www.liquibase.org/

It might be worth considering whether this approach would be useful
for us in place of our "hibernate ddl + extra sql upgrade script" .
Student project?

On 23 July 2015 at 20:36, Morten Olav Hansen <mortenoh@xxxxxxxxx> wrote:
> If you see duplicate UIDs, you can get new fresh ones at:
> /api/system/uid?n=100
>
> --
> Morten
>
> On Fri, Jul 24, 2015 at 2:14 AM, Jason Pickering
> <jason.p.pickering@xxxxxxxxx> wrote:
>>
>> Hi Lorill,
>>
>> You can try and execute this script (at your own risk against a
>> non-production database)  and see if it solves the issues you are having. As
>> Morten points out, there is no guarantee it will work, since you have been
>> operating on a database without the constraints. But it may give you an
>> indication of where you have problems.
>>
>> Regards,
>> Jason
>>
>>
>> On Thu, Jul 23, 2015 at 8:41 PM, Morten Olav Hansen <mortenoh@xxxxxxxxx>
>> wrote:
>>>
>>> No, hibernate can't make those can't of changes automatically, which is
>>> why Jason created a more manual SQL script for it, hibernate is good at
>>> adding columns etc, but can't change the constraints of an already existing
>>> column.
>>>
>>> I agree it should be added on the upgrade page, but its been there since
>>> 2.15 or 2.16.. so we would need to create several scripts (as the schema has
>>> changed from those early versions), I will leave that up to Lars, as he also
>>> have these scripts at hand.
>>>
>>> --
>>> Morten
>>>
>>> On Fri, Jul 24, 2015 at 1:36 AM, Lorill Crees <lcrees@xxxxxxxxxx> wrote:
>>>>
>>>> Thanks Morten, looking forward to getting the script as that will save a
>>>> lot of manual effort.
>>>>
>>>> Perhaps the script could be included or referenced in the upgrade notes
>>>> here?
>>>>
>>>> https://github.com/dhis2/dhis2-utils/blob/master/resources/sql/upgrade-219.sql
>>>>
>>>> Also wondering - we're using the latest DHIS 2 (2.20) and hibernate
>>>> didn't automatically update the schema with these constraint changes. Is
>>>> this because there is still an issue with hibernate? Is this something we
>>>> will need to constantly look out for when upgrading versions?
>>>>
>>>> Thanks,
>>>>
>>>> Lorill
>>>>
>>>> On Thu, Jul 23, 2015 at 8:17 AM, Morten Olav Hansen <mortenoh@xxxxxxxxx>
>>>> wrote:
>>>>>
>>>>> Hi Lorill
>>>>>
>>>>> This is known issue with older versions of DHIS 2 (it was a serious bug
>>>>> in hibernate, which we use as our ORM layer).
>>>>>
>>>>> Jason created a script that will add them back (but please know that
>>>>> there might be conflicts, as uniqueness constraints are added)
>>>>>
>>>>> I'm adding him in, as I don't have access to that script right now.. he
>>>>> can hopefully share it
>>>>>
>>>>> --
>>>>> Morten
>>>>>
>>>>> On Thu, Jul 23, 2015 at 4:22 AM, Lorill Crees <lcrees@xxxxxxxxxx>
>>>>> wrote:
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> We've noticed that many of our databases that have been upgraded from
>>>>>> 2.18 to 2.19 and then to 2.20 are missing many of the unique constraints
>>>>>> that are present when creating a 2.20 database from a new install. I
>>>>>> isolated this issue to our upgrades from 2.18 to 2.19.
>>>>>>
>>>>>> As a test I created 3 databases (Postgres 9.4.1, Tomcat 8.0.21, Java
>>>>>> 1.8.0_31):
>>>>>>
>>>>>> I downloaded the 2.18 war, created database dhis2_218, then auto
>>>>>> generated the schema by starting up the app
>>>>>> I downloaded the 2.19 war, created database dhis2_219,  then auto
>>>>>> generated the schema by starting up the app
>>>>>> I made a copy of dhis2_218 as dhis2_219migrated,  ran the upgrade
>>>>>> script here:
>>>>>> https://github.com/dhis2/dhis2-utils/blob/master/resources/sql/upgrade-219.sql,
>>>>>> then ran the 2.19 war against it for it to be auto-updated
>>>>>> (hibernate.hbm2ddl.auto = update).
>>>>>>
>>>>>> Note that I did not populate any of these instances with data in this
>>>>>> test. When doing a schema diff between dhis2_219 and dhis2_219migrated,
>>>>>> there are a large number of differences in unique constraints and fk
>>>>>> constraints (see attached screenshot for a few examples: dhis2_219 is on the
>>>>>> left and dhis2_219migrated is on the right).
>>>>>>
>>>>>> Is there a step in the migration I am missing here? We're trying to
>>>>>> figure out how to ensure our existing instances have the correct schema and
>>>>>> constraints for 2.20. We probably also have data clashes that won't conform
>>>>>> to the unique constraints that need to be added so need to deal with that
>>>>>> too.
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>> Lorill
>>>>>>
>>>>>> _______________________________________________
>>>>>> Mailing list: https://launchpad.net/~dhis2-devs
>>>>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>
>>>>>
>>>>
>>>
>>
>>
>>
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+46764147049
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>


References