MySQL Bugs: #30946: mysqldump silently ignores --default-character-set when used with --tab

Bug #30946	mysqldump silently ignores --default-character-set when used with --tab
Submitted:	11 Sep 2007 7:19	Modified:	18 Aug 2009 18:11
Reporter:	Marek Matula	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	character set, mysqldump, tab

Description:
When mysqldump is used with both "--default-character-set" and "--tab" the database content in tab-separated data files is always in its internal character set and the --default-character-set switch seems to be ignored. According to the documentation it should work: it is even suggested for data dumps:

"[...] if you write a data file with mysqldump -T or by issuing a SELECT ... INTO OUTFILE statement in mysql, be sure to use a --default-character-set option with mysqldump or mysql so that output is written in the character set to be used when the file is loaded with LOAD DATA INFILE."

How to repeat:
1. Create a table with non-UTF8 character set: eg. latin1 or latin2
2. insert some data containing non-ASCII characters
3. dump the table with --default-character-set=utf8 and --tab=/path/to/dump
4. The data in tab-separated file is still in latin1 or latin2 and not UTF8

Suggested fix:
mysqldump should not ignore --default-character-set when dumping data with --tab

Thank you for the bug report. Maybe a documentation bug.

I believe this is not a documentation bug. If it was, it would be not possible to dump (and restore) tables that have more than one charset in their fields!

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/commits/34566

