Moving to 2.16

Timothy_Harding · 7 October 2014 23:41

Hello Developer’s Group

I’m now working on migrating from 2.14 to 2.16 in our test environment and the startup for tomcat is hanging on step 7. I’ve included some relevant snippets of logs below and have attached the full logs:

stdout.txt = Standard out during importing the DHIS2 2.14 database, running the upgrade script, and starting tomcat)
catalina.out = The tomcat log for the latest attempt to start up 2.16
My Notes:

I can boot up 2.14 with the database dump without problem

I can boot up 2.16 with a blank database without problem (it creates the live environment i.e. admin:district)

The problem occurs when I try to boot up 2.16 with the 2.14 database (after I’ve run the 2.15 upgrade script found here: http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql)

It hangs at step 7 of 14 * INFO 2014-10-08 09:45:03,291 Executing startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java [localhost-startStop-1])

The system, for having such meager hardware, is really snappy with both the live version of 2.16, and our full production mirror of 2.14.

The com.hazelcast.util.HealthMonitor continues to function even after the initialization process hangs at step 7 of 14 (as you can see in catalina.out) Oct 08, 2014 10:36:58 AM com.hazelcast.util.HealthMonitor

Both Java and Postgres are using no cpu cycles after the hang, so it doesn’t feel like it is churning away at something:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

1413 dhis 20 0 1895572 570840 16532 S 1.0 44.7 1:48.39 java

906 postgres 20 0 523944 30260 28868 S 0.0 2.4 0:00.47 postgres

908 postgres 20 0 524704 77648 75724 S 0.0 6.1 0:01.04 postgres

909 postgres 20 0 524140 4304 2904 S 0.0 0.3 0:00.16 postgres

910 postgres 20 0 524140 14056 12656 S 0.0 1.1 0:00.50 postgres

911 postgres 20 0 524980 3068 1096 S 0.0 0.2 0:00.11 postgres

912 postgres 20 0 103712 1984 352 S 0.0 0.2 0:00.71 postgres

1432 postgres 20 0 526508 7656 4524 S 0.0 0.6 0:00.00 postgres

1433 postgres 20 0 529308 14604 8948 S 0.0 1.1 0:00.22 postgres

1434 postgres 20 0 536664 46672 32832 S 0.0 3.7 0:08.00 postgres

1904 postgres 20 0 525372 5652 3204 S 0.0 0.4 0:00.00 postgres

1906 postgres 20 0 525256 4860 2548 S 0.0 0.4 0:00.00 postgres

1907 postgres 20 0 525256 4856 2540 S 0.0 0.4 0:00.00 postgres

2114 postgres 20 0 525372 5600 3192 S 0.0 0.4 0:00.00 postgres

(there are a lot of postgres processes it seems like)

I saw some errors on startup but the notes for 2.16 says to expect a few the first time. I also saw some errors when I ran the import and 2.15 upgrade script.

Database import (grep for errors)

ERROR: must be owner of extension plpgsql

Upgrade script (grep for errors)

ERROR: table “patientaggregatereportmembers” does not exist

ERROR: table “patienttabularreportmembers” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientregistrationform_fixedattributes” does not exist

ERROR: table “patientregistrationform_identifiertypes” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientmobilesetting” does not exist

Any ideas? Is there an upgrade script I’ve forgotten to run or maybe a setting I’ve missed?

Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@gmail.com
+678 5955137

stdout.txt (21.4 KB)

catalina.out (31.2 KB)

jason · 8 October 2014 00:08

Hi Tim,

You may want to look here for the necessary upgrade script.

http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql

Regards,

Jason

···

On Oct 7, 2014 7:41 PM, “Timothy Harding” hardingt@gmail.com wrote:

Hello Developer’s Group

I’m now working on migrating from 2.14 to 2.16 in our test environment and the startup for tomcat is hanging on step 7. I’ve included some relevant snippets of logs below and have attached the full logs:

stdout.txt = Standard out during importing the DHIS2 2.14 database, running the upgrade script, and starting tomcat)

catalina.out = The tomcat log for the latest attempt to start up 2.16
My Notes:

I can boot up 2.14 with the database dump without problem

I can boot up 2.16 with a blank database without problem (it creates the live environment i.e. admin:district)

The problem occurs when I try to boot up 2.16 with the 2.14 database (after I’ve run the 2.15 upgrade script found here: http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql)

It hangs at step 7 of 14 * INFO 2014-10-08 09:45:03,291 Executing startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java [localhost-startStop-1])

The system, for having such meager hardware, is really snappy with both the live version of 2.16, and our full production mirror of 2.14.

The com.hazelcast.util.HealthMonitor continues to function even after the initialization process hangs at step 7 of 14 (as you can see in catalina.out) Oct 08, 2014 10:36:58 AM com.hazelcast.util.HealthMonitor

