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

The output with --hexdump is

/*!*/;
# at 1531
#091120 14:10:39 server id 1  end_log_pos 1599
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
#      5fb df 32 06 4b   02   01 00 00 00   44 00 00 00   3f 06 00 00   00 00
#      60e 01 00 00 00 27 00 00 00  04 00 00 1a 00 00 00 40 |................|
#      61e 00 00 01 00 00 00 00 00  00 00 00 06 03 73 74 64 |.............std|
#      62e 04 1c 00 1c 00 1c 00 74  65 73 74 00 42 45 47 49 |.......test.BEGI|
#      63e 4e  |N|
#       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
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
#      63f df 32 06 4b   13   01 00 00 00   37 00 00 00   76 06 00 00   00 00
#      652 11 00 00 00 00 00 00 00  04 74 65 73 74 00 07 74 |.........test..t|
#      662 65 73 74 72 62 72 00 04  0f 0f 03 0f 06 10 00 10 |estrbr..........|
#      672 00 10 00 0e  |....|
#       Table_map: `test`.`testrbr` mapped to number 17
#091120 14:10:39 server id 1  end_log_pos 1702
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
#      676 df 32 06 4b   18   01 00 00 00   30 00 00 00   a6 06 00 00   10 00
#      689 11 00 00 00 00 00 01 00  04 ff ff f0 00 01 61 01 |..............a.|
#      699 00 00 00 00 f0 00 01 61  02 00 00 00 00  |.......a.....|
#       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
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
#      6a6 df 32 06 4b   02   01 00 00 00   45 00 00 00   eb 06 00 00   00 00
#      6b9 01 00 00 00 27 00 00 00  04 00 00 1a 00 00 00 40 |................|
#      6c9 00 00 01 00 00 00 00 00  00 00 00 06 03 73 74 64 |.............std|
#      6d9 04 1c 00 1c 00 1c 00 74  65 73 74 00 43 4f 4d 4d |.......test.COMM|
#      6e9 49 54  |IT|
#       Query   thread_id=1     exec_time=39    error_code=0
SET TIMESTAMP=1258697439/*!*/;
COMMIT

On Fri, Nov 20, 2009 at 5:05 PM, Mats Kindahl <mats@stripped> wrote:
> Hi Yuan,
>
> Could you add --hexdump to the mysqlbinlog options and send the output (it will
> show the actual contents of the events, so we can see if it is a bug in
> mysqlbinlog or in the server).
>
> 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