List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 4 1999 6:29pm
Subject:Re: Export/import
View as plain text  
Michael Widenius wrote:
> 
> >>>>> "Christian" == Christian Mack <Mack@stripped> writes:
> 
> Christian> Peter Hicks wrote:
> >>
> >> Folks,
> >>
> >> I've tried in vain to get a simple, straightforward, command or script
> >> to dump the contents (data, and table structures) from a MySQL
> >> database on one server, to a text file, and then to re-create the
> >> database on another sever.
> >>
> >> Using mysql_dump and mysqlimport doesn't seem to work reliably -
> >> mysql_dump doesn't appear to escape all special characters, so I
> >> get a whole host of errors when importing.
> >>
> >> Can someone point me in the direction of a script to do this?
> >>
> >> Cheers,
> >>
> >> Peter.
> 
> Christian> Hi Peter
> 
> Christian> mysqldump does escape all special characters, but a tab is not
> considered a special character. As mysqlimport per default uses tab delimited columns you
> can have problems on reading strings (not only BLOB's) with tabs inside.
> 
> Hi!
> 
> If you use mysqldump with --tab, tab characters should be escaped by
> the the MySQL server.  If you don't use --tab, then tabs shouldn't
> need to be escaped.
> 
> Can either of you send me a complete test that doesn't work?
> 
> Regards,
> Monty

Hi Monty

On a Win NT 4.0 SP4 I used this tabledefinition and data in database 'test':
CREATE TABLE bin (
  extra_chars tinyblob,
  id tinyint(4)
);

INSERT INTO bin (extra_chars, id) VALUES ('abcdefg',0);
INSERT INTO bin (extra_chars, id) VALUES ('abc\'defg',1);
INSERT INTO bin (extra_chars, id) VALUES ('abc\"defg',2);
INSERT INTO bin (extra_chars, id) VALUES ('abc\\defg',3);
INSERT INTO bin (extra_chars, id) VALUES ('abc\0defg',4);
INSERT INTO bin (extra_chars, id) VALUES ('abc\ndefg',5);
INSERT INTO bin (extra_chars, id) VALUES ('abc\tdefg',6);

Using mysqldump like this:
C:\Mack>mysqldump --tab=sql test bin

Produces the correct tabledefinition in 'sql\bin.sql':
# MySQL dump 5.13
#
# Host: localhost    Database: test
#--------------------------------------------------------
# Server version	3.22.22

#
# Table structure for table 'bin'
#
CREATE TABLE bin (
  extra_chars tinyblob,
  id tinyint(4)
);

But also the wrong data in 'sql\bin.txt':
abcdefg	0
abc'defg	1
abc"defg	2
abc\\defg	3
abc\0defg	4
abc\
defg	5
abc\	defg	6

As you can see the <newline> is converted to '\'+<newline> instead of '\n'.
Also the <tab> is converted to '\'+<tab> instead of '\t'.

Tschau
Christian

Thread
Export/importPeter Hicks1 Jun
  • Re: Export/importChristian Mack1 Jun
    • Re: Export/importMichael Widenius3 Jun
  • Re: Export/importChristian Mack4 Jun
    • Re: Export/importMichael Widenius5 Jun