Both Java and Postgres are using no cpu cycles after the hang, so it doesn’t feel like it is churning away at something:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

1413 dhis 20 0 1895572 570840 16532 S 1.0 44.7 1:48.39 java

906 postgres 20 0 523944 30260 28868 S 0.0 2.4 0:00.47 postgres

908 postgres 20 0 524704 77648 75724 S 0.0 6.1 0:01.04 postgres

909 postgres 20 0 524140 4304 2904 S 0.0 0.3 0:00.16 postgres

910 postgres 20 0 524140 14056 12656 S 0.0 1.1 0:00.50 postgres

911 postgres 20 0 524980 3068 1096 S 0.0 0.2 0:00.11 postgres

912 postgres 20 0 103712 1984 352 S 0.0 0.2 0:00.71 postgres

1432 postgres 20 0 526508 7656 4524 S 0.0 0.6 0:00.00 postgres

1433 postgres 20 0 529308 14604 8948 S 0.0 1.1 0:00.22 postgres

1434 postgres 20 0 536664 46672 32832 S 0.0 3.7 0:08.00 postgres

1904 postgres 20 0 525372 5652 3204 S 0.0 0.4 0:00.00 postgres

1906 postgres 20 0 525256 4860 2548 S 0.0 0.4 0:00.00 postgres

1907 postgres 20 0 525256 4856 2540 S 0.0 0.4 0:00.00 postgres

2114 postgres 20 0 525372 5600 3192 S 0.0 0.4 0:00.00 postgres

(there are a lot of postgres processes it seems like)

I saw some errors on startup but the notes for 2.16 says to expect a few the first time. I also saw some errors when I ran the import and 2.15 upgrade script.

Database import (grep for errors)

ERROR: must be owner of extension plpgsql

Upgrade script (grep for errors)

ERROR: table “patientaggregatereportmembers” does not exist

ERROR: table “patienttabularreportmembers” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientregistrationform_fixedattributes” does not exist

ERROR: table “patientregistrationform_identifiertypes” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientmobilesetting” does not exist

Any ideas? Is there an upgrade script I’ve forgotten to run or maybe a setting I’ve missed?

Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@gmail.com
+678 5955137

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

bobj · 8 October 2014 00:27

Hi Jason

That looks like the same script Tim says he has applied. Must be something else.

@Tim all those postgres processes represent the pool of connections between dhis and postgres. The pool parameters are fixed somewhere deep in the webapp but I think you can expect the default is going between something like 10(min) and 40(max). Postgres is traditionally multi-process rather than multi-threaded - a bit slower in terms of context switching but very stable. One crazy thread can’t bring down the server process.

Bob

···

On 8 October 2014 01:08, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,

You may want to look here for the necessary upgrade script.

http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql

Regards,

Jason

On Oct 7, 2014 7:41 PM, “Timothy Harding” hardingt@gmail.com wrote:

Hello Developer’s Group

I’m now working on migrating from 2.14 to 2.16 in our test environment and the startup for tomcat is hanging on step 7. I’ve included some relevant snippets of logs below and have attached the full logs:

stdout.txt = Standard out during importing the DHIS2 2.14 database, running the upgrade script, and starting tomcat)

catalina.out = The tomcat log for the latest attempt to start up 2.16
My Notes:

I can boot up 2.14 with the database dump without problem

I can boot up 2.16 with a blank database without problem (it creates the live environment i.e. admin:district)

The problem occurs when I try to boot up 2.16 with the 2.14 database (after I’ve run the 2.15 upgrade script found here: http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql)

It hangs at step 7 of 14 * INFO 2014-10-08 09:45:03,291 Executing startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java [localhost-startStop-1])

The system, for having such meager hardware, is really snappy with both the live version of 2.16, and our full production mirror of 2.14.

The com.hazelcast.util.HealthMonitor continues to function even after the initialization process hangs at step 7 of 14 (as you can see in catalina.out) Oct 08, 2014 10:36:58 AM com.hazelcast.util.HealthMonitor

Both Java and Postgres are using no cpu cycles after the hang, so it doesn’t feel like it is churning away at something:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

1413 dhis 20 0 1895572 570840 16532 S 1.0 44.7 1:48.39 java

906 postgres 20 0 523944 30260 28868 S 0.0 2.4 0:00.47 postgres

908 postgres 20 0 524704 77648 75724 S 0.0 6.1 0:01.04 postgres

909 postgres 20 0 524140 4304 2904 S 0.0 0.3 0:00.16 postgres

910 postgres 20 0 524140 14056 12656 S 0.0 1.1 0:00.50 postgres

911 postgres 20 0 524980 3068 1096 S 0.0 0.2 0:00.11 postgres

912 postgres 20 0 103712 1984 352 S 0.0 0.2 0:00.71 postgres

1432 postgres 20 0 526508 7656 4524 S 0.0 0.6 0:00.00 postgres

