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
Triage: D2 (Serious) / R2 (Low) / E3 (Medium)

[29 Dec 2007 0:49] Peter Gulutzan
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';
[29 Dec 2007 6:02] Miguel Solorzano
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>
[23 Jan 2008 16:35] Rafal Somla
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.
[23 Jan 2008 17:57] Rafal Somla
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.
[28 Apr 2008 8:45] Rafal Somla
This issue is fixed by the patch for BUG#33571. The correct behaviour is tested with backup.test.
[28 Apr 2008 22:51] Paul Dubois
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).
[20 Aug 2008 14:29] Chuck Bell
Pushed to 6.0.0.
[21 Aug 2008 18:24] Paul Dubois
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.