List:Internals« Previous MessageNext Message »
From:Yuan Wang Date:November 20 2009 10:14am
Subject:Re: Primary key is miss in row based replication.
View as plain text  
Hi Mats

I have a question. Why set all columns to be replicated? It will leads
to serious performance problem. For example, if a table has lobs, then
all lobs will be written to binlog and send to slave, even when those
lobs are not read or updated?

I think only primary key is needed by the slave to identify which row
to update. I think setting all columns is only needed when a table
don't have a primary key.

On Fri, Nov 20, 2009 at 6:06 PM, Mats Kindahl <mats@stripped> wrote:
> Hi Yuan,
>
> I just checked the code for 5.1.33, and it sets all columns to be replicated.
>
> If the hexdump shows that columns are missing, it is clearly a bug in the server
> (but I cannot easily tell where).
>
> If the hexdump shows that the columns are there, it is a bug in mysqlbinlog.
>
> Best wishes,
> Mats Kindahl
>
> Yuan Wang wrote:
>> Hi Mats
>>
>> I'm sorry that I made a mistake.
>>
>> To reproduce this senario, we do the following operations, where ntse
>> is the name of our engine.
>>
>> mysql> create table testrbr(a varchar(8) primary key, b varchar(8), c
>> int, d varchar(8)) engine = ntse;
>> Query OK, 0 rows affected (0.84 sec)
>>
>> mysql> insert into testrbr values('1', 'a', 1, 'aa');Query OK, 1 row
>> affected (0.42 sec)
>>
>> mysql> update testrbr set c = c + 1 where b = 'a';Query OK, 1 row
>> affected (38.78 sec)
>> Rows matched: 1  Changed: 1  Warnings: 0
>>
>> And with mysqlbinlog --base64-output=DECODE-ROWS --verbose, we can
>> print the content of the binlog. As follows
>>
>> /*!*/;
>> # at 1531
>> #091120 14:10:39 server id 1  end_log_pos 1599  Query  
> thread_id=1
>>  exec_time=39    error_code=0
>> SET TIMESTAMP=1258697439/*!*/;
>> BEGIN
>> /*!*/;
>> # at 1599
>> # at 1654
>> #091120 14:10:39 server id 1  end_log_pos 1654  Table_map:
>> `test`.`testrbr` mapped to number 17
>> #091120 14:10:39 server id 1  end_log_pos 1702  Update_rows: table id
>> 17 flags: STMT_END_F
>> ### UPDATE test.testrbr
>> ### WHERE
>> ###   @1=''
>> ###   @2='a'
>> ###   @3=1
>> ###   @4=''
>> ### SET
>> ###   @1=''
>> ###   @2='a'
>> ###   @3=2
>> ###   @4=''
>> # at 1702
>> #091120 14:10:39 server id 1  end_log_pos 1771  Query  
> thread_id=1
>>  exec_time=39    error_code=0
>> SET TIMESTAMP=1258697439/*!*/;
>> COMMIT
>>
>> You can see that primary key is missing.
>>
>> We also use gdb to confirm that primary key is not set in the read_set.
>>
>> We implements table_flags as follows.
>>
>> ulonglong ha_ntse::table_flags() const {
>>        ftrace(ts.mysql, tout << this);
>>        ulonglong flags = HA_NO_TRANSACTIONS;
>>        flags |= HA_PARTIAL_COLUMN_READ;
>>        flags |= HA_NULL_IN_KEY;
>>        flags |= HA_DUPLICATE_POS;
>>        flags &= ~HA_NO_PREFIX_CHAR_KEYS;
>>        flags |= HA_NO_AUTO_INCREMENT;
>>        flags |= HA_FILE_BASED;
>>        flags |= HA_BINLOG_ROW_CAPABLE;
>>        flags |= HA_REC_NOT_IN_SEQ;
>>        return flags;
>>
>> On Fri, Nov 20, 2009 at 4:41 PM, Yuan Wang <wangyuanzju@stripped> wrote:
>>> Hi Mats
>>>
>>> I had posted an example.
>>>
>>> Best wishes
>>> Yuan WANG
>>>
>>> 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
>
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