alfranio correia wrote:
> After the thorough analysis made by Sven I have little to say.
> Note that using only sleep(s) to produce the desired behavior is
> not reliable. To increase the chances that everything will execute as
> desired
> I suggest to do what follows:
>
> 1 master: BEGIN
> 2 insert 10 rows into t3
> 3 INSERT INTO t2
> |
> save_master_pos
>
> | 4 INSERT INTO t1
> 5 COMMIT
> 6 slave: BEGIN
> 7 insert 100 rows into t4
> 8 SELECT * FROM t1 FOR UPDATE
> 9 START SLAVE
>
> | sync_slave_with_master|
Unfortunately, this does not work with the current implementation of
sync_slave_with_master. The saved master position is in the middle of a
transaction. sync_slave_with_master waits until the Exec_Master_Log_Pos
returned by SHOW SLAVE STATUS reaches the saved position. However,
Exec_Master_Log_Pos is only updated at transaction boundaries. So it
will not reach the saved position until the master transaction commits.
/Sven
>
> 10 sleep 2
> 11 SELECT * FROM t2 FOR UPDATE
> 12 COMMIT
>
>
> master: BEGIN
> INSERT INTO t2
>
> |save_master_pos
>
> | COMMIT
> slave: BEGIN
> SELECT * FROM t2 FOR UPDATE
> start slave;
> |
> sync_slave_with_master
>
> | sleep 10
> COMMIT
>
> /alfranio
>
>
> Sven Sandberg wrote:
>> Hi,
>>
>> OK, I think I managed to grasp what this test tries to do and why it
>> fails. See comment on the bug report.
>>
>> The patch tries to fix problem (4) of my comment. Please also fix
>> problems (1)-(3) and (5).
>>
>> I think the patch only works partially. It will wait until the IO thread
>> has copied everything from master, so that is good. However, it will not
>> synchronize the SQL thread. The problem is that Exec_Master_Log_Pos is
>> not updated inside a transaction. Exec_Master_Log_Pos is only updated
>> after commit.
>>
>> I think you could do the following:
>>
>> (1) synchronize the IO thread (as in your patch)
>>
>> (2) add a non-transactional update to another table (t5) in the master
>> transaction, just before it does 'insert into t2'. On slave, you can
>> sync by waiting until you see the row in t5. That will only sync up to
>> the statement before the deadlock. I don't think it's possible to sync
>> up to the actual deadlock in any easy way. The best I can think of is to
>> sleep 5 seconds after that.
>>
>> /Sven
>>
>> Serge Kozlov wrote:
>>
>>> #At bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-sea
>>>
>>> 2697 Serge Kozlov 2008-09-15
>>> Bug#37716. Remove real_sleep and and waiting proper values of
> Read_Master_Log_Pos and
>>> Exec_Master_Log_Pos on slave.
>>> modified:
>>> mysql-test/extra/rpl_tests/rpl_deadlock.test
>>>
>>> === modified file 'mysql-test/extra/rpl_tests/rpl_deadlock.test'
>>> --- a/mysql-test/extra/rpl_tests/rpl_deadlock.test 2008-02-03 09:00:49 +0000
>>> +++ b/mysql-test/extra/rpl_tests/rpl_deadlock.test 2008-09-15 07:46:42 +0000
>>> @@ -44,6 +44,7 @@ enable_query_log;
>>> insert into t1 values(1);
>>> commit;
>>> save_master_pos;
>>> +let $master_pos= query_get_value(show master status, Position, 1);
>>>
>>> connection slave;
>>> begin;
>>> @@ -59,12 +60,15 @@ enable_query_log;
>>> select * from t1 for update; # t1,t2 on local slave's
>>> start slave;
>>>
>>> -# bad option, todo: replicate a non-transactional t_sync with the
> transaction
>>> -# and use wait_until_rows_count macro below
>>> ---real_sleep 3 # hope that slave is blocked now
>>> -#let $count=11;
>>> -#let $table=t_sync;
>>> -#--include wait_until_rows_count.inc
>>> +# wait while slave read all events from master
>>> +let $slave_param= Read_Master_Log_Pos;
>>> +let $slave_param_value= $master_pos;
>>> +--source include/wait_for_slave_param.inc
>>> +
>>> +# wait while slave executed up to start of transaction
>>> +let $slave_param= Exec_Master_Log_Pos;
>>> +let $slave_param_value= 549;
>>> +--source include/wait_for_slave_param.inc
>>>
>>> select * from t2 for update /* dl */; # provoke deadlock, repl-ed should be
> victim
>>> commit;
>>>
>>>
>>>
>>
>>
>
--
Sven Sandberg, Software Engineer
MySQL AB, www.mysql.com
