Bug #55458 Partitioned MyISAM table gets crashed by multi-table update
Submitted: 21 Jul 2010 21:30 Modified: 16 Nov 2010 13:47
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1, 5.5 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: corruption
Triage: Triaged: D2 (Serious)

[21 Jul 2010 21:30] Elena Stepanova
Description:
Two tables, non-partitioned T1 and partitioned by hash T2, contain ~3000 records each. While executing update like
UPDATE T1, T2 SET T1.f1 = <value1>, T2.f2 = <value2> WHERE T1.f3 = <value3> AND T2.f4 = <value4>
server throws ER_CRASHED_ON_USAGE for T2.

If UPDATE IGNORE is executed instead of the regular UPDATE, release version gives the same ER_CRASHED_ON_USAGE as a warning, along with ER_UNKNOWN_ERROR, and debug version asserts with Protocol::end_statement(): Assertion `0', as in bug#55421 (the bug has a different test case though).

Observed on earlier 5.1 releases as well (tried 5.1.39).

How to repeat:
Execute the attached SQL via MySQL client or as an MTR test.
The script creates a schema and two tables, t_celosia_ddl_no_partitions and t_celosia_ddl_partitions, populates the tables, and runs an update of the form given in the description.
[21 Jul 2010 21:33] Elena Stepanova
SQL script to create and populate tables and run the query

Attachment: bug55458.sql (application/unknown, text), 365.07 KiB.

[22 Jul 2010 6:01] Shane Bester
check table reports ok after the crashed message so is this a false message or a genuine corruption?
[7 Aug 2010 21:20] Elena Stepanova
# Simplified test case:

--disable_warnings
DROP TABLE IF EXISTS t_no_partitions, t_partitions;
--enable_warnings

CREATE TABLE `t_no_partitions` (
  `id` int NOT NULL,
  `user_num` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO `t_no_partitions` VALUES (1,8601);
INSERT INTO `t_no_partitions` VALUES (2,8601);
INSERT INTO `t_no_partitions` VALUES (3,8601);
INSERT INTO `t_no_partitions` VALUES (4,8601);
CREATE TABLE `t_partitions` (
  `id` int(11) NOT NULL,
  `user_num` int DEFAULT NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=latin1
PARTITION BY HASH (id)
PARTITIONS 2;
INSERT INTO `t_partitions` VALUES (1,8601,'John');
INSERT INTO `t_partitions` VALUES (2,8601,'JS');
INSERT INTO `t_partitions` VALUES (3,8601,'John S');

UPDATE t_no_partitions t1, t_partitions t2
  SET t2.name = 'John Smith'
  WHERE t1.user_num = t2.user_num;

DROP TABLE t_no_partitions, t_partitions;
--exit

# end of test case
[7 Aug 2010 21:23] Elena Stepanova
# Fragment of trace 

>ha_partition::rnd_next
| >mi_scan
| <mi_scan
| >_mi_read_rnd_dynamic_record
| | >_mi_read_cache
| | | >_my_b_read
| | | | >my_seek
| | | | | my: fd: 58 Pos: 40  Whence: 0  MyFlags: 0
| | | | <my_seek
| | | <_my_b_read
| | | error: Error 137 reading next-multi-part block (Got 0 bytes)
| | <_mi_read_cache
| | >_mi_writeinfo
| | | info: operation: 0  tot_locks: 1
| | <_mi_writeinfo
| <_mi_read_rnd_dynamic_record
<ha_partition::rnd_next
>ha_partition::print_error
| enter: error: 127
| >handler::print_error
| | enter: error: 127
| | >my_error
| | | my: nr: 1194  MyFlags: 0  errno: 0
| | | >my_message_sql
| | | | error: error: 1194  message: 'Table 't_partitions' is marked as crashed and should be repaired'
| | | | >THD::raise_condition
| | | | | >set_error_status
| | | | | <set_error_status
[9 Aug 2010 16:28] Mattias Jonsson
Problem seems to be HA_EXTRA_PREPARE_FOR_UPDATE which is only set at start of scanning, and not when it changes partitions (i.e. later overridden by HA_EXTRA_CACHE).
[10 Aug 2010 8:44] 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/115373

3480 Mattias Jonsson	2010-08-10
      Bug#55458: Partitioned MyISAM table gets crashed by multi-table update
      
      Problem was that the handler call ::extra(HA_EXTRA_CACHE) was cached
      but the ::extra(HA_EXTRA_PREPARE_FOR_UPDATE) was not.
      
      Solution was to also cache the other call and forward it when moving
      to a new partition to scan.
     @ mysql-test/r/partition.result
        test result
     @ mysql-test/t/partition.test
        New test from bug report.
     @ sql/ha_partition.cc
        cache the HA_EXTRA_PREPARE_FOR_UPDATE just like HA_EXTRA_CACHE.
     @ sql/ha_partition.h
        Added cache flag for HA_EXTRA_PREPARE_FOR_UPDATE
[7 Sep 2010 16:00] 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/117717

3481 Mattias Jonsson	2010-09-07
      Bug#55458: Partitioned MyISAM table gets crashed by multi-table update
      
      Updated according to reviewers comments.
[14 Sep 2010 8:58] Mattias Jonsson
pushed to mysql-5.1-bugteam and mysql-5.5-merge
[28 Sep 2010 15:40] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)
[28 Sep 2010 15:43] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)
[28 Sep 2010 15:45] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)
[30 Sep 2010 8:36] Jon Stephens
Documented in the 5.5.7 and 5.6.1 changelogs as follows:

        Multi-table UPDATE statements involving a partitioned MyISAM
        table could cause this table to become corrupted. Not all tables
        affected by the UPDATE needed to be partitioned for this issue
        to be observed.

Waiting for 5.1 merge to close -> NM.
[30 Sep 2010 13:33] 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/119542

3482 Mattias Jonsson	2010-09-30
      Bug#55458: Partitioned MyISAM table gets crashed by multi-table update
      Bug#57113: ha_partition::extra(ha_extra_function):
                  Assertion `m_extra_cache' failed
      
      Fix for bug#55458 included DBUG_ASSERTS causing
      debug builds of the server to crash on
      another multi-table update.
      
      Removed the asserts since they where wrong.
     @ mysql-test/r/partition.result
        updated result
     @ mysql-test/t/partition.test
        Added test for bug#57113
     @ sql/ha_partition.cc
        Removed the assert for m_extra_cache when
        ::extra(HA_PREPARE_FOR_UPDATE) was called.
