MySQL Bugs: #13427: LOAD DATA FROM MASTER transfers previously dropped tables

Bug #13427	LOAD DATA FROM MASTER transfers previously dropped tables
Submitted:	23 Sep 2005 13:37	Modified:	29 Sep 2005 16:03
Reporter:	Ian Thurlbeck	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	MySQL-server-4.1.14-0.glibc23	OS:	Linux (Linux)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:

Standard replication setup between master and slave. After load data from master,
database is transferred and slave updates work as expected.  However, a show tables
command lists a much larger set of tables in the slave than on the master.
Indeed, the extra tables are ones previously dropped (weeks ago).

Config (both machines):
Fedora Core 4 + all current updates
www.mysql.com rpms for linux:
MySQL-client-4.1.14-0.glibc23.i386.rpm
MySQL-devel-4.1.14-0.glibc23.i386.rpm
MySQL-server-4.1.14-0.glibc23.i386.rpm
MySQL-shared-4.1.14-0.glibc23.i386.rpm

How to repeat:

Setup replication as usual:

mysql master> GRANT REPLICATION SLAVE,RELOAD,SELECT,SUPER ON *.* TO slave_user@slave IDENTIFIED BY 'blahblah';

slave /etc/my.cnf:
#skip-networking
log-bin
server-id = 2
master-host=stoer
master-user=slave_user
master-password=blahblah
replicate-do-db=vle_db
log-warnings

Issued a "load data from master" and noticed that slave has extra tables listed.

The extra tables even have all the old data in them as well.

du -sk /var/lib/mysql/DBNAME on master is 2.4MB
du -sk /var/lib/mysql/DBNAME on slave is 4.8MB

Tables are NOT in the DBNAME directory on the master, but they are there on the slave.

Looks like the slave didn't execute the drop table commands in the binary log?

Suggested fix:

None.

There is InnoDB tables involved in your test case?

Thanks in advance.

All tables MyISAM.

Seems the "load data from maste" had stopped half-way through with an error:

1062 | Error 'Duplicate entry '72' for key 1' on query. Default database: 'vle_db'. Query: 'INSERT INTO study_record_ng  VALUES ('0','matina','53.101','1','19','489965798','1','0','2005-09-23 18:28:09')'

after stopping slave, dropping the database and retrying everything worked fine.  

Did it all a third time and that worked also. Table in question has a simple
autoincrement id:

CREATE TABLE `study_record_ng` (
  `instance_id` int(11) NOT NULL auto_increment,
  `username` varchar(30) NOT NULL default '',
  `classcode` varchar(10) NOT NULL default '',
  `id` smallint(6) NOT NULL default '0',
  `qid` smallint(6) NOT NULL default '0',
  `seed` int(11) NOT NULL default '0',
  `mark_avail` float NOT NULL default '0',
  `mark_achieved` float NOT NULL default '0',
  `submit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`instance_id`),
  KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Not sure if I did something wrong or this is some sort of syncing bug.?

Thanks

Thank you for the feedback, but I was unable to find a behavior
like you reported.