List:Replication« Previous MessageNext Message »
From:Mats Kindahl Date:December 7 2010 7:23am
Subject:Re: Is there a way to associate BINLOG position with specific COMMIT
command
View as plain text  
Hi Niv,

If I understand your question right, you would like to do something like
this:

    BEGIN;
    .
    .
    .
    COMMIT;
    SELECT LAST_BINLOG_POS();

Unfortunately, there is no good and safe way to see where a transaction
or a statement were written in the binary log in a thread. The best
approach is to use SHOW MASTER STATUS to get a file and position, but
this command assumes that you do not write anything between the last
commit and the command. If you want to ensure that no other thread
writes anything between the commit and when you fetch the position, you
have to lock some table that you use to serialize all transactions.

As some people already pointed out, it is far easier to fetch the
position of the COMMIT from the binary log directly using mysqlbinlog,
which in addition does not incur an overhead.

Just my few cents,
Mats Kindahl

On 12/05/2010 02:51 AM, Niv Dalal wrote:
> Hi
> The question is how to correlate the commit command with the binlog
> position. How do you recommend to do that?
> Regards
> Niv
>
> On Dec 4, 2010, at 8:30 PM, Database System <database100@stripped> wrote:
>
>   
>> I don't understand then, why binlog position in SHOW SLAVE STATUS cannot give you
> answer on this question? (if a command didn't commit, it won't be logged into binlog).
>>
>> Lisa
>>
>> --- On Fri, 12/3/10, Niv Dalal <niv.dalal@stripped> wrote:
>>
>>     
>>> From: Niv Dalal <niv.dalal@stripped>
>>> Subject: Re: Is there a way to associate BINLOG position with specific COMMIT
> command
>>> To: "database100@stripped" <database100@stripped>
>>> Cc: "Wagner Bianchi" <wagnerbianchijr@stripped>, "Johan De Meersman"
> <vegivamp@stripped>, "Frank" <softwareengineer99@stripped>,
> "replication@stripped" <replication@stripped>
>>> Date: Friday, December 3, 2010, 8:33 PM
>>> Hi
>>> I didn't say it skipped commands in the middle, and frankly
>>> I hope
>>> this does not happen.
>>> I'm trying to understand the exact gap between the master
>>> and slave in
>>> order to close it in case of failover
>>> Thanks
>>> Niv
>>>
>>> On Dec 3, 2010, at 9:29 PM, Database System <database100@stripped>
>>> wrote:
>>>
>>>       
>>>> Are you saying the slave skipt command(s)? For
>>>>         
>>> example, the master executed 5 commands, and the slave
>>> didn't execute the third one? How did you find out? Is this
>>> your first time seen or happens multiple times? What made
>>> you think if command associates with a position can help
>>> you?
>>>       
>>>> Thanks,
>>>> Lisa
>>>>
>>>> --- On Wed, 12/1/10, Niv Dalal <niv.dalal@stripped>
>>>>         
>>> wrote:
>>>       
>>>>         
>>>>> From: Niv Dalal <niv.dalal@stripped>
>>>>> Subject: Re: Is there a way to associate BINLOG
>>>>>           
>>> position with specific COMMIT command
>>>       
>>>>> To: "Wagner Bianchi" <wagnerbianchijr@stripped>
>>>>> Cc: "Johan De Meersman" <vegivamp@stripped>,
>>>>>           
>>> "Frank" <softwareengineer99@stripped>,
>>> "replication@stripped"
>>> <replication@stripped>
>>>       
>>>>> Date: Wednesday, December 1, 2010, 2:48 PM
>>>>> Thank you for your suggestion.
>>>>> As it is working based on timely manner it can
>>>>>           
>>> help me
>>>       
>>>>> decide whether a
>>>>> command is or isn't on the slave in a matter of a
>>>>>           
>>> second.
>>>       
>>>>> However, it won't
>>>>> allow me to figure out the exact missing commands
>>>>>           
>>> in the
>>>       
>>>>> slave in order to
>>>>> make it fully functional in case of a failure.
>>>>>
>>>>> I really appreciate your responsiveness so far,
>>>>>           
>>> and I'll be
>>>       
>>>>> happy if you can
>>>>> point out to the option for truly understand
>>>>>           
>>> which
>>>       
>>>>> statements were executed
>>>>> on the slave and which are not, assuming I
>>>>>           
>>> backlogged all
>>>       
>>>>> the command in the
>>>>> application.
>>>>> Thanks
>>>>> Niv
>>>>>
>>>>> On Wed, Dec 1, 2010 at 3:23 PM, Wagner Bianchi
>>>>>           
>>> <wagnerbianchijr@stripped>wrote:
>>>       
>>>>>           
>>>>>> I apologize about my last note. I will
>>>>>>             
>>> recreate my
>>>       
>>>>> question to you.
>>>>>           
>>>>>> Isn't MaatKit tool mk-heartbeat fit the
>>>>>>             
>>> requirements?
>>>       
>>>>>> => http://www.maatkit.org/doc/mk-heartbeat.html
>>>>>>
>>>>>> Best regards.
>>>>>> --
>>>>>> WB
>>>>>>
>>>>>>
>>>>>> 2010/12/1 Wagner Bianchi <wagnerbianchijr@stripped>
>>>>>>
>>>>>> MaatKit tool isn't fit the requirements?
>>>>>>             
>>>>>>> => http://www.maatkit.org/doc/mk-heartbeat.html
>>>>>>>
>>>>>>> Best regards.
>>>>>>> --
>>>>>>> WB
>>>>>>>
>>>>>>> 2010/12/1 Niv Dalal <niv.dalal@stripped>
>>>>>>>
>>>>>>>               
>>>>>>>> 'DB Version control' may be a nice
>>>>>>>>                 
>>>>> implementation, any idea how to
>>>>>           
>>>>>>>> implement such, that will be reflected
>>>>>>>>                 
>>> in all
>>>       
>>>>> slaves and be updated after
>>>>>           
>>>>>>>> each commit on the master?
>>>>>>>>
>>>>>>>> Of course if there is something within
>>>>>>>>                 
>>> MySql
>>>       
>>>>> that allows me to correlate
>>>>>           
>>>>>>>> the information without additional
>>>>>>>>                 
>>> tables and
>>>       
>>>>> such, that may be a better
>>>>>           
>>>>>>>> solution, isn't it?
>>>>>>>> Thank you,
>>>>>>>> Niv
>>>>>>>>
>>>>>>>>
>>>>>>>> On Wed, Dec 1, 2010 at 2:17 PM, Johan
>>>>>>>>                 
>>> De
>>>       
>>>>> Meersman <vegivamp@stripped>wrote:
>>>>>           
>>>>>>>>                 
>>>>>>>>>
>>>>>>>>> On Wed, Dec 1, 2010 at 7:01 PM,
>>>>>>>>>                   
>>> Frank
>>>       
>>>>> <softwareengineer99@stripped>wrote:
>>>>>           
>>>>>>>>>                   
>>>>>>>>>> Hmmm, why are you trying to
>>>>>>>>>>                     
>>> verify
>>>       
>>>>> whether transaction reached a slave
>>>>>           
>>>>>>>>>> using COMMIT within bin logs?
>>>>>>>>>>                     
>>> Why not
>>>       
>>>>> just verify using the tables?
>>>>>           
>>>>>>>>>> Care to clarify?
>>>>>>>>>>
>>>>>>>>>>                     
>>>>>>>>> I guess that's possible, but you'd
>>>>>>>>>                   
>>> need
>>>       
>>>>> unique update sequences for
>>>>>           
>>>>>>>>> every single column of every
>>>>>>>>>                   
>>> single table
>>>       
>>>>> - quite a bit of overhead compared
>>>>>           
>>>>>>>>> to, say, a general revision number
>>>>>>>>>                   
>>> a la
>>>       
>>>>> version control.
>>>>>           
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Bier met grenadyn
>>>>>>>>> Is als mosterd by den wyn
>>>>>>>>> Sy die't drinkt, is eene kwezel
>>>>>>>>> Hy die't drinkt, is ras een ezel
>>>>>>>>>
>>>>>>>>>                   
>>>>>>>>
>>>>>>>>                 
>>>>>>>               
>>>>>>             
>>>>>           
>>>>
>>>>
>>>>         
>>> --
>>> MySQL Replication Mailing List
>>> For list archives: http://lists.mysql.com/replication
>>> To unsubscribe:    http://lists.mysql.com/replication?unsub=1
>>>
>>>
>>>       
>>
>>
>>     
>   

