CODEWITHSUNDEEP
mysqlsql
yivo
yivo

Reputation: 3594

mysqldump utility doesn't include SQL_MODE as in my.cnf in the output

Inside my.cnf I specified:

sql-mode = STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY

Then I restarted mysql server:

sudo systemctl restart mysql

Then I checked value of SQL_MODE variable:

show variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                                           |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+

Then I dumped mydatabase with a command:

mysqldump --user root --password=root --host=localhost --add-drop-table --add-locks --allow-keywords --comments --complete-insert --create-options --disable-keys --extended-insert --lock-tables --quick --quote-names --routines --set-charset --dump-date --tz-utc --verbose --databases mydatabase

Then I saw the output:

... omitted
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
... omitted

Expected output:

... omitted
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY' */;
... omitted

What's wrong?

Upvotes: 1

Views: 3212

Answers (1)

Solarflare
Solarflare

Reputation: 11106

There is nothing wrong here. That line, similar to the other line in the beginning of the dump file, changes the sql mode temporarily to have a controlled environment during the import. This ensures your server settings (e.g. the character set, the time zone or the sql mode) do not influence how the data is added. There will be a

/*!40101 SET SQL_MODE=@OLD_SQL_MODE*/;

at the end of your dump that resets the mode (and similarly the other settings) after the import is done. The server configuration is by the way not part of your dump file, you would have to backup it separately.

This is the default behaviour, see the documentation for NO_AUTO_VALUE_ON_ZERO:

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. [...] For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. [...] mysqldump now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

Upvotes: 1

Related Questions