[30 Sep 2010 13:59] 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/119550

3482 Mattias Jonsson	2010-09-30
      Bug#55458: Partitioned MyISAM table gets crashed by multi-table update
      Bug#57113: ha_partition::extra(ha_extra_function):
                  Assertion `m_extra_cache' failed
      
      Fix for bug#55458 included DBUG_ASSERTS causing
      debug builds of the server to crash on
      another multi-table update.
      
      Removed the asserts since they where wrong.
      (updated after testing the patch in 5.5).
     @ mysql-test/r/partition.result
        updated result
     @ mysql-test/t/partition.test
        Added test for bug#57113
     @ sql/ha_partition.cc
        Removed the assert for m_extra_cache when
        ::extra(HA_PREPARE_FOR_UPDATE) was called.
[30 Sep 2010 14: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/119553

3211 Mattias Jonsson	2010-09-30 [merge]
      Manual merge of post push fix for bug#55458 (bug#57113)
      into mysql-5.5-bugteam
[1 Oct 2010 19:44] Mattias Jonsson
pushed update (bad DBUG_ASSERT, see bug#57113) into mysql-5.1-bugteam, mysql-5.5-bugteam and mysql-trunk-merge.
[4 Oct 2010 12:59] Jon Stephens
Returned to NM status; already documented in 5.5, still waiting for merge to 5.1.
[6 Oct 2010 12:36] Jon Stephens
Also docum ented in the 5.1.52 changelog. Closed.
[1 Nov 2010 19:02] Bugs System
Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)
[9 Nov 2010 19:43] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:11] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:34] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[16 Nov 2010 13:47] Jon Stephens
No new changelog entries required. Returning to Closed state.