Bug #11059 mysqldump improperly handles lone single quote in mediumtext field.
Submitted: 3 Jun 2005 1:56 Modified: 3 Jun 2005 14:39
Reporter: Chris Perry Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.0.4 OS:Solaris (Solaris 10)
Assigned to: CPU Architecture:Any

[3 Jun 2005 1:56] Chris Perry
Description:
mysqldump omits closing quote on generated insert statements when the content of any text type field contains a solitary double quote.  This makes restoring data containing such instances extremely difficult.

How to repeat:
Create a table with a char/varchar/XXXtext field and insert one double quote (").  Mysqldump will generate an insert statement containing an opening single quote, but omitting the double quote contained in the field and the closing single quote.

mysql> create table bugtable (charField char(10), varcharField varchar(10), tinytextField tinytext, textField text, mediumtextField mediumtext, longtextField longtext);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into bugtable (charField, varcharField, tinytextField, textField, mediumtextField, longtextField) values ('\"','\"','\"','\"','\"','\"');
Query OK, 1 row affected (0.00 sec)

mysql> select * from bugtable \G
*************************** 1. row ***************************
      charField: "
   varcharField: "
  tinytextField: "
      textField: "
mediumtextField: "
  longtextField: "
1 row in set (0.00 sec)

mysql> quit
Bye
<289 dbm:~>mysqldump -u xxx -pxxx bug;
-- MySQL dump 10.10
--
-- Host: localhost    Database: bug
-- ------------------------------------------------------
-- Server version       5.0.4-beta-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 `bugtable`
--

DROP TABLE IF EXISTS `bugtable`;
CREATE TABLE `bugtable` (
  `charField` char(10) default NULL,
  `varcharField` varchar(10) default NULL,
  `tinytextField` tinytext,
  `textField` text,
  `mediumtextField` mediumtext,
  `longtextField` longtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bugtable`
--

/*!40000 ALTER TABLE `bugtable` DISABLE KEYS */;
LOCK TABLES `bugtable` WRITE;
INSERT INTO `bugtable` VALUES (',',',',',');
UNLOCK TABLES;
/*!40000 ALTER TABLE `bugtable` 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 */;

Suggested fix:
Correct quote escaping algorithm.
[3 Jun 2005 2:09] MySQL Verification Team
I wasn't able to repeat on Linux Debian with today BK source,
so it is an issue already fixed:

miguel@hegel:~/dbs/5.0$ bin/mysqladmin -uroot create dbbug
miguel@hegel:~/dbs/5.0$ bin/mysql -uroot dbbug
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.7-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bugtable (charField char(10), varcharField varchar(10),
    -> tinytextField tinytext, textField text, mediumtextField mediumtext,
    -> longtextField longtext);
Query OK, 0 rows affected (0.02 sec)

mysql>  insert into bugtable (charField, varcharField, tinytextField, textField,
    -> mediumtextField, longtextField) values ('\"','\"','\"','\"','\"','\"');
Query OK, 1 row affected (0.00 sec)

mysql> select * from bugtable \G
*************************** 1. row ***************************
      charField: "
   varcharField: "
  tinytextField: "
      textField: "
mediumtextField: "
  longtextField: "
1 row in set (0.00 sec)

mysql> quit
Bye
miguel@hegel:~/dbs/5.0$ bin/mysqldump -uroot dbbug
-- MySQL dump 10.10
--
-- Host: localhost    Database: dbbug
-- ------------------------------------------------------
-- Server version       5.0.7-beta-debug

/*!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 `bugtable`
--

DROP TABLE IF EXISTS `bugtable`;
CREATE TABLE `bugtable` (
  `charField` char(10) default NULL,
  `varcharField` varchar(10) default NULL,
  `tinytextField` tinytext,
  `textField` text,
  `mediumtextField` mediumtext,
  `longtextField` longtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bugtable`
--

/*!40000 ALTER TABLE `bugtable` DISABLE KEYS */;
LOCK TABLES `bugtable` WRITE;
INSERT INTO `bugtable` VALUES ('\"','\"','\"','\"','\"','\"');
UNLOCK TABLES;
/*!40000 ALTER TABLE `bugtable` 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 */;
[3 Jun 2005 14:34] Chris Perry
Upgraded servers to 5.0.6 and confirmed that this bug has been fixed.  Thanks.
[3 Jun 2005 14:39] MySQL Verification Team
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/