1
  • I deploy a docker container with Ghost inside, to the Google Cloud Run.
  • The Cloud Run service has a service account with Cloud SQL Client role.
  • I've added the SQL instance into the connections of the Cloud Run Service.
  • The Ghost's configuration file is the below property
  "database": {
    "client": "mysql",
    "connection": {
      "socketPath": "/cloudsql/xxxxxxx",
      "user": "xxxxxxx",
      "password": "xxxxxxx",
      "database": "ghost1"
    }
  },
  • I have an Google Cloud SQL - MySQL instance up and running. I can connect to it through Public IP and using the same credentials.

After I deploy the container, I am getting a "We'll be right back" page on the Ghost. When I look into the logs on each side, I see some errors which I do not understand the root cause.

Examples of the logs at Google Cloud SQL:

2021-11-14T06:40:37.183921Z 6971 [Warning] User 'mysql.session'@'localhost' was assigned access 0x8000 but was allowed to have only 0x0.

2021-11-14T06:49:09.008652Z 7002 [Note] Aborted connection 7002 to db: 'ghost1' user: 'xxxxxxx' host: 'cloudsqlproxy~107.178.207.100' (Got an error reading communication packets)

2021-11-14T06:50:29.721121Z 7471 [Note] Got timeout reading communication packets

Examples of the logs at Google Cloud Run

DatabaseError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? at DatabaseError.KnexMigrateError

I have tried a lot of combinations like using VPC connector and Private IP but I keep getting the same network timeout errors all the time. I suspect that the Ghost mysql adaptor library (knex) is doing something wrong but I am not sure whether that is true and if there is something I can do about it.

Thanks for your help

Mustafa Ekim
  • 133
  • 1
  • 10
  • https://cloud.google.com/sql/docs/mysql/error-messages – Martin Zeitler Nov 14 '21 at 07:38
  • I've already checked this page but was not helpful to me. I don't understand what I am supposed to change – Mustafa Ekim Nov 14 '21 at 08:49
  • @MustafaEkim According to [this](https://stackoverflow.com/questions/40435315/knex-timeout-acquiring-a-connection-the-pool-is-probably-full-are-you-missing), updating your knex version might solve your issue. Also, make sure you are closing your SQL connections when needed as stated [here](https://cloud.google.com/sql/docs/mysql/manage-connections). – xBurnsed Nov 15 '21 at 09:42
  • I found [this](https://stackoverflow.com/a/60938880/12512650) similar question which explains how to connect. Please note that the same user made a more in depth explanation [here](https://parondeau.com/blog/self-hosting-ghost-gcp). – Lluís Muñoz Nov 23 '21 at 15:51

3 Answers3

0

For what it's worth, we have an example app that connects over Unix sockets.

As long as you're connecting to public IP, you won't need a Serverless VPC Access connector or a Private IP.

Also, you might double check that your socket path is correct. It should look like this: /cloudsql/project-name:region:instance-name.

There's a similar question here that might help:

How do I run Ghost with MySQL on GCP?

enocom
  • 1,496
  • 1
  • 15
  • 22
  • Hi, thanks but according to the Google documentation, socketPath is correct name for the property: https://cloud.google.com/sql/docs/mysql/connect-run – Mustafa Ekim Nov 17 '21 at 18:38
  • Oh sorry. I misread and thought you were using Postgres. Let me take another look. – enocom Nov 17 '21 at 22:41
  • Updated my answer. Hope that helps. – enocom Nov 17 '21 at 22:52
  • Hi, unfortunately the socketPath property's value seems to be correct. I use public IP. I tried to use the Private IP as well. I tried to use with and without connector. I always get the same kind of errors like: [Got an error reading communication packets, User 'mysql.session'@'localhost' was assigned access 0x8000 but was allowed to have only 0x0.] – Mustafa Ekim Nov 18 '21 at 06:28
  • Hi @MustafaEkim, did you resolve it? Im getting same errors. – AdrianEunoia Feb 04 '22 at 13:24
  • Unfortunately I could not resolve the issue. I tried many approaches and finally gave up. I doubt that one of the underlying packages that the mysql library uses for establishing a connection with google cloud mysql instance is not supported – Mustafa Ekim Feb 07 '22 at 05:00
0

Make sure you allow SSL connections only and create a new certificate chain for your instance.

SSL Toggle

       "ssl": {
            "cert": "<database_cert>",
            "ca": "<database_ca>",
            "key": "<database_key>"
       }

example here

Beshoy Hanna
  • 611
  • 2
  • 9
  • 29
0

Activating SSL connections only and adding the certificate chain did not work for me.

I am using the unix socketPath to connect from Ghost to a CloudSQL database like mentioned above: /cloudsql/project-name:region:instance-name.

When running Ghost version 5.x.x the db connection stopped working after some random time. The Cloud Run logs showed: Error: connect ETIMEDOUT and Error: Connection lost: The server closed the connection.

I tested my configuration by running Ghost on localhost, using the cloudsql-proxy for authentication and it worked without problems.

I finally got it working on Cloud Run by patching the node_modules/knex/lib/client.js. Re-initialise the connection pool and try to reconnect to the database in case the pool has been destroyed.

The approach is also explained here in more detail: https://stackoverflow.com/a/69191743/2546954

Robert Stöttner
  • 23
  • 5