Make schema:dump use --single-transaction when supported

[FeBe95]

Problem

Today we encountered an issue while executing the schema:dump command in our Laravel app. Our app is connected to a database that contains about 400 tables. The execution of php artisan schema:dump took about 2:30 minutes, while being connected to our staging database. On its own, this wouldn't pose a problem. The actual problem emerges when trying to access the app/database during the execution: The app won't load at all, since the entire database is locked by mysqldump during the whole time.

Solution

mysqldump offers an option called --single-transaction, which runs the dump command on a temporary snapshot. When only dumping the database structure - like schema:dump does - this should be the preferred way of dealing with larger databases.

Possible implementations

Note: All three solutions only target the MySQL schema, since I am not familiar with PostgreSQL or SQLite and potential similar options.

• Add --single-transaction to the parameters here:
  

  framework/src/Illuminate/Database/Schema/MySqlSchemaState.php

  Lines 21 to 23 in 7e69645

  	$this->executeDumpProcess($this->makeProcess(
  	$this->baseDumpCommand().' --routines --result-file="${:LARAVEL_LOAD_PATH}" --no-data'
  	), $this->output, array_merge($this->baseVariables($this->connection->getConfig()), [

• Add a new option parameter to the schema:dump command, which optionally enables the --single-transaction mode:
  

  framework/src/Illuminate/Database/Console/DumpCommand.php

  Lines 22 to 25 in 7e69645

  	protected $signature = 'schema:dump
  	{--database= : The database connection to use}
  	{--path= : The path where the schema dump file should be stored}
  	{--prune : Delete all existing migration files}';

• Allow any kind of additional parameters for mysqldump, defined in config/database.php, similar to what spatie/laravel-backup does:

  'mysql' => [
      'dump' => [
          'useSingleTransaction' => true,
      ],
  ],

  https://github.com/spatie/laravel-backup/blob/4650f6caedebcb0ca6856b3c1e60a3dc97848538/config/backup.php#L70-L75

[FeBe95]

I am going to tag @taylorotwell here, since code history shows you being the author of many parts of this code. I hope that's okay

[FeBe95]

Another use case:
Executing php artisan schema:dump in its current state requires the LOCK TABLES privilege. We use a "developer" user for development, that does not have this privilege. So running this command with the default development configuration fails completely...