1433 postgres 20 0 529308 14604 8948 S 0.0 1.1 0:00.22 postgres

1434 postgres 20 0 536664 46672 32832 S 0.0 3.7 0:08.00 postgres

1904 postgres 20 0 525372 5652 3204 S 0.0 0.4 0:00.00 postgres

1906 postgres 20 0 525256 4860 2548 S 0.0 0.4 0:00.00 postgres

1907 postgres 20 0 525256 4856 2540 S 0.0 0.4 0:00.00 postgres

2114 postgres 20 0 525372 5600 3192 S 0.0 0.4 0:00.00 postgres

(there are a lot of postgres processes it seems like)

I saw some errors on startup but the notes for 2.16 says to expect a few the first time. I also saw some errors when I ran the import and 2.15 upgrade script.

Database import (grep for errors)

ERROR: must be owner of extension plpgsql

Upgrade script (grep for errors)

ERROR: table “patientaggregatereportmembers” does not exist

ERROR: table “patienttabularreportmembers” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientregistrationform_fixedattributes” does not exist

ERROR: table “patientregistrationform_identifiertypes” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientmobilesetting” does not exist

Any ideas? Is there an upgrade script I’ve forgotten to run or maybe a setting I’ve missed?

Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@gmail.com
+678 5955137

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 · 8 October 2014 11:32

Hi Tim,
Ah, the haze of travel. I did not see that you had already applied the upgrade script. I had a problem with your database, which others may also experience when upgrading from 2.14 to 2.16… This error is visible if you carefully examine the postgresql logs. So, it is a good exercise to figure out where they are. I am pretty sure they show up at the default logging level, but you may also need to increase the logging level to see each query which is executed. After that, you can trawl through the logs and see what is causing the error. I will give you a hint…a table which needs to be dropped cannot due to a foreign key constraint.

If you want to skip to the spoiler, I have posted some SQL here (http://pastebin.com/0UCGVhx7) which should solve your problem.

Best regards,

Jason

···

On Wed, Oct 8, 2014 at 2:27 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Hi Jason

That looks like the same script Tim says he has applied. Must be something else.

@Tim all those postgres processes represent the pool of connections between dhis and postgres. The pool parameters are fixed somewhere deep in the webapp but I think you can expect the default is going between something like 10(min) and 40(max). Postgres is traditionally multi-process rather than multi-threaded - a bit slower in terms of context switching but very stable. One crazy thread can’t bring down the server process.

Bob

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

On 8 October 2014 01:08, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,

You may want to look here for the necessary upgrade script.

http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql

Regards,

Jason

On Oct 7, 2014 7:41 PM, “Timothy Harding” hardingt@gmail.com wrote:

Hello Developer’s Group

I’m now working on migrating from 2.14 to 2.16 in our test environment and the startup for tomcat is hanging on step 7. I’ve included some relevant snippets of logs below and have attached the full logs:

stdout.txt = Standard out during importing the DHIS2 2.14 database, running the upgrade script, and starting tomcat)

catalina.out = The tomcat log for the latest attempt to start up 2.16
My Notes:

I can boot up 2.14 with the database dump without problem

I can boot up 2.16 with a blank database without problem (it creates the live environment i.e. admin:district)

The problem occurs when I try to boot up 2.16 with the 2.14 database (after I’ve run the 2.15 upgrade script found here: http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql)

It hangs at step 7 of 14 * INFO 2014-10-08 09:45:03,291 Executing startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java [localhost-startStop-1])

The system, for having such meager hardware, is really snappy with both the live version of 2.16, and our full production mirror of 2.14.

The com.hazelcast.util.HealthMonitor continues to function even after the initialization process hangs at step 7 of 14 (as you can see in catalina.out) Oct 08, 2014 10:36:58 AM com.hazelcast.util.HealthMonitor

Both Java and Postgres are using no cpu cycles after the hang, so it doesn’t feel like it is churning away at something:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

1413 dhis 20 0 1895572 570840 16532 S 1.0 44.7 1:48.39 java

906 postgres 20 0 523944 30260 28868 S 0.0 2.4 0:00.47 postgres

908 postgres 20 0 524704 77648 75724 S 0.0 6.1 0:01.04 postgres

909 postgres 20 0 524140 4304 2904 S 0.0 0.3 0:00.16 postgres

910 postgres 20 0 524140 14056 12656 S 0.0 1.1 0:00.50 postgres

911 postgres 20 0 524980 3068 1096 S 0.0 0.2 0:00.11 postgres

912 postgres 20 0 103712 1984 352 S 0.0 0.2 0:00.71 postgres

1432 postgres 20 0 526508 7656 4524 S 0.0 0.6 0:00.00 postgres

1433 postgres 20 0 529308 14604 8948 S 0.0 1.1 0:00.22 postgres

