bigquery-jdbc: unable to run SQL 'CALL BQ.ABORT_SESSION()'

[rooneyp]

Issue Details

Given a connection with EnableSessions=1, and autocommit set to false:

We are unable to close our sessions using 'CALL BQ.ABORT_SESSION()'
When we try to commit the current transaction, we receive :
com.google.cloud.bigquery.BigQueryException: No active transaction found'

We do this to clean up resources like session tables, and to be good citizens.
we are aware that there is a 24hour housekeeping job with runs server side, but we do not want to incur storage costs for session tables no longer in use, during that period

Example

here is some 'psuedo' code:

// connection has EnableSessions=1,
connection.setAutoCommit(false);

// we next run sql statements which create use a session tmp table
// conn.getStatement...

// we commit our work
connection.commit(); //this commits and creates a new txn

// we attempt to close our session, so resources are removed like session tmp tables
try (Statement st = connection.createStatement()) {
    st.execute("CALL BQ.ABORT_SESSION()");
} catch (SQLException e) {
    throw new RuntimeException(e);
}


// We attempt to commit to have the ABORT_SESSION() executed, 
// this should also enable autoCommit and create a new transaction
// but fails with 'com.google.cloud.bigquery.BigQueryException: No active transaction found'
connection().setAutoCommit(true);  

Environment

Required: Provide details about your environment. If relevant, please also provide details about your GCP environment. The environment details MUST be filled out.

Provide the relevant details about your environment:

• OS Type and Version:
• Java Version and JDK Vendor:
• (If using GraalVM) GraalVM Version:

(Optional) If deployed on GCP, provide the relevant details about your GCP deployment environment:

• Project ID:
• Cloud Services:
• Deployment Environment (Compute, GKE, Run, etc):

Dependencies

shaded driver used:

      <dependency>
          <groupId>com.google.cloud</groupId>
          <artifactId>google-cloud-bigquery-jdbc</artifactId>
          <version>1.1.0</version>
          <classifier>all</classifier>
      </dependency>

2. Provide the versions of the relevant libraries:
   • Libraries-Bom (com.google.cloud:libraries-bom):
     • (If not using Libraries-Bom) Client Libraries:
   • Gax (gax / gax-grpc / gax-httpjson):
   • Auth (google-auth-library-oauth2-http / google-auth-library-credentials):
   • Any other relevant Java SDK dependencies:
     • Google-Http-Java-Client (com.google.http-client:google-http-client):

Reproducer

Required: Provide a reproducer and the steps needed to reproduce this issue locally. If unable to provide a reproducer, please provide code snippets to help reproduce the issue. One of the two sections below MUST be filled out.

1. A reproducer is the quickest method to resolve this issue. It could be a test case or a sample application. It is easier for us to troubleshoot the problem and to verify the solution.

Steps to reproduce:

Example

1. Enable Speech API
2. Upload a .mp4 file to GCS
3. ...
4. See {ERROR}

If unable to provide a reproducer, please provide a reason: ...

2. Provide as many code snippets as possible:

Example

try (InstancesClient instancesClient = InstancesClient.create()) {
  ...
}

Logs and Stack Trace

Required: Provide logs that showcase the error. This will help show the flow of the application and help narrow down the cause. Additionally, provide a stack trace of the error if possible.

The Java SDK has a troubleshooting guide for enabling logs. This contains information regarding client-server communication, request and response details, and logging in dependency libraries. If using this guide, please obfuscate any private information (bearer tokens, request and response params, etc).

Additionally, please provide a stack trace of the error seen:

Example

TransportContext.java:347|Fatal (CERTIFICATE_UNKNOWN): PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target (

"throwable" : {

sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

	at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:456)

	at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:323)

...

Behavior

Optional: Any additional information about the behavior of the error is helpful to debug.

Behavioral Questions:

• When did the issue begin? Is this behavior related to any dependency version upgrade?
• Is this behavior flaky? Or is this consistently seen in production?
• Is this behavior related to the volume of data?