| 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: | |
| 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 | ||
[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.
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.