Bug #46895 Test "outfile_loaddata" fails (reproducible)
Submitted: 24 Aug 2009 15:38 Modified: 23 May 2011 17:27
Reporter: Joerg Bruehe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1 + 5.4 + 5.5 OS:Any (big-endian)
Assigned to: Nirbhay Choubey CPU Architecture:Any
Tags: experimental, PB, test failure
Triage: Triaged: D2 (Serious)

[24 Aug 2009 15:38] Joerg Bruehe
Description:
I have first seen this test failure in a test run on 2009-Aug-6 (5.1.38),
the test still passed in May, I have no intermediate results.

I get it only on my PPC machine, not on x86. (All are 32-bit only.)

The symptom
(unchanged since I first observed it, identical for 5.1 and 5.4):
=====
main.outfile_loaddata                    [ fail ]
        Test ended at 2009-08-06 14:49:17

CURRENT_TEST: main.outfile_loaddata
--- /MySQL/REPO/V51/work-5.1/mysql-test/r/outfile_loaddata.result       2009-08-06 12:41:50.000000000 +0300
+++ /MySQL/REPO/V51/work-5.1/mysql-test/r/outfile_loaddata.reject       2009-08-06 15:49:17.000000000 +0300
@@ -143,15 +143,8 @@
 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary FIELDS TERMINATED BY '�~J';
 Warnings:
 Warning        1638    Non-ASCII separator arguments are not fully supported
-Warning        1265    Data truncated for column 'a' at row 1
-Warning        1261    Row 1 doesn't contain data for all columns
-Warning        1261    Row 1 doesn't contain data for all columns
-Warning        1265    Data truncated for column 'a' at row 2
-Warning        1261    Row 2 doesn't contain data for all columns
-Warning        1261    Row 2 doesn't contain data for all columns
 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
 a      b       c
-1      NULL    NULL
 1      ABC-�~P�~Q�~R   DEF-�~B�~C�~D
 2      NULL    NULL
 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' LINES STARTING BY '�~J';
@@ -181,7 +174,6 @@
 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
 a      b       c
 1      ABC-�~P�~Q�~R   DEF-�~B�~C�~D
-1      ABC-�~P�~Q�~R   DEF-�~B�~C�~D�~Q�| 2
 2      NULL    NULL
 # Default (binary) charset:
 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FROM t1;
@@ -198,7 +190,7 @@
 # latin1 charset (INTO OUTFILE warning is expected):
 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET latin1 FROM t1;
 Warnings:
-Warning        1366    Incorrect string value: '\xE1\xE2\xF7' for column 'b' at row 1
+Warning        1366    Incorrect string value: '\xE1\xE2\xF7' for column 'b' at row 247
 ##################################################
 1      ABC-??? DEF-���
 2      \N      \N
@@ -213,7 +205,7 @@
 # KOI8-R charset (INTO OUTFILE warning is expected):
 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET koi8r FROM t1;
 Warnings:
-Warning        1366    Incorrect string value: '\xC2\xC3\xC4' for column 'c' at row 1
+Warning        1366    Incorrect string value: '\xC2\xC3\xC4' for column 'c' at row 196
 ##################################################
 1      ABC-��� DEF-???
 2      \N      \N

mysqltest: Result content mismatch
=====

How to repeat:
Build + test on Linux/PPC.
[3 Sep 2009 16:38] Joerg Bruehe
Now that I have built 5.4.2-beta on all platforms, I have more info:

This test failure occurs on big-endian CPUs (PowerPC, Sparc, S390, HPPA) in 32 bit builds regardless of the operating system (OS X, Solaris, Linux, HP-UX).

The symptom in 5.4.2-beta is:
=====
main.outfile_loaddata                    [ retry-fail ]
        Test ended at YYYY-MM-DD HH:MM:SS

CURRENT_TEST: main.outfile_loaddata
--- /PATH/mysql-test/r/outfile_loaddata.result
+++ /PATH/mysql-test/r/outfile_loaddata.reject
@@ -198,7 +198,7 @@
 # latin1 charset (INTO OUTFILE warning is expected):
 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET latin1 FROM t1;
 Warnings:
-Warning        1366    Incorrect string value: '\xE1\xE2\xF7' for column 'b' at row 1
+Warning        1366    Incorrect string value: '\xE1\xE2\xF7' for column 'b' at row 0
 ##################################################
 1      ABC-??? DEF-ÂÃÄ
 2      \N      \N
