App Engine Java Servlet does not connect to Cloud SQL

Question

I have created a java web servlet using app engine, the servlet makes requests to a database. I have tested the servlet locally using a local database and it worked perfectly, i then proceeded to test the servlet locally but istead accessed the Cloud SQL database, this also worked perfectly.

My problem arises after i deploy the servlet. Once deployed all database requests return the following:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not 
received any packets from the server.

I checked within the cloud console, and my app was properly added to the cloud SQL Authorized App Engine Applications under the Access Control tab.

Has anyone had similar problems with deployed app engine servlets? Any solutions or advice out there? I would appreciate any and all help!!!

UPDATE:

The above error was generated using the following code to access the db

Class.forName("com.mysql.jdbc.Driver");
Url = "jdbc:mysql://<ip-address-cloudsql>:3306/<dbname>";
Connection con = DriverManager.getConnection (Url,"root",<password>);

the same error was acheived using this code, note that it is very similar to the code shown in the example here https://developers.google.com/appengine/docs/java/cloud-sql/

Class.forName("com.mysql.jdbc.GoogleDriver");
Url = "jdbc:google:mysql://<appID:instanceID>/<dbname>? 
user=root&password=<password>";
Connection con = DriverManager.getConnection (Url);

I followed the formatting tips show in this stackoverflow post when it came to setting the url using appid and instance id: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:

Using this code resulted in the following different error:

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

I'm assuming it says localhost because my cloudsql database is set to follow the app engine servlet. Also, know that both of these methods worked fine when running the servlet locally and accessing the cloud sql database.

any thoughts? i don't know what else to try:[

Denouement

Have you changed the password for root@localhost? By default, there is no password for root@localhost. A query that would help see the state of the user/passwords is SELECT user,host,password FROM mysql.user. – Rotor

i thought i changed it in the cloud console. but when running that query a password was only set the for % user, localhost was blank. changing the password to blank was successful thank you! however i would like to add a password for localhost aswell, how can i do that? – Denouement

You should be able to set the root@localst password using a query like this: UPDATE mysql.user SET password = PASSWORD('XXXX') WHERE user = 'root' AND host = 'localhost' where XXXX is the password you want. – Rotor

One more thing: don't forget to do a FLUSH PRIVILEGES after altering the mysql.user table. Without that the mysqld will not pick up the changes. – Rotor

Answer 1

When connecting to Cloud SQL from an authorized App Engine application, the password is not required (actually it will fail if you try to connect with password).

Change your connection string to jdbc:google:mysql://<appID:instanceID>/<dbname>? user=root omitting the &password=<password> part

Jumada

Wow, this saved me hours. The error message when you try to use a password is not exactly helpful. – Joleenjolene

Yes, it wasted a lot of time trying to figure why it wasn't working. Not a helpful message indeed – Jumada

Just spent 2 hours on this :( they could have emphasized this a little bit more on their documentation... – Ootid

I was facing similar error but after removing the password it started giving error "no password". Also the url has now changed (jdbc:google:mysql://${INSTANCE_CONNECTION_NAME}/${database}‌​?user=${user}&pa‌​ssword=${password}) please refer cloud.google.com/appengine/docs/standard/java/cloud-sql/… – Reins

Can you clarify further what the appID and the instanceID should be? – Devoice

Answer 2

If you have Authorized App Engine Applications you app engine on the access control settings you do not need a password since it is local so just make you password= ""; However if you are using something remote for example phpmyadmin that is run from another host, your command line or a GCE VM that runs through a TCP , SSH or HTML you will need to have a password ="something"; where something is set by you in your access control.

At

Answer 3

To everyone from Google who are looking as to why you might be getting "com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure" on a connection.

Make sure your IP is allowed if you are calling from a test server.

I was testing at a friends house, and this unhelpful error kept showing up.

Chemism

Answer 4

When connecting to Google Cloud Sql you should be careful: -To close your opened connections -To use Exponential backoff algorithm when trying to create new connection. For more information see: https://cloud.google.com/sql/faq

Tontine

Answer 5

If you're using application.properties in Spring Boot app, then just put the below line into application.properties:

spring.datasource.url: jdbc:mysql://google/<dbname>?cloudSqlInstance=<InstanceName>&socketFactory=com.google.cloud.sql.mysql.SocketFactory&user=****&password=****

Eri

This is for local mysql server not for an application on the cloud App Engine. – Reins