[Sqlalchemy-tickets] Issue #4376: Can't determine horizontal shard for many-to-many relationship (z
Brought to you by:
zzzeek
From: Hannele K. <iss...@bi...> - 2018-11-27 01:24:16
|
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 |