MySQL Bugs: #44874: select into outfile exports wrong integer width

Bug #44874	select into outfile exports wrong integer width
Submitted:	14 May 2009 11:56	Modified:	18 May 2009 13:05
Reporter:	Marcus Mueller	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 6.0	OS:	Linux (debian 7)
Assigned to:		CPU Architecture:	Any

Description:
i try to select some data from my tables like this:

select MD_KZDMP,MD_VERSION,KVBEREICH,DATENSTELLE_IK,DATEINAME,KZ_LIEFERUNG into outfile '/tmp/test.txt' fields terminated by '' lines terminated by '\r\n' from diabt1_dok where md_ts>date_sub(now(), interval 8 day)

MD_VERSION is of type INT(2) unsigned zerofill
KZ_LIEFERUNG is of type INT(1)
all other columns are characters

On our old mySQL Server 4.x the result will be like this:
"DMKK0171590914492DDMP09000421"

With our new mySQL Server 5.0.32 the result in the text file looks like this:
"DMKK01         71590914492DDMP09000421          "

How to repeat:
it seems that the 5.x mySQL server does not correctly interpret the column with. the int(2) or int(1) is exported always as an int(11).

does anyone knows this problem?

Suggested fix:
i dont really have a fix now ...

Thank you for the bug report. Could you please try with the latest released version and if you still get the same behavior provide a dump file with create table and some insert statements. Thanks in advance.

here the create script:

CREATE TABLE `diabt1_dok` (
  `MD_INDEX` int(10) unsigned NOT NULL auto_increment,
  `MD_VERSION` int(2) unsigned zerofill NOT NULL default '00',
  `KVBEREICH` char(2) NOT NULL default '',
  `DATENSTELLE_IK` varchar(9) NOT NULL default '',
  `DATEINAME` varchar(11) NOT NULL default '',
  `KZ_LIEFERUNG` int(1) NOT NULL default '0'
  `MD_TS` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`MD_INDEX`)
) ENGINE=MyISAM AUTO_INCREMENT=34182 DEFAULT CHARSET=latin1 COMMENT='';

the table is same to the table on mysql server 4.x ...
data was migrated to 5.0.32 ...

i found a hint ... the problem is the "fields terminated by ''" statement.
if i replace it with "fields terminated by '#'" the correct width of the integer values is dumped to the file.

is this issue known allready?

I was wrong ... the terminated ('#') export correctly exports the integer values with correct length int(2) as #02# ...

problem is i need a fixed length export. so i use "terminated by ''" ... but when i do so, the int(2) field is exported as '02         ' like an int(11) ...

any suggestions?

Verfied as described:

CREATE TABLE t(i integer, k integer);
CREATE TABLE t1(i int(2), k int(2));

INSERT INTO t VALUES(1,1),(2,2),(3,3),(4,4);
INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4);

SELECT * FROM t INTO OUTFILE 'foo_t_terminated_with_nothing' FIELDS TERMINATED BY '';

SELECT * FROM t INTO OUTFILE 'foo_t_terminated_with_something' FIELDS TERMINATED BY '|';

SELECT * FROM t1 INTO OUTFILE 'foo_t1_terminated_with_nothing' FIELDS TERMINATED BY '';

SELECT * FROM t1 INTO OUTFILE 'foo_t1_terminated_with_something' FIELDS TERMINATED BY '|';

$ less foo_TABLE_terminated_with_nothing

You have 10 spaces between the first and next value

$ less foo_TABLE_terminated_with_something

None spaces between first and next value ....

Unfortunately when length was given in table it will be ignored here too and in stead of "1 " output is "1".

Hi

I found the same bug reported (Bug #1806 SELECT INTO OUTFILE) It was reported in 2003 and is marked as won't fix. I think this bug should be re-looked at and fixed. I can't imagine that it is a difficult fix!!

Obrigado ! Funcionou corretamente.