MySQL Bugs: #33571: Backup: restore failure if sql

Bug #33571	Backup: restore failure if sql_mode=ansi
Submitted:	29 Dec 2007 0:55	Modified:	21 Aug 2008 18:21
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Rafal Somla	CPU Architecture:	Any

Description:
If sql_mode=ansi, RESTORE statements can lose
a vital piece of information: 'engine=innodb'.

This is a consequence of the fact that
SHOW CREATE TABLE does not contain the engine
clause when sql_mode=ansi.
That will not be fixed, see Bug#3352 comment.

Another reason not to use SHOW, eh?

Built from mysql-6.0-backup source with BUILD/compile-pentium-debug-max.
Last changeset = "ChangeSet@1.2753, 2007-12-20 15:32:22-05:00".

How to repeat:
use test
set sql_mode=ansi;
create database wd;
create table wd.t (s1 int) engine=innodb;
backup database wd to '/usr/local/mysql/var/t54';
drop database wd;
restore from '/usr/local/mysql/var/t54';
set sql_mode='';
show create table wd.t;

Notice that 'engine=innodb' is missing.
Now it's 'engine=myisam'.

Thank you for the bug report.

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> show create table wd.t;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `s1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

The problem is that the output of SHOW CREATE statements, used to store definitions of backed-up objects, depends on the setting of sql_mode (and perhaps some other factors). Thus, a correct environment should be set-up before backup kernel asks for these statements.

However, backup kernel is now being rewritten to use server's object services (WL#3574 and WL#4205) to obtain object descriptions and re-create objects from them. When this change is made, it will be responsibility of the object services implementation to always produce correct object description (aka serialization).

I'll put a note in WL#3574 about the problems found here. When WL#4205 code is pushed to the trees the bug should be re-verified.

After implementing WL#4205 the problem is still there. That means that there is a bug in implementation of object services - the serialization string for a table depends on the current sql_mode setting but it should not.

I'll see if I can come up with an easy fix for that.

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/44439

ChangeSet@1.2606, 2008-03-26 11:46:18+01:00, rafal@quant.(none) +4 -0
  BUG#33571 (Backup: restore failure if sql_mode=ansi)
  
  This patch modifies object services methods so that sql_mode is locally changed to
  default when operations which depend on it are executed. These operations are 
  obtaining the serialization string (format of SHOW CREATE output depends on sql_mode)
  and "executing" an object (syntax of CREATE statement depends on sql_mode).

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/44464

ChangeSet@1.2606, 2008-03-26 18:00:17+01:00, rafal@quant.(none) +4 -0
  BUG#33571 (Backup: restore failure if sql_mode=ansi)
  
  This patch modifies object services methods so that sql_mode is locally changed to
  default when operations which depend on it are executed. These operations are 
  obtaining the serialization string (format of SHOW CREATE output depends on sql_mode)
  and "executing" an object (syntax of CREATE statement depends on sql_mode).

Patch approved pending addition of comments for the following in si_objects.h.

bool Obj::execute(THD *thd)
bool Obj::serialize(THD *thd, String *serialization)

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/44662

ChangeSet@1.2606, 2008-03-31 09:16:58+02:00, rafal@quant.(none) +4 -0
  BUG#33571 (Backup: restore failure if sql_mode=ansi)
  
  This patch modifies object services methods so that sql_mode is locally changed to
  default when operations which depend on it are executed. These operations are 
  obtaining the serialization string (format of SHOW CREATE output depends on sql_mode)
  and "executing" an object (syntax of CREATE statement depends on sql_mode).

Pushed to 6.0.0.

Pushed to 6.0.5, the same release when BACKUP DATABASE/RESTORE originally appeared. So this bug is not in any released version; no changelog entry needed.

Pushed into 6.0.8-alpha  (revid:sp1r-rafal@quant.(none)-20080331071658-43318) (version source revid:konstantin@mysql.com-20080911140522-3cmh8tsfep5j6dsj) (pib:3)