Bug #36922 Synchronization between master and slave (replication) fails
Submitted: 23 May 2008 16:47
Reporter: Joerg Bruehe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.62 OS:HP/UX (HP-PA (all versions))
Assigned to: Assigned Account CPU Architecture:Any
Tags: hp/ux, replication, sync
Triage: Triaged: D2 (Serious)

[23 May 2008 16:47] Joerg Bruehe
Description:
This is a new effect in the 5.0.62 release build.

It occurs in all runs on a HP-UX (11.00 + 11.11) machine with a HP-PA CPU (32 + 64 bit), but not on HP-UX/IA64 or on any other platform.

Symptom is identical in all these runs, just sometimes port# and the length of the quoted last SQL statement differ.

rpl_sp_effects                 [ fail ]

=== SHOW MASTER STATUS ===
---- 1. ----
File    slave-bin.000001
Position        3613
Binlog_Do_DB
Binlog_Ignore_DB
==========================

=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State  Waiting for master to send event
Master_Host     127.0.0.1
Master_User     root
Master_Port     12000
Connect_Retry   1
Master_Log_File master-bin.000001
Read_Master_Log_Pos     3878
Relay_Log_File  slave-relay-bin.000003
Relay_Log_Pos   3641
Relay_Master_Log_File   master-bin.000001
Slave_IO_Running        Yes
Slave_SQL_Running       No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno      1064
Last_Error      Error 'You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version
  for the right syntax to use near '' at line 1' on query.
   Default database: 'test'. 
    Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
     SQL SECURITY DEFINER VIEW `v1` AS '
Skip_Counter    0
Exec_Master_Log_Pos     3503
Relay_Log_Space 4871
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed      No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master
=========================

mysqltest: At line 108: could not sync with master ('select master_pos_wait('master-bin.000001', 3878)' returned NULL)

The result from queries just before the failure was:
< snip >
slave:  6
slave:  7
slave:  8
slave:  10
slave:  11
slave:  3
drop procedure p1;
delete from t1;
delete from t2;
delete from t1;
insert into t2 values(1),(2);
create view v1 as select f1(a) from t2;
select * from v1;
f1(a)
2
3
select 'master:',a from t1;
master: a
master: 1
master: 2

More results from queries before failure can be found in /PATH/mysql-test/var/log/rpl_sp_effects.log

Stopping All Servers
The result from queries just before the failure was:
< snip >
slave:  6
slave:  7
slave:  8
slave:  10
slave:  11
slave:  3
drop procedure p1;
delete from t1;
delete from t2;
delete from t1;
insert into t2 values(1),(2);
create view v1 as select f1(a) from t2;
select * from v1;
f1(a)
2
3
select 'master:',a from t1;
master: a
master: 1
master: 2

More results from queries before failure can be found in /PATH/mysql-test/var/log/rpl_sp_effects.log

Stopping All Servers

How to repeat:
Found by running the test suite.
[23 May 2008 17:10] Andrei Elkin
can relate to Bug #32575 and Bug #36570.
[23 May 2008 17:36] Joerg Bruehe
Same build, same platforms, same test runs, other test - same problem:

rpl_drop_view                  [ fail ]

=== SHOW MASTER STATUS ===
---- 1. ----
File    slave-bin.000001
Position        665
Binlog_Do_DB
Binlog_Ignore_DB
==========================

=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State  Waiting for master to send event
Master_Host     127.0.0.1
Master_User     root
Master_Port     12000
Connect_Retry   1
Master_Log_File master-bin.000001
Read_Master_Log_Pos     1316
Relay_Log_File  slave-relay-bin.000003
Relay_Log_Pos   693
Relay_Master_Log_File   master-bin.000001
Slave_IO_Running        Yes
Slave_SQL_Running       No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno      1064
Last_Error      Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'test'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS '
Skip_Counter    0
Exec_Master_Log_Pos     555
Relay_Log_Space 2309
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed      No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master
=========================

mysqltest: At line 25: could not sync with master ('select master_pos_wait('master-bin.000001', 1316)' returned NULL)

The result from queries just before the failure was:
< snip >
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
drop table if exists t1, t2;
drop view if exists v1, v2, v3, not_exist_view;
create table t1 (a int);
create table t2 (b int);
create table t3 (c int);
create view v1 as select * from t1;
create view v2 as select * from t2;
create view v3 as select * from t3;
drop view not_exist_view;
ERROR 42S02: Unknown table 'not_exist_view'
drop view v1, not_exist_view;
ERROR 42S02: Unknown table 'not_exist_view'
select * from v1;
ERROR 42S02: Table 'test.v1' doesn't exist
drop view v2, v3;

More results from queries before failure can be found in /PATH/mysql-test/var/log/rpl_drop_view.log

