MySQL Bugs: #8275: Mysqldump adds incorect path when full dump, ie 'c:\mysql\data' should be c:\\m

Bug #8275	Mysqldump adds incorect path when full dump, ie 'c:\mysql\data' should be c:\\m
Submitted:	2 Feb 2005 20:58	Modified:	15 Apr 2005 17:48
Reporter:	Matt Ryan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S1 (Critical)
Version:	4.1.8, 4.1.9	OS:	2003, 2000
Assigned to:		CPU Architecture:	Any

Description:
When a full database dump is performed, some tables have extra settings added, which are in the wrong format.

Restore is not possible without manually editing the dump, dump file is 20-40 gig making this impossible

On some tables the following is added

DATA DIRECTORY='E:\mysql\data\testdb\' 
INDEX DIRECTORY='E:\mysql\data\testdbl\' 

it should be using the following

DATA DIRECTORY='E:\\mysql\\data\\testdb\\' 
INDEX DIRECTORY='E:\\mysql\\data\\testdbl\\' 

when tables are dumped individually the data & index directory settings are not included in the dump, they are only included when a full database dump is done, and only on some of the tables, not all have the extra info.

How to repeat:
Repeats on every database I've got, when I do a full dump

unable to force repeat by individual tables

Suggested fix:
Fix or provide option to remove the "directory" setting

Matt, how did you create on windows tables with that data directory? Actualy this construction (create table .... data directory =.. ) doesn't work on windows since (afaicr) 4.0.1.

I did not use them when I created the database

they only show up when I dump with mysqldump

the only fix is to manually edit the dump file and remove them

if I mysqldump each table separately they dont show up, they only show up when I dump the whole database

I believe it's related to this bug

http://bugs.mysql.com/bug.php?id=6660 

this was fixed in one of the newer 4.1 builds, but it seems that it only fixed it if you do each table separatly, did not fix it if you dump the whole database.

I tried dropping the problem table, and rebuilding it, and it still puts in the data directory option when I do a full dump on the database

Could you give an example of command you used for creating table? what options did you use for mysqldump? 

I see that indexes and datafiles are in different directories (testdb and testdbl), do you have databases with those names? what is datadir of your server (you can check it in output of 'mysqladmin var' )?

This is the first table that bugs, I can dump and restore it fine, but if I do the whole database it inserts those extra parameters that the restore chokes on

there's 9 more of these, ie FY02, FY03, FY04 etc

our backup command (i've tried every option I can think of, same results)

mysqldump -u mrblahh -pbigdatadump --opt --all-databases --quote-names > /intranet/backup/finlog.sql

CREATE TABLE `nxg_fy01` (
  `doc_no` char(14) default NULL,
  `fapc` char(5) default NULL,
  `eoe` char(4) default NULL,
  `ta` char(2) default NULL,
  `f` char(1) default NULL,
  `amount` double(13,2) default NULL,
  `blk` char(3) default NULL,
  `julian` char(4) default NULL,
  `dov` char(6) default NULL,
  `mnhrs` double(9,0) default NULL,
  `afcr` char(1) default NULL,
  `fac` char(1) default NULL,
  `lc` char(2) default NULL,
  `dept` char(2) default NULL,
  `bsn` char(4) default NULL,
  `limit` char(4) default NULL,
  `py` char(1) default NULL,
  `oa` char(2) default NULL,
  `rd` char(1) default NULL,
  `asn` char(4) default NULL,
  `pe` char(4) default NULL,
  `fsn` char(6) default NULL,
  `odc` char(1) default NULL,
  `pfc` char(3) default NULL,
  `stanfins` char(2) default NULL,
  `dodaac` char(6) default NULL,
  `apc` char(4) default NULL,
  `date` date default NULL,
  `dic` char(3) default NULL,
  `mon` char(3) default NULL,
  `fy` char(1) default NULL,
  `ams` char(11) default NULL,
  `pmaj` char(1) default NULL,
  `pminor` char(1) default NULL,
  `progelem` char(6) default NULL,
  `mdep` char(4) default NULL,
  KEY `dodaac` (`dodaac`),
  KEY `apc` (`apc`),
  KEY `don` (`doc_no`),
  KEY `fapc` (`fapc`),
  KEY `julian` (`julian`),
  KEY `fy` (`fy`),
  KEY `pmaj` (`pmaj`),
  KEY `pminor` (`pminor`),
  KEY `ams` (`ams`),
  KEY `progelem` (`progelem`),
  KEY `ta` (`ta`)
) ENGINE=MyISAM PACK_KEYS=1

Do you need more feedback?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

reopened

Was there anything else needed? the ticket auto closed waiting feedback, but I provided feedback twice

Related to the same issue as:

http://bugs.mysql.com/bug.php?id=6660