[Sqlalchemy-tickets] Issue #3930: Randomly inserting in the wrong order. (zzzeek/sqlalchemy)

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

New issue 3930: Randomly inserting in the wrong order.
https://bitbucket.org/zzzeek/sqlalchemy/issues/3930/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. 

```
#!python

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