Bug #18099 restore operation with query browser fails where command line works
Submitted: 9 Mar 2006 9:18 Modified: 24 Nov 2006 14:59
Reporter: Andrew Storm Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:1.1.20 OS:Microsoft Windows (Windows XP)
Assigned to: Mike Lischke CPU Architecture:Any
Tags: Source Editors

[9 Mar 2006 9:18] Andrew Storm
Description:
trying to restore a database that had been saved using mysqldump causes the following errors

syntax error (No 1064) near ';
UNLOCK TABLES;
/*!40000 ALTER TABLE 'ByronBay' ENABLE KEYS */;

--
-- Tab' at line 1

error 1146
Table 'ozretreats.perth' doesn't exist.

The portion of the script immediately before the syntax error contains the data, and if I delete all but one record of the data the restore operation works. The whole script, minus the data is:

-- MySQL dump 10.9
--
-- Host: localhost    Database: OzRetreats
-- ------------------------------------------------------
-- Server version	4.1.11-Max-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `ByronBay`
--

DROP TABLE IF EXISTS `ByronBay`;
CREATE TABLE `ByronBay` (
  `RegistrationNumber` int(11) NOT NULL auto_increment,
  `Name` varchar(50) default NULL,
  `Street` varchar(50) default NULL,
  `City` varchar(50) default NULL,
  `State` varchar(20) default NULL,
  `Postcode` int(11) default NULL,
  `Country` varchar(50) default 'Australia',
  `Phone` varchar(20) default NULL,
  `Email` varchar(50) default NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `PaidBy` varchar(10) default NULL,
  `Paid` enum('y','n') default NULL,
  `Cancellation` enum('n','y') default 'n',
  `Fee` float default '0',
  `Scholarship` enum('n','y') default 'n',
  `Balance` float default '0',
  PRIMARY KEY  (`RegistrationNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ByronBay`
--

/*!40000 ALTER TABLE `ByronBay` DISABLE KEYS */;
LOCK TABLES `ByronBay` WRITE;
INSERT INTO `ByronBay` VALUES .... ;
UNLOCK TABLES;
/*!40000 ALTER TABLE `ByronBay` ENABLE KEYS */;

--
-- Table structure for table `Perth`
--

DROP TABLE IF EXISTS `Perth`;
CREATE TABLE `Perth` (
  `RegistrationNumber` int(11) NOT NULL auto_increment,
  `Name` varchar(50) default NULL,
  `Street` varchar(50) default NULL,
  `City` varchar(50) default NULL,
  `State` varchar(20) default NULL,
  `Postcode` int(11) default NULL,
  `Country` varchar(50) default 'Australia',
  `Phone` varchar(20) default NULL,
  `Email` varchar(50) default NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `PaidBy` varchar(10) default NULL,
  `Paid` enum('y','n') default NULL,
  `Cancellation` enum('n','y') default 'n',
  `Fee` float default '0',
  `Scholarship` enum('n','y') default 'n',
  `Balance` float default '0',
  PRIMARY KEY  (`RegistrationNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Perth`
--

/*!40000 ALTER TABLE `Perth` DISABLE KEYS */;
LOCK TABLES `Perth` WRITE;
INSERT INTO `Perth` VALUES ... ;
UNLOCK TABLES;
/*!40000 ALTER TABLE `Perth` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

How to repeat:
The problem happens each time I try to restore the database using query browser. I just tried again in command line and it works fine.
[9 Mar 2006 10:58] Valeriy Kravchuk
Thank you for a problem report. Please, specify the exact version of QB used and steps you performed to restore.
[10 Mar 2006 6:44] Andrew Storm
The query browser version is 1.1.20.

The steps I took to do the restore were:
1)create database ozretreats;
2)change the default schema in query browser to ozretreats;
3)open the script in query browser and execute.

By the way, I think the problem might perhaps have something to do with the line that the data is dumped on, which I have deleted in the script I copied to you.
[23 Mar 2006 13:40] Valeriy Kravchuk
Please, upload the entire dump that demonstrates the problem.
[24 Mar 2006 6:15] Andrew Storm
I cannot upload the entire dump file, as the data in it contains some sensitive information which I cannot broadcast in public in a forum such as this. You will have to figure out the problem in some other way. If it helps, i tried deleting all but one record from the data, and the restore operation worked.
[27 Mar 2006 13:41] Valeriy Kravchuk
Are there embedded single quotes (') or newlines in the data values (strings)? Can you demonstrate the same behaviour on some non-sensitive, sample data?
[28 Mar 2006 8:15] Andrew Storm
Yes, the problem seems to be an escaped quote, i.e. \'. I can reproduce the problem with the following script:

-- MySQL dump 10.9
--
-- Host: localhost    Database: OzRetreats
-- ------------------------------------------------------
-- Server version	4.1.11-Max-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `ByronBay`
--

DROP TABLE IF EXISTS `ByronBay`;
CREATE TABLE `ByronBay` (
  `RegistrationNumber` int(11) NOT NULL auto_increment,
  `Name` varchar(50) default NULL,
  `Street` varchar(50) default NULL,
  `City` varchar(50) default NULL,
  `State` varchar(20) default NULL,
  `Postcode` int(11) default NULL,
  `Country` varchar(50) default 'Australia',
  `Phone` varchar(20) default NULL,
  `Email` varchar(50) default NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `PaidBy` varchar(10) default NULL,
  `Paid` enum('y','n') default NULL,
  `Cancellation` enum('n','y') default 'n',
  `Fee` float default '0',
  `Scholarship` enum('n','y') default 'n',
  `Balance` float default '0',
  PRIMARY KEY  (`RegistrationNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ByronBay`
--

/*!40000 ALTER TABLE `ByronBay` DISABLE KEYS */;
LOCK TABLES `ByronBay` WRITE;
INSERT INTO `ByronBay` VALUES (1,'No-one','00 X\'xxxxx','xxxxxxx','NSW',0000,'Australia','00 0000 0000','','2005-05-31 18:58:27','cheque','y','n',0,'n',0),(2,'Nobody','00 xxxxx xxx','xxxxxx','xxxxxxx',0000,'New Zealand','00 000 0000','xxxxx@xyz.co.nz','2005-06-11 14:54:10','Visa','y','n',0,'n',0);
UNLOCK TABLES;
/*!40000 ALTER TABLE `ByronBay` ENABLE KEYS */;

--
-- Table structure for table `Perth`
--

DROP TABLE IF EXISTS `Perth`;
CREATE TABLE `Perth` (
  `RegistrationNumber` int(11) NOT NULL auto_increment,
  `Name` varchar(50) default NULL,
  `Street` varchar(50) default NULL,
  `City` varchar(50) default NULL,
  `State` varchar(20) default NULL,
  `Postcode` int(11) default NULL,
  `Country` varchar(50) default 'Australia',
  `Phone` varchar(20) default NULL,
  `Email` varchar(50) default NULL,
  `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `PaidBy` varchar(10) default NULL,
  `Paid` enum('y','n') default NULL,
  `Cancellation` enum('n','y') default 'n',
  `Fee` float default '0',
  `Scholarship` enum('n','y') default 'n',
  `Balance` float default '0',
  PRIMARY KEY  (`RegistrationNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Perth`
--

/*!40000 ALTER TABLE `Perth` DISABLE KEYS */;
LOCK TABLES `Perth` WRITE;
INSERT INTO `Perth` VALUES (2,'abc xyz','111 xxxyyy','abcxyz','WA',6000,'Australia','00 0000 0000','xxxx@xyz.com.au','2005-06-14 21:12:09','Visa','y','n',0,'n',0),(3,'No-one','000 xyz xx','xxxxx','WA',60000,'Australia','00 0000 0000','xxx@xyx.com.au','2005-06-14 21:12:09','Visa','y','n',0,'n',0);
UNLOCK TABLES;
/*!40000 ALTER TABLE `Perth` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
[6 Apr 2006 8:51] Valeriy Kravchuk
Verified just as described with the script from the last comment.
[24 Nov 2006 14:59] Mike Lischke
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/