From: Date: December 3 2003 5:40am
Subject: Drop foreign key replication problem
List-Archive: http://lists.mysql.com/mysql/154918
Message-Id: <20031203134041.0898eee6.bkesuma@ml.gaijinweb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hi,

I am running a master and a slave. When I created a foreign key in
master, it looked like this:
CONSTRAINT `0_373` FOREIGN KEY (`prefecture_id`) REFERENCES `prefecture`
(`id`)

But on the slave it was like:
CONSTRAINT `0_451` FOREIGN KEY (`prefecture_id`) REFERENCES `prefecture`
(`id`)

The problem is, when I deleted this foreign key on master using command:
ALTER TABLE input DROP FOREIGN KEY 0_373

The slave produced an error:
ERROR: 1025  Error on rename of './new_atpress/input' to
'./new_atpress/#sql2-3a54-2' (errno: 140)
031203 13:27:30  Slave: Error 'Error on rename of './new_atpress/input'
to './new_atpress/#sql2-3a54-2' (errno: 140)' on query 'alter table
input drop foreign key 0_373'. Default database: 'new_atpress',
Error_code: 1025
031203 13:27:30  Error running query, slave SQL thread aborted. Fix the
problem, and restart the slave SQL thread with "SLAVE START". We stopped
at log 'borneo-bin.010' position 901130237

Versions:
MySQL 4.0.15
Linux 2.4.18

I guess this is because the constraint name difference of the foreign
keys. How do I fix this problem? Can I just manually drop the foreign
key on slave and skip this "alter table input drop foreign key 0_373"?
How to do it? Please help, and thank you very much.