When you’ve built a database that includes foreign key constraints between parent and child tables, loading fixtures using rake load_fixtures and unit testing can fail with errors such as:
Mysql::Error: #23000Cannot add or update a child row: a foreign key constraint fails (`depot_development/line_items`, CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE): INSERT INTO line_items (`quantity`, `product_id`, `id`, `unit_price`) VALUES (1, 1, 1, 29.95)
</code>
*OR*
<code>
ActiveRecord::StatementInvalid: Mysql::Error: Cannot delete or update a parent row: a foreign key constraint fails: DELETE FROM line_items WHERE id = 1
… because the child record is being inserted into the database before the parent record. A similar error can occur when updating fixtures using the same rake task because the old parent record is being deleted before the child record.
In order to load or update fixtures which include parent-child records you need to disable foreign key constraint checking temporarily, but re-enable it after the fixtures have loaded so that your database behaves as it should.
Instead of using rake load_fixtures, add the following task to the lib/tasks directory and use rake load_fixtures_without_constraints instead.
desc "Loads fixtures whilst turning foreign key constraints checking off"
task :load_fixtures_without_constraints => :environment do
require 'active_record/fixtures'
ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 0"
Dir.glob(File.join(RAILS_ROOT, 'test', 'fixtures', '*.{yml,csv}')).each do |fixture_file|
Fixtures.create_fixtures('test/fixtures', File.basename(fixture_file, '.*'))
end
ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 1"
end
You can also turn off constraints when fixtures are loaded to run unit tests by adding the following to the end of test_helper.rb:
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
I have the Fixture class modified the way specified here. But when I now run all the tests, I get the error which makes it seem that the class is getting defined more than once somehow.
SystemStackError: stack level too deep
config/../vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:199:in `update'
./test/functional/admin/../../test_helper.rb:69:in `original_delete_existing_fixtures'
./test/functional/admin/../../test_helper.rb:70:in `original_delete_existing_fixtures'
I got the same error =(
somebody can help me?
(Edited from an earlier posting by Anonymous Coward).
—-
The alternative way:
I wrote a rake task based on weenie’s one that let you assign a sequence number to your .yml files in order to mantain foreign keys relations.
When you’ve built a database that includes foreign key constraints between parent and child tables, loading fixtures using rake load_fixtures and unit testing can fail with errors such as:
Mysql::Error: #23000Cannot add or update a child row: a foreign key constraint fails (`depot_development/line_items`, CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE): INSERT INTO line_items (`quantity`, `product_id`, `id`, `unit_price`) VALUES (1, 1, 1, 29.95)
</code>
*OR*
<code>
ActiveRecord::StatementInvalid: Mysql::Error: Cannot delete or update a parent row: a foreign key constraint fails: DELETE FROM line_items WHERE id = 1
… because the child record is being inserted into the database before the parent record. A similar error can occur when updating fixtures using the same rake task because the old parent record is being deleted before the child record.
In order to load or update fixtures which include parent-child records you need to disable foreign key constraint checking temporarily, but re-enable it after the fixtures have loaded so that your database behaves as it should.
Instead of using rake load_fixtures, add the following task to the lib/tasks directory and use rake load_fixtures_without_constraints instead.
desc "Loads fixtures whilst turning foreign key constraints checking off"
task :load_fixtures_without_constraints => :environment do
require 'active_record/fixtures'
ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 0"
Dir.glob(File.join(RAILS_ROOT, 'test', 'fixtures', '*.{yml,csv}')).each do |fixture_file|
Fixtures.create_fixtures('test/fixtures', File.basename(fixture_file, '.*'))
end
ActiveRecord::Base.connection.update "SET FOREIGN_KEY_CHECKS = 1"
end
You can also turn off constraints when fixtures are loaded to run unit tests by adding the following to the end of test_helper.rb:
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
I have the Fixture class modified the way specified here. But when I now run all the tests, I get the error which makes it seem that the class is getting defined more than once somehow.
SystemStackError: stack level too deep
config/../vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:199:in `update'
./test/functional/admin/../../test_helper.rb:69:in `original_delete_existing_fixtures'
./test/functional/admin/../../test_helper.rb:70:in `original_delete_existing_fixtures'
I got the same error =(
somebody can help me?
(Edited from an earlier posting by Anonymous Coward).
—-
The alternative way:
I wrote a rake task based on weenie’s one that let you assign a sequence number to your .yml files in order to mantain foreign keys relations.