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

[uselessscat]

• 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]
      }
    }
    [...]

[themsaid]

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.

[ryzr]

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?

[uselessscat]

@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']}`;");
}

[gitkv]

For pgsql:

$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()));
}

[ChrisEdwards]

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();

[RoiDayan1]

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

[minota87]

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!

[hugeps]

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

whenever I'm running something I get the error:
php artisan db:create
{"error":"An error occurred while connecting to the database 'homestead'. The error reported by the server was: SQLSTATE[HY000] [1049] Unknown database 'homestead'"}

Can this be some kind of middleware that tries to initialise DB before the handle method even gets invoked?