CODEWITHSUNDEEP
mysqldockercakephpgoogle-cloud-sqldocker-container
Me JW Crouch
Me JW Crouch

Reputation: 11

CakePHP connect to Google SQL Proxy Container

For starters, I have a docker container running for "cloud-sql-connectors/cloud-sql-proxy:2.14.3" and it has credentials and appears to have started and is running fine.

2025-01-29 12:31:44 2025/01/29 18:31:44 Authorizing with the credentials file at "/path/to/service-account-key.json"
2025-01-29 12:31:44 2025/01/29 18:31:44 [project-name:us-central1:instance-name] Listening on [::]:5432
2025-01-29 12:31:44 2025/01/29 18:31:44 The proxy has started successfully and is ready for new connections!

I have a CakePHP app, just the basic starter app right now, and I am trying to connect to the SQL database through the proxy container.

In my 'app.php' file, I have 'Datasources' as below

'Datasources' => [
        'default' => [
            'className' => Connection::class,
            'driver' => Mysql::class,
            'persistent' => false,
            'host' => '[::]:5432', // I have also tried 'localhost:5432'
            'username' => 'myusername',
            'password' => 'mypassword',
            'database' => 'mydatabasename',
            'encoding' => 'utf8mb4',
            'timezone' => 'UTC',
            'cacheMetadata' => true,
            'encoding' => 'utf8mb4',
            'flags' => [],
            'cacheMetadata' => true,
            'log' => false,
            'quoteIdentifiers' => false,
        ]

The docker container was created using:

docker run -d -v F:\local\path\to\service-account-key.json:/path/to/service-account-key.json -p 127.0.0.1:5432:5432 gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.14.3 --address 0.0.0.0 --port 5432 --credentials-file /path/to/service-account-key.json project-name:us-central1:instance-name

I am trying to develop the app on my local machine, while using the Google Cloud SQL database so I don't have to worry about figuring this step out after development. Needing to bake the database in question so I can actually start figuring out what I need to do in regards to my own logic.

I'm running the app inside a docker container as well, in hopes to load it up to App Engine.

All online searches either take me to Google's documentation which has very little about actually use cases after getting the proxy running... or 7+ year old CakePHP stuff that doesn't really talk about Google SQL Proxy at all.

And searching the title of the question here on Stack Overflow gave 0 results.

The CakePHP debug landing page says it isn't connecting:

Database CakePHP is NOT able to connect to the database.

Connection to Mysql could not be established: SQLSTATE[HY000] [2002] Connection refused

But the Proxy's container doesn't even show an attempt to connect was made (if I try to go to localhost:5432 the container does accept the connection, but is closed by client/browser, due to it not sending any actual code to display on the HTML, it shows:

localhost sent an invalid response.

ERR_INVALID_HTTP_RESPONSE"

Any help appreciated, thanks in advance.

Upvotes: 1

Views: 46

Answers (1)

Jonathan Hess
Jonathan Hess

Reputation: 356

Jonathan from the Cloud SQL Auth Proxy team here. It looks like you have your proxy set up correctly. I think that the configuration of your application's docker container may need to be adjusted.

You got the first step correct. You started your proxy docker container with this command:

docker run -d \ 
  - v F:\local\path\to\service-account-key.json:/path/to/service-account-key.json \
  -p 127.0.0.1:5432:5432 \
  gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.14.3 \
  --address 0.0.0.0 \
  --port 5432 \
  --credentials-file /path/to/service-account-key.json \
  project-name:us-central1:instance-name

This flag: -p 127.0.0.1:5432:5432 binds host machine port 127.0.0.1:5432 to port 5432 inside the container, where the proxy is listening for connections.

Now, you need to run your application's container so that it can reach the host network interface and connect to 127.0.0.1:5432.

There are several ways to do this, but for local development the easiest is to start your application container with the --network=host flag. This way the PHP application inside the container will behave as if it were connected directly on your host machine's network interfaces, which will give it access to the host machine's 127.0.0.1:5432 port.

docker run -d \
   --network=host \
   your-application-image

In your PHP application, configure the database to connect to 127.0.0.1:5432 It is generally better to use the localhost IP address than the hostname "localhost".

'Datasources' => [
        'default' => [
            'className' => Connection::class,
            'driver' => Mysql::class,
            'persistent' => false,
            'host' => '127.0.0.1:5432',
            'username' => 'myusername',
            'password' => 'mypassword',
            'database' => 'mydatabasename',
            'encoding' => 'utf8mb4',
            'timezone' => 'UTC',
            'cacheMetadata' => true,
            'encoding' => 'utf8mb4',
            'flags' => [],
            'cacheMetadata' => true,
            'log' => false,
            'quoteIdentifiers' => false,
        ]

As a more secure alternative, you may consider using Docker Compose, however that is beyond my area of expertise.

Let us know if this works for you.

Upvotes: 0

Related Questions