1434 postgres 20 0 536664 46672 32832 S 0.0 3.7 0:08.00 postgres

1904 postgres 20 0 525372 5652 3204 S 0.0 0.4 0:00.00 postgres

1906 postgres 20 0 525256 4860 2548 S 0.0 0.4 0:00.00 postgres

1907 postgres 20 0 525256 4856 2540 S 0.0 0.4 0:00.00 postgres

2114 postgres 20 0 525372 5600 3192 S 0.0 0.4 0:00.00 postgres

(there are a lot of postgres processes it seems like)

I saw some errors on startup but the notes for 2.16 says to expect a few the first time. I also saw some errors when I ran the import and 2.15 upgrade script.

Database import (grep for errors)

ERROR: must be owner of extension plpgsql

Upgrade script (grep for errors)

ERROR: table “patientaggregatereportmembers” does not exist

ERROR: table “patienttabularreportmembers” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientregistrationform_fixedattributes” does not exist

ERROR: table “patientregistrationform_identifiertypes” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientmobilesetting” does not exist

Any ideas? Is there an upgrade script I’ve forgotten to run or maybe a setting I’ve missed?

Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@gmail.com
+678 5955137

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

Timothy_Harding · 9 October 2014 01:01

Thanks Jason for the direction, let me give that a shot.

So I’ve just enabled logging inside the postgres.conf and looks like I may have found it here:

2014-10-09 11:28:31 VUT ERROR: cannot drop table patientidentifiertype because other objects depend on it

2014-10-09 11:28:31 VUT DETAIL: constraint fk_program_patientidentifiertypes_patientidentifiertypeid on table program_patientidentifiertypes depends on table patientidentifiertype

2014-10-09 11:28:31 VUT HINT: Use DROP … CASCADE to drop the dependent objects too.

2014-10-09 11:28:31 VUT STATEMENT: DROP TABLE patientidentifiertype

As an aside, stackoverflow mentioned that logging was not on by default for postgres, but there is a 1.1MB file in my /var/log/postgres/ directory, so something was taking STDERR and putting into a file)

dhis@dhis216:/var/log/postgresql$ ls -la

total 1124

drwxrwxr-t 2 root postgres 4096 Oct 9 11:13 .

drwxrwxr-x 10 root syslog 4096 Oct 9 11:13 …

-rw------- 1 postgres postgres 278 Oct 9 11:13 postgresql-2014-10-09_111335.log

-rw-r----- 1 postgres adm 1122520 Oct 9 11:13 postgresql-9.3-main.log

-rw-r----- 1 postgres adm 6537 Oct 5 06:38 postgresql-9.3-main.log.1

So I’m guessing the table is: patientidentifiertype, which just needs a CASCADE appending to the DROP query. Curious, I finally took a look at the pastebin link and lo and behold:

DROP TABLE patientidentifiertype CASCADE;

Thanks for that Jason, we are getting closer now :).

Sadly, it seems to be hanging on something else now (still Step 7), the last lines from the log file (attached) are:

2014-10-09 11:50:36 VUT ERROR: column “groupby” of relation “trackedentityattribute” does not exist

2014-10-09 11:50:36 VUT STATEMENT: ALTER TABLE trackedentityattribute DROP COLUMN groupBy

but that may not be what is hanging, as entire log file is filled with attempts to ALTER followed by ‘does not exist’, but trackedentity does coorespond with the step:

INFO 2014-10-09 11:50:35,345 Executing startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java [localhost-startStop-1])

Any help is appreciate, thanks again.

postgresql-2014-10-09_114547.log (68.2 KB)

···

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Wed, Oct 8, 2014 at 10:32 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,
Ah, the haze of travel. I did not see that you had already applied the upgrade script. I had a problem with your database, which others may also experience when upgrading from 2.14 to 2.16… This error is visible if you carefully examine the postgresql logs. So, it is a good exercise to figure out where they are. I am pretty sure they show up at the default logging level, but you may also need to increase the logging level to see each query which is executed. After that, you can trawl through the logs and see what is causing the error. I will give you a hint…a table which needs to be dropped cannot due to a foreign key constraint.

If you want to skip to the spoiler, I have posted some SQL here (http://pastebin.com/0UCGVhx7) which should solve your problem.

Best regards,

Jason

On Wed, Oct 8, 2014 at 2:27 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Hi Jason

That looks like the same script Tim says he has applied. Must be something else.

@Tim all those postgres processes represent the pool of connections between dhis and postgres. The pool parameters are fixed somewhere deep in the webapp but I think you can expect the default is going between something like 10(min) and 40(max). Postgres is traditionally multi-process rather than multi-threaded - a bit slower in terms of context switching but very stable. One crazy thread can’t bring down the server process.

Bob

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

–

On 8 October 2014 01:08, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,

You may want to look here for the necessary upgrade script.

http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql

Regards,

Jason

On Oct 7, 2014 7:41 PM, “Timothy Harding” hardingt@gmail.com wrote:

Hello Developer’s Group

I’m now working on migrating from 2.14 to 2.16 in our test environment and the startup for tomcat is hanging on step 7. I’ve included some relevant snippets of logs below and have attached the full logs:

stdout.txt = Standard out during importing the DHIS2 2.14 database, running the upgrade script, and starting tomcat)

