rspec users - Aug 2007 - Foreign key constraints, fixtures, and rake task

I''ve got a spec that loads a fixture in the "before" block. 
This works
fine running scripts/spec, but when I run rake spec instead, I get:

ActiveRecord::StatementInvalid in ''User in fixture :quentin with an IM 
service but no IM name should be invalid''
Mysql::Error: Cannot delete or update a parent row: a foreign key 
constraint fails (`mediajoint_development/accounts`, CONSTRAINT 
`accounts_opener_id_fk` FOREIGN KEY (`opener_id`) REFERENCES `users` 
(`id`)): DELETE FROM users
/Users/jay/Documents/eclipse/mediajoint/vendor/plugins/rspec_on_rails/lib/spec/rails/dsl/behaviour/model.rb:12:in
`before_eval''

I''ve even manually run the entire "ruby -I" command that rake
generates,
and it works fine.

I can''t see that the rake task''s doing anything different
other than
db:test:prepare, which I can do manually with no problem.

What''s rake doing differently?

Jay Levitt

* Jay Levitt <lists-rspec at shopwatch.org> [20070827
03:51]:
> I''ve got a spec that loads a fixture in the "before"
block.  This works
> fine running scripts/spec, but when I run rake spec instead, I get:
> 
> ActiveRecord::StatementInvalid in ''User in fixture :quentin with
an IM
> service but no IM name should be invalid''
> Mysql::Error: Cannot delete or update a parent row: a foreign key 
> constraint fails (`mediajoint_development/accounts`, CONSTRAINT 
> `accounts_opener_id_fk` FOREIGN KEY (`opener_id`) REFERENCES `users` 
> (`id`)): DELETE FROM users
>
/Users/jay/Documents/eclipse/mediajoint/vendor/plugins/rspec_on_rails/lib/spec/rails/dsl/behaviour/model.rb:12:in
> `before_eval''
> 
> I''ve even manually run the entire "ruby -I" command that
rake generates,
> and it works fine.
> 
> I can''t see that the rake task''s doing anything different
other than
> db:test:prepare, which I can do manually with no problem.
> 
> What''s rake doing differently?
rake spec isn''t doing anything fundamentally different, but the order
the specs are run is propably randomly different from when you run
them with spec so they happen to pass in one case and fail in the
other.

Using fixtures when there are foreign key constraints in the db can be
a pain - if the speed is acceptable it''s propably best to do

fixtures :all

whenever you need any of the fixtures, or leave the fixtures
declaration out if a describe block doesn''t need any.

I guess the error you are seeing is because a fixtures declaration is
encountered that references users fixtures, but not accounts. When
preparing the fixtures for a describe block, ActiveRecord deletes all
rows in all declared tables and then fills them with the fixtures
data. In your case it wants to delete all from users but doesn''t do
anything with accounts where there are still rows from a spec that ran
previously, and those rows reference users. You could also try to add
:accounts to that particular failing spec to get rid of this one
error.


Til

Tilmann Singer wrote:
> * Jay Levitt <lists-rspec at shopwatch.org> [20070827 03:51]:
>> What''s rake doing differently?
> 
> rake spec isn''t doing anything fundamentally different, but the
order
> the specs are run is propably randomly different from when you run
> them with spec so they happen to pass in one case and fail in the
> other.
Nope, it''s definitely not random - script/spec works every time, rake 
fails every time.

I''m not actually using :accounts at all in my specs; it''s only
referenced in the database constraints.  (It''s tested in Test::Unit at 
the moment.)  So the order wouldn''t matter.

I''m sure I can work around it by loading all the fixtures in some order
that makes the database happy; I''m more curious as to why it happens 
with "rake spec", and not with (what seems to be) the equivalent 
script/spec command.

Unfortunately, test.log doesn''t show me anything that happens during
the
failed rake run.  I''ll have to see if there''s a way to turn up
logging
on MySQL itself to see what''s different, or maybe switch to a TCP
socket
and use Wireshark (nee Ethereal).

