| 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: | |
| 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 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/
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.