Gateway database fail over fails: FUNCTION ssg.next

Products

CA API Gateway

Issue/Introduction

A stored procedure was introduced in version 6.1.0 of the Gateway to better handle database fail over events and provide more unique database record keys. Under certain circumstances, this stored procedure may not be created on a secondary database node. This article will prescribe the process to create that stored procedure on the impacted system in order to ensure full fail over capabilities are actualized and available.

This issue may be occurring if the following error messages may be presented in the Gateway log file:
WARNING 30 org.hibernate.util.JDBCExceptionReporter: SQL Error: 1305, SQLState:42000
SEVERE 30 org.hibernate.util.JDBCExceptionReporter: FUNCTION ssg.next_hi does not exist

The following error message or messages may be present in the Policy Manager log file:
WARNING: Exception during remote API call: org.springframework.remoting.RemoteAccessException: Could not deserialize result from HTTP invoker remote service [https://securespangateway/ssg/manager/PolicyAdmin]; nested exception is java.lang.ClassNotFoundException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException
com.l7tech.console.logging.DefaultErrorHandler handle

WARNING: The Policy Manager encountered an internal error or misconfiguration and was unable to complete the operation.
Caused by: org.springframework.remoting.RemoteAccessException: Could not deserialize result from HTTP invoker remote service [https://securespangateway/ssg/manager/PolicyAdmin]; nested exception is java.lang.ClassNotFoundException
caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

Environment

All supported versions of the API Gateway

Cause

It is imperative that the source of this error be confirmed before proceeding. Perform the following procedure to verify whether the errors specified above are caused by the absence of a specific stored procedure:

Log into the Gateway appliance as the ssgconfig user
Select Option #3: Use a privileged shell (root)
Access the MySQL prompt
Execute the following SQL query:

show function status\G;

The following output should be printed upon executing the query:

*************************** 1. row ***************************

Db: ssg

Name: next_hi

Type: FUNCTION

Definer: gateway@localhost

Modified: 2014-07-10 12:50:09

Created: 2014-07-10 12:50:09

Security_type: INVOKER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

If the SQL record above is not returned then the stored procedure is not present and will need to be addressed in this article. If the error messages above are being presented even though this stored procedure is present then please contact Layer 7 Support at Broadcom.

Resolution

If the stored procedure is not present then the following process can be executed to restore the function:

1. Log in to the Gateway appliance as the ssgconfig user
2. Select Option #3: Use a privileged shell (root)
3. Log in to the MySQL server: mysql ssg -u gateway -p
NOTE: The value of "gateway" may vary in your environment based upon your configuration but the "root" user should not be used.
4. Enter the credentials for the specified user
5. Run the following SQL queries:

DROP FUNCTION IF EXISTS ssg.next_hi;
delimiter //
CREATE FUNCTION ssg.next_hi() RETURNS bigint NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER
BEGIN
UPDATE hibernate_unique_key SET next_hi=last_insert_id(next_hi)+IF(@@global.server_id=0,1,2);
RETURN IF((last_insert_id()%2=0 and @@global.server_id=1) or (last_insert_id()%2=1 and @@global.server_id=2),last_insert_id()+1,last_insert_id());
END
//
delimiter ;