MySQL Bugs: #55458: Partitioned MyISAM table gets crashed by multi-table update

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

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.

SQL script to create and populate tables and run the query

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

check table reports ok after the crashed message so is this a false message or a genuine corruption?

# 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

# 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

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).

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

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.

pushed to mysql-5.1-bugteam and mysql-5.5-merge

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

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)

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.

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.

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.

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

pushed update (bad DBUG_ASSERT, see bug#57113) into mysql-5.1-bugteam, mysql-5.5-bugteam and mysql-trunk-merge.

Returned to NM status; already documented in 5.5, still waiting for merge to 5.1.

Also docum ented in the 5.1.52 changelog. Closed.

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)

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)

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)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

No new changelog entries required. Returning to Closed state.