catalina.out = The tomcat log for the latest attempt to start up 2.16
My Notes:

I can boot up 2.14 with the database dump without problem

I can boot up 2.16 with a blank database without problem (it creates the live environment i.e. admin:district)

The problem occurs when I try to boot up 2.16 with the 2.14 database (after I’ve run the 2.15 upgrade script found here: http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql)

It hangs at step 7 of 14 * INFO 2014-10-08 09:45:03,291 Executing startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java [localhost-startStop-1])

The system, for having such meager hardware, is really snappy with both the live version of 2.16, and our full production mirror of 2.14.

The com.hazelcast.util.HealthMonitor continues to function even after the initialization process hangs at step 7 of 14 (as you can see in catalina.out) Oct 08, 2014 10:36:58 AM com.hazelcast.util.HealthMonitor

Both Java and Postgres are using no cpu cycles after the hang, so it doesn’t feel like it is churning away at something:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

1413 dhis 20 0 1895572 570840 16532 S 1.0 44.7 1:48.39 java

906 postgres 20 0 523944 30260 28868 S 0.0 2.4 0:00.47 postgres

908 postgres 20 0 524704 77648 75724 S 0.0 6.1 0:01.04 postgres

909 postgres 20 0 524140 4304 2904 S 0.0 0.3 0:00.16 postgres

910 postgres 20 0 524140 14056 12656 S 0.0 1.1 0:00.50 postgres

911 postgres 20 0 524980 3068 1096 S 0.0 0.2 0:00.11 postgres

912 postgres 20 0 103712 1984 352 S 0.0 0.2 0:00.71 postgres

1432 postgres 20 0 526508 7656 4524 S 0.0 0.6 0:00.00 postgres

1433 postgres 20 0 529308 14604 8948 S 0.0 1.1 0:00.22 postgres

1434 postgres 20 0 536664 46672 32832 S 0.0 3.7 0:08.00 postgres

1904 postgres 20 0 525372 5652 3204 S 0.0 0.4 0:00.00 postgres

1906 postgres 20 0 525256 4860 2548 S 0.0 0.4 0:00.00 postgres

1907 postgres 20 0 525256 4856 2540 S 0.0 0.4 0:00.00 postgres

2114 postgres 20 0 525372 5600 3192 S 0.0 0.4 0:00.00 postgres

(there are a lot of postgres processes it seems like)

I saw some errors on startup but the notes for 2.16 says to expect a few the first time. I also saw some errors when I ran the import and 2.15 upgrade script.

Database import (grep for errors)

ERROR: must be owner of extension plpgsql

Upgrade script (grep for errors)

ERROR: table “patientaggregatereportmembers” does not exist

ERROR: table “patienttabularreportmembers” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientregistrationform_fixedattributes” does not exist

ERROR: table “patientregistrationform_identifiertypes” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientmobilesetting” does not exist

Any ideas? Is there an upgrade script I’ve forgotten to run or maybe a setting I’ve missed?

Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@gmail.com
+678 5955137

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

Timothy_Harding · 9 October 2014 05:48

Well, we’ve got some good news as I got it to work, my steps below:
0. Clean out the db

Import 2.14 DHIS2 database
Reset Ownership to ‘dhis’ user
Run 2.15 upgrade script
Cascade drop the patientidentifiertype table
Boot up 2.15
Shutdown 2.15, replace with 2.16
Boot up 2.16

We are done with this saga for the time being. Thanks Bob and Jason for all the help :)! Now to start poking around in 2.16.

···

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Thu, Oct 9, 2014 at 12:01 PM, Timothy Harding hardingt@gmail.com wrote:

Thanks Jason for the direction, let me give that a shot.

So I’ve just enabled logging inside the postgres.conf and looks like I may have found it here:

2014-10-09 11:28:31 VUT ERROR: cannot drop table patientidentifiertype because other objects depend on it

2014-10-09 11:28:31 VUT DETAIL: constraint fk_program_patientidentifiertypes_patientidentifiertypeid on table program_patientidentifiertypes depends on table patientidentifiertype

2014-10-09 11:28:31 VUT HINT: Use DROP … CASCADE to drop the dependent objects too.

2014-10-09 11:28:31 VUT STATEMENT: DROP TABLE patientidentifiertype

As an aside, stackoverflow mentioned that logging was not on by default for postgres, but there is a 1.1MB file in my /var/log/postgres/ directory, so something was taking STDERR and putting into a file)