ChangeSet@1.2531, 2007-09-26 18:22:25+02:00, gshchepa@devsrv-b.mysql.com +5 -0
  Fixed bug #30946.
  When mysqldump was used with both --default-character-set and
  --tab options the database content in tab-separated data files
  was always in its internal character set and the
  --default-character-set switch was ignored.
  
  The mysqldump client uses the "SELECT * INTO OUTFILE" statement
  to provide the --tab command line switch logic. It is supposed
  to use the symmetrical "LOAD DATA INFILE" statement to load a
  resulting data file back to a database.
  By the definition the "LOAD DATA INFILE" statement interprets
  all fields in the file as having the same character set, regardless
  of the data types of the columns into which field values are loaded.
  The character_set_database system variable or the "CHARACTER
  SET" closure of the "LOAD DATA INFILE" statement is used to define
  that character set. However, the "SELECT ... INTO OUTFILE" statement
  dumps table fields in its internal character sets, so "SELECT INTO
  OUTFILE" and "LOAD DATA INFILE" statements are not symmetrical.
  To not to modify current server semantic (only to extend it) the
  "SELECT ... INTO OUTFILE" statement syntax was extended with the
  "CHARACTER SET" statement like in the "LOAD DATA INFILE".
  
  Documentation should be updated:
  [ http://dev.mysql.com/doc/refman/5.1/en/select.html ]
  SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE;
  the syntax for the export_options part of the statement consists of
  the same CHARACTER SET, FIELDS and LINES clauses that are used with
          +^^^^^^^^^^^^^^
  the LOAD DATA INFILE statement.

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/commits/35051

ChangeSet@1.2582, 2007-10-06 16:10:17+05:00, gshchepa@gleb.loc +7 -0
  Bug#30946: mysqldump silently ignores --default-character-set
  when used with --tab.
  
  Added optional CHARACTER SET clause to the SELECT ... INTO OUTFILE
  (to complement the same clause in LOAD DATA INFILE).
  mysqldump is updated to use it in --tab mode.

What does the following mean?

"content in tab-separated data files was always in its internal character set"

Whose internal character set?  The MySQL server, mysqldump, the database, the table, the columns?

It would be very helpful if the documentation answered the question, "What character set is used for SELECT INTO OUTFILE?"

> What does the following mean?

> "content in tab-separated data files was always in its internal character set"

> Whose internal character set?  The MySQL server, mysqldump, the database,
> the table, the columns?

Character set of each column (charset of whole row is a mix of column charsets, or in other words it is BINARY charset).

> It would be very helpful if the documentation answered the question,
>  "What character set is used for SELECT INTO OUTFILE?"

Current implementation uses BINARY charset. Bugfix will extend SELECT INTO OUTFILE syntax to allow data dumping with a special charset value.

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/commits/41685

ChangeSet@1.2594, 2008-02-05 06:37:04+04:00, gshchepa@host.loc +10 -0
  Fixed bugs #30946 and #31677.
  Bug#30946: mysqldump silently ignores --default-character-set
  when used with --tab.
  
  Added optional CHARACTER SET clause to the SELECT ... INTO OUTFILE
  (to complement the same clause in LOAD DATA INFILE).
  mysqldump is updated to use it in --tab mode.
  
  Added complete support for multibyte delimiter characters,
  support for UCS2 harsets and hexadecimal representation of
  malformed characters been added.
  
  Fixed bug with 8-bit separators of LOAD DATA INFILE statement.
  
  New error message has been provided: "Malformed ... delimiter value",
  where "..." is one of "FIELDS TERMINATED BY", "FIELDS ENCLOSED BY",
  "FIELDS ESCAPED BY", "LINES TERMINATED BY" or "LINES STARTING BY",
  to report errors on bad SELECT ... INTO OUTFILE separator values.

Hi Gleb, I applied your patch and found some problems:

1. Crash with ucs2 table and big5 output:

CREATE TABLE t1 (
  a varchar(10) default NULL,
  b varchar(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=ucs2;

delete from t1;
insert into t1 values (0xFFFF,0xFFFF);
select * into outfile 't1.txt' character set big5 from t1;

ERROR 2013 (HY000): Lost connection to MySQL server during query

2. Wrong file with ucs2 table and default (binary) character set:

drop table if exists t1;
create table t1 (a varchar(10), b varchar(20)) character set ucs2;
insert into t1 values ('a1','b1');
select * into outfile 't1.txt' from t1;
select load_File('test/t1.txt');

+--------------------------+
| load_File('test/t1.txt') |
+--------------------------+
| \0\0\01       \0\0\01
         |
+--------------------------+

This result is wrong. I would expect:
\0a\01<tab>\0b\01<tab><nl>

More comments coming

Sergei set bug #37610 as duplicate of this bug here.

I made some comments and also debugging on that bug. Please read it also.

Bug #43288 is related to this bug here.

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/commits/76888

Review comments sent by e-mail:

http://lists.mysql.com/commits/79141

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/commits/79147

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/commits/79554

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/commits/79790

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/commits/79959

3052 Gleb Shchepa	2009-08-04
      Additional post-commit fix (bug #30946): server version
      in a conditional commentary at the new SELECT ... INTO
      OUTFILE ... CHARACTER SET syntax has been updated to 5.1.38.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:gshchepa@mysql.com-20090803192436-r8nqv6zi0a6did5t) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)

Noted in 5.1.38, 5.4.4 changelogs.

Previously, SELECT ... INTO OUTFILE dumped column values without
character set conversion, which could produce data files that cannot
be imported without error if different columns used different 
character sets. A consequence of this is that mysqldump ignored the
--default-character-set option if the --tab option was given (which
causes SELECT ... INTO OUTFILE to be used to dump data.)

INTO OUTFILE now can be followed by a CHARACTER SET clause indicating
the character set to which dumped values should be converted. Also,
mysqldump adds a CHARACTER SET clause to the SELECT ... INTO OUTFILE
statement used to dump data, so that --default-character-set is no
longer ignored if --tab is given.

Other changes are that SELECT ... INTO OUTFILE enforces that ENCLOSED
BY and ESCAPED BY arguments must be a single character, and SELECT
... INTO OUTFILE and LOAD DATA INFILE produce warnings if non-ASCII
field or line separators are specified.

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

This fix now has been pushed into 5.4.2.

I tracked a new bug back to this one (http://bugs.mysql.com/bug.php?id=53088).  It appears the new charset conversion code truncates all converted text/blob columns to MAX_FIELD_WIDTH (766) bytes.  I am not sure whether it is desirable to convert binary format columns to the default charset at all.