MySQL Bugs: #12564: mysqldump behavior change --fields-terminated-by and server version 3 or 4,5

Bug #12564	mysqldump behavior change --fields-terminated-by and server version 3 or 4,5
Submitted:	13 Aug 2005 8:30	Modified:	12 Apr 2006 19:01
Reporter:	James Day
Status:	Closed
Category:	Server: Docs	Severity:	S3 (Non-critical)
Version:	mysqldump 10.10, also one with 4.1	OS:	Linux (linux)
Assigned to:	Paul DuBois	Target Version:
Triage:	D4 (Minor)

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.

Originally reported on linux, checked on Windows XP.

tested against on suse 9.3, got the same results

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.

[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

Changing status to Verified, category to documentation as Documenting status is strictly
for bugfixes requiring a changelog entry.

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.