App engine fails to connect to Google cloud sql

Question

My Google app engine application fails to connect to Google cloud sql instance with this error:

'PDOException' with message 'SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it.

I've checked the documentation and followed instructions closely. I added the app as an authorised application and set the root password using the cloud console. I also gave the instace an ip address and can connect to it from Workbench on the local development machine. Using workbench, I added a user and configured permissions. But I'm still unable to connect to it from the dev version of the app (same ip as workbench) or the deployed app.

Here's my connection line:

$conn = new PDO('mysql:unix_socket=/cloudsql/****:****;charset=utf8', 'the_username', 'its_password');

this question appears to be similar to this problem. However, even after setting up a new user and granting permissions I'm still unable to connect. Any idea where I went wrong?

Thanks

Answer 1

Note that setting the root passwords only applies the connections coming over IP. The connections from App Engines show to the MySQL server as coming from localhost. So make sure the password for 'root@localhost' is the one you configured in the code.

A quick way to check the state of the passwords is to connect over IP and issue the following query:

mysql> SELECT user,host,password FROM mysql.user;
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| root  | localhost |                                           |
| root  | 127.0.0.1 |                                           |
| root  | ::1       |                                           |
|       | localhost |                                           |
| root  | %         | *3D56A309CD04FA2EEF181462E59011F075C89548 |
| admin | 127.0.0.1 |                                           |
+-------+-----------+-------------------------------------------+
6 rows in set (0.11 sec)

mysql> SELECT password('xxx');
+-------------------------------------------+
| password('xxx')                           |
+-------------------------------------------+
| *3D56A309CD04FA2EEF181462E59011F075C89548 |
+-------------------------------------------+
1 row in set (0.11 sec)

mysql>

The above shows how an instance shows up after setting the root password for connections over IP ('root@%') to 'xxx'. Note that the password for 'root@localhost' is blank so connections from App Engine still do not require any passwords.

Answer 2

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:///? user=root omitting the &password= part

Answer 3

One thing I tried was trying to connect to my application via a MySQL client directly to my proxy to see if my code was the problem: See: Connecting mysql Client Using the Cloud SQL Proxy

That still didn't work for me, which lead me to believe it may be something with the actual network connection. I noticed the Cloud SQL proxy I was running locally did return the right instance IP address (the error looked something like this:

couldn't connect to "{connectionName}": dial tcp {ip address}:3307: getsockopt: operation timed out

You can confirm the IP Address returned there matches with the one in the Cloud SQL Console.

I then tried connecting via a different internet connection and was able to connect. Turns out it was the firewall in the original connection that was not letting the connection happen! Double check any security settings in place that may prevent the connection from going through.