Emergency! problems for dhis2.32/33: duplicate key value violates unique

Support - Assistance technique
1

2.32.2.33 new install , do analysis. will cause this.

windows, now ubuntu. I don’t know why?
SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint “_dateperiodstructure_temp_pkey” Detail: Key (dateperiod)=(1988-04-10) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “_dateperiodstructure_temp_pkey” Detail: Key (dateperiod)=(1988-04-10) already exists.

3

QQ图片20200221160905

QQ图片20200221161006
QQ图片202002211610061255×552 30.1 KB
QQ图片20200221161021
QQ图片202002211610211341×474 39.2 KB
QQ图片20200221161029

error found in dhis 2.33 , using demo db from dhis2.org.

env: ubuntu 18, psql -10, tomcat-9, java-8

dhis ver 2.32 have same problem too.

4

Hi @linxd,

could you let us know which calendar you have under Apps > System settings > Calendar?

best,

Lars

11

iso-8601

I have set postgresql local to zh_CN. its still. you can try at: http://swx.wj120.com.cn:20003/dhis

I am trying 2.33.2 now, hope will dispear. I am collection china new SARS info.
please help. this will cause work stop.

2.33.2 still ,dont’ know why

can work in another : just ubuntu 16.x ,ubuntu 18 not work?

same db(ubuntu 16,ps 10.10) -->ubuntu 16+tomcat8/9 OK, but

for ubuntu 18+tomcat 8/9 || windows server 2008, not ok .

why only this: Key (dateperiod)=(1988-04-10) ??? :grin: :smile:

org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint “_dateperiodstructure_temp_pkey”
ost 细：Key (dateperiod)=(1988-04-10) already exists.; nested exception is org.p
gresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “_dateperiodstructure_temp_pkey”
详细：Key (dateperiod)=(1988-04-10) already exists.
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:242)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1442)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:646)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:935)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:989)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:980)
at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.batchUpdate(JdbcResourceTableStore.java:212)
at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.generateResourceTable(JdbcResourceTableStore.java:137)
at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDatePeriodTable(DefaultResourceTableService.java:189)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205)
at com.sun.proxy.$Proxy246.generateDatePeriodTable(Unknown Source)
at org.hisp.dhis.analytics.table.DefaultAnalyticsTableGenerator.generateResourceTables(DefaultAnalyticsTableGenerator.java:219)
at org.hisp.dhis.analytics.table.DefaultAnalyticsTableGenerator.generateTables(DefaultAnalyticsTableGenerator.java:127)
at org.hisp.dhis.analytics.table.scheduling.AnalyticsTableJob.execute(AnalyticsTableJob.java:84)
at org.hisp.dhis.scheduling.DefaultJobInstance.executeJob(DefaultJobInstance.java:157)
at org.hisp.dhis.scheduling.DefaultJobInstance.execute(DefaultJobInstance.java:71)
at org.hisp.dhis.scheduling.DefaultSchedulingManager.lambda$internalExecuteJobConfiguration$3(DefaultSchedulingManager.java:293)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “_dateperiodstructure_temp_pkey”
详细：Key (dateperiod)=(1988-04-10) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1299)
at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1324)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:467)
at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:840)
at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1538)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2437)
at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$2(JdbcTemplate.java:949)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:616)
… 30 more

16

same db under ubuntu 16+pg 10, windows analysis, error! and then using same dhis2 in ubuntu 16, startup and do analysis, it’s ok, then windows do again ,still
error.

HibernateIdentifiableObjectStore.java: 某些输入文件使用了未经检查或不安全的操作。

[INFO] — maven-compiler-plugin:3.8.1:compile (default-compile) @ dhis-service-core —
[INFO] Changes detected - recompiling the module!
[INFO] Compiling 498 source files to D:\dhis\dhis223\trunk\core\dhis2-core\dhis-2\dhis-services\dhis-service-core\target\classes
[INFO] /D:/dhis/dhis223/trunk/core/dhis2-core/dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/security/oauth2/DefaultClientDetailsUserDe
tailsService.java: 某些输入文件使用或覆盖了已过时的 API。
[INFO] /D:/dhis/dhis223/trunk/core/dhis2-core/dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/security/oauth2/DefaultClientDetailsUserDe
tailsService.java: 有关详细信息, 请使用 -Xlint:deprecation 重新编译。
[INFO] /D:/dhis/dhis223/trunk/core/dhis2-core/dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/common/hibernate/HibernateIdentifiableObje
ctStore.java: 某些输入文件使用了未经检查或不安全的操作。
[INFO] /D:/dhis/dhis223/trunk/core/dhis2-core/dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/common/hibernate/HibernateIdentifiableObje
ctStore.java: 有关详细信息, 请使用 -Xlint:unchecked 重新编译。
[INFO]

I have test diffenert database encoding: all same result
CREATE DATABASE dhis2
WITH OWNER = postgres
ENCODING = ‘UTF8’
TABLESPACE = test
LC_COLLATE = ‘zh_CN.UTF-8’
LC_CTYPE = ‘zh_CN.UTF-8’
CONNECTION LIMIT = -1;

