Google Cloud SQL Authentication Error When Trying to Connect Through Android App

Question

The task I am trying to complete is to connect to a Google Cloud MySQL database via an Android application. I am following this guide: https://cloud.google.com/sql/docs/mysql/connect-connectors. I have added my IP address to the allowed networks for the database instance and have enabled the cloud SQL API. I used the example code as a starting point, but I get this error when I run the application (using a simulator):

2023-03-25 15:58:37.727 13755-13755/com.example.connectiontest E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.example.connectiontest, PID: 13755
    com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Could not create connection to database server.
        at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:595)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:581)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
        at com.example.connectiontest.MainActivity$1.onClick(MainActivity.java:53)
        at android.view.View.performClick(View.java:7506)
        at com.google.android.material.button.MaterialButton.performClick(MaterialButton.java:1202)
        at android.view.View.performClickInternal(View.java:7483)
        at android.view.View.-$$Nest$mperformClickInternal(Unknown Source:0)
        at android.view.View$PerformClick.run(View.java:29334)
        at android.os.Handler.handleCallback(Handler.java:942)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loopOnce(Looper.java:201)
        at android.os.Looper.loop(Looper.java:288)
        at android.app.ActivityThread.main(ActivityThread.java:7872)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:548)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:936)
     Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server.
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
        at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:997)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:814)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:444)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:237)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:560)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) 
        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81) 
        at com.example.connectiontest.MainActivity$1.onClick(MainActivity.java:53) 
        at android.view.View.performClick(View.java:7506) 
        at com.google.android.material.button.MaterialButton.performClick(MaterialButton.java:1202) 
        at android.view.View.performClickInternal(View.java:7483) 
        at android.view.View.-$$Nest$mperformClickInternal(Unknown Source:0) 
        at android.view.View$PerformClick.run(View.java:29334) 
        at android.os.Handler.handleCallback(Handler.java:942) 
        at android.os.Handler.dispatchMessage(Handler.java:99) 
        at android.os.Looper.loopOnce(Looper.java:201) 
        at android.os.Looper.loop(Looper.java:288) 
        at android.app.ActivityThread.main(ActivityThread.java:7872) 
        at java.lang.reflect.Method.invoke(Native Method) 
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:548) 
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:936) 
     Caused by: java.lang.RuntimeException: Unable to obtain credentials to communicate with the Cloud SQL API
        at com.google.cloud.sql.core.CoreSocketFactory$ApplicationDefaultCredentialFactory.create(CoreSocketFactory.java:392)
        at com.google.cloud.sql.core.CoreSocketFactory.getInstance(CoreSocketFactory.java:140)
        at com.google.cloud.sql.core.CoreSocketFactory.connect(CoreSocketFactory.java:256)
        at com.google.cloud.sql.core.CoreSocketFactory.connect(CoreSocketFactory.java:215)
        at com.google.cloud.sql.mysql.SocketFactory.connect(SocketFactory.java:57)
        at com.google.cloud.sql.mysql.SocketFactory.connect(SocketFactory.java:43)
        at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:63)
        at com.mysql.cj.NativeSession.connect(NativeSession.java:120)
        at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:944)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:814) 
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:444) 
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:237) 
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198) 
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121) 
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358) 
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) 
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477) 
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:560) 
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) 
        at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81) 
        at com.example.connectiontest.MainActivity$1.onClick(MainActivity.java:53) 
        at android.view.View.performClick(View.java:7506) 
        at com.google.android.material.button.MaterialButton.performClick(MaterialButton.java:1202) 
        at android.view.View.performClickInternal(View.java:7483) 
        at android.view.View.-$$Nest$mperformClickInternal(Unknown Source:0) 
        at android.view.View$PerformClick.run(View.java:29334) 
        at android.os.Handler.handleCallback(Handler.java:942) 
        at android.os.Handler.dispatchMessage(Handler.java:99) 
        at android.os.Looper.loopOnce(Looper.java:201) 
        at android.os.Looper.loop(Looper.java:288) 
        at android.app.ActivityThread.main(ActivityThread.java:7872) 
        at java.lang.reflect.Method.invoke(Native Method) 
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:548) 
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:936) 
     Caused by: java.io.IOException: The Application Default Credentials are not available. They are available if running in Google Compute Engine. Otherwise, the environment variable GOOGLE_APPLICATION_CREDENTIALS must be defined pointing to a file defining the credentials. See https://developers.google.com/accounts/docs/application-default-credentials for more information.
        at com.google.auth.oauth2.DefaultCredentialsProvider.getDefaultCredentials(DefaultCredentialsProvider.java:125)
2023-03-25 15:58:37.728 13755-13755/com.example.connectiontest E/AndroidRuntime:     at com.google.auth.oauth2.GoogleCredentials.getApplicationDefault(GoogleCredentials.java:125)
        at com.google.auth.oauth2.GoogleCredentials.getApplicationDefault(GoogleCredentials.java:97)
        at com.google.cloud.sql.core.CoreSocketFactory$ApplicationDefaultCredentialFactory.create(CoreSocketFactory.java:390)
            ... 33 more

