Will submit new patch with better testcase.
Satya B wrote:
> #At file:///home/satya/WORK/mysql-5.1-bugteam-40827/ based on
> revid:alfranio.correia@stripped
>
> 2751 Satya B 2009-02-25
> BUG#40827 - Killing insert-select to MyISAM can cause table corruption
>
> Killing insert-select statement on MyISAM corrupts the table.
>
> Killing the insert-select statement corrupts the MyISAM table only
> when the destination table is empty and when it has indexes. When
> we bulk insert huge data and if the destination table is empty we
> disable the indexes for fast inserts, data is then inserted and
> indexes are re-enabled after bulk_insert operation
>
> Killing the query, aborts the repair table operation during enable indexes
> phase leading to table corruption.
>
> We now truncate the table when we detect that enable indexes is
> killed for bulk insert query.As we have an empty table before the operation,
> we can fix by truncating the table.
> modified:
> mysql-test/r/myisam.result
> mysql-test/t/myisam.test
> storage/myisam/ha_myisam.cc
>
> per-file messages:
> mysql-test/r/myisam.result
> Modified result file for testcase added for BUG#40827
> mysql-test/t/myisam.test
> Modified myisam.test for BUG#40827 to add testcase which tests if
> myisam table is ok when insert select query is killed.
> storage/myisam/ha_myisam.cc
> Fixed end_bulk_insert() method to truncate the table when we detect enable
> index operation is killed.
> === modified file 'mysql-test/r/myisam.result'
> --- a/mysql-test/r/myisam.result 2008-08-26 13:53:22 +0000
> +++ b/mysql-test/r/myisam.result 2009-02-25 07:37:13 +0000
> @@ -2226,4 +2226,47 @@ Key Start Len Index Type
> 1 2 30 multip. varchar
> 2 33 30 multip. char NULL
> DROP TABLE t1;
> +CREATE TABLE `t1` (
> +`id` BIGINT(20) ,
> +`id1` BIGINT(20) AUTO_INCREMENT,
> +KEY(id1), KEY(id)
> +) ENGINE=MyISAM;
> +CREATE TABLE `t2` (
> +`id` BIGINT(20) ,
> +`id1` BIGINT(20) AUTO_INCREMENT,
> +KEY (id1), KEY(id)
> +) ENGINE=MyISAM;
> +INSERT INTO t2 (id) VALUES (123);
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +INSERT INTO t2 (id) SELECT id FROM t2;
> +# Switch to insert Connection
> +# Send insert data
> +INSERT INTO t1(id) SELECT id FROM t2;
> +# Switch to default Connection
> +# Wait for insert data to reach the debug point
> +SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST
> +WHERE STATE = 'Sending data' AND
> +INFO = "INSERT INTO t1(id) SELECT id FROM t2"
> +INTO @thread_id;
> +KILL QUERY @thread_id;
> +CHECK TABLE t1;
> +Table Op Msg_type Msg_text
> +test.t1 check status OK
> +DROP TABLE t1,t2;
> End of 5.1 tests
>
> === modified file 'mysql-test/t/myisam.test'
> --- a/mysql-test/t/myisam.test 2008-08-26 13:53:22 +0000
> +++ b/mysql-test/t/myisam.test 2009-02-25 07:37:13 +0000
> @@ -1476,6 +1476,55 @@ CREATE TABLE t1 (
> --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
> --exec $MYISAMCHK -d $MYSQLTEST_VARDIR/master-data/test/t1
> DROP TABLE t1;
> +#
> +# BUG#40827 - Killing insert-select to MyISAM can cause table corruption
> +#
> +CONNECT (insertConn, localhost, root,,);
> +
> +CREATE TABLE `t1` (
> +`id` BIGINT(20) ,
> +`id1` BIGINT(20) AUTO_INCREMENT,
> + KEY(id1), KEY(id)
> +) ENGINE=MyISAM;
> +
> +CREATE TABLE `t2` (
> +`id` BIGINT(20) ,
> +`id1` BIGINT(20) AUTO_INCREMENT,
> + KEY (id1), KEY(id)
> +) ENGINE=MyISAM;
> +
> + INSERT INTO t2 (id) VALUES (123);
> +
> +let $i = 18;
> +while ($i > 0)
> +{
> + INSERT INTO t2 (id) SELECT id FROM t2;
> + dec $i;
> +}
> +
> +--echo # Switch to insert Connection
> +CONNECTION insertConn;
> +--echo # Send insert data
> +SEND INSERT INTO t1(id) SELECT id FROM t2;
> +
> +--echo # Switch to default Connection
> +CONNECTION default;
> +--echo # Wait for insert data to reach the debug point
> +
> +let $wait_condition=
> + SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
> + WHERE STATE = "Sending data" AND
> + INFO = "INSERT INTO t1(id) SELECT id FROM t2";
> +--source include/wait_condition.inc
> +
> +SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST
> +WHERE STATE = 'Sending data' AND
> +INFO = "INSERT INTO t1(id) SELECT id FROM t2"
> +INTO @thread_id;
> +
> +KILL QUERY @thread_id;
> +CHECK TABLE t1;
> +DROP TABLE t1,t2;
>
> --echo End of 5.1 tests
>
>
> === modified file 'storage/myisam/ha_myisam.cc'
> --- a/storage/myisam/ha_myisam.cc 2008-03-28 10:14:27 +0000
> +++ b/storage/myisam/ha_myisam.cc 2009-02-25 07:37:13 +0000
> @@ -1499,6 +1499,9 @@ void ha_myisam::start_bulk_insert(ha_row
> /*
> Only disable old index if the table was empty and we are inserting
> a lot of rows.
> + Note that in end_bulk_insert() we may truncate the table if
> + enable_indexes() failed, thus it's essential that indexes are
> + disabled ONLY for an empty table.
> We should not do this for only a few rows as this is slower and
> we don't want to update the key statistics based of only a few rows.
> */
> @@ -1532,8 +1535,27 @@ int ha_myisam::end_bulk_insert()
> {
> mi_end_bulk_insert(file);
> int err=mi_extra(file, HA_EXTRA_NO_CACHE, 0);
> - return err ? err : can_enable_indexes ?
> - enable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE) : 0;
> + if (!err)
> + {
> + if (can_enable_indexes)
> + {
> + /*
> + Truncate the table when enable index operation is killed.
> + After truncating the table we don't need to enable the
> + indexes, because the last repair operation is aborted after
> + setting the indexes as active and trying to recreate them.
> + */
> +
> + if (((err= enable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE)) != 0) &&
> + current_thd->killed)
> + {
> + delete_all_rows();
> + /* not crashed, despite being killed during repair */
> + file->s->state.changed&=
> ~(STATE_CRASHED|STATE_CRASHED_ON_REPAIR);
> + }
> + }
> + }
> + return err;
> }
>
>
>
>
>
