Yii2/PHP: Check connection to MySQL and PostgreSQL

Question

When starting my Yii2/PHP application, how can I check if / wait until the database is up?

Currently with MySQL I use:

$time = time();
$ok = false;
do {
  try {
    $pdo = new PDO($dsn,$username,$password);
    if ($pdo->query("SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA")) 
        $ok=true;
  } catch (\Exception $e) {
    sleep(1);
  }
} while (!$ok && time()<$time+30);

Now I want make my application running with MySQL and PostgreSQL.

But SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA does not work in PostgreSQL.

Is there a SQL-statement (using PDO database connectivity) that works on both database systems to check if the database is up and running?

why do you need to do such a thing? Surely the database is already running by the time someone is making a request to the application? — ADyson
– ADyson, Commented Jan 8, 2019 at 22:46
I have a docker-compose with different containers for database and app. And it is not sufficient to make the app "depend_on:" database, since the database init takes some additional time after the operating system is running. — WeSee
– WeSee, Commented Jan 8, 2019 at 22:49
why not make it depend on the database? You can't do anything useful with the application until it's running, so what's the difference? But anyway, I don't think you actually need to run a query in order to know if the database is up...all you need to do is check that the command to open a connection succeeds. — ADyson
– ADyson, Commented Jan 8, 2019 at 22:52

Alejandro · Accepted Answer · 2019-01-09 01:23:41Z

5

Yii2 has a property to verify if a connection exists or not, it is really not necessary to create a script for this, since this framework has an abstraction implemented for the databases it supports ($isActive property).

$isActive public read-only property Whether the DB connection is established

public boolean getIsActive ( )

You can do the check in your default controller in the following way:

<?php

class DefaultController extends Controller 
{
    public function init()
    {
        if (!Yii::$app->db->isActive) {
            // The connection does not exist.
        }

        parent::init();
    }
}

It is not good practice to force waiting for a connection to a database unless there are very specific requirements. The availability of a connection to the database must be a mandatory requirement for an application to start and the application should not "wait" for the database to be available.

There are ways to run containers in docker in an orderly manner or with a specific requirement, this link could give you a better option instead of delegating this to the application.

answered Jan 9, 2019 at 1:23

Alejandro

8861 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

rob006 Over a year ago

isActive() is not really reliable in some cases - it does not test if connection is still active, so isActive() may return true also for disconnected connection.

Alejandro Over a year ago

The case where isActive returns a true value is when the database server is restarted while the script is running. Is a feature to consider within the framework that is already working to implemen.

rob006 Over a year ago

Connection can be also closed after long inactivity - it is quite common problem for long-running scripts.

Alejandro Over a year ago

Right, but this case only happens in Yii2 if you first configured the database connection with the parameter 'attributes' => [PDO::ATTR_PERSISTENT => true]

rob006 Over a year ago

It is not true, and ATTR_PERSISTENT may be really bad idea if you don't know what you're doing.

|

danblack · Accepted Answer · 2019-01-08 23:13:35Z

3

You could use SELECT 1 which is standard SQL.

You can use dbfiddle to test against various servers.

The server could go away an any time so checking the error response with every query is a much better approach.

answered Jan 8, 2019 at 23:13

danblack

14.9k2 gold badges27 silver badges49 bronze badges

Collectives™ on Stack Overflow

Yii2/PHP: Check connection to MySQL and PostgreSQL

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related