MySQL Bugs: #49628: corrupt table after legal SQL, LONGTEXT column

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

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

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

Test script

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

Improved version of my script

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

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

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.

Approved. See email for a small suggestion.

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.

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)

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)

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)

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.

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)

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)

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)