Cloud SQL in App Engine Flexible Environment + Custom Docker

[Prashant V]

I was trying to get a cloud sql instance working inside of a container running on App Engine Flexible Environment, and hit a few issues:

First, I couldn't find /cloudsql directory containing unix sockets at all. I'm not sure if this is in custom containers by default?

https://cloud.google.com/appengine/docs/python/cloud-sql/

I managed to get something working by finding a beta_settings key in app.yaml and set it to:

beta_settings:                                                                                                        │

  cloud_sql_instances: <project-id>:<region-id>:<database-id>

This led to the /cloudsql directory existing, but the contents didn't match what I expected, I expected /cloudsql/<app-id>:<database-id>, but it had <region-id> in the middle.

After all that, it still failed to connect, giving me errors that the remote mysql had gone away (even though the app was definitely in the authorized list).

I ended up connecting via IP as a workaround, but I'd like to get this working with the socket. Is Cloud SQL supported in the Flexible Environment?

[Nick (Cloud Platform Support)]

This is interesting. It appears possible, but I can't confirm (and I'm not sure if you can, any longer), that even after enabling the beta_setting and attempting a socket connection, the IP of the Managed VM instance had changed, not being a static IP but ephemeral, resulting in the instance refusing connection. If possible, could you share the logs of the errors you saw? To answer your posts' ending question, Cloud SQL with Flexible Environment apps is supported as a beta feature for now in Python, Node.js, and Go, while Java is not yet supported.

[Prashant V]

Thanks for the reply Nick. Here's the exact error from the container,

Warning: mysqli::mysqli(): MySQL server has gone away in /var/www/html/test.php on line 2
Warning: mysqli::mysqli(): Error while reading greeting packet. PID=222 in /var/www/html/test.php on line 2
Warning: mysqli::mysqli(): (HY000/2006): MySQL server has gone away in /var/www/html/test.php on line 2

I'm using a custom docker container for Wordpress (so PHP), and tried a manual PHP script which produced that.

I changed my database settings to allow all (0.0.0.0/0), and tried a user that can connect remotely (verified from my local machine).

It doesn't really give a very helpful error, I think the error logs are on the server side. I can give you the database ID off-thread if you'd like.

[Prashant V]

I treid out a python app to see whether it was a runtime specific issue. I created a simple app based on the sample app here: https://github.com/GoogleCloudPlatform/python-docs-samples/tree/master/managed_vms/cloudsql

It also failed with a similar error which leads me to believe that this is just an issue with the App Engine flexible environment when trying to access CloudSQL. 

Exception from python:

Traceback (most recent call last):

  File "/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 2074, in _wrap_pool_connect
    return fn()
  File "/env/lib/python3.4/site-packages/sqlalchemy/pool.py", line 376, in connect
    return _ConnectionFairy._checkout(self)
  File "/env/lib/python3.4/site-packages/sqlalchemy/pool.py", line 713, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/env/lib/python3.4/site-packages/sqlalchemy/pool.py", line 480, in checkout
    rec = pool._do_get()
  File "/env/lib/python3.4/site-packages/sqlalchemy/pool.py", line 1060, in _do_get
    self._dec_overflow()
  File "/env/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/env/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 184, in reraise
    raise value
  File "/env/lib/python3.4/site-packages/sqlalchemy/pool.py", line 1057, in _do_get
    return self._create_connection()
  File "/env/lib/python3.4/site-packages/sqlalchemy/pool.py", line 323, in _create_connection
    return _ConnectionRecord(self)
  File "/env/lib/python3.4/site-packages/sqlalchemy/pool.py", line 449, in __init__
    self.connection = self.__connect()
  File "/env/lib/python3.4/site-packages/sqlalchemy/pool.py", line 607, in __connect
    connection = self.__pool._invoke_creator(self)
  File "/env/lib/python3.4/site-packages/sqlalchemy/engine/strategies.py", line 97, in connect
    return dialect.connect(*cargs, **cparams)
  File "/env/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 385, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/env/lib/python3.4/site-packages/pymysql/__init__.py", line 88, in Connect
    return Connection(*args, **kwargs)
  File "/env/lib/python3.4/site-packages/pymysql/connections.py", line 679, in __init__
    self.connect()
  File "/env/lib/python3.4/site-packages/pymysql/connections.py", line 890, in connect
    self._get_server_information()
  File "/env/lib/python3.4/site-packages/pymysql/connections.py", line 1190, in _get_server_information
    packet = self._read_packet()
  File "/env/lib/python3.4/site-packages/pymysql/connections.py", line 946, in _read_packet
    packet_header = self._read_bytes(4)
  File "/env/lib/python3.4/site-packages/pymysql/connections.py", line 982, in _read_bytes
    2013, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

