SQL Alchemy + Testing webserver with InnoDB fails.

Question

Hi,

I am currently trying to move my DB tables over to InnoDB from MyISAM. I am having timing issues with requests and cron jobs that are running on the server that is leading to some errors. I am quite sure that transaction support will help me with the problem. I am therefore transitioning to InnoDB.

I have a suite of tests which make calls to our webservices REST API and receive XML responses. The test suite is fairly thorough, and it's written in Python and uses SQLAlchemy to query information from the database. When I change the tables in the system from MyISAM to InnoDB however, the tests start failing. However, the tests aren't failing because the system isn't working, they are failing because the ORM is not correctly querying the rows from the database I am testing on. when I step through the code I see the correct results, but the ORM is not returning the correct results at all.

Basic flow is:

class UnitTest(unittest.TestCase):                                                                               
    def setUp(self):            
        # Create a test object in DB that gets affected by the web server                                                                                 
        testObject = Obj(foo='one')                                                                              
        self.testId = testObject.id

        session.add(testObject)                                                                                  
        session.commit()                                                                                         

    def tearDown(self):
        # Clean up after the test
        testObject = session.query(Obj).get(self.testId)                                                         

        session.delete(testObject)                                                                               
        session.commit()  

    def test_web_server(self):
        # Ensure the initial state of the object.                                                                                   
        objects = session.query(Obj).get(self.testId)    
        assert objects.foo == 'one'                                                                              

        # This will make a simple HTTP get call on an url that will modify the DB
        response = server.request.increment_foo(self.testId)

        # This one fails, the object still has a foo of 'one'                                                    
        # When I stop here in a debugger though, and look at the database,
        # The row in question actually has the correct value in the database.
        # ????
        objects = session.query(Obj).get(self.testId)                                                            
        assert objects.foo == 'two'

Using MyISAM tables to store the object and this test will pass. However, when I change to InnoDB tables, this test will not pass. What is more interesting is that when I step through the code in the debugger, I can see that the datbase has what I expect, so it's not a problem in the web server code. I have tried nearly every combination of expire_all, autoflush, autocommit, etc. etc, and still can't get this test to pass.

I can provide more info if necessary.

Thanks, Conrad

Answer 1

The problem is that you put the line self.testId = testObject.id before new object is added to session, flushed, and SQLAlchemy assigned ID to it. Thus self.testId is always None. Move this line below session.commit().