/**
* Create MySQL database
*
* Note: Loads parameters from ENV vars, if empty.
*
* Creates database and grants permissions to user
*
* @param null $db database name `DATABASE_DSN_DB`
* @param null $dsn database base-DSN `DATABASE_DSN_BASE`
* @param null $root `DB_ENV_MYSQL_ROOT_USER
* @param null $rootPassword `DB_ENV_MYSQL_ROOT_USER`
* @param null $user `DB_ENV_MYSQL_USER`
* @param null $pass `DB_ENV_MYSQL_PASSWORD
*
* @throws \yii\base\ExitException
*/
public function actionCreate($db = null, $dsn = null, $root = null, $rootPassword = null, $user = null, $pass = null)
{
// check dsn
if ($db === null) {
$db = getenv("DATABASE_DSN_DB");
}
if (empty($db)) {
$this->stdout('No database configured, aborting.');
return;
}
// check root user settings
$root = $root ?: getenv("DB_ENV_MYSQL_ROOT_USER");
if ($root === null) {
$this->stdout('No root user configured, aborting.');
return;
}
$rootPassword = $rootPassword ?: getenv("DB_ENV_MYSQL_ROOT_PASSWORD");
if (empty($rootPassword)) {
$this->stdout('No root password configured, aborting.');
return;
}
$user = $user ?: getenv("DB_ENV_MYSQL_USER");
$pass = $pass ?: getenv("DB_ENV_MYSQL_PASSWORD");
$dsn = $dsn ?: getenv("DATABASE_DSN_BASE");
if (empty($user) || empty($pass) || empty($dsn)) {
$this->stdout('Configuration failed, aborting.');
return;
}
// trying to connect to database with PDO (20 times, interval 1 second)
$this->stdout("Checking database connection on DSN '{$dsn}' with user '{$root}'");
try {
// retry an operation up to 20 times
$pdo = \igorw\retry($this->mysqlRetryMaxCount, function () use($dsn, $root, $rootPassword) {
$this->stdout('.');
sleep($this->mysqlRetryTimeout);
return new \PDO($dsn, $root, $rootPassword);
});
} catch (FailingTooHardException $e) {
$this->stdout("\n\nError: Unable to connect to database '" . $e->getMessage() . "''");
\Yii::$app->end(1);
}
$this->stdout(' [OK]');
// try to create a database for the user
$this->stdout("\nCreating database '{$db}' and granting permissions to user '{$user}' on DSN '{$dsn}' with user '{$root}'");
try {
\igorw\retry($this->mysqlRetryMaxCount, function () use($dsn, $root, $rootPassword, $pdo, $user, $pass, $db) {
$pdo->exec("CREATE DATABASE IF NOT EXISTS `{$db}`;\n GRANT ALL ON `{$db}`.* TO '{$user}'@'%' IDENTIFIED BY '{$pass}';\n FLUSH PRIVILEGES;");
$this->stdout('.');
sleep($this->mysqlRetryTimeout);
});
} catch (FailingTooHardException $e) {
$this->stdout("\n\nError: Unable to setup database '" . $e->getMessage() . "''");
\Yii::$app->end(1);
}
$this->stdout(' [OK]');
$this->stdout("\n");
}
/**
* create MySQL database from ENV vars and grant permissions
*
* Note: skips creation, if root password is not set
*
* @param $db database name
*/
public function actionCreateMysqlDb($db = null)
{
if ($db === null) {
$db = getenv("DATABASE_DSN_DB");
}
if (empty($db)) {
$this->stdout('No database configured, skipping setup.');
return;
}
$root = getenv("DB_ENV_MYSQL_ROOT_USER") ?: 'root';
$root_password = getenv("DB_ENV_MYSQL_ROOT_PASSWORD");
if (empty($root_password)) {
return;
}
$user = getenv("DB_ENV_MYSQL_USER");
$pass = getenv("DB_ENV_MYSQL_PASSWORD");
$dsn = getenv("DATABASE_DSN_BASE");
$this->stdout("Checking database connection on DSN '{$dsn}' with user '{$root}'");
// trying to connect to database with PDO (20 times, interval 1 second)
try {
// retry an operation up to 20 times
$dbh = \igorw\retry($this->mysqlRetryMaxCount, function () use($dsn, $root, $root_password) {
$this->stdout('.');
sleep($this->mysqlRetryTimeout);
return new \PDO($dsn, $root, $root_password);
});
} catch (FailingTooHardException $e) {
$this->stdout("\n\nError: Unable to connect to database '" . $e->getMessage() . "''");
\Yii::$app->end(1);
}
$this->stdout(' [OK]');
// wait one more second to harden startup and create database credentials
sleep($this->mysqlPreSetupPause);
$this->stdout("\nCreating database '{$db}' and granting permissions to user '{$user}' on DSN '{$dsn}' with user '{$root}'\n");
try {
$dbh->exec("CREATE DATABASE IF NOT EXISTS `{$db}`;\n GRANT ALL ON `{$db}`.* TO '{$user}'@'%' IDENTIFIED BY '{$pass}';\n FLUSH PRIVILEGES;") or die(print_r($dbh->errorInfo(), true));
} catch (\PDOException $e) {
die("DB ERROR: " . $e->getMessage());
}
}
