Data Not Showing Up in Reports and Pivot Tables

ifeanyiokoye · 21 June 2016 14:13

Hello,

Weare currently experiencing an issue with the analytics on our DHIS2installation.

Theinstance went live on Thursday and data entry commenced that same day. OnSaturday, I tried to run dataset reports and got no output (just the formstructure). Pivot tables also returned no data.

Iran the analytics generation, which completed successfully each time from thefront end but still no data. Tried running maintenance but that went onindefinitely.

Iasked our IT folks to pull the database and system logs and noticed some errors(see attached PostgreSQL and Catalina logs).

Here'sa subsection:

INFO 2016-06-18 20:51:25,517 Query failed, likely because the requestedanalytics table does not exist (JdbcAnalyticsManager.java[SimpleAsyncTaskExecutor-487])

org.springframework.jdbc.BadSqlGrammarException:StatementCallback; bad SQL grammar [select"dx","co","monthly","uidlevel1",sum(value) as value from analytics_2016 where "dx" in ('txAGdhIyCoP')and "co" in ('L8BiaJcGlDA', 'Nii07uRlq9I', 'aajj1DKxqPa','deVhyY1hfDO', 'QLmvl8CxNYs', 'UdufjVycW0s', 'Kf0uVFPuA8s', 'hlgJQWw4vlY','RSq3AOSIUh1', 'pErlxyLqK8G', 'ofRCydRthqG', 'E0oECMnEhKK', 'Lr49tVveh4n','AmgnnAu4Raw', 'hvMYNwgY0fN', 'wy5xiY6VZNM', 'yKSHLcb0fVY', 'M3gaIIkX5Ea','zq5W69j1A5O', 'iBipMNkNvEU', 'mQGIahEkqQr', 'rVbaaj8FKdA', 'iOr97drPOoZ','WW0OzurqWny', 'dAZsyxKTru2', 'z4R5rXEPVcN', 'GenN3reQduf', 'FWDHae3zoIz','dwPdgJxljWD', 'rfVGtr1syCt', 'FQ0T8oicM1q', 'zeOLrHi1DED', 'wZey9PSViRb','x4aIvEhgnCX', 'skAHXkrqvUx', 'L6j7K9kmltt', 'LVhjAVcUC8z', 'XhCoL9nItWA','O1uKi3kQsGi', 'Fmy4z8hQFri', 'B6Kdba5mGpB', 'Ya8X9slYFDV', 'H8HAVyunZwj','ofCLpSSvqFp', 'IXTMAi8ASDP', 'C0cgd3RxLRF', 'e2o6yW76PzJ', 'nddeqMEE0s4','qn32iuP28aI', 'REeTM8YMG5t', 'VfrL2mVMKtW', 'qU7giGjkkUw', 'oysF1o76Qxl','HPAxapmoBvr', 's9CyZFKA9n7', 'A8A8JF9lpue', 'qTTDZKszW4O', 'ujy6e2O2Y6W','BRmlLoDHsjT', 'e27h0KUYBEK', 'okuojwTFZo3', 'dQfpHrCSl0J', 'xBHYXY7Lfip','kru3fGkvxka', 'GJMevtelfZp', 'TrhQf5tFVih', 'sbWJ3tN5DuY', 'VGt0aLQgbdc') and"monthly" in ('201604') and "uidlevel1" in ('C983g0J6p9m')and ( "sASLYFgAkiG" in ('CW20DvNk041') ) and ("LKC6LlcxjzT" in ('jj2GfLHSgGP') ) group by"dx","co","monthly","uidlevel1"];nested exception is org.postgresql.util.PSQLException: ERROR: column "sASLYFgAkiG" does not exist

Position: 1213

       atorg\.springframework\.jdbc\.support\.SQLStateSQLExceptionTranslator\.doTranslate\(SQLStateSQLExceptionTranslator\.java:99\)

       atorg\.springframework\.jdbc\.support\.AbstractFallbackSQLExceptionTranslator\.translate\(AbstractFallbackSQLExceptionTranslator\.java:73\)

       atorg\.springframework\.jdbc\.support\.AbstractFallbackSQLExceptionTranslator\.translate\(AbstractFallbackSQLExceptionTranslator\.java:81\)

       at org\.springframework\.jdbc\.support\.AbstractFallbackSQLExceptionTranslator\.translate\(AbstractFallbackSQLExceptionTranslator\.java:81\)

       at org\.springframework\.jdbc\.core\.JdbcTemplate\.execute\(JdbcTemplate\.java:416\)

       at org\.springframework\.jdbc\.core\.JdbcTemplate\.query\(JdbcTemplate\.java:471\)

       at org\.springframework\.jdbc\.core\.JdbcTemplate\.queryForRowSet\(JdbcTemplate\.java:526\)

       atorg\.hisp\.dhis\.analytics\.data\.JdbcAnalyticsManager\.getKeyValueMap\(JdbcAnalyticsManager\.java:395\)

       atorg\.hisp\.dhis\.analytics\.data\.JdbcAnalyticsManager\.getAggregatedDataValues\(JdbcAnalyticsManager\.java:143\)

       at sun\.reflect\.GeneratedMethodAccessor538\.invoke\(Unknown Source\)

Thetext in red is pretty recurrent across the catalina logs from the 18th of June.

