Framework: Create Database using DB::statement doesn't works.

Created on 30 May 2017 · 7Comments · Source: laravel/framework

Laravel Version: 5.4.17
PHP Version: PHP 5.6.30-0+deb8u1 (cli) (built: Feb 8 2017 08:50:21)
Database Driver & Version:
CLIENT mysql Ver 8.0.1-dmr for Linux on x86_64 (MySQL Community Server (GPL))
SERVER Server version: 8.0.1-dmr MySQL Community Server (GPL)
PDO MYSQL: Client API version => 5.5.55

Description:

When i tried to do a statement query to create a new database it gives me this error.

[PDOException]
SQLSTATE[42000] [1049] Unknown database 'mydatabase'

I do this in the console.php file that defines artisan commands.

Steps To Reproduce:

This is the code that (tries to) create my database:

Artisan::command('migrate:createdb', function () {
    $databaseName = Config::get('database.connections.' . Config::get('database.default') . '.database');
    $this->comment("Creating database \"" . $databaseName . "\"");
    $result = DB::statement('CREATE SCHEMA `'. $databaseName .'` DEFAULT CHARACTER SET utf8;');
})->describe('Create the default database');

Also I have tried these expression:

DB::connection()->setDatabaseName('');
DB::connection()->reconnect();

DB::unprepared('CREATE DATABASE mydatabase');

DB::connection()->unprepared('CREATE SCHEMA `'. $databaseName .'` DEFAULT CHARACTER SET utf8;');

But nothing works :/

So, finally I made this:

    $host = "localhost";
    $root = "root";
    $root_password = "";

    try {
        $dbh = new PDO("mysql:host=$host", $root, $root_password);

        $dbh->exec('CREATE SCHEMA `' . $databaseName . '` DEFAULT CHARACTER SET utf8;') 
        or die(print_r($dbh->errorInfo(), true));
    } catch (PDOException $e) {
        die("DB ERROR: " . $e->getMessage());
    }

I think that this problem occurs because the connection tries to connect to the default database (setted in the configuration), so i can't do any query if the database doesn't exists. A way to check that is catching the exception generated when the statement is executed. Also, in the documentation nothing indicates how to do this without auto-select the database of the connection.

PDOException {#517
  #message: "SQLSTATE[42000] [1049] Unknown database 'newintranet'"
  #code: 1049
  #file: "/var/www/intranet/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php"
  #line: 68
  +errorInfo: null
  -trace: {
    /var/www/intranet/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:68: {
      : 
      :     return new PDO($dsn, $username, $password, $options);
      : }
      arguments: {
        $dsn: "mysql:host=127.0.0.1;port=3306;dbname=newintranet"
        $username: "root"
        $passwd: ""
        $options: array:5 [ …5]
      }
    }
    /var/www/intranet/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:45: {
      :         $dsn, $username, $password, $options
      :     );
      : } catch (Exception $e) {
      arguments: {
        $dsn: "mysql:host=127.0.0.1;port=3306;dbname=newintranet"
        $username: "root"
        $password: ""
        $options: array:5 [ …5]
      }
    }
    [...]

Source

uselessscat

Most helpful comment

my working example based on the answers of all of you:

create new artisan command:
php artisan make:command mysql

the content of App\Console\Commands\mysql.php :

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class mysql extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'mysql:createdb {name?}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Create a new mysql database schema based on the database config file';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $schemaName = $this->argument('name') ?: config("database.connections.mysql.database");
        $charset = config("database.connections.mysql.charset",'utf8mb4');
        $collation = config("database.connections.mysql.collation",'utf8mb4_unicode_ci');

        config(["database.connections.mysql.database" => null]);

        $query = "CREATE DATABASE IF NOT EXISTS $schemaName CHARACTER SET $charset COLLATE $collation;";

        DB::statement($query);

        config(["database.connections.mysql.database" => $schemaName]);

    }
}

then run: (schema_name is optionally)
php artisan mysql:createdb schema_name

RoiDayan1 on 21 Aug 2018

👍8

All 7 comments

I think that this problem occurs because the connection tries to connect to the default database (setted in the configuration), so i can't do any query if the database doesn't exists

That's it, that's how the query builder is supposed to work, it needs to connect to a DB before running queries.

themsaid on 31 May 2017

Doesn't particularly answer your question, but may help:

// Setting connection info from console prompts
$this->config->set('database.connections.mysql.host', $dbHost);
$this->config->set('database.connections.mysql.port', $dbPort);
$this->config->set('database.connections.mysql.database', null);
$this->config->set('database.connections.mysql.username', $dbUser);
$this->config->set('database.connections.mysql.password', $dbPass);

// Creating DB if it doesn't exist
$this->db->purge('mysql');
$this->db->connection('mysql')->getPdo()->exec("CREATE DATABASE IF NOT EXISTS `{$dbName}`");

