MySQL Bugs: #39216: VIEWs are not properly replicated.

Bug #39216	VIEWs are not properly replicated.
Submitted:	3 Sep 2008 15:17	Modified:	4 Sep 2008 22:21
Reporter:	Mathieu Jobin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.67	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	replication view views

Description:
VIEWs are not properly replicated.

errors often happens. I cannot find anything in the documentation about it.

it feels like we would need to disable replication for view and re-create them by hand on the slaves. but we would wish mysql replication system to handle all that automatically.

How to repeat:
setup replication which has views on the master

Suggested fix:
replicate view properly. do not duplicate data, only the view create statement should be replicated. or make it an option.

Thank you for a problem report. What exct version, 5.0.x, you had used? Please, send also some small but complete repeatable test case that demonstrates the problem.

Version is 5.0.67

After removing the view giving the error, another error came up with the next view in line

Note: the views were not present on the slave since they are not relevant on that server.

Please provide the teste case: my.ini (master/slave) and create tables/views.
Thanks.

my.ini on slave

[mysqld]
server-id=2
master-host=aaaaaaaa
master-user=uuuuuuu
master-password=pppppppp
master-connect-retry = 60
replicate-do-db=glgi

# The TCP/IP Port the MySQL Server will listen on
port=3306

----------------------------------------------------
my.ini on master

[mysqld]
server-id=1
log-bin=mysql-bin
max_binlog_size = 104857600
binlog-do-db=glgi

# The TCP/IP Port the MySQL Server will listen on
port=3306

the error log has this entry for the error:

080903  9:56:51 [ERROR] Slave: Error 'Unknown table 'annex_as_for_schedule_as_with_waiver'' on query. Default database: 'glgi'. Query: 'DROP VIEW `annex_as_for_schedule_as_with_waiver`', 

The view is created with:

select `annex_as`.`id` AS `id`,
	  `annex_as`.`annex_a_date` AS `annex_a_date`,
          `annex_as`.`email_date` AS `email_date`,
          `schedule_as`.`scha_date` AS `scha_date`,
          `schedule_as`.`waiver` AS `waiver` 
from (`annex_as` join `schedule_as` 
on((`annex_as`.`scha_id` = `schedule_as`.`scha_id`))) 
where (`schedule_as`.`waiver` = _utf8'1') 
order by `annex_as`.`id` desc

we have decided to delete the views from the master, to eliminate the problem. as they were not so important to our system.

therefor, we don't know when we are gonna be able to do replication test on view again.

thanks

Thank you for the feedback.

Error log says:

080903  9:56:51 [ERROR] Slave: Error 'Unknown table
'annex_as_for_schedule_as_with_waiver'' on query. Default database: 'glgi'. Query: 'DROP
VIEW `annex_as_for_schedule_as_with_waiver`', 

So you have problem when dropping view not existing on slave. Which is expected. If you didn't issue DROP VIEW command on master please provide real query you used when created view.

WORKSFORME

views were not duplicated on the slave before the replication was turn on.
therefor, we experienced that problem.

everything seems perfect now.

sorry