Could not execute JDBC batch update

Products

• CA API Gateway

Issue/Introduction

We are using Gateway 10 CR3 Docker Image.
Error we keep seeing in logs :

Connection is read-only. Queries leading to data modification are not allowed; nested exception is java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed

Could not execute JDBC batch update; SQL [update service_usage set authorized, completed, request where nodeid and serviceid]; Connection is read-only. Queries leading to data modification are not allowed; nested exception is java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed

 

Environment

Release : 10.0

Cause

If those instances are using failover mode to the database.

Based on documentation: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-config-failover.html

we can try to add failOverReadOnly=false to the connection string to avoid running into readonly mode. 

 

Resolution

"jdbc:mysql://IP1:port1,IP2:port2/CommonDbName?failOverReadOnly=false"

In this situation at any point of time your application will be connected to only one db, which is on IP1, and once this db is down, it will switch to second DB i.e. IP2, and since you have marked failOverReadOnly to false, second DB will be active in both read/write mode.