mysqltest: At line NNN: could not sync with master ('select master_pos_wait('master-bin.000001', 1316)' returned NULL)

The result from queries just before the failure was:
< snip >
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
drop table if exists t1, t2;
drop view if exists v1, v2, v3, not_exist_view;
create table t1 (a int);
create table t2 (b int);
create table t3 (c int);
create view v1 as select * from t1;
create view v2 as select * from t2;
create view v3 as select * from t3;
drop view not_exist_view;
ERROR 42S02: Unknown table 'not_exist_view'
drop view v1, not_exist_view;
ERROR 42S02: Unknown table 'not_exist_view'
select * from v1;
ERROR 42S02: Table 'test.v1' doesn't exist
drop view v2, v3;

More results from queries before failure can be found in /PATH/mysql-test/var/log/rpl_drop_view.log
[23 May 2008 17:46] Joerg Bruehe
Similar problem in test "rpl_ddl" (same platforms).

Test requires InnoDB, so it is not run in "classic" configuration,
so it is only 18 failures and not 34.
[23 May 2008 20:42] Joerg Bruehe
Test "rpl_sp" has does not always fail on these platforms:

Machine         Product         Test     Result

hp3750          classic         normal   pass
hp3750          classic         ps       pass

hp3750          cluster         normal   pass
hp3750          cluster         ps       pass

hp3750          enterprise-gpl  normal   pass
hp3750          enterprise-gpl  ps       pass

hp3750          enterprise      normal   pass
hp3750          enterprise      ps       pass

hp3750-64bit    classic         debug    pass
hp3750-64bit    classic         normal         fail
hp3750-64bit    classic         ps             fail

hp3750-64bit    cluster         debug
hp3750-64bit    cluster         normal         fail
hp3750-64bit    cluster         ps

hp3750-64bit    enterprise-gpl  debug    pass
hp3750-64bit    enterprise-gpl  normal         fail
hp3750-64bit    enterprise-gpl  ps             fail

hp3750-64bit    enterprise      debug    pass
hp3750-64bit    enterprise      normal         fail
hp3750-64bit    enterprise      ps             fail

hpux11          classic         debug          fail
hpux11          classic         normal   pass
hpux11          classic         ps       pass

hpux11          cluster         debug          fail
hpux11          cluster         normal   pass
hpux11          cluster         ps       pass

hpux11          enterprise-gpl  debug          fail
hpux11          enterprise-gpl  normal   pass
hpux11          enterprise-gpl  ps       pass

hpux11          enterprise      debug          fail
hpux11          enterprise      normal   pass
hpux11          enterprise      ps       pass

hpux11-64bit    classic         debug    pass

hpux11-64bit    cluster         debug    pass

hpux11-64bit    enterprise-gpl  debug    pass

hpux11-64bit    enterprise      debug    pass

And if it fails, it looks like this:

=====
rpl_sp                         [ fail ]

=== SHOW MASTER STATUS ===
---- 1. ----
File    slave-bin.000001
Position        7094
Binlog_Do_DB
Binlog_Ignore_DB
==========================

=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State  Queueing master event to the relay log
Master_Host     127.0.0.1
Master_User     root
Master_Port     12000
Connect_Retry   1
Master_Log_File master-bin.000001
Read_Master_Log_Pos     7485
Relay_Log_File  slave-relay-bin.000003
Relay_Log_Pos   7122
Relay_Master_Log_File   master-bin.000001
Slave_IO_Running        Yes
Slave_SQL_Running       No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno      1064
Last_Error      Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
 for the right syntax to use near '' at line 1' on query. Default database: 'test'. Query: 'CREATE ALGORITHM=UNDEFINED DEFI
NER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS '
Skip_Counter    0
Exec_Master_Log_Pos     6984
Relay_Log_Space 8384
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed      No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master
=========================

mysqltest: At line 399: could not sync with master ('select master_pos_wait('master-bin.000001', 7485)' returned NULL)

The result from queries just before the failure was:
< snip >
use test;
use test;
drop function if exists f1;
create function f1() returns int reads sql data
begin
declare var integer;
declare c cursor for select a from v1;
open c;
fetch c into var;
close c;
return var;
end|
create view v1 as select 1 as a;
create table t1 (a int);
insert into t1 (a) values (f1());
select * from t1;
a
1
drop view v1;
drop function f1;

More results from queries before failure can be found in /PATH/mysql-test/var/log/rpl_sp.log
=====
[14 Jul 2008 15:01] Jonathan Perkin
Happens in 5.0.64 too, where the platform has been changed to HPUX 11.31
[28 Oct 2008 15:24] Joerg Bruehe
Symptom occurs again in 5.0.72.

It also happens on the host in Trondheim which we didn't use before.