MySQL Lists are EOL. Please join:

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

He Zhenxing wrote:
> 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.


I agree with Jasonh here.
Although your latest patch may fix the problem in MyIsam because locks are held on tables,
it will break
replication when other engines and concurrent transactions are involved.

I think this is not a replication bug.

Cheers.

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