List:Internals« Previous MessageNext Message »
From:Mats Kindahl Date:November 26 2009 9:15am
Subject:Re: Primary key is miss in row based replication.
View as plain text  

Yuan Wang wrote:
> Thanks. I  think I will add primary keys in my engine automatically
> for UPDATE and DELETE.
> 
> Sorry for one more question. My engine just fill the columns marked in
> read_set (and primary keys as above) to before_record, and leaves
> others untouched. I though that these unneeded columns can left as
> garbage in before_record because MySQL will not use them. Now I know
> this is not true, they will be written out into the binlog. However I
> still won't fill these unneeded columns for perfmance reasons. So,
> should I mark these unfilled columns to be NULL?

Unfortunately, the support that Falcon requested is what is required to not have
to fill in the columns that are not in the read_set, so I think you have to fill
 in all columns *when the binlog is enabled.*

We are working on fixing this interface, but right now it is assumed that all
the columns are filled in with the right values for the row.

Stupid, I know, but we are working on fixing this.

Best wishes,
Mats Kindahl

> 
> On Thu, Nov 26, 2009 at 3:47 AM, Mats Kindahl <mats@stripped> wrote:
>>
>> Yuan Wang wrote:
>>> Hi Mats
>>>
>>> Sorry but I can't catch your meaning. Calling bitmap_set_all() in
>>> binlog_log_row() has nothing to do with read_set, and the engine read
>>> just the columns needed in read_set. So calling bitmap_set_all()  will
>>> write all columns in before_record. However, those columns that are
>>> not denoted in read_set is already missing in before_record, for the
>>> engine just doesn't read them. So you will write out some NULLs or
>>> garbage.
>>>
>>> Is that right?
>> Aha, I understand. That is the same problem as what Falcon was having, that it
>> just read the columns in the read_set but all columns were replicated.
>>
>> I read the "primary key values not included" as that they were missing entirely,
>> not that the actual values for the primary key was missing.
>>
>> Otherwise, you are entirely correct.
>>
>> As Kristian pointed out, the support was mainly added to make NDB work properly,
>> but the semantics is not very clear or precise and no real attempt at trying to
>> make it work for any engine was made when the sets were introduced.
>>
>> We are working on specifying a precise semantics to allow engines that ignore
>> the read_set and write_set (such as MyISAM) cooperate with engines that use them
>> (such as NDB) and at the same time allow row-based replication to work
>> correctly. This is the worklog I pointed out.
>>
>> Row-based replication is designed to allow partial rows to be replicated, but
>> this support is currently "plugged" at server level and the full row is
>> replicated regardless of what the read_set and write_set are.
>>
>> Best wishes,
>> Mats Kindahl
>>
>>> On Wed, Nov 25, 2009 at 6:45 AM, Mats Kindahl <mats@stripped> wrote:
>>>> Hi Yuan!
>>>>
>>>> Sorry for the late reply, I'm trying to juggle too many things
> currently.
>>>>
>>>> It is good that you managed to solve your problem, but there is still
> something
>>>> that seems strange that I would like to make you aware of.
>>>>
>>>> Yuan Wang wrote:
>>>>> Hi Mats
>>>>>
>>>>> I have checked the code of 5.1 (current head version of 5.1 branch
> in
>>>>> launchpad), and it don't seem to fill missing attributes
> automatically
>>>>> when RBR is used.
>>>>>
>>>>> The patch (http://lists.mysql.com/commits/40151) of bug 33055
>>>>> (http://bugs.mysql.com/bug.php?id=33055) does the following change
> to
>>>>> st_table::mark_columns_needed_for_delete().
>>>>>
>>>>> -  if (file->ha_table_flags() &
> HA_PRIMARY_KEY_REQUIRED_FOR_DELETE)
>>>>> +  if (file->ha_table_flags() &
> HA_PRIMARY_KEY_REQUIRED_FOR_DELETE ||
>>>>> +      mysql_bin_log.is_open() && in_use &&
>>>>> +      in_use->current_stmt_binlog_row_based)
>>>>>
>>>>> This patch has been pushed into 6.0. However,
>>>>> st_table::mar_columns_needed_for_update don't have this change.
>>>> That is correct, and the reason is because there were a patch pushed to
> 6.0 to
>>>> make Falcon work correctly that made row-based replication only replicate
> the
>>>> columns that are explicitly marked, but no other.
>>>>
>>>> However, for 5.1, the full row is written to the binary log regardless of
> what
>>>> columns that are marked in the write_set.
>>>>
>>>> You can see this in the binlog_log_row function, where the columns are
> set in
>>>> the following code (I marked the line):
>>>>
>>>>    /*
>>>>      If there are no table maps written to the binary log, this is
>>>>      the first row handled in this statement. In that case, we need
>>>>      to write table maps for all locked tables to the binary log.
>>>>    */
>>>>    if (likely(!(error= bitmap_init(&cols,
>>>>                                    use_bitbuf ? bitbuf : NULL,
>>>>                                    (n_fields + 7) & ~7UL,
>>>>                                    FALSE))))
>>>>    {
>>>>>>>   bitmap_set_all(&cols);
>>>>      if (likely(!(error= write_locked_table_maps(thd))))
>>>>        error= (*log_func)(thd, table,
> table->file->has_transactions(),
>>>>                           &cols, table->s->fields,
>>>>                           before_record, after_record);
>>>>
>>>>> It seems that if my storage engine mark the
>>>>> HA_PRIMARY_KEY_REQUIRED_FOR_DELETE flag in table_flags, then the
>>>>> server will add primary key to binlog automatically, which is just
>>>>> what we need.
>>>> Yes, that is correct, but I am still surprised that it affects
> replication, for
>>>> the reason given above.
>>>>
>>>> Just my few cents,
>>>> Mats Kindahl
>>>>
>>>>> On Fri, Nov 20, 2009 at 4:04 PM, Mats Kindahl <mats@stripped>
> wrote:
>>>>>> Hi Yuan,
>>>>>>
>>>>>> In 5.1, the full row is sent over to the slave, so I am a little
> surprised that
>>>>>> there is anything missing at all.
>>>>>>
>>>>>> Do you have an example I can look at?
>>>>>>
>>>>>> Best wishes,
>>>>>> Mats Kindahl
>>>>>>
>>>>>> Yuan Wang wrote:
>>>>>>> We are developed a storage engine using row based
> replication. During
>>>>>>> the test we found that primary key values will not be
> included in the
>>>>>>> binlog event. So the slave is impossible to identify which
> row to
>>>>>>> update.
>>>>>>>
>>>>>>> Is this a bug or is there a flag to tell MySQL to include
> primary key?
>>>>>>> We are based on 5.1.33 currently.
>>>>>>>
>>>>>> --
>>>>>> Mats Kindahl
>>>>>> Senior Software Engineer
>>>>>> Database Technology Group
>>>>>> Sun Microsystems
>>>>>>
>>>> --
>>>> Mats Kindahl
>>>> Senior Software Engineer
>>>> Database Technology Group
>>>> Sun Microsystems
>>>>
>>> --
>>> MySQL Internals Mailing List
>>> For list archives: http://lists.mysql.com/internals
>>> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
>>>
>> --
>> Mats Kindahl
>> Senior Software Engineer
>> Database Technology Group
>> Sun Microsystems
>>
> 
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
> 

-- 
Mats Kindahl
Senior Software Engineer
Database Technology Group
Sun Microsystems
Thread
Primary key is miss in row based replication.Yuan Wang20 Nov
  • Re: Primary key is miss in row based replication.Venu Kalyan20 Nov
  • Re: Primary key is miss in row based replication.Venu Kalyan20 Nov
  • Re: Primary key is miss in row based replication.Mats Kindahl20 Nov
    • Re: Primary key is miss in row based replication.Yuan Wang21 Nov
      • Re: Primary key is miss in row based replication.Mats Kindahl24 Nov
        • Re: Primary key is miss in row based replication.Yuan Wang25 Nov
          • Re: Primary key is miss in row based replication.Kristian Nielsen25 Nov
          • Re: Primary key is miss in row based replication.Mats Kindahl25 Nov
            • Re: Primary key is miss in row based replication.Yuan Wang26 Nov
              • Re: Primary key is miss in row based replication.Kristian Nielsen26 Nov
                • Re: Primary key is miss in row based replication.Mats Kindahl26 Nov
              • Re: Primary key is miss in row based replication.Mats Kindahl26 Nov
Re: Primary key is miss in row based replication.Yuan Wang20 Nov
  • Re: Primary key is miss in row based replication.Mats Kindahl20 Nov
    • Re: Primary key is miss in row based replication.Yuan Wang20 Nov
  • Re: Primary key is miss in row based replication.Mats Kindahl20 Nov
    • Re: Primary key is miss in row based replication.Yuan Wang20 Nov
      • Re: Primary key is miss in row based replication.Mats Kindahl20 Nov