dhis@dhis216:/var/log/postgresql$ ls -la

total 1124

drwxrwxr-t 2 root postgres 4096 Oct 9 11:13 .

drwxrwxr-x 10 root syslog 4096 Oct 9 11:13 …

-rw------- 1 postgres postgres 278 Oct 9 11:13 postgresql-2014-10-09_111335.log

-rw-r----- 1 postgres adm 1122520 Oct 9 11:13 postgresql-9.3-main.log

-rw-r----- 1 postgres adm 6537 Oct 5 06:38 postgresql-9.3-main.log.1

So I’m guessing the table is: patientidentifiertype, which just needs a CASCADE appending to the DROP query. Curious, I finally took a look at the pastebin link and lo and behold:

DROP TABLE patientidentifiertype CASCADE;

Thanks for that Jason, we are getting closer now :).

Sadly, it seems to be hanging on something else now (still Step 7), the last lines from the log file (attached) are:

2014-10-09 11:50:36 VUT ERROR: column “groupby” of relation “trackedentityattribute” does not exist

2014-10-09 11:50:36 VUT STATEMENT: ALTER TABLE trackedentityattribute DROP COLUMN groupBy

but that may not be what is hanging, as entire log file is filled with attempts to ALTER followed by ‘does not exist’, but trackedentity does coorespond with the step:

INFO 2014-10-09 11:50:35,345 Executing startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java [localhost-startStop-1])

Any help is appreciate, thanks again.

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Wed, Oct 8, 2014 at 10:32 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,
Ah, the haze of travel. I did not see that you had already applied the upgrade script. I had a problem with your database, which others may also experience when upgrading from 2.14 to 2.16… This error is visible if you carefully examine the postgresql logs. So, it is a good exercise to figure out where they are. I am pretty sure they show up at the default logging level, but you may also need to increase the logging level to see each query which is executed. After that, you can trawl through the logs and see what is causing the error. I will give you a hint…a table which needs to be dropped cannot due to a foreign key constraint.

If you want to skip to the spoiler, I have posted some SQL here (http://pastebin.com/0UCGVhx7) which should solve your problem.

Best regards,

Jason

On Wed, Oct 8, 2014 at 2:27 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Hi Jason

That looks like the same script Tim says he has applied. Must be something else.

@Tim all those postgres processes represent the pool of connections between dhis and postgres. The pool parameters are fixed somewhere deep in the webapp but I think you can expect the default is going between something like 10(min) and 40(max). Postgres is traditionally multi-process rather than multi-threaded - a bit slower in terms of context switching but very stable. One crazy thread can’t bring down the server process.

Bob

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

–

On 8 October 2014 01:08, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,

You may want to look here for the necessary upgrade script.

http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql

Regards,

Jason

On Oct 7, 2014 7:41 PM, “Timothy Harding” hardingt@gmail.com wrote:

Hello Developer’s Group

I’m now working on migrating from 2.14 to 2.16 in our test environment and the startup for tomcat is hanging on step 7. I’ve included some relevant snippets of logs below and have attached the full logs:

stdout.txt = Standard out during importing the DHIS2 2.14 database, running the upgrade script, and starting tomcat)

catalina.out = The tomcat log for the latest attempt to start up 2.16
My Notes:

I can boot up 2.14 with the database dump without problem

I can boot up 2.16 with a blank database without problem (it creates the live environment i.e. admin:district)

The problem occurs when I try to boot up 2.16 with the 2.14 database (after I’ve run the 2.15 upgrade script found here: http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql)

It hangs at step 7 of 14 * INFO 2014-10-08 09:45:03,291 Executing startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java [localhost-startStop-1])

The system, for having such meager hardware, is really snappy with both the live version of 2.16, and our full production mirror of 2.14.

The com.hazelcast.util.HealthMonitor continues to function even after the initialization process hangs at step 7 of 14 (as you can see in catalina.out) Oct 08, 2014 10:36:58 AM com.hazelcast.util.HealthMonitor

Both Java and Postgres are using no cpu cycles after the hang, so it doesn’t feel like it is churning away at something:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

1413 dhis 20 0 1895572 570840 16532 S 1.0 44.7 1:48.39 java

906 postgres 20 0 523944 30260 28868 S 0.0 2.4 0:00.47 postgres

908 postgres 20 0 524704 77648 75724 S 0.0 6.1 0:01.04 postgres

909 postgres 20 0 524140 4304 2904 S 0.0 0.3 0:00.16 postgres

910 postgres 20 0 524140 14056 12656 S 0.0 1.1 0:00.50 postgres

911 postgres 20 0 524980 3068 1096 S 0.0 0.2 0:00.11 postgres

912 postgres 20 0 103712 1984 352 S 0.0 0.2 0:00.71 postgres

