List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 5 1999 10:35am
Subject:Re: Export/import
View as plain text  
<cut>

>> 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

Christian> Hi Monty

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

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

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

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

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

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

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

Thanks for the test!

The above isn't actually a problem for mysqlimport:

A test run:

shell> mysqldump --tab=/tmp test bin
shell> mysqlimport --delete test /tmp/bin.txt
test.bin: Records: 7  Deleted: 0  Skipped: 0  Warnings: 0
shell> mysqldump test bin
# MySQL dump 6.0
#
# Host: localhost    Database: test
#--------------------------------------------------------
# Server version        3.22.23-debug

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

#
# Dumping data for table 'bin'
#

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

Which is exactly as it should be.

I have now updated the MySQL manual with the following to explain
this:

     In the resulting text file, only the following characters are
     escaped by the `ESCAPED BY' character:

        * The `ESCAPED BY' character

        * The first character in `FIELDS TERMINATED BY'

        * The first character in `LINES TERMINATED BY'

     Additionally `ASCII 0' is converted to `ESCAPED BY' followed by 0
     (`ASCII 48').

     The reason for the above is that one MUST escape any `FIELDS
     TERMINATED BY', `ESCAPED BY' or `LINES TERMINATED BY' characters
     to be able to reliable be able to read the file back. `ASCII 0' is
     escaped to make it easier to view with some pages.  As the above
     file doesn't have to conform to any SQL syntax nothing else needs
     to be escaped.

     As the resulting file doesn't have to conform to the SQL syntax
     nothing else needs to be escaped.

--

Note that the characters are only escaped, not escaped + converted.

Regards,
Monty
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