[Nick (Cloud Platform Support)]

Hey Prashant,

Usually issues are reported in the Public Issue Tracker, rather than in Groups as here, but it turns out that the information you've provided is very close to adequate for a Public Issue Tracker report, and I can just go ahead and use it to create an issue tracker thread, once I reproduce the behaviour. In order to make sure I do so properly, however, could you just let me know what changes you'd made to the example app before deploying?

Once I've got that information and got the issue reproduced and a tracking thread made in the Public Issue Tracker, I'll post it back here so you can star the issue to follow it.

Cheers!

Nick
Cloud Platform Community Support

[Prashant V]

Thanks Nick.

I deployed the application as is

https://github.com/GoogleCloudPlatform/python-docs-samples/blob/master/managed_vms/cloudsql

But then I SSH'd into the VM, entered the container, and ran python from the location where the code was, and did:

import main
main.db.create_all()

Which should throw an erorr. (Main reason I did SSH is so I could tweak the DB parameters without having to redeploy)

[Nick (Cloud Platform Support)]

Is main a module you wrote? Why do you expect it should create an error?

[Prashant Varanasi]

Sorry, I should have been clearer- I don't expect it to error of things are working correctly. The fact that it does error is the bug.

--
You received this message because you are subscribed to a topic in the Google Groups "Google App Engine" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-appengine/4V7k7giW8zU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-appengi...@googlegroups.com.
To post to this group, send email to google-a...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-appengine.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-appengine/050c28f0-3351-417d-b589-a56943e3d266%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Nick (Cloud Platform Support)]

Thanks for clarifying, apologies that my last reply was lacking context. I'll take a look at this behaviour now and report back...

On Thursday, April 7, 2016 at 3:44:03 PM UTC-4, Prashant V wrote:

Sorry, I should have been clearer- I don't expect it to error of things are working correctly. The fact that it does error is the bug.

To unsubscribe from this group and all its topics, send an email to google-appengine+unsubscribe@googlegroups.com.
To post to this group, send email to google-appengine@googlegroups.com.

[Nick (Cloud Platform Support)]

Hey Prashant, 

I've deployed the given app and have observed the same as you, that the socket in /cloudsql in the app container is not following the formula shown in the docs sample, being that it contains the region while the docs sample does not. However, the pattern of the socket name is given by the project settings itself in app.yaml. As can be seen in the example app.yaml, we have:

beta_settings:
	cloud_sql_instances: PROJECT:us-central1:INSTANCE

If you change this pattern, the unix socket should also change. The example app you used isn't from the same codebase as the document linked. I hope this has helped clarify the situation.

Best wishes,

Nick
Cloud Platform Community Support

On Thursday, April 7, 2016 at 3:44:03 PM UTC-4, Prashant V wrote:

Sorry, I should have been clearer- I don't expect it to error of things are working correctly. The fact that it does error is the bug.

To unsubscribe from this group and all its topics, send an email to google-appengine+unsubscribe@googlegroups.com.
To post to this group, send email to google-appengine@googlegroups.com.

[Les Vogel]

