Re: [Dhis2-devs] Schema missing many unique constraints
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.picker...@gmail.com 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 morte...@gmail.com 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 lcr...@2paths.com 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 morte...@gmail.com 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 lcr...@2paths.com 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): 1. I downloaded the 2.18 war, created database *dhis2_218, *then auto generated the schema by starting up the app 2. I downloaded the 2.19 war, created database *dhis2_219, * then auto generated the schema by starting up the app 3. 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp -- Jason P. Pickering email: jason.p.picker...@gmail.com tel:+46764147049 ___ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
Re: [Dhis2-devs] Schema missing many unique constraints
Thanks Morten. On Thu, Jul 23, 2015 at 12:36 PM, Morten Olav Hansen morte...@gmail.com 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.picker...@gmail.com 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 morte...@gmail.com 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 lcr...@2paths.com 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 morte...@gmail.com 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 lcr...@2paths.com 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): 1. I downloaded the 2.18 war, created database *dhis2_218, *then auto generated the schema by starting up the app 2. I downloaded the 2.19 war, created database *dhis2_219, * then auto generated the schema by starting up the app 3. 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp -- Jason P. Pickering email: jason.p.picker...@gmail.com tel:+46764147049 ___ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
Re: [Dhis2-devs] 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 morte...@gmail.com 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.picker...@gmail.com 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 morte...@gmail.com 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 lcr...@2paths.com 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 morte...@gmail.com 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 lcr...@2paths.com 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp -- Jason P. Pickering email: jason.p.picker...@gmail.com tel:+46764147049 ___ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp ___ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
Re: [Dhis2-devs] Schema missing many unique constraints
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 lcr...@2paths.com 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): 1. I downloaded the 2.18 war, created database *dhis2_218, *then auto generated the schema by starting up the app 2. I downloaded the 2.19 war, created database *dhis2_219, * then auto generated the schema by starting up the app 3. 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp ___ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
Re: [Dhis2-devs] Schema missing many unique constraints
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 morte...@gmail.com 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 lcr...@2paths.com 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 morte...@gmail.com 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 lcr...@2paths.com 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): 1. I downloaded the 2.18 war, created database *dhis2_218, *then auto generated the schema by starting up the app 2. I downloaded the 2.19 war, created database *dhis2_219, * then auto generated the schema by starting up the app 3. 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp -- Jason P. Pickering email: jason.p.picker...@gmail.com tel:+46764147049 constraints.sql Description: application/sql ___ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
Re: [Dhis2-devs] Schema missing many unique constraints
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 morte...@gmail.com 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 lcr...@2paths.com 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): 1. I downloaded the 2.18 war, created database *dhis2_218, *then auto generated the schema by starting up the app 2. I downloaded the 2.19 war, created database *dhis2_219, * then auto generated the schema by starting up the app 3. 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp ___ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
Re: [Dhis2-devs] Schema missing many unique constraints
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 lcr...@2paths.com 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 morte...@gmail.com 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 lcr...@2paths.com 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): 1. I downloaded the 2.18 war, created database *dhis2_218, *then auto generated the schema by starting up the app 2. I downloaded the 2.19 war, created database *dhis2_219, * then auto generated the schema by starting up the app 3. 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp ___ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
