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:
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

[23 Sep 2005 13:37] Ian Thurlbeck

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:

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:
server-id = 2

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:

[23 Sep 2005 19:49] MySQL Verification Team
There is InnoDB tables involved in your test case?

Thanks in advance.
[27 Sep 2005 8:20] Ian Thurlbeck
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`)

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

[29 Sep 2005 16:03] MySQL Verification Team
Thank you for the feedback, but I was unable to find a behavior
like you reported.