Database Dump creates .sql - not .dump anymore

When I ran a database dump to have a faster setup time instead of running all migration for some database I had not ran this for before I realized the command now dumps as .sql and no longer .dump

php artisan schema:dump --database=mysql_testing
mysqldump: unknown variable 'column-statistics=0'
INFO  Database schema dumped successfully.

it created database/schema/mysql_testing-schema.sql . Is this new since Laravel 9 perhaps ? Does this work the same using php artisan migrate later on compared to .dump files like older database/schema/mysql-schema.dump ?

LaryAI

2 years ago

Level 58

The behavior you're observing is indeed a change that was introduced in Laravel 8.x. Starting from Laravel 8, the php artisan schema:dump command generates a .sql file instead of a .dump file. This change was made to standardize the output format of the schema dump to be SQL, which is more universally recognized and can be used across different database systems.

The .sql file generated by the schema:dump command contains the SQL statements needed to create the database schema. This file can be used to quickly set up the database schema without running all the migrations, which can be particularly useful for large applications with many migrations.

To use the .sql file generated by the schema:dump command, you can run the php artisan migrate command with the --schema-path option to specify the path to the .sql file. For example:

php artisan migrate --schema-path=database/schema/mysql_testing-schema.sql

This will apply the SQL schema dump before running any remaining migrations that were created after the schema dump was generated.

Regarding the mysqldump: unknown variable 'column-statistics=0' error, this is an issue with the mysqldump client that may be related to the version of MySQL or MariaDB you are using. The column-statistics option is enabled by default in newer versions of mysqldump and may not be recognized by older server versions. You can resolve this by either upgrading your database server to a version that supports this option or by configuring mysqldump to not include column statistics by adding the following to your my.cnf or my.ini file under the [mysqldump] section:

[mysqldump]
column-statistics=0

Alternatively, if you do not have access to the configuration file, you can pass the --column-statistics=0 option directly to mysqldump by modifying the dump configuration in your config/database.php file for the MySQL connection:

'mysql' => [
    // ...
    'dump' => [
        'dump_binary_path' => '/path/to/your/mysqldump', // only the path, without 'mysqldump'
        'use_single_transaction',
        'do_not_use_column_statistics' => true, // add this line
    ],
    // ...
],

Remember to replace /path/to/your/mysqldump with the actual path to your mysqldump binary if it's not in the system's default PATH.

In summary, the .sql file works similarly to the .dump file for the purpose of setting up the database schema, and you can use it with the php artisan migrate command. The column-statistics error can be resolved by adjusting your mysqldump configuration.

1 like

# # This group is read both by the client and the server # use it for options that affect everything # [client-server] # # include *.cnf from the config directory # !includedir /opt/homebrew/etc/my.cnf.d [mysqldump] column-statistics=0

php artisan schema:dump --database=mysql_testing mysqldump: unknown variable 'column-statistics=0' mysqldump: unknown variable 'column-statistics=0' mysqldump: unknown variable 'column-statistics=0' mysqldump: unknown variable 'column-statistics=0' ...