MySQL Bugs: #32190: Online backup does not save character set settings for databases

Bug #32190	Online backup does not save character set settings for databases
Submitted:	8 Nov 2007 17:07	Modified:	26 Feb 2008 0:45
Reporter:	Chuck Bell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0/5.2	OS:	Any
Assigned to:	Rafal Somla	CPU Architecture:	Any

Description:
If a database has had its character set changed with an ALTER DATABASE command, and the database is backed up then restored it looses the character set assignment.

How to repeat:
The following can be used to test the bug.

CREATE DATABASE db1 CHARACTER SET latin1;

# Should show character set = latin1.
SHOW CREATE DATABASE db1;

# Add some data
CREATE TABLE db1.t1 (a INT);
INSERT INTO db1.t1 VALUES (1), (2), (3);

# Now alter the database and change character set 
ALTER DATABASE db1 CHARACTER SET latin2;

# Should show character set = latin2.
SHOW CREATE DATABASE db1;

BACKUP DATABASE db1 TO 'db1.bak';

DROP DATABASE db1;

RESTORE FROM 'db1.bak';

# Should show character set = latin2.
SHOW CREATE DATABASE db1;

Below is an example of this test running:

mysql> CREATE DATABASE db1 CHARACTER SET latin1;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> # Should show character set = latin1.
mysql> SHOW CREATE DATABASE db1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> # Add some data
mysql> CREATE TABLE db1.t1 (a INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO db1.t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> # Now alter the database and change character set
mysql> ALTER DATABASE db1 CHARACTER SET latin2;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> # Should show character set = latin2.
mysql> SHOW CREATE DATABASE db1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin2 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> BACKUP DATABASE db1 TO 'db1.bak';
+------------------------------+
| Backup Summary               |
+------------------------------+
|  header     =       13 bytes |
|  meta-data  =       91 bytes |
|  data       =       30 bytes |
|               -------------- |
|  total             134 bytes |
+------------------------------+
5 rows in set (0.16 sec)

mysql>
mysql> DROP DATABASE db1;
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> RESTORE FROM 'db1.bak';
+------------------------------+
| Restore Summary              |
+------------------------------+
|  header     =       13 bytes |
|  meta-data  =       91 bytes |
|  data       =       30 bytes |
|               -------------- |
|  total             134 bytes |
+------------------------------+
5 rows in set (0.02 sec)

mysql>
mysql> # Should show character set = latin2.
mysql> SHOW CREATE DATABASE db1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Ensure the database metadata is included in the backup.

Note: The repair of this bug may change the results for the backup_ddl_blocker test. Specifically, test #5 has a result that checks to see that an ALTER DATABASE command in progress at the start of the backup was allowed to finish and therefore is in the backup image, but it is not (hence the discovery of this bug). The DDL blocker was implemented as part of WL#4062.

Thank you for the bug report.

PROPOSED SOLUTION

Change method backup::meta::Db::get_create_stmt() to return a correct CREATE DATABASE statement with DEFAULT CHARSET and COLLATION clauses (as produced by SHOW CREATE DATABASE). This will make the backup kernel to store this statement in a backup image and use it when creating database upon restore.

To do that, a function analogous to store_create_info() would be needed. Such function is not implemented in the server now. Construction of CREATE DATABASE statement is done inside mysql_show_create_db() function (sql_show.cc) and is intermixed with code for sending reply to a client issuing SHOW CREATE DATABASE statement.

I plan to extract the relevant code from mysq_show_create_db() and put it into new function store_db_cteate_info().

To consider: refactor mysql_show_create_db() to use the new function.

Note: Part of WL#3574 is to define a general C interface for getting CREATE statements for various types of objects. However, the interface have not been defined yet. When this happens, we should use the general interface here.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39778

ChangeSet@1.2748, 2007-12-12 14:55:50+01:00, rafal@quant.(none) +6 -0
  BUG#32190 (Online backup does not save character set settings for databases).
  
  This patch redefines backup::meta::Db::get_create_stmt() method to return a
  correct CREATE DATABASE statement with all necessary clauses. This will make the 
  backup kernel to use this statement when creating a database during restore. 
  Thus the default character set and collation will be restored as needed.

Patch approved pending the addition of an appropriate DROP DATABASE db3 statement to the backup test. 

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.backup                    [ pass ]              0
main.backup_commit_blocker     [ pass ]              0
main.backup_ddl_blocker        [ pass ]              0
main.backup_errors             [ pass ]              0
main.backup_fkey               [ pass ]              0
main.backup_no_data            [ fail ]

--- d:/source/c++/mysql-6.0_BUG_review/mysql-test/r/backup_no_data.result2007-12
-13 02:19:55 +03:00
+++ d:\source\c++\mysql-6.0_BUG_review\mysql-test\r\backup_no_data.reject2007-12
-13 02:28:16 +03:00
@@ -11,6 +11,7 @@
 SHOW DATABASES;
 Database
 information_schema
+db3

...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39866

ChangeSet@1.2748, 2007-12-13 10:26:34+01:00, rafal@quant.(none) +8 -0
  BUG#32190 (Online backup does not save character set settings for databases).
  
  This patch redefines backup::meta::Db::get_create_stmt() method to return a
  correct CREATE DATABASE statement with all necessary clauses. This will make the 
  backup kernel to use this statement when creating a database during restore. 
  Thus the default character set and collation will be restored as needed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39869

ChangeSet@1.2748, 2007-12-13 11:13:34+01:00, rafal@quant.(none) +10 -0
  BUG#32190 (Online backup does not save character set settings for databases).
  
  This patch redefines backup::meta::Db::get_create_stmt() method to return a
  correct CREATE DATABASE statement with all necessary clauses. This will make the 
  backup kernel to use this statement when creating a database during restore. 
  Thus the default character set and collation will be restored as needed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39893

ChangeSet@1.2748, 2007-12-13 15:11:59+01:00, rafal@quant.(none) +10 -0
  BUG#32190 (Online backup does not save character set settings for databases).
  
  This patch redefines backup::meta::Db::get_create_stmt() method to return a
  correct CREATE DATABASE statement with all necessary clauses. This will make the 
  backup kernel to use this statement when creating a database during restore. 
  Thus the default character set and collation will be restored as needed.

Pushed into 6.0-backup tree.

Pushed into 6.0.5-alpha

Noted in 6.0.5 changelog.

Online Backup did not always save database character set and collation information.

Correction: No changelog entry needed; this bug did not appear in any released version.