Re: Is there a way to associate BINLOG position with specific COMMIT command

[Mats Kindahl]

On 12/07/2010 03:38 PM, MARK CALLAGHAN wrote:
> On Mon, Dec 6, 2010 at 11:23 PM, Mats Kindahl <mats.kindahl@stripped> wrote:
>   
>> 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.
>>     
> Mats - I assume you mean no good way without modifying the server and
> that I could add the SQL command COMMIT_AND_RETURN_BINLOG_POS.
>   

Yes, of course. I assumed that Niv was not prepared to patch the server
to solve his problem. :)

If he's prepared to do that, it is probably better to introduce a
function or session variable to hold the position of the last committed
statement/transaction since that can be used from SQL. A
COMMIT_AND_RETURN_BINLOG_POS would *require* the use of a client to read
the result from that command. There is even a worklog for that: WL#4047.

Just my few cents,
Mats Kindahl