MySQL Bugs: #4838: DDL statements for views are not written to the binary log

Bug #4838	DDL statements for views are not written to the binary log
Submitted:	31 Jul 2004 18:12	Modified:	25 Feb 2005 19:56
Reporter:	Georg Richter
Status:	Closed
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.1	OS:	Linux (Linux)
Assigned to:	Bugs System	Target Version:

Description:
DDL statements for views are not written to the binary log 

How to repeat:
5.0.1-alpha-debug-log 
[16:01] root@test> create table t1 (a int); 
Query OK, 0 rows affected (0.05 sec) 
 
5.0.1-alpha-debug-log 
[16:01] root@test> insert into t1 values (1); 
Query OK, 1 row affected (0.00 sec) 
 
5.0.1-alpha-debug-log 
[16:01] root@test> create view v1 as select a from t1; 
Query OK, 0 rows affected (0.00 sec) 
 
5.0.1-alpha-debug-log 
[16:01] root@test> insert into v1 values (2); 
Query OK, 1 row affected (0.00 sec) 
 
 
beethoven:/data/mysql-5.0 # mysqlbinlog beethoven-bin.000001 
.... 
#040731 16:01:13 server id 2  end_log_pos 1088  Query   thread_id=3     exec_time=0     
error_code=0 
SET TIMESTAMP=1091282473; 
create table t1 (a int); 
# at 1088 
#040731 16:01:19 server id 2  end_log_pos 1170  Query   thread_id=3     exec_time=0     
error_code=0 
SET TIMESTAMP=1091282479; 
insert into t1 values (1); 
# at 1170 
#040731 16:01:35 server id 2  end_log_pos 1252  Query   thread_id=3     exec_time=0     
error_code=0 
SET TIMESTAMP=1091282495; 
insert into v1 values (2);

Hello Sanja, this is yours. In my humble opinion, it's critical that basic operations on
views work with replication in 5.0.1, otherwise some users simply won't be able to start
using them. I attach here a test which I have prepared and which you could extend and
then include in our testsuite in 5.0; I called it rpl_view.test:

# Very basic test for replication of views

source include/master-slave.inc;
create table t1 (a int);
insert into t1 values (1);
create view v1 as select a from t1;
insert into v1 values (2);
select * from v1 order by a;
sync_slave_with_master;
select * from v1 order by a;
connection master;
update v1 set a=3 where a=1;
select * from v1 order by a;
sync_slave_with_master;
select * from v1 order by a;
connection master;
delete from v1 where a=2;
select * from v1 order by a;
sync_slave_with_master;
select * from v1 order by a;
connection master;
drop view v1;
sync_slave_with_master;
select * from v1 order by a;

# TODO: add a test for ALTER VIEW if it exists, and any other statements Sanja can think
of :)
# cleanup
connection master;
drop table t1;
sync_slave_with_master;

ChangeSet
  1.1786 05/01/17 01:18:08 bell@sanja.is.com.ua +4 -0
  added replication of VIEW DDL commands (BUG#4838)

Thank you for bugreport!
Bugfix is pushed into 5.0.3 source repository.

Noted in 5.0.3 changelog.