@@ -213,7 +213,7 @@
 # KOI8-R charset (INTO OUTFILE warning is expected):
 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET koi8r FROM t1;
 Warnings:
-Warning        1366    Incorrect string value: '\xC2\xC3\xC4' for column 'c' at row 1
+Warning        1366    Incorrect string value: '\xC2\xC3\xC4' for column 'c' at row 0
 ##################################################
 1      ABC-áâ÷ DEF-???
 2      \N      \N

mysqltest: Result content mismatch
=====

The warnings about "Data truncated", which are present in the ".result" file, are missing on Linux only, present on OS X and Solaris.

Sometimes, especially on HP-UX, the "row" reported is neither 1 nor 0 but some huge garbage.

There are few cases where the exact symptom differs, but these are exceptions which should hopefully disappear when the main platforms (Solaris Sparc, OS X PPC) are fixed.
[23 Sep 2009 23:20] [ name withheld ]
I am seeing this also, using 5.1.39 on Fedora 12 on PPC64.  It might fail on PPC too, not sure.
I can confirm it does not happen on X86_64 on the same OS/compiler/build options.  I'm not entirely convinced about the bigendian/littleendian theory though --- after looking at the source code in sql_load.cc a little bit, I wonder if it is a question of whether "char" is signed or unsigned.  There's a bunch of int vs char comparisons and cross-assignments that look a tad fragile to me.

The other thing that seems a bit odd to me is that the behavior embodied in the expected-results file at lines 143ff looks *wrong*, and what the PPC machine is giving seems *right*.  I see that all of this test is new since 5.1.37 ... did anyone check if the results enshrined in the file are actually correct?
[20 Oct 2009 11:35] Joerg Bruehe
188 occurrences of this test failure during the build of 5.1.40.
[18 Nov 2009 17:58] Joerg Bruehe
No surprise: Same issue in the builds of 5.5.0-beta.
[20 Jan 2010 15:13] Alexander Nozdrin
Now it started to fail in PB (next-mr, 6.0):

CURRENT_TEST: main.outfile_loaddata
--- /export/home/pb2/test/sb_0-1205169-1263936450.4/mysql-5.5.99-m3-solaris10-sparc-test/mysql-test/r/outfile_loaddata.result	2010-01-19 23:38:21.000000000 +0300
+++ /export/home/pb2/test/sb_0-1205169-1263936450.4/mysql-5.5.99-m3-solaris10-sparc-test/mysql-test/r/outfile_loaddata.reject	2010-01-20 01:00:15.709555689 +0300
@@ -198,7 +198,7 @@
 # latin1 charset (INTO OUTFILE warning is expected):
 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET latin1 FROM t1;
 Warnings:
-Warning	1366	Incorrect string value: '\xE1\xE2\xF7' for column 'b' at row 1
+Warning	1366	Incorrect string value: '\xE1\xE2\xF7' for column 'b' at row 0
 ##################################################
 1	ABC-???	DEF-ÂÃÄ
 2	\N	\N
@@ -213,7 +213,7 @@
 # KOI8-R charset (INTO OUTFILE warning is expected):
 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET koi8r FROM t1;
 Warnings:
-Warning	1366	Incorrect string value: '\xC2\xC3\xC4' for column 'c' at row 1
+Warning	1366	Incorrect string value: '\xC2\xC3\xC4' for column 'c' at row 0
 ##################################################
 1	ABC-áâ÷	DEF-???
 2	\N	\N

mysqltest: Result content mismatch
[20 Jan 2010 15:14] Alexander Nozdrin
The test started to fail on Solaris only.
Making it experimental on Solaris.
[28 Jan 2010 9:28] Alexander Nozdrin
After latest merge from 5.1 the test started to fail
in trunk (mysql-trunk-merge).
[28 Jan 2010 18:49] Bugs System
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/98503

2961 Alexander Nozdrin	2010-01-28
      Make the following tests experimental:
        - main.outfile_loaddata @solaris due to Bug#46895
        - main.signal_demo3 @solaris due to Bug#47791
        - main.sp @solaris due to Bug#47791
        - rpl.rpl_slave_load_remove_tmpfile @windows due to Bug#50474
