[Sqlalchemy-tickets] Issue #4376: Can't determine horizontal shard for many-to-many relationship (zzzeek/sqlalchemy)

[Hannele K. <iss...@bi...>]

New issue 4376: Can't determine horizontal shard for many-to-many relationship
https://bitbucket.org/zzzeek/sqlalchemy/issues/4376/cant-determine-horizontal-shard-for-many

Hannele Kormano:

I'm running into trouble updating a many-to-many relationship with horizontal shards. It looks like there are cases where SQLAlchemy doesn't pass an instance nor a query to the shard_chooser function, so I'm unsure what assumptions we should be making. It looks like a reasonable thing to do might be to return a dummy value in those cases as there shouldn't actually be any action -- it does also look like the shard_chooser is called separately for each kind of many-to-many update, but I'd want to make sure there aren't other reasons we should throw an error in this case. 

Reproducible in at least SQLAlchemy 1.2, with MySQL 5.6

One consistent way to reproduce this issue is to add to an empty collection -- by definition there are no deletes to process. In the tests below, I've set up the shard_mapper to throw an error in those cases where neither an instance nor a query is passed in.

    from sqlalchemy import *
    from sqlalchemy.orm import *
    from sqlalchemy.ext.horizontal_shard import ShardedSession
    from sqlalchemy.testing import fixtures
    from sqlalchemy import testing


    class UpdateManyToManyShardedTest(fixtures.DeclarativeMappedTest):
        @classmethod
        def setup_classes(cls):
            Base = cls.DeclarativeBasic

            class Book(Base):
                __tablename__ = 'book'
                id = Column(Integer, primary_key=True)
                authors = relationship(
                    'Author', secondary='book_author', back_populates='books')

            class BookAuthor(Base):
                __tablename__ = 'book_author'
                authorid = Column(ForeignKey('author.id'), primary_key=True)
                bookid = Column(ForeignKey('book.id'), primary_key=True)

            class Author(Base):
                __tablename__ = 'author'
                id = Column(Integer, primary_key=True)
                books = relationship(
                    'Book', secondary='book_author', back_populates='authors')

        def test_update_many_to_many_sharded(self):
            session = ShardedSession(
                shards={"test": testing.db},
                shard_chooser=self.shard_chooser,
                id_chooser=lambda *args: None,
                query_chooser=lambda *args: ['test']
            )

            Book, Author = self.classes("Book", "Author")
            book = Book()
            book.authors.append(Author())

            session.add(book)
            session.commit()

        def test_update_many_to_many_sharded__save_junction_table_directly(self):
            session = ShardedSession(
                shards={"test": testing.db},
                shard_chooser=self.shard_chooser,
                id_chooser=lambda *args: None,
                query_chooser=lambda *args: ['test']
            )

            Book, Author, BookAuthor = self.classes("Book", "Author", "BookAuthor")

            book = Book()
            author = Author()

            session.add(book)
            session.add(author)
            session.commit()

            book_author = BookAuthor()
            book_author.bookid = book.id
            book_author.authorid = author.id

            session.add(book_author)
            session.commit()

        def shard_chooser(self, mapper, instance, clause=None):
            if not instance and not clause:
                raise Exception('Cannot determine shard')

            return 'test'

Both tests end up with a similar stack trace:

    ======================================================================
    ERROR: test.orm.test_sharded_m2m.UpdateManyToManyShardedTest.test_update_many_to_many_sharded__save_junction_table_directly
    ----------------------------------------------------------------------
    Traceback (most recent call last):
      File "/Users/hkormano/Library/Python/2.7/lib/python/site-packages/nose/case.py", line 197, in runTest
        self.test(*self.arg)
      File "/Users/hkormano/Projects/sqlalchemy/test/orm/test_sharded_m2m.py", line 60, in test_update_many_to_many_sharded__save_junction_table_directly
        session.commit()
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 954, in commit
        self.transaction.commit()
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 467, in commit
        self._prepare_impl()
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 447, in _prepare_impl
        self.session.flush()
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2313, in flush
        self._flush(objects)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2440, in _flush
        transaction.rollback(_capture_exception=True)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/util/langhelpers.py", line 66, in __exit__
        compat.reraise(exc_type, exc_value, exc_tb)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2404, in _flush
        flush_context.execute()
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 398, in execute
        rec.execute(self)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 512, in execute
        self.dependency_processor.process_deletes(uow, states)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/dependency.py", line 1025, in process_deletes
        secondary_update, secondary_delete)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/dependency.py", line 1096, in _run_crud
        connection = uowcommit.transaction.connection(self.mapper)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 293, in connection
        bind = self.session.get_bind(bindkey, **kwargs)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/ext/horizontal_shard.py", line 237, in get_bind
        mapper, instance, clause=clause)
      File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/ext/horizontal_shard.py", line 215, in _choose_shard_and_assign
        shard_id = self.shard_chooser(mapper, instance, **kw)
      File "/Users/hkormano/Projects/sqlalchemy/test/orm/test_sharded_m2m.py", line 71, in shard_chooser
        raise Exception('Cannot determine shard')
    Exception: Cannot determine shard