Weare running DHIS2 2.22 build 22057.

Dokindly take a look and help us resolve this.

Wouldgladly provide any other information required.

Thankyou.

Warm Regards,

Ifeanyi

Read DrMaestro's Blog

Michael_Mwebaze · 22 June 2016 02:49

Hello Ifeanyi,

Are you able to generate resource tables without any errors?
Do you have any values/names for the various Orgunit levels? If yes try to rename them and save them just to be certain they are saved in the database.
generate resource and analytic tables again
Hope this helps.

Michael

···

On 21 June 2016 at 10:13, Ifeanyi Okoye ifeanyiokoye@yahoo.com wrote:

Hello,

Weare currently experiencing an issue with the analytics on our DHIS2installation.

Theinstance went live on Thursday and data entry commenced that same day. OnSaturday, I tried to run dataset reports and got no output (just the formstructure). Pivot tables also returned no data.

Iran the analytics generation, which completed successfully each time from thefront end but still no data. Tried running maintenance but that went onindefinitely.

Iasked our IT folks to pull the database and system logs and noticed some errors(see attached PostgreSQL and Catalina logs).

Here’sa subsection:

INFO 2016-06-18 20:51:25,517 Query failed, likely because the requestedanalytics table does not exist (JdbcAnalyticsManager.java[SimpleAsyncTaskExecutor-487])

org.springframework.jdbc.BadSqlGrammarException:StatementCallback; bad SQL grammar [select"dx",“co”,“monthly”,“uidlevel1”,sum(value) as value from analytics_2016 where “dx” in (‘txAGdhIyCoP’)and “co” in (‘L8BiaJcGlDA’, ‘Nii07uRlq9I’, ‘aajj1DKxqPa’,‘deVhyY1hfDO’, ‘QLmvl8CxNYs’, ‘UdufjVycW0s’, ‘Kf0uVFPuA8s’, ‘hlgJQWw4vlY’,‘RSq3AOSIUh1’, ‘pErlxyLqK8G’, ‘ofRCydRthqG’, ‘E0oECMnEhKK’, ‘Lr49tVveh4n’,‘AmgnnAu4Raw’, ‘hvMYNwgY0fN’, ‘wy5xiY6VZNM’, ‘yKSHLcb0fVY’, ‘M3gaIIkX5Ea’,‘zq5W69j1A5O’, ‘iBipMNkNvEU’, ‘mQGIahEkqQr’, ‘rVbaaj8FKdA’, ‘iOr97drPOoZ’,‘WW0OzurqWny’, ‘dAZsyxKTru2’, ‘z4R5rXEPVcN’, ‘GenN3reQduf’, ‘FWDHae3zoIz’,‘dwPdgJxljWD’, ‘rfVGtr1syCt’, ‘FQ0T8oicM1q’, ‘zeOLrHi1DED’, ‘wZey9PSViRb’,‘x4aIvEhgnCX’, ‘skAHXkrqvUx’, ‘L6j7K9kmltt’, ‘LVhjAVcUC8z’, ‘XhCoL9nItWA’,‘O1uKi3kQsGi’, ‘Fmy4z8hQFri’, ‘B6Kdba5mGpB’, ‘Ya8X9slYFDV’, ‘H8HAVyunZwj’,‘ofCLpSSvqFp’, ‘IXTMAi8ASDP’, ‘C0cgd3RxLRF’, ‘e2o6yW76PzJ’, ‘nddeqMEE0s4’,‘qn32iuP28aI’, ‘REeTM8YMG5t’, ‘VfrL2mVMKtW’, ‘qU7giGjkkUw’, ‘oysF1o76Qxl’,‘HPAxapmoBvr’, ‘s9CyZFKA9n7’, ‘A8A8JF9lpue’, ‘qTTDZKszW4O’, ‘ujy6e2O2Y6W’,‘BRmlLoDHsjT’, ‘e27h0KUYBEK’, ‘okuojwTFZo3’, ‘dQfpHrCSl0J’, ‘xBHYXY7Lfip’,‘kru3fGkvxka’, ‘GJMevtelfZp’, ‘TrhQf5tFVih’, ‘sbWJ3tN5DuY’, ‘VGt0aLQgbdc’) and"monthly" in (‘201604’) and “uidlevel1” in (‘C983g0J6p9m’)and ( “sASLYFgAkiG” in (‘CW20DvNk041’) ) and (“LKC6LlcxjzT” in (‘jj2GfLHSgGP’) ) group by"dx",“co”,“monthly”,“uidlevel1”];nested exception is org.postgresql.util.PSQLException: ERROR: column “sASLYFgAkiG” does not exist

Position: 1213
       atorg.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)



       atorg.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)



       atorg.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)



       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)



       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)



       at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:471)



       at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:526)



       atorg.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:395)



       atorg.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:143)



       at sun.reflect.GeneratedMethodAccessor538.invoke(Unknown Source)
Thetext in red is pretty recurrent across the catalina logs from the 18th of June.

Weare running DHIS2 2.22 build 22057.

Dokindly take a look and help us resolve this.

Wouldgladly provide any other information required.

Thankyou.

Warm Regards,

Ifeanyi

Read DrMaestro’s Blog

www.ifeanyiokoye.com

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp