JDBC Provisioning Rule timeout/duplicates

[ssolig (Sasha Solig)]

We have had a long running issue with our JDBC provisioning rule where an apparent timeout occurs which results in a second provisioning (create) event. However the original provisioning request is ultimately successful (despite the error message to the contrary) and we end up with duplicate records inserted into the SQL table. This happens at random times a few times a month and does not seem to be associated with SQL patching windows or periods of high system load.

We have tried increasing the provisioningTimeout from the default 60 seconds (first to 90, then to 120) but this has not made any difference. Looking at the timing of the first and subsequent provisioning events, they appear to be well within the timeout period in any case (e.g. 3-4 seconds between the initial FAILED request and the second PASSED one). The actual timeout error received is as follows:

["Failure trying to get a pooled connection to [jdbc:sqlserver://<sqlserver>] java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object","Failure trying to get a pooled connection to [jdbc:sqlserver://<sqlserver>] java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object"]

The rule itself was based on the published example. I do note that there is a placeholder comment in the create block for checking whether the account exists or not, but I’ve not seen any example code which suggests how to do this:

// Ideally we should first check to see if the account already exists.
// As written, this just assumes it does not.

Has anyone else experienced this issue and/or been able to implement the check for an existing account? Would appreciate some insights into a possible solution as every duplicate account created causes significant work in cleaning up and confusion for end users.

[jesvin90 (Jesvin Joseph)]

Hi @ssolig

You could try adding an if condition to see if the row with native identity exists in the table. Here is an example you may want to start with :

// Check if the user already exists
        String checkUserQuery = "SELECT 1 FROM users WHERE login = ?";
        PreparedStatement checkStmt = connection.prepareStatement(checkUserQuery);
        checkStmt.setString(1, (String) account.getNativeIdentity());
        ResultSet rs = checkStmt.executeQuery();

        if (rs.next()) {
            // User already exists
            System.out.println("User already exists.");
            result.setStatus(ProvisioningResult.STATUS_FAILED);
        } else {
            // User does not exist, proceed with insert
            statement = connection.prepareStatement("insert into users (login,first,last,role,status) values (?,?,?,?,?)");
            statement.setString(1, (String) account.getNativeIdentity());
            statement.setString(2, getAttributeRequestValue(account, "first"));
            statement.setString(3, getAttributeRequestValue(account, "last"));
            statement.setString(4, getAttributeRequestValue(account, "role"));
            statement.setString(5, getAttributeRequestValue(account, "status"));
            statement.executeUpdate();

            result.setStatus(ProvisioningResult.STATUS_COMMITTED);
        }

[ssolig (Sasha Solig)]

Thanks @jesvin90 that was exactly what I was looking for - I will give that a go!