MySQL Bugs: #20596: Views don't replicate with LOAD DATA FROM MASTER

Bug #20596	Views don't replicate with LOAD DATA FROM MASTER
Submitted:	21 Jun 2006 9:09	Modified:	30 Aug 2006 18:36
Reporter:	Jay Bertrand	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.25-BK, 5.0.22	OS:	Linux (Debian Etch Gnu/Linux)
Assigned to:	Andrei Elkin	CPU Architecture:	Any

Description:
Hi
When I want replicate a database using LOAD DATA FROM MASTER, I have a problem when the database contains view. 
The error :
LOAD DATA FROM MASTER;
> Error from master: ''test.myview' is not BASE TABLE'

How to repeat:
Config's master :
bind-adress=10.0.0.1
server-id=1
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=test

Config's slave :
server-id=2
master-host=10.0.0.1
master-port=3306
master-user=replicator
master-password=replicator
replicate-do-db=test

SQL on master : 
CREATE TABLE  `test`.`mytable` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `hum` varchar(45),
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;
CREATE VIEW  `test`.`myview` AS select * from `test`.`mytable`;

SQL on slave : 
LOAD DATA FROM MASTER;
> Error from master: ''test.myview' is not BASE TABLE'

Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux:

openxs@suse:~/dbs/5.0/mysql-test> ./mysql-test-run --record rpl_20596
Logging: ./mysql-test-run --record rpl_20596
Installing Test Databases
Removing Stale Files
Installing Master Databases
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables     --b
asedir=.. --datadir=mysql-test/var/master-data --skip-innodb --skip-ndbcluster -
-skip-bdb
Installing Master Databases 1
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables     --b
asedir=.. --datadir=mysql-test/var/master-data1 --skip-innodb --skip-ndbcluster
--skip-bdb
Installing Slave Databases
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables     --b
asedir=.. --datadir=mysql-test/var/slave-data --skip-innodb --skip-ndbcluster --
skip-bdb
Manager disabled, skipping manager start.
Loading Standard Test Databases
Starting Tests

TEST                            RESULT
-------------------------------------------------------
rpl_20596                      [ fail ]

Errors are (from /home/openxs/dbs/5.0/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 15: query 'load data from master' failed: 1188: Error from ma
ster: ''test.v1' is not BASE TABLE'
(the last lines may be the most important ones)

Aborting: rpl_20596 failed in default mode. To continue, re-run with '--force'.

Ending Tests
Shutting-down MySQL daemon

Master shutdown finished
Slave shutdown finished
openxs@suse:~/dbs/5.0/mysql-test> cat t/rpl_20596.test
source include/master-slave.inc;
--disable_warnings
drop table if exists t1,v1;
drop view if exists t1,v1;
sync_slave_with_master;
reset master;
--enable_warnings

connection master;
create table t1 (a int);
insert into t1 values (1);
create view v1 as select a from t1;
insert into v1 values (2);
connection slave;
load data from master;

Since the current implementation of LOAD DATA FROM MASTER
is very limited, a possible plan is to deprecate this
functionality from versions 4.1, 5.0 and 5.1 and in
later versions (> 5.1) introduce a more advanced technique
(online backup) that cover more engines.

Since the current implementation of LOAD DATA FROM MASTER
and LOAD TABLE FROM MASTER is very limited, these statements
are deprecated in versions 4.1, 5.0 and 5.1 of MySQL. We will
introduce a more advanced technique (online backup) in a 
future (>5.1) version, that will have the added advantage of
working with more storage engines.

Due to the deprecated status of the statements, this bug will
not be fixed. For 5.1 and earlier, the recommended alternative 
solution to usign LOAD DATA|TABLE FROM MASTER is mysqldump on 
the master piped (or output copied) to the mysql client on the 
slave. This also has the advantage of working for all storage 
engines.

A deprecation warning will be added for these statements in
4.1, 5.0 and 5.1 shortly.