It won't help anyone on this thread, but since Nick mentioned Java earlier, I'd like to point folks at https://github.com/GoogleCloudPlatform/cloud-sql-mysql-socket-factory -- we are still working on examples & formal documentaiton.

To unsubscribe from this group and all its topics, send an email to google-appengi...@googlegroups.com.
To post to this group, send email to google-a...@googlegroups.com.

Visit this group at https://groups.google.com/group/google-appengine.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-appengine/050c28f0-3351-417d-b589-a56943e3d266%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google App Engine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-appengi...@googlegroups.com.
To post to this group, send email to google-a...@googlegroups.com.

Visit this group at https://groups.google.com/group/google-appengine.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-appengine/31522fdf-4ef9-412d-8019-adbaf416619b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

•  Les Vogel   •  Cloud Developer Relations   •  le...@google.com   •  +1-408-676-7023

[Prashant V]

Hi Nick,

The pattern in the app.yaml has to be PROJECT:REGION:INSTANCE, otherwise it throws an error. I tried to make it just PROJECT:INSTANCE, and I got this error:

ERROR: (gcloud.preview.app.deploy) Error Response: [13] Invalid Cloud SQL name: [PROJECT]:[INSTANCE]

If you deploy that application, are you able to get it to work? No matter what I change, I cannot get it to work using Unix sockets. I'm not sure whether it's App Engine's flexible environment, or Cloud SQL that's causing the error. Unfortunately the error message has no useful information either for me to debug any further.

Thanks,

 - Prashant

On Thursday, 7 April 2016 14:09:37 UTC-7, Nick (Cloud Platform Support) wrote:

Hey Prashant, 

To unsubscribe from this group and all its topics, send an email to google-appengi...@googlegroups.com.
To post to this group, send email to google-a...@googlegroups.com.

[Nick (Cloud Platform Support)]

Hey Prashant,

Upon updating the relevant testing environment, I've observed what you have seen. It appears this is working as intended, and now becomes an issue with the documentation, which we've received a request to update. In the meantime, simply alter your code to expect to connect via a named socket which includes the region as part of the instance id.

Let me know if you still have any questions after this and I'll be happy to help. 

Sincerely, 

Nick
Cloud Platform Community Support

[Prashant V]

Hi Nick,

Even with the region in the app.yaml, it still does NOT work. Without the region, it's not even possible to deploy. With the region, I get the error that I mentioned,

Warning: mysqli::mysqli(): MySQL server has gone away in /var/www/html/test.php on line 2
Warning: mysqli::mysqli(): Error while reading greeting packet. PID=222 in /var/www/html/test.php on line 2
Warning: mysqli::mysqli(): (HY000/2006): MySQL server has gone away in /var/www/html/test.php on line 2

There's a documentation issue, but there's also an issue with named sockets not working from the App Engine Flexible environment at all. Have you tried to deploy the sample application, with the region set, and have it working?

Thanks for all your help with this, but I've spent much more time on this approach than I expected, and will try other options.

 - Prashant

[Nick (Cloud Platform Support)]

Hey Prashant,

Thank you for your reply. While you had mentioned an error like this initially, this is the first time I've seen the actual error text. I'm unfamiliar with the error message you saw and didn't see it when I deployed the test app from python-docs-samples. It appears you're using PHP, and possibly this is from your own codebase?

If you have any issues with the platform not working according to what you understand should be the behaviour after reading documentation, you should post to the Public Issue Tracker, attaching an app which can reliably reproduce the behaviour, and as much details (including error text) as possible. At this stage, I advise posting the code you're using along with your Public Issue Tracker report, and this will enable us to take a look at your situation and see if there's a solution either on our end or yours.

Cheers!

Nick
Cloud Platform Community Support

[Luke Love]

I got this same issue and resolved it

database.yml 

  > host: 123.123.123.123

app.yml

  > cloud_sql_instances: bookshelf-app-1234:us-central1:my-bookshelf-sql