phpmyadmin Question posted in
The official documentation can be found here.

How to create a new database by Yii2 migration, when I have to use it by default? – Phpmyadmin

I have a task which is described like “Creating a new database with Yii2 migration, and use it by default”. But I have some collusion: I have to set up at lest one default database in my main-local.php file to start migration. But also I have to create this database with initial migration.

return [
    'db' => [
        'class' => 'yiidbConnection',
        'dsn' => 'mysql:host=localhost;dbname=default_base',
        'username' => 'root',
        'password' => '',
        'charset' => 'utf8',
    ],
];

How can I deal with it? To create a database with migration and set up as default one.

When I execute my migration (there is code from my migration file):

public function safeUp()
{
    $this->execute("CREATE DATABASE default_base");
}

I have an exception:

General error: 1007 Can't create database 'default_base'; database exists

I realize that is because I have already created default_base in phpmyadmin (click button “created database”), and connected it with app in my main-local.php file.
But I need this base to be created only when I execute yii migrate.

UPD

I have tried the way rob006 recommended but I get connection error

Exception 'yiidbException' with message 'SQLSTATE[HY000] [1049] Unknown database 'default_base''

My main-local.php looks like the way exactly rob006 recommends, but I still can`t migrate. It works only if I set:

'preinstallDb' => [
                  'class'    => 'yiidbConnection',
                  'dsn'      => 'mysql:host=localhost',
                  'username' => 'root',
                  'password' => '',
                  'charset'  => 'utf8',
            ],
            'db'           => [
                  'class'    => 'yiidbConnection',
                  'dsn'      => 'mysql:host=localhost;dbname=already_exists_db',
                  'username' => 'root',
                  'password' => '',
                  'charset'  => 'utf8',
            ],

With the already exists database. What do I do wrong again?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I find the only way make it work for me. Just overriding init() in MigrateController. First I added controllerMap in main-local.php file:

    'controllerMap' => [
            'migrate' => [
                  'class' => 'consolecontrollersMigrateController',]]

    Then put new controller in consolecontrollers:

     namespace consolecontrollers;
use yiiconsolecontrollersMigrateController as BasicMigrateController;
use Yii;
class MigrateController extends BasicMigrateController
{
   public function init()
    {
       Yii::$app->preinstallDb->createCommand('CREATE DATABASE IF NOT EXISTS default_base')->execute();
       parent::init();
    }
}

  2. 0

    You can define DSN without database name:

    'dsn' => 'mysql:host=localhost',

    And use this connection to create database. 

    Yii::$app->preinstallDb->createCommand('CREATE DATABASE default_base')->execute();

    However you can’t really create database in migration – MigrateController needs database for migration table to save migrations history. You would need to create database before MigrateController will try to access migration table. You can do this by overriding MigrateController::getMigrationHistory():

    class MigrateController extends yiiconsolecontrollersMigrateController {

    protected function getMigrationHistory($limit) {
        Yii::$app->preinstallDb->createCommand('CREATE DATABASE IF NOT EXISTS default_base')->execute();

        return parent::getMigrationHistory($limit);
    }
}

    And use your controller in console app config:

    'controllerMap' => [
    'migrate' => [
        'class' => MigrateController::class,
    ],
],

    But honestly, it is ugly solution with a lot of hardcoding, it may be better to create some setup utility which will perform database creation outside of main app. Also usually for security reasons DB user used by application should not even have privileges to create database, so you may reconsider your use case – do you really need to create database by app?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search