I read up on the Application Default Credentials, but I do not believe environment variables are a thing on mobile devices (correct me if I am wrong), and I have been unsuccessful in finding an alternate method of authentication in Google's docs. My simplified application code is below for reference.

Main Activity

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class MainActivity extends AppCompatActivity {

    private static final String CONNECTION_NAME = "***";
    private static final String DB_NAME = "***";
    private static final String DB_USER = "***";
    private static final String DB_PASSWORD = "***";

    Button testConn;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        testConn = (Button) findViewById(R.id.button);
        testConn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String jdbcURL = String.format("jdbc:mysql:///%s", DB_NAME);
                Properties connProps = new Properties();
                connProps.setProperty("user", DB_USER);
                connProps.setProperty("password", DB_PASSWORD);
                connProps.setProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
                connProps.setProperty("cloudSqlInstance", CONNECTION_NAME);

 
                HikariConfig config = new HikariConfig();
                config.setJdbcUrl(jdbcURL);
                config.setDataSourceProperties(connProps);
                config.setConnectionTimeout(10000); 

                HikariDataSource connectionPool = new HikariDataSource(config);//this is where the code breaks

                try {
                    Connection conn = connectionPool.getConnection();
                    String stmt = "SELECT test_field FROM test_table limit 1;";
                    PreparedStatement selectStmt = conn.prepareStatement(stmt);
                    selectStmt.setQueryTimeout(10); 
                    ResultSet rs = selectStmt.executeQuery();
                    while (rs.next()) {
                        Toast.makeText(getApplicationContext(), rs.getString("test_field"), Toast.LENGTH_LONG).show();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        });
    }
}

build.gradle

plugins {
    id 'com.android.application'
}

android {
    namespace 'com.example.connectiontest'
    compileSdk 33

    defaultConfig {
        applicationId "com.example.connectiontest"
        minSdk 26
        targetSdk 33
        versionCode 1
        versionName "1.0"

        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
    }

    packagingOptions {
        resources.excludes.add("META-INF/*")
    }

    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
        }
    }
    compileOptions {
        sourceCompatibility JavaVersion.VERSION_1_8
        targetCompatibility JavaVersion.VERSION_1_8
    }
}

dependencies {

    implementation 'androidx.appcompat:appcompat:1.6.1'
    implementation 'com.google.android.material:material:1.8.0'
    implementation 'androidx.constraintlayout:constraintlayout:2.1.4'
    implementation files('libs/mysql-connector-j-8.0.32.jar')
    implementation 'com.zaxxer:HikariCP:3.4.5'
    implementation 'com.google.cloud.sql:mysql-socket-factory-connector-j-8:1.11.0'
    testImplementation 'junit:junit:4.13.2'
    androidTestImplementation 'androidx.test.ext:junit:1.1.5'
    androidTestImplementation 'androidx.test.espresso:espresso-core:3.5.1'
}

Any help is be greatly appreciated!

Please read: [JDBC vs Web Service for Android](https://stackoverflow.com/q/15853367/295004) — Morrison Chang, Mar 26 '23 at 00:13
Thanks @MorrisonChang. I am changing my app structure to route through a web server instead of directly connecting. — CurtisF, Mar 26 '23 at 18:59
Seems like your issue is resolved, Can you share yours solution as answer so that it helps others too? — Roopa M, Mar 27 '23 at 09:20

score 0 · Accepted Answer · answered Mar 28 '23 at 16:48

For anyone having a similar issue, I solved this by using the following architecture: Android App <-> Web Server <-> Cloud SQL.

App code to request data from server

String url = "http://999.999.9.99:999";//URL of web server. This example just plugs into one that is locally hosted.
StringRequest strRequest = new StringRequest(Request.Method.GET, url,
                response -> {
                    String result = response.toString();
                    Toast.makeText(MainActivity.this,result, Toast.LENGTH_LONG).show();
                },
                error -> {
                    Toast.makeText(MainActivity.this, "Error", Toast.LENGTH_LONG).show();
                    Log.e("MainActivity", error.getCause().getMessage());
                    Log.e("MainActivity", "loadDogImage error: ${error.localizedMessage}");
                });
       
requestQueue = Volley.newRequestQueue(MainActivity.this);
requestQueue.add(strRequest);

Server code. I used node.js + the MySQL node library

const express = require("express");
const app = express();
const port = 80;
const mysql = require('mysql');

let connection = mysql.createConnection({
  host: '99.99.99.999',//cloud SQL access IP Address
  user: 'some_user', //username
  database: 'some_database', //database name
  password: 'some_password' //user password
});

  connection.connect(function(err) {
    if (err) {
      console.error('Error connecting: ' + err.stack);
      return;
    }
    console.log('Connected as thread id: ' + connection.threadId);
  });

app.get("/", (req, res) => {
  console.log(req.url);
  connection.query(
      "SELECT * FROM `test_table` LIMIT 1", 
      function(error, results, fields) {
        if (error) throw error;
        res.json(results);
      }
    );
});

app.listen(port,err => {
  if (err) {
    console.log("there was a problem", err);
    return;
  }
  console.log('listening on port ' + port);

});

Google Cloud SQL Authentication Error When Trying to Connect Through Android App

1 Answers1