Jay

On 8/27/07, Jay Levitt <lists-rspec at shopwatch.org>
wrote:
> Tilmann Singer wrote:
> > * Jay Levitt <lists-rspec at shopwatch.org> [20070827 03:51]:
> >> What''s rake doing differently?
> >
> > rake spec isn''t doing anything fundamentally different, but
the order
> > the specs are run is propably randomly different from when you run
> > them with spec so they happen to pass in one case and fail in the
> > other.
>
> Nope, it''s definitely not random - script/spec works every time,
rake
> fails every time.
By default, the spec.opts file, which is loaded w/ the rake task, but
not implicitly with the script/spec command, uses --reverse, so the
files are run in reverse order. That would support Tilmann''s theory
combined with your observation that they consistently pass one way and
fail the other.

This information should shed some light on your questions below as well.

Cheers,
David
>
> I''m not actually using :accounts at all in my specs; it''s
only
> referenced in the database constraints.  (It''s tested in
Test::Unit at
> the moment.)  So the order wouldn''t matter.
>
> I''m sure I can work around it by loading all the fixtures in some
order
> that makes the database happy; I''m more curious as to why it
happens
> with "rake spec", and not with (what seems to be) the equivalent
> script/spec command.
>
> Unfortunately, test.log doesn''t show me anything that happens
during the
> failed rake run.  I''ll have to see if there''s a way to
turn up logging
> on MySQL itself to see what''s different, or maybe switch to a TCP
socket
> and use Wireshark (nee Ethereal).
>
> Jay
>
>
>
> _______________________________________________
> rspec-users mailing list
> rspec-users at rubyforge.org
> http://rubyforge.org/mailman/listinfo/rspec-users
>

On Aug 27, 2007, at 8:08 AM, David Chelimsky wrote:
> On 8/27/07, Jay Levitt <lists-rspec at shopwatch.org> wrote:
>> Tilmann Singer wrote:
>>> * Jay Levitt <lists-rspec at shopwatch.org> [20070827 03:51]:
>>>> What''s rake doing differently?
>>>
>>> rake spec isn''t doing anything fundamentally different,
but the
>>> order
>>> the specs are run is propably randomly different from when you run
>>> them with spec so they happen to pass in one case and fail in the
>>> other.
>>
>> Nope, it''s definitely not random - script/spec works every
time, rake
>> fails every time.
>
> By default, the spec.opts file, which is loaded w/ the rake task, but
> not implicitly with the script/spec command, uses --reverse, so the
> files are run in reverse order. That would support Tilmann''s
theory
> combined with your observation that they consistently pass one way and
> fail the other.
>
> This information should shed some light on your questions below as  
> well.
>
> Cheers,
> David
>
Just wondering about your usage of constraints. We ran into this  
issue recently and came to the conclusion that unless profiling shows  
an issue, use AR to enforce referential integrity. You might be able  
to get by using :dependent => :destroy, or one of the other options.  
As for the indexing, you can set it up on your own using add_index in  
the migration. That helps save us the headaches of migrations and  
fixture issues.

Just an idea.

Jim
>>
>> I''m not actually using :accounts at all in my specs;
it''s only
>> referenced in the database constraints.  (It''s tested in  
>> Test::Unit at
>> the moment.)  So the order wouldn''t matter.
>>
>> I''m sure I can work around it by loading all the fixtures in
some
>> order
>> that makes the database happy; I''m more curious as to why it
happens
>> with "rake spec", and not with (what seems to be) the
equivalent
>> script/spec command.
>>
>> Unfortunately, test.log doesn''t show me anything that happens
>> during the
>> failed rake run.  I''ll have to see if there''s a way
to turn up
>> logging
>> on MySQL itself to see what''s different, or maybe switch to a
TCP
>> socket
>> and use Wireshark (nee Ethereal).
>>
>> Jay
>>
>>
>>
>> _______________________________________________
>> rspec-users mailing list
>> rspec-users at rubyforge.org
>> http://rubyforge.org/mailman/listinfo/rspec-users
>>
> _______________________________________________
> rspec-users mailing list
> rspec-users at rubyforge.org
> http://rubyforge.org/mailman/listinfo/rspec-users