1432 postgres 20 0 526508 7656 4524 S 0.0 0.6 0:00.00 postgres

1433 postgres 20 0 529308 14604 8948 S 0.0 1.1 0:00.22 postgres

1434 postgres 20 0 536664 46672 32832 S 0.0 3.7 0:08.00 postgres

1904 postgres 20 0 525372 5652 3204 S 0.0 0.4 0:00.00 postgres

1906 postgres 20 0 525256 4860 2548 S 0.0 0.4 0:00.00 postgres

1907 postgres 20 0 525256 4856 2540 S 0.0 0.4 0:00.00 postgres

2114 postgres 20 0 525372 5600 3192 S 0.0 0.4 0:00.00 postgres

(there are a lot of postgres processes it seems like)

I saw some errors on startup but the notes for 2.16 says to expect a few the first time. I also saw some errors when I ran the import and 2.15 upgrade script.

Database import (grep for errors)

ERROR: must be owner of extension plpgsql

Upgrade script (grep for errors)

ERROR: table “patientaggregatereportmembers” does not exist

ERROR: table “patienttabularreportmembers” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientregistrationform_fixedattributes” does not exist

ERROR: table “patientregistrationform_identifiertypes” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientmobilesetting” does not exist

Any ideas? Is there an upgrade script I’ve forgotten to run or maybe a setting I’ve missed?

Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@gmail.com
+678 5955137

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

Knut_Staring · 9 October 2014 05:50

Good - and you’ll be happy to know that 2.17 is just around the corner

···

On Thu, Oct 9, 2014 at 7:48 AM, Timothy Harding hardingt@gmail.com wrote:

Well, we’ve got some good news as I got it to work, my steps below:
0. Clean out the db

Import 2.14 DHIS2 database

Reset Ownership to ‘dhis’ user

Run 2.15 upgrade script

Cascade drop the patientidentifiertype table

Boot up 2.15

Shutdown 2.15, replace with 2.16

Boot up 2.16

We are done with this saga for the time being. Thanks Bob and Jason for all the help :)! Now to start poking around in 2.16.

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

–
Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

Skype: knutstar

http://dhis2.org

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Thu, Oct 9, 2014 at 12:01 PM, Timothy Harding hardingt@gmail.com wrote:

Thanks Jason for the direction, let me give that a shot.

So I’ve just enabled logging inside the postgres.conf and looks like I may have found it here:

2014-10-09 11:28:31 VUT ERROR: cannot drop table patientidentifiertype because other objects depend on it

2014-10-09 11:28:31 VUT DETAIL: constraint fk_program_patientidentifiertypes_patientidentifiertypeid on table program_patientidentifiertypes depends on table patientidentifiertype

2014-10-09 11:28:31 VUT HINT: Use DROP … CASCADE to drop the dependent objects too.

2014-10-09 11:28:31 VUT STATEMENT: DROP TABLE patientidentifiertype

As an aside, stackoverflow mentioned that logging was not on by default for postgres, but there is a 1.1MB file in my /var/log/postgres/ directory, so something was taking STDERR and putting into a file)

dhis@dhis216:/var/log/postgresql$ ls -la

total 1124

drwxrwxr-t 2 root postgres 4096 Oct 9 11:13 .

drwxrwxr-x 10 root syslog 4096 Oct 9 11:13 …

-rw------- 1 postgres postgres 278 Oct 9 11:13 postgresql-2014-10-09_111335.log

-rw-r----- 1 postgres adm 1122520 Oct 9 11:13 postgresql-9.3-main.log

-rw-r----- 1 postgres adm 6537 Oct 5 06:38 postgresql-9.3-main.log.1

So I’m guessing the table is: patientidentifiertype, which just needs a CASCADE appending to the DROP query. Curious, I finally took a look at the pastebin link and lo and behold:

DROP TABLE patientidentifiertype CASCADE;

Thanks for that Jason, we are getting closer now :).

Sadly, it seems to be hanging on something else now (still Step 7), the last lines from the log file (attached) are:

2014-10-09 11:50:36 VUT ERROR: column “groupby” of relation “trackedentityattribute” does not exist

2014-10-09 11:50:36 VUT STATEMENT: ALTER TABLE trackedentityattribute DROP COLUMN groupBy

but that may not be what is hanging, as entire log file is filled with attempts to ALTER followed by ‘does not exist’, but trackedentity does coorespond with the step:

INFO 2014-10-09 11:50:35,345 Executing startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java [localhost-startStop-1])

Any help is appreciate, thanks again.

Timothy Harding
PeaceCorps Volunteer

Republic of Vanuatu

hardingt@gmail.com

+678 5955137

