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