As people have pointed out, using DB foreign key constraints within 
rails can be a nightmare when it comes to fixtures.  In the past when I 
have used DB constraints I have always disabled the constraints when 
loading the fixtures so it wouldn''t be an issue.  The way I did it was 
the following:
class Fixtures
   alias :original_delete_existing_fixtures :delete_existing_fixtures
   alias :original_insert_fixtures :insert_fixtures

    def delete_existing_fixtures
        @connection.update "SET FOREIGN_KEY_CHECKS = 0",
''Fixtures
deactivate foreign key checks.'';
        original_delete_existing_fixtures
        @connection.update "SET FOREIGN_KEY_CHECKS = 1",
''Fixtures
activate foreign key checks.'';
    end

    def insert_fixtures
        @connection.update "SET FOREIGN_KEY_CHECKS = 0",
''Fixtures
deactivate foreign key checks.'';
        original_insert_fixtures
        @connection.update "SET FOREIGN_KEY_CHECKS = 1",
''Fixtures
activate foreign key checks.'';
    end
end


Note, that this is MySQL specific and I would place this is my 
test_helper.rb (this was pre-rSpec.)  I have not used this code with 
rpsec but since rspec uses rails Fixtures class (right?) you should be 
able to use this as well. 

-Ben

Jim Deville wrote:
> On Aug 27, 2007, at 8:08 AM, David Chelimsky wrote:
>
>   
>> On 8/27/07, Jay Levitt <lists-rspec at shopwatch.org> wrote:
>>     
>>> Tilmann Singer wrote:
>>>       
>>>> * Jay Levitt <lists-rspec at shopwatch.org> [20070827
03:51]:
>>>>         
>>>>> What''s rake doing differently?
>>>>>           
>>>> rake spec isn''t doing anything fundamentally
different, but the
>>>> order
>>>> the specs are run is propably randomly different from when you
run
>>>> them with spec so they happen to pass in one case and fail in
the
>>>> other.
>>>>         
>>> Nope, it''s definitely not random - script/spec works every
time, rake
>>> fails every time.
>>>       
>> By default, the spec.opts file, which is loaded w/ the rake task, but
>> not implicitly with the script/spec command, uses --reverse, so the
>> files are run in reverse order. That would support Tilmann''s
theory
>> combined with your observation that they consistently pass one way and
>> fail the other.
>>
>> This information should shed some light on your questions below as  
>> well.
>>
>> Cheers,
>> David
>>
>>     
>
> Just wondering about your usage of constraints. We ran into this  
> issue recently and came to the conclusion that unless profiling shows  
> an issue, use AR to enforce referential integrity. You might be able  
> to get by using :dependent => :destroy, or one of the other options.  
> As for the indexing, you can set it up on your own using add_index in  
> the migration. That helps save us the headaches of migrations and  
> fixture issues.
>
> Just an idea.
>
> Jim
>
>   
>>> I''m not actually using :accounts at all in my specs;
it''s only
>>> referenced in the database constraints.  (It''s tested in  
>>> Test::Unit at
>>> the moment.)  So the order wouldn''t matter.
>>>
>>> I''m sure I can work around it by loading all the fixtures
in some
>>> order
>>> that makes the database happy; I''m more curious as to why
it happens
>>> with "rake spec", and not with (what seems to be) the
equivalent
>>> script/spec command.
>>>
>>> Unfortunately, test.log doesn''t show me anything that
happens
>>> during the
>>> failed rake run.  I''ll have to see if there''s a
way to turn up
>>> logging
>>> on MySQL itself to see what''s different, or maybe switch
to a TCP
>>> socket
>>> and use Wireshark (nee Ethereal).
>>>
>>> Jay
>>>
>>>
>>>
>>> _______________________________________________
>>> rspec-users mailing list
>>> rspec-users at rubyforge.org
>>> http://rubyforge.org/mailman/listinfo/rspec-users
>>>
>>>       
>> _______________________________________________
>> rspec-users mailing list
>> rspec-users at rubyforge.org
>> http://rubyforge.org/mailman/listinfo/rspec-users
>>     
>
> _______________________________________________
> rspec-users mailing list
> rspec-users at rubyforge.org
> http://rubyforge.org/mailman/listinfo/rspec-users
>

