Bug #49628 corrupt table after legal SQL, LONGTEXT column
Submitted: 11 Dec 2009 16:23 Modified: 18 Jun 2010 1:02
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0,5.1,5.4,6.0 OS:Any
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: corruption

[11 Dec 2009 16:23] Matthias Leich
Description:
I get when running a RQG test with one thread 
based on the grammars 
   conf/replication-dml_sql.yy
   conf/replication-dml_data.yy
sometimes an output like
SELECT * FROM test . table1_myisam_int_autoinc
ORDER BY `col_varchar_257_utf8`
INTO OUTFILE '/tmp/gentest3598.tmp' 
failed: 1194 Table 'table1_myisam_int_autoinc'
        is marked as crashed and should be repaired

I will upload shrinked grammar files or
and test for MTR soon.

How to repeat:
Not available
[16 Dec 2009 15:57] Matthias Leich
Snip of my test protocol:
-------------------------
...
SELECT col_char_10 FROM t1;
ERROR HY000: Table 't1' is marked as crashed and should be repaired
REPAIR TABLE t1;
Table Op Msg_type Msg_text
test.t1  repair   info  Found block that points outside data file at 0
test.t1  repair   info  Found block that points outside data file at 28
test.t1  repair   warning  Number of rows changed from 2 to 1
    <== This loss of data because t1 contained
        two rows even before the last REPLACE INTO t1 ... 
test.t1  repair   status   OK

Please used the attached files,
store earth579kb.jpg somewhere,
adjust t/bug49628.test at the beginning so
that the storage place of earth579kb.jpg is correct
and run
   ./mysql-test-run  --mem --mysqld=--secure-file-priv= bug49628

I apologize for the size of the script and
the huge amount of tables and columns but
I already spent more than one day for the
simplification.

My environment:
---------------
- mysql-5.0-bugteam    ~ Nov 2009
  mysql-5.1-bugteam    ~ Nov 2009
  mysql-5.1-rep+3      ~ Dec 2009
  mysql-6.0-codebase-bugfixing ~ Dec 2009
- ./BUILD/compile-pentium64-debug-max
[16 Dec 2009 15:58] Matthias Leich
Test script

Attachment: bug49628.test (application/octet-stream, text), 2.55 KiB.

[16 Dec 2009 16:07] Matthias Leich
Improved version of my script

Attachment: bug49628.test (application/octet-stream, text), 2.60 KiB.

[16 Dec 2009 16:09] Matthias Leich
The improved version of my script does not an
additional file with data.
You just need to run
   ./mysql-test-run --mem --mysqld=--secure-file-priv= bug49628

The messages around REPAIR TABLE will be
EPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	info	Found block that points outside data file at 0
test.t1	repair	warning	Number of rows changed from 2 to 1
test.t1	repair	status	OK
[8 Feb 2010 9:40] 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/99573

3370 Sergey Vojtovich	2010-02-08
      BUG#49628 - corrupt table after legal SQL, LONGTEXT column
      
      Bulk REPLACE or bulk INSERT ... ON DUPLICATE KEY UPDATE may
      break dynamic record MyISAM table.
      
      The problem is limited to bulk REPLACE and INSERT ... ON
      DUPLICATE KEY UPDATE, because only these operations may
      be done via UPDATE internally and may request write cache.
      
      When flushing write cache, MyISAM may write remaining
      cached data at wrong position. Fixed by requesting write
      cache to seek to a correct position.
     @ mysql-test/r/myisam.result
        A test case for BUG#49628.
     @ mysql-test/t/myisam.test
        A test case for BUG#49628.
     @ storage/myisam/mi_dynrec.c
        delete_dynamic_record() may change data file position.
        IO cache must be notified as it may still have cached
        data, which has to be flushed later.
[8 Feb 2010 12:02] Ingo Strüwing
Approved. See email for a small suggestion.
[12 Feb 2010 12:30] 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/100121

3370 Sergey Vojtovich	2010-02-12
      BUG#49628 - corrupt table after legal SQL, LONGTEXT column
      
      Bulk REPLACE or bulk INSERT ... ON DUPLICATE KEY UPDATE may
      break dynamic record MyISAM table.
      
      The problem is limited to bulk REPLACE and INSERT ... ON
      DUPLICATE KEY UPDATE, because only these operations may
      be done via UPDATE internally and may request write cache.
      
      When flushing write cache, MyISAM may write remaining
      cached data at wrong position. Fixed by requesting write
      cache to seek to a correct position.
     @ mysql-test/r/myisam.result
        A test case for BUG#49628.
     @ mysql-test/t/myisam.test
        A test case for BUG#49628.
     @ storage/myisam/mi_dynrec.c
        delete_dynamic_record() may change data file position.
        IO cache must be notified as it may still have cached
        data, which has to be flushed later.
[1 Mar 2010 8:43] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joerg@mysql.com-20100212173307-ph563zr4wmoklgwd) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:35] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:40] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100221213311-xf5nyv391dsw9v6j) (merge vers: 5.5.2-m2) (pib:16)
[2 Mar 2010 14:45] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)
[8 Apr 2010 17:54] Paul Dubois
Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

For dynamic format MyISAM tables containing LONGTEXT columns, a bulk
INSERT ... ON DUPLICATE KEY UPDATE or bulk REPLACE could cause
corruption.
[17 Jun 2010 12:03] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:45] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:30] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)