ALTER DATABASE dhis2
SET lc_time = ‘zh_CN.UTF-8’;

CREATE DATABASE dhist
WITH OWNER = postgres
ENCODING = ‘UTF8’
TABLESPACE = test
LC_COLLATE = ‘zh_CN.UTF-8’
LC_CTYPE = ‘zh_CN.UTF-8’
CONNECTION LIMIT = -1;

ALTER DATABASE dhist
SET lc_time = ‘en_US.UTF-8’;

CREATE DATABASE dhist1
WITH OWNER = postgres
ENCODING = ‘UTF8’
TABLESPACE = pg_default
LC_COLLATE = ‘zh_CN.UTF-8’
LC_CTYPE = ‘zh_CN.UTF-8’
CONNECTION LIMIT = -1;

got it ，just jre version.! 1.8.0.127 ok, 1.8.0.>200 may not .
another reson may runing jre lower than compiling jre version.

but the final reason is timezone setting.

17

Hey @linxd,
so were you able to resolve this?

I see that this post is directly related to your other post https://community.dhis2.org/t/resource-table-error-duplicate-key-value-violates-unique-constraint/38427 …

19

new install of 2.32.4 , maintain resource table cause this, because other system having some data alse found this issue when I do it.

  • INFO 2020-02-18 09:20:26,863 no recipient (SmsMessageSender.java [taskScheduler-7])
  • ERROR 2020-02-18 09:20:26,868 Job ‘inMemoryResourceTableJob’ failed (DefaultJobInstance.java [taskScheduler-13])
    org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?
    ,?,?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint “_dateperiodstructure_temp_pkey”
    璇︾粏锛欿ey (dateperiod)=(1988-04-10) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violat
    nique constraint “_dateperiodstructure_temp_pkey”
    璇︾粏锛欿ey (dateperiod)=(1988-04-10) already exists.
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:238)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:655)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:668)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:956)
    at org.springframework.jdbc.core.BatchUpdateUtils.executeBatchUpdate(BatchUpdateUtils.java:40)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1006)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1001)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.batchUpdate(JdbcResourceTableStore.java:203)
    at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.generateResourceTable(JdbcResourceTableStore.java:128)
    at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDatePeriodTable(DefaultResourceTableService.java:200)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy135.generateDatePeriodTable(Unknown Source)
    at org.hisp.dhis.analytics.table.DefaultAnalyticsTableGenerator.generateResourceTables(DefaultAnalyticsTableGenerator.java:188)
    at org.hisp.dhis.analytics.table.DefaultAnalyticsTableGenerator.generateResourceTables(DefaultAnalyticsTableGenerator.java:156)
    at org.hisp.dhis.analytics.table.scheduling.ResourceTableJob.execute(ResourceTableJob.java:59)
    at org.hisp.dhis.scheduling.DefaultJobInstance.executeJob(DefaultJobInstance.java:155)
    at org.hisp.dhis.scheduling.DefaultJobInstance.execute(DefaultJobInstance.java:69)
    at org.hisp.dhis.scheduling.DefaultSchedulingManager.lambda$internalExecuteJobConfiguration$3(DefaultSchedulingManager.java:283)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(Unknown Source)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
    Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “_dateperiodstructure_temp_pkey”
    璇︾粏锛欿ey (dateperiod)=(1988-04-10) already exists.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1299)
    at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1324)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:467)
    at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:840)
    at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1538)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2437)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:972)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:956)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:639)
    … 31 more

why 1988-04-10?

This happens in window server 2008. but not in ubuntu. even connect to the same database

postgresql 10. tomcat 9

now apatch-9, jdk-11, centos-7.6 , dhis-2.37, still cause same error

1 Like
20

I can’t confirm. but now I have reolved it.

2 Likes
21

Hi @linxd ,

We are experiencing the same issue.

As I understand the issue is related to time zone settings, could you please elaborate about the steps to resolve it at our ends? Shall we down grade JRE to the lower than 1.8.0.200? please advise.

Hereunder the details of our env.
Version:
2.36.3
Build revision:
a95cf40
Build date:
2021-07-08 22:13
Jasper reports version:
6.3.1
Database version:
PostgreSQL 10.17
Read replica count:
0
Java opts:
-Xmx7500m -Xms4000m -Djdk.tls.ephemeralDHKeySize=2048 -Djava.protocol.handler.pkgs=org.apache.catalina.webresources -Dorg.apache.catalina.security.SecurityListener.UMASK=0027
Java version:
1.8.0_292
Java vendor:
Red Hat, Inc.
OS name:
Linux
OS architecture:
amd64
OS version:
4.18.0-240.22.1.el8_3.x86_64
Server memory:
Mem Total in JVM: 4952 Free in JVM: 4102 Max Limit: 6667
CPU cores:
4

Thank you.