Strange Rails Behaviour with MySQL (delete cascade)

Rails
#1

hi,
i have a problem that involve mysql. i habe two tables: groups,ranks
ranks have a foraign key from groups with delete on cascade.
In my ranks migration i use this

execute ‘ALTER TABLE ranks ADD FOREIGN KEY (group_id) REFERENCES groups
(id) ON DELETE CASCADE;’

if i do the following in the mysql console i get an empty result as
expected.

DELETE FROM groups WHERE id=3; SELECT * FROM ranks WHERE group_id=3;

BUT here comes the Problem:

Group.find(3).destroy
Rank.find_by_group_id(3)

returns several ranks!!

First when i add “sleep 3” between these commands it works

Group.find(3).destroy
sleep 3
Rank.find_by_group_id(3)

returns nil as expected
but thats not the solution i really want…
is it a rails bug or mysql problem?

Rank.find_by_group_id(3) should first be executed if the
delete-statement(with delete on cascade) is finished.
is this shitty behaviour a rails bug or what? can some one help me out
of this?

#2

Use delete instead with exists? :slight_smile:

if Group.exists?(3)
Group.delete(3)
end

Rank.find_by_group_id(3)

#3

I dont see any differences. can you explain me what it should do?

#4

Andreas Kane wrote:

I dont see any differences. can you explain me what it should do?

I had the same problem like you.

The destroy did not delete immediately, I had to wait few seconds before
it did it.

The delete did what I wanted, and that was deleting it as soon as I call
it, and then I could do whatever I want after that.

Did you try the code?

#5

On 10/13/07, Jamal S. [email protected] wrote:

it, and then I could do whatever I want after that.
You can also have AR handle the referential integrity:

class Group < ActiveRecord::Base
has_many :ranks, :dependent => :destroy
end

class Rank
belongs_to :group
end

When you delete a group, AR will delete its ranks in the same
transaction so you shouldn’t see the delay.

Or course you might want to also declare the constraints in SQL if non
AR code is manipulating the database.

Rick DeNatale

My blog on Ruby
http://talklikeaduck.denhaven2.com/

#6

the problem is that destroying of some childs is forbidden.
that could be overrided by setting :dependent => destroy_all

but there are much more models involved in this delete cascade stuff, so
i had to define 5 has_manys with :dependent=> destroy_all in the model
on top an for them the finder sqls cause rails can not handle has manys
through 2 models.