Bug #4838 DDL statements for views are not written to the binary log

• Submitted: 31 Jul 2004 16:12
• Modified: 25 Feb 2005 18:56
• Reporter: Georg Richter
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.0.1
• OS: Linux (Linux)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[31 Jul 2004 16:12] Georg Richter

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);

[31 Jul 2004 16:53] Guilhem Bichot

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;

[16 Jan 2005 23:20] Oleksandr Byelkin

ChangeSet
  1.1786 05/01/17 01:18:08 [e-mail] +4 -0
  added replication of VIEW DDL commands (BUG#4838)

[24 Feb 2005 2:15] Oleksandr Byelkin

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

[25 Feb 2005 18:56] Paul DuBois

Noted in 5.0.3 changelog.