Data Visualizer - SQLState: 25006 Error: cannot execute nextval() in a read-only transaction

[Ulanbek]

Dear community and server admins,

I am trying to create a single value visualisation on Data visualiser. Everything is fine until I am trying to save the visualisation.

Below error message from catalina.log:

• WARN 2023-06-01T11:15:36,843 SQL Error: 0, SQLState: 25006 (SqlExceptionHelper.java [http-nio-8085-exec-7]) IDFtfH/AwopUAFcfA95Olkh079HLVAPmxKH5WKJEoIT8I=
• ERROR 2023-06-01T11:15:36,844 ERROR: cannot execute nextval() in a read-only transaction (SqlExceptionHelper.java [http-nio-8085-exec-7]) IDFtfH/AwopUAFcfA95Olkh079HLVAPmxKH5WKJEoIT8I=

org.hibernate.exception.GenericJDBCException: could not extract ResultSet

and Postgresql.log:

2023-06-01 11:14:55.345 CEST [5274] STATEMENT:
2023-06-01 11:15:36.843 CEST [5196] ERROR: cannot execute nextval() in a read-only transaction
2023-06-01 11:15:36.843 CEST [5196] STATEMENT: select nextval (‘hibernate_sequence’)
2023-06-01 11:15:36.843 CEST [5196] ERROR: current transaction is aborted, commands ignored until end of transaction block
2023-06-01 11:15:36.843 CEST [5196] STATEMENT:

Question: can anyone share their postgresql.conf settings?
Currently I am using DHIS2.37.9.1 and PostgreSQL 14.8 and I have tried to set default_transaction_read_only = false. But still have an error.
Any ideas and help please?

Thank you

[Ulanbek]

Hi there again,

Just downgraded to version 2.37.8.1. Everything works perfectly fine…

It seems something in version 2.37.9.1

Can anyone have a look please from @dhis2-analytics team?

Thank you

[Gassim (AL-Gassim Sharaf Addin)]

@Ulanbek

default_transaction_read_only = false.

Could you try to change the value ‘false’ to ‘off’ instead?

Also, the issue might be similar to this topic:

ERROR/ cannot execute nextval() in a read-only transaction

Hello, When trying to enter data in some of our datasets we’re having errors to save the data and the box turn to red. In the logs we are having errors related to the query in to the database. See logs : We’re on DHIS2 Version 2.35.6 * INFO 2021-11-08T08:09:36,657 'admin' create org.hisp.dhis.period.Period, name: 2021Q4, uid: 2021Q4 (AuditLogUtil.java [http-nio-8080-exec-4]) dhis2-web_1 | * WARN 2021-11-08T08:09:36,663 SQL Error: 0, SQLState: 25006 (SqlExceptionHelper.java [http-ni…

Thanks!

[Ulanbek]

Hi @Gassim,

I did. Basically default value is default_transaction_read_only =off.
Didn’t work…
But as soon as I downgraded everything started to work perfectly fine

Regards

[joao.machiana]

Hi
We are having issues creating or updating existing Data Visualizer favorites (visualizations). When the user clicks on File->Save or File->Save as (and write a name for the visualization) it returns the following error to the screen

The server showed the following log errors:
Tomcat:

Postgres:

Any idea on how to fix this issue.
Our server setup is as follows:
Tomcat 9
Postgres 14.9
Java 11
Dhis2 2.37.9.1
RAM 96GB
CPU 48Cores

Best regards

[Gassim (AL-Gassim Sharaf Addin)]

Hi @joao.machiana

Thank you for your post! As you can see I moved the topic to this one because it’s exactly the same issues and the same dhis2 version 2.37.9.1. I think it’s a bug related to this: [DHIS2-11856] - Jira

Another community member (@diallotafsir52) had faced this issue and solved it by making some changes, see the suggestion:

ERROR/ cannot execute nextval() in a read-only transaction

Resolved by running . select nextval (‘hibernate_sequence’); once directly in the database.

[Ulanbek]

Hi @joao.machiana,

I had the same error. What I did:

• Downgraded to. 2.37.8
• Run Tomcat
• Created what I need
• Upgrade back to 2.37.9.1

It seems some bugs on 2.37.9, and after downgrade and upgrade process that bug just dissappeared.

Try it please.
Regards,

[Gassim (AL-Gassim Sharaf Addin)]

Thank you for sharing @Ulanbek! I’ve also shared this with @dhis2-analytics with hope that they’ll investigate the issue further.

[joao.machiana]

Hi
Thanks for the tip @Ulanbek it helped, but unfortunately did not completely solve the problem.
I’m able to create and save successfully new visualizations.
But when I’m updating an existing visualization it returns the reported error.
As a work around deleting the existing visualization and creating a new one the same name works perfectly (we can live with that for now)

Best regards