On Wed, Oct 8, 2014 at 10:32 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,
Ah, the haze of travel. I did not see that you had already applied the upgrade script. I had a problem with your database, which others may also experience when upgrading from 2.14 to 2.16… This error is visible if you carefully examine the postgresql logs. So, it is a good exercise to figure out where they are. I am pretty sure they show up at the default logging level, but you may also need to increase the logging level to see each query which is executed. After that, you can trawl through the logs and see what is causing the error. I will give you a hint…a table which needs to be dropped cannot due to a foreign key constraint.

If you want to skip to the spoiler, I have posted some SQL here (http://pastebin.com/0UCGVhx7) which should solve your problem.

Best regards,

Jason

On Wed, Oct 8, 2014 at 2:27 AM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Hi Jason

That looks like the same script Tim says he has applied. Must be something else.

@Tim all those postgres processes represent the pool of connections between dhis and postgres. The pool parameters are fixed somewhere deep in the webapp but I think you can expect the default is going between something like 10(min) and 40(max). Postgres is traditionally multi-process rather than multi-threaded - a bit slower in terms of context switching but very stable. One crazy thread can’t bring down the server process.

Bob

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

–

On 8 October 2014 01:08, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tim,

You may want to look here for the necessary upgrade script.

http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql

Regards,

Jason

On Oct 7, 2014 7:41 PM, “Timothy Harding” hardingt@gmail.com wrote:

Hello Developer’s Group

I’m now working on migrating from 2.14 to 2.16 in our test environment and the startup for tomcat is hanging on step 7. I’ve included some relevant snippets of logs below and have attached the full logs:

stdout.txt = Standard out during importing the DHIS2 2.14 database, running the upgrade script, and starting tomcat)

catalina.out = The tomcat log for the latest attempt to start up 2.16
My Notes:

I can boot up 2.14 with the database dump without problem

I can boot up 2.16 with a blank database without problem (it creates the live environment i.e. admin:district)

The problem occurs when I try to boot up 2.16 with the 2.14 database (after I’ve run the 2.15 upgrade script found here: http://www.dhis2.org/download/resources/sql/rename-patient-to-trackedentity.sql)

It hangs at step 7 of 14 * INFO 2014-10-08 09:45:03,291 Executing startup routine [7 of 14, runlevel 4]: TrackedEntityTableAlteror (DefaultStartupRoutineExecutor.java [localhost-startStop-1])

The system, for having such meager hardware, is really snappy with both the live version of 2.16, and our full production mirror of 2.14.

The com.hazelcast.util.HealthMonitor continues to function even after the initialization process hangs at step 7 of 14 (as you can see in catalina.out) Oct 08, 2014 10:36:58 AM com.hazelcast.util.HealthMonitor

Both Java and Postgres are using no cpu cycles after the hang, so it doesn’t feel like it is churning away at something:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

1413 dhis 20 0 1895572 570840 16532 S 1.0 44.7 1:48.39 java

906 postgres 20 0 523944 30260 28868 S 0.0 2.4 0:00.47 postgres

908 postgres 20 0 524704 77648 75724 S 0.0 6.1 0:01.04 postgres

909 postgres 20 0 524140 4304 2904 S 0.0 0.3 0:00.16 postgres

910 postgres 20 0 524140 14056 12656 S 0.0 1.1 0:00.50 postgres

911 postgres 20 0 524980 3068 1096 S 0.0 0.2 0:00.11 postgres

912 postgres 20 0 103712 1984 352 S 0.0 0.2 0:00.71 postgres

1432 postgres 20 0 526508 7656 4524 S 0.0 0.6 0:00.00 postgres

1433 postgres 20 0 529308 14604 8948 S 0.0 1.1 0:00.22 postgres

1434 postgres 20 0 536664 46672 32832 S 0.0 3.7 0:08.00 postgres

1904 postgres 20 0 525372 5652 3204 S 0.0 0.4 0:00.00 postgres

1906 postgres 20 0 525256 4860 2548 S 0.0 0.4 0:00.00 postgres

1907 postgres 20 0 525256 4856 2540 S 0.0 0.4 0:00.00 postgres

2114 postgres 20 0 525372 5600 3192 S 0.0 0.4 0:00.00 postgres

(there are a lot of postgres processes it seems like)

I saw some errors on startup but the notes for 2.16 says to expect a few the first time. I also saw some errors when I ran the import and 2.15 upgrade script.

Database import (grep for errors)

ERROR: must be owner of extension plpgsql

Upgrade script (grep for errors)

ERROR: table “patientaggregatereportmembers” does not exist

ERROR: table “patienttabularreportmembers” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientregistrationform_fixedattributes” does not exist

ERROR: table “patientregistrationform_identifiertypes” does not exist

ERROR: table “patientregistrationform_attributes” does not exist

ERROR: table “patientmobilesetting” does not exist

Any ideas? Is there an upgrade script I’ve forgotten to run or maybe a setting I’ve missed?

Timothy Harding
PeaceCorps Volunteer
Republic of Vanuatu
hardingt@gmail.com
+678 5955137

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