MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:He Zhenxing Date:March 26 2010 9:41am
Subject:Re: bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3001)
Bug#51851
View as plain text  
Hi Daogang,

The patch is still not OK, it will not fix the problem of
INSERT...SELECT, I think the problem should not be fixed in the
mysql_load() nor mysql_insert() code, it should be fixed in the
partition code.

Dao-Gang.Qu@stripped wrote:
> #At file:///home/daogangqu/mysql/bzrwork/bug51851/mysql-trunk-bugfixing/ based on
> revid:vvaintroub@stripped
> 
>  3001 Dao-Gang.Qu@stripped	2010-03-26
>       Bug #51851  Server with SBR locks mutex twice on LOAD DATA into partitioned
> MyISAM table
>       
>       In a multi-row insert statement like INSERT SELECT and LOAD DATA
>       where the number of candidate rows to insert is not known in
>       advance we must hold a lock/mutex for the whole statement if we
>       have statement based replication. Because the statement-based
>       binary log contains only the first generated value used by the
>       statement, and slaves assumes all other generated values used by
>       this statement were consecutive to this first one, we must
>       exclusively lock the generator until the statement is done. It
>       will be unlocked at the end of the statement by
>       ha_partition::release_auto_increment. During the process, the
>       thread tries to lock it when updating the MyISAM table share info.
>       So the mutex is locked twice on LOAD DATA into partitioned MyISAM
>       table.
>       
>       To fix the problem, release the auto increment after all the rows
>       are inserted and before the MyISAM table share info is updated.
> 
>     modified:
>       mysql-test/extra/rpl_tests/rpl_loaddata.test
>       mysql-test/suite/rpl/r/rpl_loaddata.result
>       sql/sql_load.cc
> === modified file 'mysql-test/extra/rpl_tests/rpl_loaddata.test'
> --- a/mysql-test/extra/rpl_tests/rpl_loaddata.test	2010-02-24 13:52:27 +0000
> +++ b/mysql-test/extra/rpl_tests/rpl_loaddata.test	2010-03-26 05:57:56 +0000
> @@ -264,4 +264,32 @@ SELECT * FROM t1;
>  DROP TABLE t1;
>  -- sync_slave_with_master
>  
> +#
> +# Bug#51851  Server with SBR locks mutex twice on LOAD DATA into partitioned MyISAM
> table
> +# This test verifies whether server with SBR locks mutex twice on LOAD DATA
> +# into partitioned MyISAM table.
> +#
> +
> +--connection master
> +perl;
> +open( INIT, ">init_file.txt");
> +print INIT "1234\n";
> +print INIT "5678\n";
> +print INIT "abcd\n";
> +close( INIT );
> +EOF
> +
> +USE test;
> +CREATE TABLE t1
> +(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name TINYBLOB NOT NULL,
> +modified TIMESTAMP DEFAULT '0000-00-00 00:00:00',
> +INDEX namelocs (name(255))) ENGINE = MyISAM
> +PARTITION BY HASH(id) PARTITIONS 2;
> +
> +LOAD DATA LOCAL INFILE 'init_file.txt' INTO TABLE t1 (name);
> +SELECT * FROM t1 ORDER BY id;
> +REMOVE_FILE init_file.txt;
> +DROP TABLE t1;
> +--source include/master-slave-end.inc
> +
>  # End of 4.1 tests
> 
> === modified file 'mysql-test/suite/rpl/r/rpl_loaddata.result'
> --- a/mysql-test/suite/rpl/r/rpl_loaddata.result	2010-01-19 16:36:14 +0000
> +++ b/mysql-test/suite/rpl/r/rpl_loaddata.result	2010-03-26 05:57:56 +0000
> @@ -132,3 +132,16 @@ Field 3	'Field 4'
>  'Field 5' 	'Field 6'
>  Field 6	 'Field 7'
>  DROP TABLE t1;
> +USE test;
> +CREATE TABLE t1
> +(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name TINYBLOB NOT NULL,
> +modified TIMESTAMP DEFAULT '0000-00-00 00:00:00',
> +INDEX namelocs (name(255))) ENGINE = MyISAM
> +PARTITION BY HASH(id) PARTITIONS 2;
> +LOAD DATA LOCAL INFILE 'init_file.txt' INTO TABLE t1 (name);
> +SELECT * FROM t1 ORDER BY id;
> +id	name	modified
> +1	1234	0000-00-00 00:00:00
> +2	5678	0000-00-00 00:00:00
> +3	abcd	0000-00-00 00:00:00
> +DROP TABLE t1;
> 
> === modified file 'sql/sql_load.cc'
> --- a/sql/sql_load.cc	2010-02-24 17:04:00 +0000
> +++ b/sql/sql_load.cc	2010-03-26 05:57:56 +0000
> @@ -171,6 +171,7 @@ int mysql_load(THD *thd,sql_exchange *ex
>    char *tdb= thd->db ? thd->db : db;		// Result is never null
>    ulong skip_lines= ex->skip_lines;
>    bool transactional_table;
> +  bool auto_increment_released= false;
>    THD::killed_state killed_status= THD::NOT_KILLED;
>    DBUG_ENTER("mysql_load");
>  
> @@ -475,6 +476,11 @@ int mysql_load(THD *thd,sql_exchange *ex
>        error= read_sep_field(thd, info, table_list, fields_vars,
>                              set_fields, set_values, read_info,
>  			    *enclosed, skip_lines, ignore);
> +
> +    /* Release the auto increment after all the rows are inserted */
> +    table->file->ha_release_auto_increment();
> +    auto_increment_released= true;
> +
>      if (thd->locked_tables_mode <= LTM_LOCK_TABLES &&
>          table->file->ha_end_bulk_insert() && !error)
>      {
> @@ -612,7 +618,8 @@ int mysql_load(THD *thd,sql_exchange *ex
>  err:
>    DBUG_ASSERT(transactional_table || !(info.copied || info.deleted) ||
>                thd->transaction.stmt.modified_non_trans_table);
> -  table->file->ha_release_auto_increment();
> +  if (!auto_increment_released)
> +    table->file->ha_release_auto_increment();
>    table->auto_increment_field_not_null= FALSE;
>    thd->abort_on_warning= 0;
>    DBUG_RETURN(error);
> 


Thread
bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3001) Bug#51851Dao-Gang.Qu26 Mar
  • Re: bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3001)Bug#51851He Zhenxing26 Mar
    • Re: bzr commit into mysql-trunk-bugfixing branch (Dao-Gang.Qu:3001)Bug#51851Alfranio Correia26 Mar