Bug #77769 mysqldump includes ALTER DATABASE statement even if --no-create-db option used

• Submitted: 17 Jul 2015 16:32
• Modified: 22 Jul 2015 13:28
• Reporter: kip robinson
• Status: Verified
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: 5.5/5.6/5.7
• OS: Any
• CPU Architecture: Any

[17 Jul 2015 16:32] kip robinson

Description:
The mysqldump command can include ALTER DATABASE statements, even when I use the --no-create-db option. This makes it difficult to restore a mysqldump on a different database server using a different schema.

In our case, our company converted from latin1 to utf8 storage, but references to latin1 still linger in information_schema.views and information_schema.triggers. The mysqldump inserts these alter commands to try to recreate the exact data

How to repeat:
# Create a database and a test table, view, and trigger, all under 'latin1' encoding
#------------------------------------------------------------
SET character_set_client = latin1
;
SET collation_connection = latin1_swedish_ci
;
DROP DATABASE IF EXISTS utf8test
;
CREATE DATABASE utf8test DEFAULT CHARACTER SET latin1
;
USE utf8test
;
CREATE TABLE `testtable` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `strr` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1
;
CREATE VIEW `testview` AS (SELECT `testtable`.`id` AS `id`,`testtable`.`strr` AS `strr` FROM `testtable` WHERE (`testtable`.`strr` LIKE 'a%'))
;
DELIMITER $$

CREATE
    TRIGGER `utf8test`.`testtrigger` BEFORE INSERT
    ON `utf8test`.`testtable`
    FOR EACH ROW BEGIN
      SET new.strr = CONCAT(new.strr, 'x');

    END$$

DELIMITER ;
;

# Now convert everything to utf8 (after view/trigger already exist)
#------------------------------------------------------------
SET character_set_client = utf8
;
SET collation_connection = utf8_general_ci
;
ALTER SCHEMA utf8test DEFAULT CHARACTER SET utf8
;
ALTER DATABASE utf8test DEFAULT CHARACTER SET utf8
;
ALTER TABLE utf8test.testtable CONVERT TO CHARACTER SET utf8
;
ALTER TABLE utf8test.testtable DEFAULT CHARACTER SET utf8
;

# These queries will show the references to latin1
#------------------------------------------------------------
SELECT * FROM information_schema.triggers
WHERE trigger_schema=SCHEMA()
;
SELECT * FROM information_schema.views
WHERE table_schema=SCHEMA()
;

# Run mysqldump from command line:
#------------------------------------------------------------
C:\MySQL5.6\bin>mysqldump -h localhost -u root -p12345 --port 3306 --single-transaction --compress --order-by-primary --routines --no-create-db utf8test > c:\temp\utf8test_dump.sql

==> The database dump includes the following:
ALTER DATABASE `utf8test` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

==> TWO PROBLEMS:
1. If this is imported on a server lacking "utf8test" database, the import will fail.
2. If this is imported on a server with an unrelated "utf8test" database, the import will modify that database, not the current database.

Suggested fix:
Leave these ALTER statements out if user passed "--no-create-db" option.

Alternatively, support a syntax like:

ALTER DATABASE DATABASE() ...

This way the sqldump can alter the correct database when imported.

[22 Jul 2015 13:04] MySQL Verification Team

Dump file

Attachment: utf8test_dump.sql (text/x-sql), 4.27 KiB.

[22 Jul 2015 13:28] MySQL Verification Team

Thank you for the bug report and test case.

[30 Nov 2020 23:11] Mark B

It's been over 5 years. Any update on this? I just ran into this issue today.

[17 Nov 2021 15:00] Sil M

I found a workaround that worked for me: Make sure your database collation and charset are the same as the database defaults and/or server defaults. Also, mysqldump keeps generating ALTER DATABASE statements if you have procedures, functions or triggers that were created while the database had a different collation. That can be fixed by recreating them. To identify the procedures you can either check your mysqldump sql output file or query the information_schema views:

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, DATABASE_COLLATION
    FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA NOT IN ('mysql', 'sys');

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DATABASE_COLLATION
    FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA NOT IN ('mysql', 'sys');