MySQL Bugs: #6660: mysqldump creates bad pathnames on Windows

Bug #6660	mysqldump creates bad pathnames on Windows
Submitted:	16 Nov 2004 10:20	Modified:	7 Apr 2005 1:55
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.1.10	OS:	Windows (Windows)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
When mysqldump dumps a table with a DATA DIRECTORY create option on Windows it creates the path with backslashes, which will generate a syntax error when importing from the dump.

How to repeat:
Create a table that will generate a DATA DIRECTORY for mysqldump (this I'm not sure how to do, since it should be ignored on Windows, but I have seen this at multiple times on Windows, just not sure when it's triggered).

Suggested fix:
C:\path => C:/path or C:\\path

According with the Manual:

DATA DIRECTORY and INDEX DIRECTORY 
The DATA DIRECTORY and INDEX DIRECTORY options for CREATE TABLE are ignored on Windows, because Windows doesn't support symbolic links. These options also are ignored on systems that have a non-functional realpath() call. 

Since the DATA DIRECTORY is ignored as mentioned above,  can be considered
not a bug ?

Then I would say that mysqldump on windows never should output a DATA DIRECTORY in the table creation, since it should be ignored on that platform.

Sorry I don't understand what you meant. I wasn't able for to get a
mysqldump like you mentioned because the create table ignores the
data dirctory clause. See below both show create table output
on Linux and Windows:

miguel@hegel:~/dbs/4.1$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.8-debug-log

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

mysql> create database mytest;
Query OK, 1 row affected (0.00 sec)

mysql> use mytest;
Database changed
mysql> create table t1 (id int) data directory="/home/miguel/mydata";
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='/home/miguel/mydata/'
1 row in set (0.01 sec)

mysql> 

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.7-nt-log

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

mysql> create database mytest;
Query OK, 1 row affected (0.01 sec)

mysql> use mytest;
Database changed
mysql> create table t1 (id int) data directory="c:/mydata";
Query OK, 0 rows affected (0.10 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

The problem only appears with mysqldump, not with SHOW CREATE TABLE.

Still broken in 4.1.9 on winxp

Sorry I wasn't able to repeat with latest Windows BK source.
Below how I verified:

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.11-nt

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

mysql> create database testbug;
Query OK, 1 row affected (0.00 sec)

mysql> use testbug;
Database changed
mysql> create table t1 (id int) data directory="c:/mydata";
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values (1), (2), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> exit
Bye

C:\mysql\bin>mysqldump -uroot testbug > my.sql

C:\mysql\bin>type my.sql
-- MySQL dump 10.9
--
-- Host: localhost    Database: testbug
-- ------------------------------------------------------
-- Server version       4.1.11-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' */;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t1`
--

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

C:\mysql\bin>mysql -uroot testbug < my.sql

C:\mysql\bin>mysql -e"select * from testbug.t1" -uroot
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

Found a way to reproduce this, it seems that when you create a table like a MERGE table it'll somehow go in and change the underlaying MyISAM tables and add the DATA DIRECTORY path.
See test-case below:

# Default storage engine for server is InnoDB

CREATE TABLE `log` (
   `host` varchar(100) NOT NULL default '',
   `visited` datetime NOT NULL default '0000-00-00 00:00:00',
   `timezone` varchar(6) NOT NULL default '',
   `cmd` text NOT NULL,
   `returncode` int(11) NOT NULL default '0',
   `bytes` varchar(10) default NULL,
   `referer` text,
   `browser` text
 );

-- Query OK, 0 rows affected (0.00 sec)

ALTER TABLE log ENGINE = MyISAM;

-- Query OK, 0 rows affected (0.00 sec)
-- Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE log2 LIKE log;
-- Query OK, 0 rows affected (0.00 sec)

CREATE TABLE l LIKE log;
-- Query OK, 0 rows affected (0.00 sec)

ALTER TABLE l ENGINE = MERGE UNION = (log, log2);

-- Query OK, 0 rows affected (0.00 sec)
-- Records: 0  Duplicates: 0  Warnings: 0

exit;

C:\>mysqldump --master-data --single-transaction bug > tmp.sql

Creates:

-- MySQL dump 10.9
--
-- Host: localhost    Database: bug
-- ------------------------------------------------------
-- Server version	4.1.10-nt-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' */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000001', MASTER_LOG_POS=43049414;

--
-- Table structure for table `l`
--

DROP TABLE IF EXISTS `l`;
CREATE TABLE `l` (
  `host` varchar(100) NOT NULL default '',
  `visited` datetime NOT NULL default '0000-00-00 00:00:00',
  `timezone` varchar(6) NOT NULL default '',
  `cmd` text NOT NULL,
  `returncode` int(11) NOT NULL default '0',
  `bytes` varchar(10) default NULL,
  `referer` text,
  `browser` text
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 UNION=(`log`,`log2`);

--
-- Table structure for table `log`
--

DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `host` varchar(100) NOT NULL default '',
  `visited` datetime NOT NULL default '0000-00-00 00:00:00',
  `timezone` varchar(6) NOT NULL default '',
  `cmd` text NOT NULL,
  `returncode` int(11) NOT NULL default '0',
  `bytes` varchar(10) default NULL,
  `referer` text,
  `browser` text
) ENGINE=MyISAM DEFAULT CHARSET=utf8 DATA DIRECTORY='C:\mysql\Data\bug\' INDEX DIRECTORY='C:\mysql\Data\bug\';

--
-- Dumping data for table `log`
--

/*!40000 ALTER TABLE `log` DISABLE KEYS */;
LOCK TABLES `log` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `log` ENABLE KEYS */;

--
-- Table structure for table `log2`
--

DROP TABLE IF EXISTS `log2`;
CREATE TABLE `log2` (
  `host` varchar(100) NOT NULL default '',
  `visited` datetime NOT NULL default '0000-00-00 00:00:00',
  `timezone` varchar(6) NOT NULL default '',
  `cmd` text NOT NULL,
  `returncode` int(11) NOT NULL default '0',
  `bytes` varchar(10) default NULL,
  `referer` text,
  `browser` text
) ENGINE=MyISAM DEFAULT CHARSET=utf8 DATA DIRECTORY='C:\mysql\Data\bug\' INDEX DIRECTORY='C:\mysql\Data\bug\';

--
-- Dumping data for table `log2`
--

/*!40000 ALTER TABLE `log2` DISABLE KEYS */;
LOCK TABLES `log2` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `log2` 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 */;

Thank you for the test case.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23009

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23047

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23060

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

This was pushed 3/15.  Sorry, but forgot to set to documenting.

This was fixed in 4.1 and 5.0 (as of 3/15)

Noted in 4.1.11, 5.0.3 changelogs.