MySQL Bugs: #59704: ALTER DATABASE statements in mysqldump

Bug #59704	ALTER DATABASE statements in mysqldump
Submitted:	24 Jan 2011 19:57	Modified:	26 Apr 2013 17:16
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.54 (and more)	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
Now that this http://bugs.mysql.com/59398 has been 'fixed' (????) I will have to post another report as my comments were not considered.

Under specific circumstances (when, why?) mysqldump generates ALTER DATABASE statements in the dump.

1) this is totally unneeded (or at least not documented why it is). What the ALTER DATABASE statements do (changing default collation) is not required for statements executed/recorded.

2) with the example from this other report an error occurs (and consequently executing the script may abort) if the dump is imported to a server where the named database (in ALTER DATABASE) does not exist. There us no CREATE DATABASE + USE statement in the dump so it is both valid (and common) to import such dump to an existing database named differently. 

How to repeat:
see above

Suggested fix:
Get rid of the ALTER DATABASE statements introduced recently in mysqldump.

Bug #59398 is NOT fixed yet. It just has patch pending. Maybe you should wait for some more time for the final fix?

It is written there that it is 'fixed' by adding quotes. The pending patch will - if approved - not address my comments and concerns.

That is also OK to fix in 2 steps as the immediate fix for the issue originally reported there is to use quotes. This will solve the problem that `data-base` will fail to restore (into `data-base`). But not errors when restoring into `dbase`.

I simply added this report while I still remembered. We can wait for final conclusion in the other one and just let this one stay as 'open' for as long, I think (as 'open' it will not time out I believe).

Often bugs/issues are discovered while reading/testing/verifying other bug reports.  It is not always easy/obvious to decide if new 'aspects' should be considered in first bug or a new report should be created.

After some thinking and checking I do see 2 problems that you correctly pointed out here:

1. There is no way to control adding ALTER DATABASE to the dump. This may lead to problem when restoring dump to other database. We need something like --add-alter-database, similar to --add-create-database.

2. Our manual, http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html, does not explain when ALTER DATABASE is added (if non-default character set for the database was defined by explicit ALTER DATABASE?), and why.

Well .. thanks.

But I think basically these ALTERs make no sense at all. They occurred in recent  5.1 and 5.5 versions (but it seems only under rare circumstances that I do not claim to understand).  I cannot see how changing the default database collation forth and back makes any sense in such script as there are no SELECT .. WHERE/ORDER BY/GROUP BY etc. in a dump. I cannot see any statement that requires a specific collation.

I *suspect* (but don't know) that this has happened because of integration needs with the Enterprise Backup tool. If so I think it is problematic.

Unfortunatelly this is still an issue in:

mysqldump  Ver 10.13 Distrib 5.5.17, for linux2.6 (x86_64)

while making dumps of triggers only:

mysqldump -uroot -p --default-character-set=utf8 -t -d somedb > somedb.sql

After this in somedb.sql file there are many ALTERs:

/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
ALTER DATABASE `somedb` CHARACTER SET utf8 COLLATE utf8_general_ci ;
ALTER DATABASE `somedb` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;

ALTER DATABASE was introduced with the patch for Bug#16291. These statements can occur while dumping events/routines/triggers for a database.

From the patch:

2. Wrong mysqldump-output.

  The original query can contain several encodings (by means of character set
  introducers). The problem here was that we tried to convert original query
  to the mysqldump-client character set.

  Moreover, we stored queries in different character sets for different
  objects (views, for one, used UTF8, triggers used original character set).

  The solution is
    - to store definition queries in the original character set;
    - to change SHOW CREATE statement to output definition query in the
      binary character set (i.e. without any conversion);
    - introduce SHOW CREATE TRIGGER statement;
    - to dump special statements to switch the context to the original one
      before dumping and restore it afterwards.

  Note, in order to preserve the database collation at the creation time,
  additional ALTER DATABASE might be used (to temporary switch the database
  collation back to the original value). In this case, ALTER DATABASE
  privilege will be required. This is a backward-incompatible change."

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Adding this to mysqldump section:

mysqldump output can include ALTER DATABASE statements that change
the database collation. These may be used when dumping stored
programs to preserve their character encodings. To reload a dump file
containing such statements, the ALTER privilege for the affected
database is required.

The issue with the 'solution' above is when you import the dump into a database with another name. Mysqldump is used for more purposes than just backing up and restoring the same database.

Is there a workaround for this case?

You could do something like this:

cat export.sql | grep -v "ALTER DATABASE .old_database_name. CHARACTER SET" | mysql -u -p -h some.host.name