Thread
Is there a way to associate BINLOG position with specific COMMIT commandNiv Dalal1 Dec
  • Re: Is there a way to associate BINLOG position with specific COMMIT commandWagner Bianchi1 Dec
    • Re: Is there a way to associate BINLOG position with specific COMMIT commandJohan De Meersman1 Dec
      • Re: Is there a way to associate BINLOG position with specific COMMIT commandNiv Dalal1 Dec
        • Re: Is there a way to associate BINLOG position with specific COMMIT commandWagner Bianchi1 Dec
          • Re: Is there a way to associate BINLOG position with specific COMMIT commandNiv Dalal1 Dec
            • Re: Is there a way to associate BINLOG position with specific COMMIT commandFrank1 Dec
              • Re: Is there a way to associate BINLOG position with specific COMMIT commandJohan De Meersman1 Dec
                • Re: Is there a way to associate BINLOG position with specific COMMIT commandNiv Dalal1 Dec
                • Re: Is there a way to associate BINLOG position with specific COMMIT commandNiv Dalal1 Dec
                  • Re: Is there a way to associate BINLOG position with specific COMMIT commandWagner Bianchi1 Dec
                    • Re: Is there a way to associate BINLOG position with specific COMMIT commandWagner Bianchi1 Dec
                      • Re: Is there a way to associate BINLOG position with specific COMMIT commandNiv Dalal1 Dec
                        • Re: Is there a way to associate BINLOG position with specific COMMIT commandDatabase System4 Dec
                          • Re: Is there a way to associate BINLOG position with specific COMMIT commandNiv Dalal4 Dec
                            • Re: Is there a way to associate BINLOG position with specific COMMIT commandDatabase System5 Dec
                              • Re: Is there a way to associate BINLOG position with specific COMMIT commandNiv Dalal5 Dec
                                • Re: Is there a way to associate BINLOG position with specific COMMITcommandMats Kindahl7 Dec
                                  • Re: Is there a way to associate BINLOG position with specific COMMIT commandMARK CALLAGHAN7 Dec
                                    • Re: Is there a way to associate BINLOG position with specific COMMITcommandMats Kindahl7 Dec