Bug #12564 mysqldump behavior change --fields-terminated-by and server version 3 or 4,5
Submitted: 13 Aug 2005 6:30 Modified: 12 Apr 2006 17:01
Reporter: James Day Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:mysqldump 10.10, also one with 4.1 OS:Linux (linux)
Assigned to: Paul DuBois CPU Architecture:Any

[13 Aug 2005 6:30] James Day
Description:
This appears to be an undocumented deliberate change in mysqldump behavior. The behavior varies depending on the server version mysqldump is connecting to.

Initial reporter said that on upgrading from MySQL 3.23 to 4.1 the behavior of mysqldump with --fields-terminated-by null had changed. Documented behavior is:

http://dev.mysql.com/doc/mysql/en/load-data.html

"If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (non-delimited) format is used. With fixed-row format, no delimiters are used between fields (but you can still have a line terminator). Instead, column values are written and read using the ``display'' widths of the columns. For example, if a column is declared as INT(7), values for the column are written using seven-character fields. On input, values for the column are obtained by reading seven characters."

Reported that with 3.23 the output was as described, with fields being truncated if they don't fit the specified display width but that with 4.1 the fields are placed in 11 character wide fields for int(4) and 20 for int(8).

Reproducing with mysqldump 10.10 on Windows I found that when connecting to a 3.23.58 server mysqldump works as described in the manual, truncating the fields to the display width. When connecting to a 4.1 or 5.0 server it uses the wider fields, ignoring the specified display width.

Not checked: whether load data behavior has also changed in this way.

How to repeat:
On 3.23.58, 4.1.12a and 5.0. servers do:

use test
create table 5945load (
  id int(4) unsigned NOT NULL auto_increment,
  int4test int(4) unsigned NOT NULL,
  int8test int(8) unsigned NOT NULL,
  pad int(4) unsigned NOT NULL default 1,
  UNIQUE KEY id_key (id)
) type=MyISAM;

insert into 5945load ( int4test, int8test ) values (1,1), (12345,123456789);

On each server in turn with 10.10 mysqldump (from MySQL 5.0.) or version with MySQL 4.1 do:

mysqldump -t test 5945load > 5945load.sql --tab=. --fields-terminated-by= --port=4250 -u root

Observe that when connecting to the 3.23.58 server the result in 5945load.txt is:

1   1   1       1
2   1234123456781

(the third one on the first line is over the 1 in 1234578 on the second line and the fourth 1 over the final 1)

This is the documented behavior.

When connecting to 4.1 or 5.0 servers with the same mysqldump client the result is:

1          1          1          1
2          12345      123456789  1

(1 in first row above first column of each number in second row)

Suggested fix:
Check whether this is a deliberate change. If it's deliberate, change to documentation bug and update documentation. If it's not deliberate, restore documented behavior.
[13 Aug 2005 6:32] James Day
Originally reported on linux, checked on Windows XP.
[13 Aug 2005 10:57] Aleksey Kishkin
tested against on suse 9.3, got the same results
[15 Oct 2005 1:11] Patrick Galbraith
This isn't mysqldump - the problem is in the server:

mysql> select * INTO outfile './foo3.txt' FIELDS TERMINATED BY '' ENCLOSED BY '' FROM widtest;
Query OK, 2 rows affected (0.40 sec)

mysql> \q
Bye

patg@krsna:~/mysql-build/mysql-4.1/client> vi ../mysql-test/var/master-data/foo3.txt 
1          1          1          1
2          12345      123456789  1

So, the next step is to identify what controls this in the server.
[17 Oct 2005 20:59] Patrick Galbraith
[22:53] 	<patg>	monty: I have a table of int(4) and int(8), int(4) has 12345 in it, and int(8) has 123456789 in it, and the outfile has all 5 for the int(4) and all 9 for the int(8)

[22:54] 	<monty>	Patg, if you are using int(4) for an int with 12345, the output format will not work!
[22:54] 	<monty>	FIELDS TERMINATEd BY ""requires that your data is not outside of your limits. if it is, you are (and have always been) on your own
[22:54] 	<patg>	monty: the way the docs describe it, it should dump 12345 as 1234 and 123456789 as 12345678
[22:55] 	<patg>	monty: hmmm....
[22:55] 	<monty>	No, that's not true anymore and doesn't have to be
[22:55] 	<patg>	monty: so, it's not supposed to truncate?
[22:55] 	<monty>	If you are outside of the limits, there is no guranteees
[22:55] 	<monty>	no
[22:55] 	<patg>	monty: ok, then this is a documentation issue then.. 
[22:56] 	<monty>	The simple reason is that truncating is more wrong than writing the whole number
[22:56] 	<monty>	I remember a change for this a long time ago, but it should have been documented in the change log (but of course, there is a chance we forgot...)
[22:56] 	<patg>	monty: I'm glad to learn this ;) I suppose there are often cases where users are historically used to the way mysql once worked even if 'working' was wrong, and when that's changed, they think it's a bug.
<monty>	Yes, but if they expect int(4) to trunk, they are not using the format in an 'expected' manner
[19 Jan 2006 22:32] Mike Hillyer
Changing status to Verified, category to documentation as Documenting status is strictly for bugfixes requiring a changelog entry.
[12 Apr 2006 17:01] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Fixed-row format dump/reload behavior for LOAD DATA/SELECT ... INTO OUTFILE
changed in 4.1.12/5.0.6: Declared display width is ignored.  Instead a field
large enough to hold all values is used.  

I've updated the LOAD DATA INFILE section, the 4.1.12/5.0.6 changelog
sections, and the 4.1 and 5.0 upgrade sections.