Connecting to Google SQL with a simple Java program (JDBC)

Question

I have a simple Java program (not a web application/Google Engine/etc.). I went over every guide in Google Cloud SQL website, and I can't figure out how to connect to the server.

Right now I have

Class.forName("com.mysql.jdbc.Driver");

and

connection = DriverManager.getConnection("jdbc:mysql://x:3306/y?user=root");

Where x is IP address of the instance (taken from the properties of the instance) and y is simply the instance ID.

Am I doing something wrong? I also set a password for the root as one of the tutorials suggested. Should I specify it somewhere in the code?

Thanks.

EDIT

Following mohan rathour's link, I obtained Cloud SQL MySQL Socket Factory and my code now looks like:

Class.forName("com.mysql.jdbc.Driver");

and

String jdbcUrl = String.format("jdbc:mysql://google/%s?cloudSqlInstance=%s&"+"socketFactory=com.google.cloud.sql.mysql.SocketFactory",
                    "dbname",
                    "instanceConnectionName");

connection = DriverManager.getConnection(jdbcUrl, "userName", "password");

I get the following in my console:

INFO: Connecting to Cloud SQL instance [instanceName].
com.google.cloud.sql.mysql.SslSocketFactory getInstance
INFO: First Cloud SQL connection, generating RSA key pair.

But then I get this exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.

SOLUTION

No need to use "Cloud SQL MySQL Socket Factory".

In the Google SQL panel, go to your instance panel -> Access Control -> Authorization, add the following network address and click save.

0.0.0.0/0

Now you can connect with this code:

Class.forName("com.mysql.jdbc.Driver");
...
String url= String.format("jdbc:mysql://x:3306/y");
connection = DriverManager.getConnection(url, z, w);

Where x is your instance IP (get it from the panel), y is the instance name, z is the database (not account) user name (usually "root") and w is the user password (you have to set it. look at Google's tutorials).

If you're getting an error message, you're doing something wrong. Are you getting an error message? — Kayaman
– Kayaman, Commented Nov 29, 2016 at 9:08
Yes, I'm getting an SQLException for the connection=... line. The exception is: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure — John Benedetto
– John Benedetto, Commented Nov 29, 2016 at 9:11
@Kayaman this is not a duplicate. I have no problem connecting with other MySQL services in exactly the same way. The problem here is related to Google SQL. — John Benedetto
– John Benedetto, Commented Nov 29, 2016 at 9:18
Well did you read the link provided by mohan rathour? You need to take extra steps to connect from outside the cloud. — Kayaman
– Kayaman, Commented Nov 29, 2016 at 9:58

Vadim · Accepted Answer · 2016-12-03 04:08:34Z

2

By default, Cloud SQL instances do not allow connections on the public IP. You must add individual subnets from which you would like to connect. You can see the full documentation here: https://cloud.google.com/sql/docs/external#appaccessIP

An alternative that does not require whitelisting IP addresses is using the Cloud SQL Socket Library that uses an SSL session with short-lived certs: https://cloud.google.com/sql/docs/external#java

Whitelisting 0.0.0.0/0 is not recommended as it opens the database to the entire world.

answered Dec 3, 2016 at 4:08

Vadim

5,1261 gold badge28 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kevin Workman Over a year ago

Can you expand on why whitelisting 0.0.0.0/0 is not recommended? Users still need a username and password to interact with the database. Having the connection open but requiring a username and password is exactly how e.g. AWS RDS does it. So why go through the extra hoops of whitelisting specific ip addresses?

Vadim Over a year ago

Port 3306 is constantly scanned by many actors. If a running database is found, it is probed for logins. It's possible that they might not find an account today, but they will keep probing and might find one in the future. Someone might add a new account that is not as secure without realizing that the DB is open to the world. Most people do not expect a database server to be accessible from the open internet. RDS has "security groups" which serves the same purpose.

Kevin Workman Over a year ago

Interesting, thanks for the info. I guess my confusion is caused by the RDS "security groups" not being activated by default. Does that mean that RDS by default is open to the same risks you just described? I guess that means Amazon decided it was an acceptable risk, at least by default? I'm surprised these services don't have login attempt rate limiters to solve this problem. Anyway, thanks again for the info! +1

Vadim Over a year ago

I haven't used RDS much, but I'm pretty sure it's locked down by default as well. At the end of the day, both RDS and Cloud SQL run MySQL and are generally limited by what MySQL provides. MySQL has a host-based login attempt limit, but that's not that hard to get around if you can keep changing your source IP.

Vadim Over a year ago

Understood. We'd like to make the default setup for new Cloud SQL instances easier, but not everything is ready for that yet.

|

mohan rathour · Accepted Answer · 2016-11-29 10:11:49Z

1

Have you created local instance of google mysql or you can connecting direclty. you can check below link Google mysql database cloud connection in java or check with google driver like Class.forName("com.mysql.jdbc.GoogleDriver");

edited Nov 29, 2016 at 10:11

answered Nov 29, 2016 at 8:58

mohan rathour

4302 silver badges12 bronze badges

6 Comments

John Benedetto Over a year ago

It works with a local database and also with other free MySQL services. What should I changed the connection URL to, if I'm using Google SQL?

mohan rathour Over a year ago

Make sure there is no firewall blocking the access to port 3306

John Benedetto Over a year ago

Where should I check it? I already used 3306 as a port for a free MySQL service and it worked fine. Maybe my url is wrong?

mohan rathour Over a year ago

Have you created local instance of google mysql or you can connecting direclty. you can check below link cloud.google.com/sql/docs/external#java or chek with google driver like Class.forName("com.mysql.jdbc.GoogleDriver");

Kayaman Over a year ago

@mohanrathour You should probably edit your answer to be about the link you provided, since that's the real issue here, not the connecting code.

|

Collectives™ on Stack Overflow

Connecting to Google SQL with a simple Java program (JDBC)

2 Answers 2

6 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related