App Engine App cannot connect to Cloud SQL instance

[Karoly Kantor]

My python App Engine app cannot connect to my brand new Cloud SQL instance.

Log says: Not authorized to access instance ....

They are in the same project, Google doc says it should be authorized by default.

Please help me with any ideas on what might have gone wrong.

Thank you.

[George (Cloud Platform Support)]

Hello Karoly,

Have you granted access to your app engine application? You may find an example in the “Using Google Cloud SQL” online document. Have you followed the indicated steps or a similar procedure?

More detail on your connection setup and connecting attempts should prove quite helpful.

[Karoly Kantor]

Thanks George for the reply.

I am told by the 2nd generation SQL Google page that my application is given access by default because it is in the same project.

More info:

1. I am using python and the web2py framework in App Engine.

2. The app works perfectly with Google Datastore but i want to use SQL as well so that I can make a decision what is the final future db platform

3. My connection problem is with a second generation SQL instance.

4. I have just created a first generation instance for test, and the connection problem is gone. Maybe my framework is compatible with 1st gen but not with 2nd gen? If yes, what is the difference?

Thanks for your help.

[George (Cloud Platform Support)]

Your first generation CloudSQL instance has the app name as an authorized user, the second generation doesn’t. What happens if you create a service account for your app on the second generation instance?

Your logs show connection errors to the instance from a user without password.

[Karoly Kantor]

Thanks again, George. It might be that the web2py data base abstraction layer I am using was built for 1st Gen only? I will try to ask them. Maybe it is not passing a user name / password, at least I cannot find a place to set it.

Is it the case that the app should pass the user name/password for the application, e.g. root / pw or any other db user I create?

I have actually added a service account for my app and given it all existing SQL authorizations, but it did not help.

[George (Cloud Platform Support)]

Szia Karoly, 

Assuming you have taken care of this already, just an extra sanity check here: you have to add your MySQL credential to dev_appserver.py like:

dev_appserver.py [root folder of web2py] --mysql_user=[mysql user] --mysql_password=[mysql password] . 

[Karoly Kantor]

No i haven' done that. Are we onto something? :-)

This might be very newbie, but I thought dev_appserver is for local development and it has nothing to do with the environment in the cloud. What should I read to understand this concept?

For now, as I started out with datastore and trying out SQL as well, i am still using GoogleAppEngineLauncher to deploy my app, and the app is still configured to use datastore in the local dev env.

(web2py has a data base abstraction layer that lets me write code that runs on SQL and datastore without modification. This is one of the reasons I chose it, as I do not yet know which one will be better for me on the long run. Currently my code works fine locally with datstore and with 1st gen SQL in the cloud.)

[George (Cloud Platform Support)]

You are right, the configuration is for the local development server. The best would be to send me the code of your app and whatever information is needed to be able to reproduce the issue on this side. To maintain confidentiality, you can do this via private email (You can use the topmost option from the drop-down menu under the reply button, "reply privately to author"). 

[Karoly Kantor]

Dear George, 

Actually, I am using the open source web2py framework ( www.web2py.com ), and the relevant part of my own code is one line only:

db = DAL('google:sql://myprojectname:europe-west1:instance-2ndgen-1/mysql')

This should connect to the data base and create a DAL object for me, which is the class for the data base abstraction layer. And this is what results in the App Engine log entry "Not authorized to access instance ...."

Please note that the same line with a 1st gen instance works perfectly and connects me to the data base, although I am not setting a user name and password anywhere.

 db = DAL('google:sql://myprojectname:instance-1stgen-1/mysql')

I start to suspect that web2py was written / tested with 1st gen where username / password were not necessary, and now it is broken with 2nd gen.

[Karoly Kantor]

The myprojectname-project vs myprojectname discrepancy is something I only made here, it's not there in the code... No way to edit a post here :-( 

On Tuesday, April 25, 2017 at 5:22:08 PM UTC+2, Karoly Kantor wrote:

Dear George, 

Actually, I am using the open source web2py framework ( www.web2py.com ), and the relevant part of my own code is one line only:

db = DAL('google:sql://myprojectname-project:europe-west1:instance-2ndgen-1/mysql')

[George (Cloud Platform Support)]

You can edit your posting from the same drop-down menu mentioned, under the reply button. I have actually modified your project name in your post, as desired. 

Testing connections to your instance-2ndgen-1 is not feasible at the moment, as it is stopped: the developers' console indicates: "This instance is stopped. Start it to allow connections and operations."

Your logs indicate a connection attempt with database_id: "your_project_name:instance-2ndgen-1". For CloudSQL second generation you need to include the region name in the connection string, as well. You may re-check the web2py documentation. 

[Karoly Kantor]

Thanks again for your time. I really appreciate it.

- I have stopped  instance-2ndgen-1 to save cost as it was unused, it is restarted now.

- I have attempted to connect to it with and without the region name, but none of those attempts worked. That's why you probably see the last attempt without the region.

- web2py doc tells me the connection string is to be as below, did you find any other useful information?

Google/SQL

google:sql://project:instance/database

 

[Karoly Kantor]

Hello George, I hope you are still out there.

I have hacked web2py a little bit to send user (root) and password in the connection request. That's what you can see in the latest logs of myprojectname:europe-west1:instance-2ndgen-1

It did not help :-(

[George (Cloud Platform Support)]

What I meant in my answer was to try replacing "project: instance" in the connection string with "project:region: instance", something like project1:us-central1:instance1/unidb . 

Can you connect if you choose a created database such as unidb, in place of a system database? 

As an alternative to the current connection procedure, you may choose to use a proxy, an option described in the "About the Cloud SQL Proxy" document. 

[Karoly Kantor]

George,

1. Yes, I attempted to connect both with and without the region, e.g. myprojectname:europe-west1:instance-2ndgen-1 AND yprojectname:instance-2ndgen-1

1. Yes, originally I connected to data base mysql and when I realized this was not a good idea, I created my own. Both worked with a 1st gen instance, none of them with a 2nd gen.

I will send you a specific log entry in a private message so you can see.

I am yet to try the proxy thing, but i feel it should not be necessary as my app should be granted access to the instance by default according to the documentation, as they are in the same project.

Thank you.

[Karoly Kantor]

In the meantime I solved this problem, maybe this will be useful to others:

My application was using a deprecated data base driver "rdbms". Apparently this one works with 1st generation but not with 2nd. I changed to the "MySQLdb" driver and all is well now. 

Be careful with the args though, the are different, especially tricky was that this driver accepts "passwd" as an arg, and not "password".