This is more of a meta-question than I need actual help with but I could not find information about it elsewhere. Today, I tried to delete two tables from a database and upon trying I get the error message that a "foreign key constraint fail" has occurred. No problem, just delete the foreign key as well. So my query looked like the following:
ALTER TABLE Database.mytable
DROP FOREIGN KEY abc_ibfk_1;
ALTER TABLE Database.mytable
DROP COLUMN abc;
I get another error message that says "Error Code: 1025. Error on rename of './Database/#sql-461_somerandomnumbers' to './Database/mytable' (errno: 150)"
I figure I would use the built-in right-click, drop column in the edit table so I use that and realise it uses a "drop index" as well. So I change my code to look exactly like Workbench would do itself:
ALTER TABLE Database.mytable
DROP FOREIGN KEY abc_ibfk_1;
ALTER TABLE Database.mytable
DROP COLUMN abc, DROP INDEX abc;
Now, this is exactly as Workbench would do it, only that when I type it myself it will not execute (above error message) but when I allow Workbench to execute the query it works fine.
So, my question is why is Workbench able to execute the drop query when I can't copy/paste it?
Probably your manually exectued query is (wrongly) augmented, e.g. the automatic LIMIT clause is added. After running that query look in the output area (Action Output). It contains the exact query that was sent to the server. Does it contain anything unusual? Alternatively use the admin section to look at the general log (if enabled) to see what query was sent.