You could use the Config and DB facades rather than DI if necessary.

My use-case is a little different - it creates databases when scaffolding new projects via artisan command. But it means I don't need to create any new PDO objects or hard-code DB information. I'm not sure if you can jump right into using DB functions from here, or whether you'd need to 'reset' any of the DB instance objects. Hopefully this is somewhat helpful?

ryzr on 31 May 2017

👍6 😄2

@themsaid I thought that the statement DB::connection() did not select a database 😇 (I thought it connected and then uses use {database}; like this line, but not... so DB::connection() doesn't work as i expected) So, solved my problem creating a new connection with null database, thus:

'mysql' => [ ... ],
'mysql_only_connect'  => [
    'driver'    => 'mysql',
    'host'      => env('DB_HOST', '127.0.0.1'),
    'port'      => env('DB_PORT', '3306'),
    'database'  => null,
    'username'  => env('DB_USERNAME', 'root'),
    'password'  => env('DB_PASSWORD', '')
],

So, selecting the connection i can use the statement to configure Mysql without any particular database

DB::connection('mysql_only_connect')->statement("CREATE DATABASE $database DEFAULT CHARACTER SET utf8;");

Sorry :P

PS: When the USE {database} is called? I have the impression that it runs even when the database was selected on the connection, then that exec would be unnecessary:

$connection = $this->createConnection($dsn, $config, $options);
if (! empty($config['database'])) {
    $connection->exec("use `{$config['database']}`;");
}

uselessscat on 2 Jun 2017

👍4

For pgsql:

```$dbhost = env('DB_HOST');
$dbname = env('DB_DATABASE');
$dbuser = env('DB_USERNAME');
$dbpass = env('DB_PASSWORD');
try {
$db = new \PDO("pgsql:host=$dbhost", $dbuser, $dbpass);
$test = $db->exec("CREATE DATABASE \"$dbname\" WITH TEMPLATE = template0 encoding = 'UTF8' lc_collate='C.UTF-8' lc_ctype='C.UTF-8';");
if($test === false)
throw new \Exception($db->errorInfo()[2]);
$this->info(sprintf('Successfully created %s database', $dbname));
}
catch (\Exception $exception) {
$this->error(sprintf('Failed to create %s database: %s', $dbname, $exception->getMessage()));
}

gitkv on 27 Feb 2018

I found it best to use the Laravel config objects directly so you pick up any valid Laravel configuration values, not just env vars (like values specified in database.php).

To use the DB::statement command, you have to dynamically set the database config value to null.

Here is the flow: I set the database to null, executed the statement, then set it back and purged the database-less connection from the cache (so the next connection will use the newly created database).

// Get the default connection name, and the database name for that connection from laravel config.
$connectionName = config('database.default');
$databaseName = config("database.connections.{$connectionName}.database");

// Set the database name to null so DB commands connect to raw mysql, not a database.
config(["database.connections.{$connectionName}.database" => null]);

// Create the db if it doesn't exist.
DB::statement("CREATE DATABASE IF NOT EXISTS " . $databaseName);

// Reset database name and purge database-less connection from cache.
config(["database.connections.{$connectionName}.database" => $databaseName ]);
DB::purge();

ChrisEdwards on 3 Jul 2018

👍5

my working example based on the answers of all of you:

create new artisan command:
php artisan make:command mysql

the content of App\Console\Commands\mysql.php :

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class mysql extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'mysql:createdb {name?}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Create a new mysql database schema based on the database config file';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $schemaName = $this->argument('name') ?: config("database.connections.mysql.database");
        $charset = config("database.connections.mysql.charset",'utf8mb4');
        $collation = config("database.connections.mysql.collation",'utf8mb4_unicode_ci');

        config(["database.connections.mysql.database" => null]);

        $query = "CREATE DATABASE IF NOT EXISTS $schemaName CHARACTER SET $charset COLLATE $collation;";

        DB::statement($query);

        config(["database.connections.mysql.database" => $schemaName]);

    }
}

then run: (schema_name is optionally)
php artisan mysql:createdb schema_name

RoiDayan1 on 21 Aug 2018

👍8

Hey Folks,
I case some get the same problem, the function above only worked for me after force to reconnect
using DB::reconnect('mysql');

   public function handle()
    {
        $schemaName = $this->argument('name') ?: config("database.connections.mysql.database");
        $charset = config("database.connections.mysql.charset",'utf8mb4');
        $collation = config("database.connections.mysql.collation",'utf8mb4_unicode_ci');

        config(["database.connections.mysql.database" => null]);

        DB::reconnect('mysql');

        $query = "CREATE DATABASE IF NOT EXISTS $schemaName CHARACTER SET $charset COLLATE $collation;";

        DB::statement($query);

        config(["database.connections.mysql.database" => $schemaName]);
    }

Cheers!