Sven Sandberg wrote:
> 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
>
Sven, the idea is not to wait for the uncommitted transaction
but to wait until the slave applies the committed transactions thus
reducing the probability of the sleep value being not enough.
Alfranio.
>
>> 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;
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>
>
>
