| Bug #17434 | restore fail when backup sql contain \' | ||
|---|---|---|---|
| Submitted: | 15 Feb 2006 20:36 | Modified: | 19 Apr 2006 11:28 |
| Reporter: | Pornchai Rungsirijaratthong | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Administrator | Severity: | S1 (Critical) |
| Version: | 1.1.8, 1.1.4 | OS: | Windows (Windows XP, 2000) |
| Assigned to: | CPU Architecture: | Any | |
[16 Feb 2006 9:35]
Valeriy Kravchuk
Thank you for a problem report. What do you mean by "restore failure". I see no error messages during restore of simple backup of your table, with only one row of data containing \', on XP.
[16 Feb 2006 13:52]
Christoph Kopetzky
Try version 1.1.7
[17 Feb 2006 15:47]
Pornchai Rungsirijaratthong
Show status success
Attachment: error in MySQL admin.jpg (image/jpeg, text), 114.03 KiB.
[17 Feb 2006 15:48]
Pornchai Rungsirijaratthong
No row in table!
Attachment: error in MySQL admin 1.jpg (image/jpeg, text), 66.16 KiB.
[17 Feb 2006 15:48]
Pornchai Rungsirijaratthong
sql to reproduce bug
Attachment: backup.sql (application/octet-stream, text), 2.42 KiB.
[17 Feb 2006 15:52]
Pornchai Rungsirijaratthong
Yes. Admin say it is successful. But no record in table. Hint is Use 2 insert sql statement with the first one contain ' in data. Do you try my SQLs yet? If not, I've attached it for you. Regards, Pornchai
[20 Feb 2006 13:54]
Valeriy Kravchuk
Yes, MySQL Administrator 1.1.8 is not able to restore the following simple backup script created by the same version: -- MySQL Administrator dump 1.4 -- -- ------------------------------------------------------ -- Server version 5.0.15-nt /*!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' */; -- -- Create schema test6 -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ test6; USE test6; -- -- Table structure for table `test6`.`pmtask` -- DROP TABLE IF EXISTS `pmtask`; CREATE TABLE `pmtask` ( `ID` bigint(20) NOT NULL, `tasksCode` varchar(255) default NULL, `description` varchar(255) default NULL, `note` varchar(255) default NULL, `spec` varchar(255) default NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Dumping data for table `test6`.`pmtask` -- /*!40000 ALTER TABLE `pmtask` DISABLE KEYS */; INSERT INTO `pmtask` (`ID`,`tasksCode`,`description`,`note`,`spec`) VALUES (6914049,'VT-9300','Check all screw at conveyor and tighten if it\'s necessary','','-'); /*!40000 ALTER TABLE `pmtask` 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 */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
[19 Apr 2006 11:28]
Valeriy Kravchuk
I was not able to repeat anymore (with the same small dump from my last comment) with MySQL Administrator 1.1.9 on XP and MySQL server 5.0.21-BK running on Linux. Please, try to use the latest versions. Reopen this report in case of any similar problem with 5.0.20 and 1.1.9 or newer versions.
Description: restore fail when backup sql contain \' e.g. INSERT INTO "pmtask" ("ID","tasksCode","description","note","spec") VALUES (6389761,'2400','Grease the ball screw slides','','-'), (6422529,'2400','Check the conveyor belts for wear','','-'), (6455297,'2400','Check the inline material filter for clogging','','-'), (6488065,'2400','Inspect all moving cables for excessive wear','','-'), (6520833,'2400','Check the slides for wear and smooth operation','','-'), (6553601,'VT-9300','Visual inspect signal status light','','-'), (6586369,'VT-9300','Clean and vacuum all components','','-'), (6619137,'VT-9300','Visual inspect adapter drive belts','','-'), (6651905,'VT-9300','Visual inspect adapter clamp','','-'), (6684673,'VT-9300','Turn off system AC power','','-'), (6717441,'VT-9300','Use vacuum cleaner & bush to clean','','-'), (6750209,'VT-9300','Apply grease at movment axis X, Y, Z if it\'s necessary','','-'), (6782977,'VT-9300','Access the lubrication nipple on the X & Y axis lead screw','','-'), (6815745,'VT-9300','Apply the grease for lead screw for X, Y, & Z axis','','-'), (6848513,'VT-9300','Clean the surface of adapter sensor','','-'); INSERT INTO "pmtask" ("ID","tasksCode","description","note","spec") VALUES (6881281,'VT-9300','Inspect the adapter drive belt, Y & Z aixs and change if necessary','','-'), (6914049,'VT-9300','Check all screw at conveyor and tighten if it\'s necessary','','-'), (6946817,'VT-9300','Check the pressure within the range of 4.5-5.5 bar & clean the filter','','4.5-5.5'), (6979585,'SLCF 24','Check and clean the ready state of chain conveyor','','-'), (7012353,'SLCF 24','Check and clean the ready state of finger','','-'), (7045121,'SLCF 24','Check and clean the finger cleaner tank','','-'), (7077889,'SLCF 24','Check all terminal of electrical system','','-'), (7110657,'SLCF 24','Check and clean the alignment solder pot module','','-'), (7143425,'SLCF 24','Check and clean heater element solder pot module','','-'), (7176193,'SLCF 24','Check the lubricate drive shaft','','-'), (7208961,'SLCF 24','Check and clean solder pot and solder dross','','-'), (7241729,'SLCF 24','Check and clean pump of motor solder pot module','','-'), (7274497,'SLCF 24','Clean dust collector wave soldering','','-'); How to repeat: try this: create table PMTask ( ID bigint not null, tasksCode varchar(255), description varchar(255), note varchar(255), spec varchar(255), primary key (ID) ); INSERT INTO "pmtask" ("ID","tasksCode","description","note","spec") VALUES (6389761,'2400','Grease the ball screw slides','','-'), (6422529,'2400','Check the conveyor belts for wear','','-'), (6455297,'2400','Check the inline material filter for clogging','','-'), (6488065,'2400','Inspect all moving cables for excessive wear','','-'), (6520833,'2400','Check the slides for wear and smooth operation','','-'), (6553601,'VT-9300','Visual inspect signal status light','','-'), (6586369,'VT-9300','Clean and vacuum all components','','-'), (6619137,'VT-9300','Visual inspect adapter drive belts','','-'), (6651905,'VT-9300','Visual inspect adapter clamp','','-'), (6684673,'VT-9300','Turn off system AC power','','-'), (6717441,'VT-9300','Use vacuum cleaner & bush to clean','','-'), (6750209,'VT-9300','Apply grease at movment axis X, Y, Z if it\'s necessary','','-'), (6782977,'VT-9300','Access the lubrication nipple on the X & Y axis lead screw','','-'), (6815745,'VT-9300','Apply the grease for lead screw for X, Y, & Z axis','','-'), (6848513,'VT-9300','Clean the surface of adapter sensor','','-'); INSERT INTO "pmtask" ("ID","tasksCode","description","note","spec") VALUES (6881281,'VT-9300','Inspect the adapter drive belt, Y & Z aixs and change if necessary','','-'), (6914049,'VT-9300','Check all screw at conveyor and tighten if it\'s necessary','','-'), (6946817,'VT-9300','Check the pressure within the range of 4.5-5.5 bar & clean the filter','','4.5-5.5'), (6979585,'SLCF 24','Check and clean the ready state of chain conveyor','','-'), (7012353,'SLCF 24','Check and clean the ready state of finger','','-'), (7045121,'SLCF 24','Check and clean the finger cleaner tank','','-'), (7077889,'SLCF 24','Check all terminal of electrical system','','-'), (7110657,'SLCF 24','Check and clean the alignment solder pot module','','-'), (7143425,'SLCF 24','Check and clean heater element solder pot module','','-'), (7176193,'SLCF 24','Check the lubricate drive shaft','','-'), (7208961,'SLCF 24','Check and clean solder pot and solder dross','','-'), (7241729,'SLCF 24','Check and clean pump of motor solder pot module','','-'), (7274497,'SLCF 24','Clean dust collector wave soldering','','-'); Suggested fix: Parsing those sql are not correct! They must be 3 sql statement but Admin treat as 2 sql!!