[1 Feb 2010 12:03] Georgi Kodinov
Started failing in 5.1-bugteam (solaris).
[1 Feb 2010 12:07] Bugs System
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/98819

3345 Georgi Kodinov	2010-02-01
      Made outfile_testdata experimental in 5.1-bugteam, pending the
      resulution of bug #46895.
[4 Feb 2010 10:19] Bugs System
Pushed into 5.1.44 (revid:joro@sun.com-20100204101444-2j32mhqroo0iiio6) (version source revid:joro@sun.com-20100201120521-kk9tz02fcffbm083) (merge vers: 5.1.43) (pib:16)
[5 Feb 2010 11:47] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100203162117-gjiiuzj6sq2ohlss) (pib:16)
[5 Feb 2010 11:53] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alfranio.correia@sun.com-20100121210527-rbuheu5rnsmcakh1) (merge vers: 5.5.99-m3) (pib:16)
[5 Feb 2010 11:59] Bugs System
Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alik@sun.com-20100203140148-nmlve92a9cq69vp9) (merge vers: 5.5.2-m2) (pib:16)
[12 Mar 2010 14:14] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:30] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:46] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[16 Mar 2010 9:34] Bugs System
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/103375

3436 Bernd Ocklin	2010-03-09
      make failing solaris test cases experimental Bug#47128, Bug#46895
[23 May 2011 17:27] Paul Dubois
Noted in 5.1.58, 5.5.14, 5.6.3 changelogs.

On some platforms, the Incorrect value: xxx for column yyy at row zzz
error produced by LOAD DATA INFILE could have an incorrect value of
zzz.    

CHANGESET - http://lists.mysql.com/commits/137629
[11 Jul 2011 22:47] [ name withheld ]
This test still fails for me in 5.5.14, when building on RHEL6 PPC64:

$ diff -c r/outfile_loaddata.result  r/outfile_loaddata.reject
*** r/outfile_loaddata.result   Tue Jun 21 12:42:40 2011
--- r/outfile_loaddata.reject   Mon Jul 11 16:27:44 2011
***************
*** 143,157 ****
  LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary FIELDS TERMINATED BY 'Ñ';
  Warnings:
  Warning       1638    Non-ASCII separator arguments are not fully supported
- Warning       1265    Data truncated for column 'a' at row 1
- Warning       1261    Row 1 doesn't contain data for all columns
- Warning       1261    Row 1 doesn't contain data for all columns
- Warning       1265    Data truncated for column 'a' at row 2
- Warning       1261    Row 2 doesn't contain data for all columns
- Warning       1261    Row 2 doesn't contain data for all columns
  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
  a     b       c
- 1     NULL    NULL
  1     ABC-ÐÐ  DEF-ÃÃÃ

  2     NULL    NULL
  SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' LINES STARTING BY 'Ñ';
--- 143,150 ----
***************
*** 181,187 ****
  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
  a     b       c
  1     ABC-ÐÐ  DEF-ÃÃÃ

- 1     ABC-ÐÐ  DEF-ÃÃÃ
                       ÃÅ 2
  2     NULL    NULL
  # Default (binary) charset:
  SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FROM t1;
--- 174,179 ----
[31 May 2014 14:14] Laurynas Biveinis
$ bzr log -r 4623
------------------------------------------------------------
revno: 4623
committer: Tor Didriksen <tor.didriksen@oracle.com>
branch nick: 5.5-merge
timestamp: Wed 2014-04-23 17:01:35 +0200
message:
  Backport from trunk:
    Bug#18396916 MAIN.OUTFILE_LOADDATA TEST FAILS ON ARM, AARCH64, PPC/PPC64
    
    The recorded results for the failing tests were wrong.
    They were introduced by the patch for
    Bug#30946 mysqldump silently ignores --default-character-set when used with --tab
    
    Correct results were returned for platforms where 'char' is implemented as unsigned.
    This was reported as 
    Bug#46895 Test "outfile_loaddata" fails (reproducible)
    Bug#11755168 46895: TEST "OUTFILE_LOADDATA" FAILS (REPRODUCIBLE)
    The patch for that bug fixed only parts of the problem,
    leaving the incorrect results in the .result file.
    
    Solution: use 'uchar' for field_terminator and line_terminator on all platforms.
    Also: remove some un-necessary casts, leaving the ones we actually need.