Using mysqli_connect() for Wordpress connection to Cloud SQL on Google App Engine

Question

I am trying to run Wordpress on Google App Engine standard environment. I have configured a Cloud SQL for MySQL Second Generation instance and can access it using Cloud SQL Proxy with this command:

cloud_sql_proxy  -instances=my_project_id:us-central1:my_project=tcp:3306

The wp-config.php file:

if (isset($_SERVER['GAE_ENV'])) {
    define('DB_HOST', ':/cloudsql/my_project_id:us-central1:my_project'); 
} else {
    define('DB_HOST', '127.0.0.1'); 
}

Finally, I connect to the database using this:

$dbConn = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD) or die (mysqli_error($dbConn));
mysqli_select_db($dbConn, DB_NAME) or die(mysqli_error($dbConn));

This setup works perfectly from the local development environment, which is Cloud Shell. The website runs and I am able to query the database and insert records etc. My problem arises when I deploy to my_project_id.appspot.com using google app deploy. The website runs, but when I try to query the database I receive this error:

Warning: mysqli_connect(): php_network_getaddresses: getaddrinfo failed: Name or service not known in /srv/wp-content/themes/mytheme/system/db.php on line 14 

Line 14 is $dbConn = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD) so I am guessing that mysqli must not like the format of the specified DB_HOST which is :/cloudsql/my_project_id:us-central1:my_project.

In this Community Tutorial there is sample code which uses a unix socket and PDO to connect to the database. I don't know if I should be adding these lines to the app.yaml file and someone using this different connection string.

env_variables:
  MYSQL_DSN: mysql:unix_socket=/cloudsql/my_project_id:us-central1:my_project;dbname=my_dbname
  MYSQL_USER: username
  MYSQL_PASSWORD: password  

My apologies for the lengthy question, but I wanted to provide as much information as possible. Anyone have any ideas what I am doing wrong? Thanks.

Answer 1

It looks like you might be passing in the information incorrectly into mysqli_connect. If you take a look a the documentation for it, it actually takes in 6 parameters: host, username, passwd, dbname, port, socket.

Under host, you can read the following:

Passing the NULL value or the string "localhost" to this parameter, the local host is assumed. When possible, pipes will be used instead of the TCP/IP protocol.

Under socket, it clarifies it should be the socket path:

Specifies the socket or named pipe that should be used.

So you need to call mysqli_connect like this:

mysqli_connect (null, "user", "password", "database", 3306, "/cloudsql/<INSTANCE_CONNECTION_NAME>")

Answer 2

From Cloud Shell, your environment is all setup properly in order to directly connect to Cloud SQL. From AppEngine there's a few other steps necessary in order to connect.

Check out this documentation:

https://cloud.google.com/sql/docs/mysql/connect-app-engine

It should get you up and running.

Answer 3

The answer by @Kurtisvg is absolutely correct in terms of this being the proper format for connecting to Cloud SQL using mysqli_connect:

mysqli_connect (null, DB_USER, DB_PASSWORD, DB_NAME, 3306, "/cloudsql/<INSTANCE_CONNECTION_NAME>")

The original question, however, also mentioned that I was trying to get the connection working for a Wordpress installation. These are the two areas with additional information relevant to this tutorial on how to run Wordpress on Google App Engine standard.

1. Specifying correct mysqli_connect() parameters
Testing the app in the local development environment only required mysqli_connect to use these four parameters: DB_HOST, DB_USER, DB_PASSWORD, DB_NAME. Once the app is deployed to Google App Engine, the mysqli_connect has to use all six parameters: DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_PORT, DB_SOCK. This is the code in db.php that provides the correct parameters, depending on the environment:

if (isset($_SERVER['GAE_ENV'])) {
    $dbConn = mysqli_connect (null, DB_USER, DB_PASSWORD, DB_NAME, DB_PORT, DB_SOCK); 
} else {  // local environment
    $dbConn = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); 
    }

2. Specify correct Wordpress configuration in wp-config.php
What I was finding was that in order Wordpress to function correctly, it was necessary to not only define and use the socket DB_SOCK in mysqli_connect(), but I also had to define a DB_HOST for purposes of the Wordpress installation. This was the configuration that worked in my wp-config.php file:

define('DB_USER', 'user');
define('DB_PASSWORD', 'password');
define('DB_NAME', 'database');
define('DB_PORT', 3306);    

// Check for Google App Engine Environment
if (isset($_SERVER['GAE_ENV'])) {
    $onGae = true;
    define('DB_HOST', ':/cloudsql/<INSTANCE_CONNECTION_NAME>'); 
    define('DB_SOCK', '/cloudsql/<INSTANCE_CONNECTION_NAME>');
} else {
    $onGae = false;
    define('DB_HOST', '127.0.0.1'); 
}

In the above code, the variable for DB_HOST requires a full-colon : at the beginning of the socket. This DB_HOST variable is not used as one of the connection parameters of mysqli_connect when in the GAE environment. This variable does seem to be used elsewhere in Wordpress (such as setup-config.php), which is why it needs to be defined. The variable for DB_SOCK does not require the full-colon : in order to work in the GAE environment. This socket needs to be the last (6th) parameter of mysqli_connect, with the first parameter specified as null, which forces the connection to use the socket.

It took a while to get this sorted, but eventually got it working using these settings above. I wonder if anyone else had such a complicated experience as I did getting Wordpress on Google App Engine standard environment to connect to a Cloud SQL for MySQL Second Generation instance. I hope these comments help someone.

Answer 4

You are better off using wpdb class. Wpdb is a WordPress database access abstraction class. Check out this documentation