Failure on OTK maintanance tasks after upgrade

Products

• CA API Gateway

Issue/Introduction

Facing an issue with OTK maintenance tasks.

It gives an error on query execution:

2022-12-02T04:48:50.011-0300 WARNING 709 com.l7tech.server.jdbc.JdbcQueryingManagerImpl: Failed to perform querying since You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
2022-12-02T04:48:50.012-0300 WARNING 709 com.l7tech.external.assertions.jdbcquery.server.ServerJdbcQueryAssertion: 9104: "Perform JDBC Query" assertion failed due to: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
2022-12-02T04:48:50.012-0300 INFO    709 com.l7tech.server.policy.assertion.ServerAuditDetailAssertion: -4: An error occured when searching for expired sessions
2022-12-02T04:48:50.012-0300 WARNING 709 com.l7tech.server: Executing background policy for scheduled task OTK Database Maintenance - sessions

This is configured with a remote mysql host.

Environment

Release : 10.1

Resolution

The remote database was configured with init_connect SET sql_safe_updates=1

This configuration forces the update/delete to have a key field in the where clause

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_safe_updates 

 

For example, when the task tried to execute the query to clean expired session, the column expiration is not a key or indexed, causing the failure 

DELETE FROM oauth_session
                WHERE expiration <= ${now.seconds} 

The sql_safe_updates should be off as in our ova. 

From mysql on the ova.

> show global variables like '%safe%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| log_statements_unsafe_for_binlog | ON    |
| sql_safe_updates                 | OFF   |
+----------------------------------+-------+