Randomly inserting in the wrong order.

[Timothée Jeannin]

When trying to insert sequentially a model (team in the example) then another model with a foreign key pointing to the first model (user in the example) I was expecting sqlalchemy to either insert in the order I called db.session.add or resolve the correct insertion order. It looks like it randomly resolve to an incorrect insertion order.

It look like the insertion order is computed in the sort_as_subsets method from the topological.py module.

Sometimes the insertion is in the correct order:

#!

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO team (team_id) VALUES (%s)
INFO:sqlalchemy.engine.base.Engine:(1,)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO user (user_id, team_id) VALUES (%s, %s)
INFO:sqlalchemy.engine.base.Engine:((1, 1), (2, 1), (3, 1))
INFO:sqlalchemy.engine.base.Engine:COMMIT

And sometimes it starts by inserting the users instead of inserting the team:

#!

INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO user (user_id, team_id) VALUES (%s, %s)
INFO:sqlalchemy.engine.base.Engine:((1, 1), (2, 1), (3, 1))
INFO:sqlalchemy.engine.base.Engine:ROLLBACK

Error
Traceback (most recent call last):
  File "/home/tim/Workspace/dota_memories/website/tests/test_sqla.py", line 45, in test_method
    db.session.commit()
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 874, in commit
    self.transaction.commit()
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 461, in commit
    self._prepare_impl()
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl
    self.session.flush()
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2139, in flush
    self._flush(objects)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush
    flush_context.execute()
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute
    rec.execute(self)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute
    uow
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 799, in _emit_insert_statements
    execute(statement, multiparams)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _execute_context
    context)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 113, in do_executemany
    rowcount = cursor.executemany(statement, parameters)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 262, in executemany
    r = self._query('\n'.join([query[:p], ',\n'.join(q), query[e:]]))
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 354, in _query
    rowcount = self._do_query(q)
  File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 318, in _do_query
    db.query(q)
IntegrityError: (_mysql_exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`memories_tests`.`user`, CONSTRAINT `user_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`team_id`))') [SQL: u'INSERT INTO user (user_id, team_id) VALUES (%s, %s)'] [parameters: ((1, 1), (2, 1), (3, 1))]

I created a simple example that runs 100 times the same test to demonstrate that it is randomly failing.

import logging
from unittest import TestCase

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

db = SQLAlchemy()

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:toor@localhost:3306/memories_tests'
db.init_app(app)


class User(db.Model):
    user_id = db.Column(db.BigInteger, primary_key=True, autoincrement=False)
    team_id = db.Column(db.BigInteger, db.ForeignKey('team.team_id'), nullable=True)


class Team(db.Model):
    team_id = db.Column(db.BigInteger, primary_key=True, autoincrement=False)


class TeamTests(TestCase):
    def setUp(self):
        self.context = app.test_request_context()
        self.context.push()
        db.drop_all()
        db.create_all()

    def tearDown(self):
        db.session.remove()
        db.drop_all()
        self.context.pop()


def add_test_method(index):
    def test_method(self):
        db.session.add(Team(team_id=1))
        db.session.add(User(user_id=1, team_id=1))
        db.session.add(User(user_id=2, team_id=1))
        db.session.add(User(user_id=3, team_id=1))
        db.session.commit()

    test_method.__name__ = 'test_method_' + str(index)
    setattr(TeamTests, test_method.__name__, test_method)


for i in range(100):
    add_test_method(i)

Here is what I use :

• mysql 5.7.17
• Ubuntu 16.04
• SQLAlchemy 1.1.6
• Flask 0.11.1
• Flask-SQLAlchemy 2.1
• MySQL-python 1.2.5

[Mike Bayer]

Hello -

Thanks for the clear test case.

Session.add() does not preserve the order of INSERT between entirely different models, only those rows within a single table. So the .add() calls for User are maintained in order.

In this case, you have a dependency between Team and User, but you have not informed the SQLAlchemy ORM that such a dependency exists. The normal way to do this would be to indicate a relationship between the two objects:

class User(db.Model):
    user_id = db.Column(db.BigInteger, primary_key=True, autoincrement=False)
    team_id = db.Column(db.BigInteger, db.ForeignKey('team.team_id'), nullable=True)

    team = relationship("Team")

class Team(db.Model):
    team_id = db.Column(db.BigInteger, primary_key=True, autoincrement=False)

no bug is illustrated here and the above fix illustrates the correct use of the API so let me know that's working. Thanks!

[Timothée Jeannin]

Hi,

I didn't realize it is mandatory to specify the relationship on the model. It is working now, thank you for your quick answer.

SqlAlchemy really is great !

Tim