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

Bug #33570	Backup: restore failure if sql_mode change
Submitted:	29 Dec 2007 0:49	Modified:	21 Aug 2008 18:24
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:
I backup a database.
I change sql_mode.
I try to restore.
Failure.

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:
set sql_mode=ansi;
create database wc;
backup database wc to '/usr/local/mysql/var/t48';
set sql_mode='';
drop database wc;
restore from '/usr/local/mysql/var/t48';

Thank you for the bug report.

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

mysql> drop database wc;
Query OK, 0 rows affected (0.00 sec)

mysql> restore from '/home/miguel/dbs/6.0b/var/t48';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"wc" /*!40100 DEFAULT CHARACTER SET latin1 */' at line 1
mysql> set sql_mode=ansi;
Query OK, 0 rows affected (0.00 sec)

mysql> restore from '/home/miguel/dbs/6.0b/var/t48';
+-----------+
| backup_id |
+-----------+
| 5         | 
+-----------+
1 row in set (1.69 sec)

mysql>

REFINED PROBLEM DESCRIPTION.

SQL statements used to store definitions of objects (in form of CREATE statements) contain clauses which are specific to mysql. If these statements are executed in some restricted mode which doesn't support all mysql-specific constructs, we get errors during restore.

A solution will be to change to the "native" mode during RESTORE operation and then switch back to the mode specified by user.

This bug should be re-verified when backup kernel is refactored to use object services API (WL#3574 and WL#4205) as this will change the way objects are serialized and re-created.

This issue is fixed by the patch for BUG#33571. The correct behaviour is tested with backup.test.

This report should not be in Documenting status yet. The patch for Bug#33571 has not yet been pushed to a main tree. Changing to Patch queued (the current status of #33571).

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.