David Chelimsky wrote:
> On 8/27/07, Jay Levitt <lists-rspec at shopwatch.org> wrote:
>> Tilmann Singer wrote:
>>> * Jay Levitt <lists-rspec at shopwatch.org> [20070827 03:51]:
>>>> What''s rake doing differently?
>>> rake spec isn''t doing anything fundamentally different,
but the order
>>> the specs are run is propably randomly different from when you run
>>> them with spec so they happen to pass in one case and fail in the
>>> other.
>> Nope, it''s definitely not random - script/spec works every
time, rake
>> fails every time.
> 
> By default, the spec.opts file, which is loaded w/ the rake task, but
> not implicitly with the script/spec command, uses --reverse, so the
> files are run in reverse order. That would support Tilmann''s
theory
> combined with your observation that they consistently pass one way and
> fail the other.
Nope:

# lib/tasks/jaytest.rake
rspec_base = File.expand_path(File.dirname(__FILE__) + 
''/../../vendor/plugins/rspec/lib'')
$LOAD_PATH.unshift(rspec_base) if File.exist?(rspec_base)
require ''spec/rake/spectask''
require ''spec/translator''

desc "Run my bad spec"
Spec::Rake::SpecTask.new(:jaytest) do |t|
   t.spec_files = FileList[''spec/models/jaytest_spec.rb'']
end

# spec/models/jaytest_spec.rb
require File.dirname(__FILE__) + ''/../spec_helper''

describe User, "in fixture :quentin" do
   fixtures :users

   it "should" do
     @user = users(:quentin)
   end

end

# mysql>show create table accounts
..
CONSTRAINT `accounts_opener_id_fk` FOREIGN KEY (`opener_id`) REFERENCES 
`users` (`id`)
..

# ''rake jaytest'' output

F

1)
ActiveRecord::StatementInvalid in ''User in fixture :quentin
should''
Mysql::Error: Cannot delete or update a parent row: a foreign key 
constraint fails (`mediajoint_development/accounts`, CONSTRAINT 
`accounts_opener_id_fk` FOREIGN KEY (`opener_id`) REFERENCES `users` 
(`id`)): DELETE FROM users
/Users/jay/Documents/eclipse/mediajoint/vendor/plugins/rspec_on_rails/lib/spec/rails/dsl/behaviour/model.rb:12:in
`before_eval''

Finished in 0.031189 seconds

1 example, 1 failure
rake aborted!
Command ruby 
-I"/Users/jay/Documents/eclipse/mediajoint/vendor/plugins/rspec/lib" 
"/Users/jay/Documents/eclipse/mediajoint/vendor/plugins/rspec/bin/spec"
"spec/models/jaytest_spec.rb"  failed

# ruby 
-I"/Users/jay/Documents/eclipse/mediajoint/vendor/plugins/rspec/lib" 
"/Users/jay/Documents/eclipse/mediajoint/vendor/plugins/rspec/bin/spec"
"spec/models/jaytest_spec.rb" output

Jim Deville wrote:
>>
> 
> Just wondering about your usage of constraints. We ran into this  
> issue recently and came to the conclusion that unless profiling shows  
> an issue, use AR to enforce referential integrity. You might be able  
> to get by using :dependent => :destroy, or one of the other options.  
> As for the indexing, you can set it up on your own using add_index in  
> the migration. That helps save us the headaches of migrations and  
> fixture issues.
Just the usual debate of "single layer of cleverness" vs.
"defense in
depth" against bugs.  It''s not a performance thing; we''re
not at that
level yet.

Jay