[Rails] maddening intermittent failures in unit tests with "working" code

[Chris]

Hi all,

This testing problem has been a sink for time today, and is still unresolved.

Basically I have some unit tests that test simple functions (example below) 
that depend on join operations in a habtm relationship, and I suspect I am 
getting "false" failures, i.e ones that do not logically make any sense. I 
need fresh sets of eyes to take a look and see if I'm mising something (my 
error, rails bug?, mysql bug?).  

It's almost as though there is a race condition somewhere, or an unreliable 
SQL transaction.

def reservations
  # called by User object, returns count of reservations with associated Zimp 
objects
  # Zimp and User are AR classes mapped to MySQL schema shown later below.
  count = 0
  self.zimps.each { |x| count += x.reserved.to_i }
  count
end

A typical unit test for this function would be to create a User object and 
call the function, and check the return result

def test_working
  @user=User.find(:first)
  assert_equal 2, @user.reservations
end 

For the test data loaded from the fixtures below, two records should be 
returned, and the result should be 2.  90% of the time in testing this is what 
happens.

But sometimes this will fail with a result of zero (no matching records, i.e. 
the join operation produced no output).  "Sometimes" means that typically in 
reptitive runs in the same environment it will continue to fail, until I make 
unrelated code changes, e.g. adding logger output, adding more code somewhere 
else, adding a test. etc. 

I have tried running tests with rake, from within the radrails environment, 
from script/console, and with a coverage tool.  Eventually I have reproduced 
the failure in multiple environments. 

I have included most the information I think could be of use, including the 
test_helper.rb configuration, including a mod that disables constraint 
checking in SQL (without this, the tests fail).

At a loss...

thanks

Chris

================== Extra info ===========

The SQL produced by the reservations function is:

SELECT * FROM zimps LEFT JOIN users_zimps ON zimps.id = users_zimps.zimp_id 
WHERE (users_zimps.user_id = 5 ) 

SCHEMA
------
CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  [....]
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `users_zimps` (
  `zimp_id` int(10) unsigned NOT NULL default '0',
  `user_id` int(10) unsigned NOT NULL default '0',
  `reserved` int(10) unsigned NOT NULL default '1',
  KEY `fk_reservations_zimp` (`zimp_id`),
  KEY `fk_reservations_user` USING BTREE (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `zimps` (
  `id` int(11) NOT NULL auto_increment,
  [...]
  PRIMARY KEY  (`id`),
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

users_zimps.yml
---------------
alice_buys_chris_bday_qty1:
  zimp_id: 1
  user_id: 5
  reserved: 1
alice_buys_chris_bday_qty2:
  zimp_id: 3
  user_id: 5
  reserved: 1

users.yml
---------
otipher:
  id: 1
bunky:
  id: 2
email_bob:
  id: 3
email_sally:
  id: 4
alice:
  id: 5

zimps.yml
---------
otipher_bday_codebook:
  id:          1
otipher_bday_headfirst:
  id:          2
otipher_bday_cookbook:
  id:          3

test_helper.rb
--------------
ENV["RAILS_ENV"] = "test"
require File.expand_path(File.dirname(__FILE__) + "/../config/environment")
require 'test_help'
require 'logger'

class Test::Unit::TestCase
  self.use_transactional_fixtures = true
  self.use_instantiated_fixtures  = false
  
  # From http://rails.techno-weenie.net/tip/2005/11/20/log_within_tests
  def logger
  RAILS_DEFAULT_LOGGER
  end
end

# from 
http://wiki.rubyonrails.com/rails/pages/DisableForeignKeyChecksUnderMySql
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