search

kvesteri / sqlalchemy-continuum

Versioning extension for SQLAlchemy.
BSD 3-Clause "New" or "Revised" License
582 stars 125 forks source link

Sequence failure with MySQL/MariaDB in Transaction class #264

Open Glandos opened 2 years ago

Glandos commented 2 years ago

In https://github.com/spiral-project/ihatemoney/ we still support MariaDB, and are trying to upgrade to SQLAlchemy 1.4 However, CI tests are failing with MariaDB 10.3 with the following log:

2022-01-25T21:52:21.9386170Z self = <sqlalchemy.engine.base.Connection object at 0x7fc169e3ded0>
2022-01-25T21:52:21.9387149Z dialect = <sqlalchemy.dialects.mysql.pymysql.MySQLDialect_pymysql object at 0x7fc16a315510>
2022-01-25T21:52:21.9388315Z constructor = <bound method DefaultExecutionContext._init_compiled of <class 'sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb'>>
2022-01-25T21:52:21.9390210Z statement = <sqlalchemy.dialects.mysql.mysqldb.MySQLCompiler_mysqldb object at 0x7fc169e542d0>
2022-01-25T21:52:21.9390909Z parameters = [{'remote_addr': None}]
2022-01-25T21:52:21.9391974Z execution_options = immutabledict({'autocommit': True, 'compiled_cache': {(<sqlalchemy.dialects.mysql.pymysql.MySQLDialect_pymysql object ...emote_addr',), False, False), <sqlalchemy.dialects.mysql.mysqldb.MySQLCompiler_mysqldb object at 0x7fc169e542d0>, 1]}})
2022-01-25T21:52:21.9393494Z args = (<sqlalchemy.dialects.mysql.mysqldb.MySQLCompiler_mysqldb object at 0x7fc169e542d0>, [{'remote_addr': None}], <sqlalchemy.sql.dml.Insert object at 0x7fc169e54e50>, [])
2022-01-25T21:52:21.9394126Z kw = {'cache_hit': symbol('CACHE_MISS')}
2022-01-25T21:52:21.9394535Z branched = <sqlalchemy.engine.base.Connection object at 0x7fc169e3ded0>
2022-01-25T21:52:21.9395180Z conn = <sqlalchemy.pool.base._ConnectionFairy object at 0x7fc169e3db10>
2022-01-25T21:52:21.9395476Z 
2022-01-25T21:52:21.9395922Z     def _execute_context(
2022-01-25T21:52:21.9396227Z         self,
2022-01-25T21:52:21.9396519Z         dialect,
2022-01-25T21:52:21.9396797Z         constructor,
2022-01-25T21:52:21.9397281Z         statement,
2022-01-25T21:52:21.9397586Z         parameters,
2022-01-25T21:52:21.9397914Z         execution_options,
2022-01-25T21:52:21.9398223Z         *args,
2022-01-25T21:52:21.9398836Z         **kw
2022-01-25T21:52:21.9399283Z     ):
2022-01-25T21:52:21.9399634Z         """Create an :class:`.ExecutionContext` and execute, returning
2022-01-25T21:52:21.9400193Z         a :class:`_engine.CursorResult`."""
2022-01-25T21:52:21.9400505Z     
2022-01-25T21:52:21.9400767Z         branched = self
2022-01-25T21:52:21.9401083Z         if self.__branch_from:
2022-01-25T21:52:21.9401600Z             # if this is a "branched" connection, do everything in terms
2022-01-25T21:52:21.9402033Z             # of the "root" connection, *except* for .close(), which is
2022-01-25T21:52:21.9402597Z             # the only feature that branching provides
2022-01-25T21:52:21.9402938Z             self = self.__branch_from
2022-01-25T21:52:21.9403230Z     
2022-01-25T21:52:21.9403630Z         try:
2022-01-25T21:52:21.9403932Z             conn = self._dbapi_connection
2022-01-25T21:52:21.9404245Z             if conn is None:
2022-01-25T21:52:21.9404561Z                 conn = self._revalidate_connection()
2022-01-25T21:52:21.9405033Z     
2022-01-25T21:52:21.9405470Z             context = constructor(
2022-01-25T21:52:21.9406229Z >               dialect, self, conn, execution_options, *args, **kw
2022-01-25T21:52:21.9408689Z             )
2022-01-25T21:52:21.9408818Z 
2022-01-25T21:52:21.9409299Z /home/runner/work/ihatemoney/ihatemoney/.tox/py/lib/python3.7/site-packages/sqlalchemy/engine/base.py:1703: 
2022-01-25T21:52:21.9409652Z _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
2022-01-25T21:52:21.9409794Z 
2022-01-25T21:52:21.9410193Z cls = <class 'sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb'>
2022-01-25T21:52:21.9410824Z dialect = <sqlalchemy.dialects.mysql.pymysql.MySQLDialect_pymysql object at 0x7fc16a315510>
2022-01-25T21:52:21.9411220Z connection = <sqlalchemy.engine.base.Connection object at 0x7fc169e3ded0>
2022-01-25T21:52:21.9411592Z dbapi_connection = <sqlalchemy.pool.base._ConnectionFairy object at 0x7fc169e3db10>
2022-01-25T21:52:21.9412843Z execution_options = immutabledict({'autocommit': True, 'compiled_cache': {(<sqlalchemy.dialects.mysql.pymysql.MySQLDialect_pymysql object ...emote_addr',), False, False), <sqlalchemy.dialects.mysql.mysqldb.MySQLCompiler_mysqldb object at 0x7fc169e542d0>, 1]}})
2022-01-25T21:52:21.9413991Z compiled = <sqlalchemy.dialects.mysql.mysqldb.MySQLCompiler_mysqldb object at 0x7fc169e542d0>
2022-01-25T21:52:21.9414437Z parameters = [{'remote_addr': None}]
2022-01-25T21:52:21.9414735Z invoked_statement = <sqlalchemy.sql.dml.Insert object at 0x7fc169e54e50>
2022-01-25T21:52:21.9415118Z extracted_parameters = [], cache_hit = symbol('CACHE_MISS')
2022-01-25T21:52:21.9415288Z 
2022-01-25T21:52:21.9415458Z     @classmethod
2022-01-25T21:52:21.9415641Z     def _init_compiled(
2022-01-25T21:52:21.9415833Z         cls,
2022-01-25T21:52:21.9415993Z         dialect,
2022-01-25T21:52:21.9416183Z         connection,
2022-01-25T21:52:21.9416364Z         dbapi_connection,
2022-01-25T21:52:21.9416575Z         execution_options,
2022-01-25T21:52:21.9416776Z         compiled,
2022-01-25T21:52:21.9416946Z         parameters,
2022-01-25T21:52:21.9417143Z         invoked_statement,
2022-01-25T21:52:21.9417337Z         extracted_parameters,
2022-01-25T21:52:21.9417561Z         cache_hit=CACHING_DISABLED,
2022-01-25T21:52:21.9418124Z     ):
2022-01-25T21:52:21.9418610Z         """Initialize execution context for a Compiled construct."""
2022-01-25T21:52:21.9419033Z     
2022-01-25T21:52:21.9419225Z         self = cls.__new__(cls)
2022-01-25T21:52:21.9419484Z         self.root_connection = connection
2022-01-25T21:52:21.9419755Z         self._dbapi_connection = dbapi_connection
2022-01-25T21:52:21.9420049Z         self.dialect = connection.dialect
2022-01-25T21:52:21.9420341Z         self.extracted_parameters = extracted_parameters
2022-01-25T21:52:21.9420656Z         self.invoked_statement = invoked_statement
2022-01-25T21:52:21.9420931Z         self.compiled = compiled
2022-01-25T21:52:21.9421166Z         self.cache_hit = cache_hit
2022-01-25T21:52:21.9421530Z     
2022-01-25T21:52:21.9422118Z         self.execution_options = execution_options
2022-01-25T21:52:21.9422407Z     
2022-01-25T21:52:21.9422615Z         self._is_future_result = (
2022-01-25T21:52:21.9422879Z             connection._is_future
2022-01-25T21:52:21.9423202Z             or self.execution_options.get("future_result", False)
2022-01-25T21:52:21.9423466Z         )
2022-01-25T21:52:21.9423664Z     
2022-01-25T21:52:21.9423891Z         self.result_column_struct = (
2022-01-25T21:52:21.9424212Z             compiled._result_columns,
2022-01-25T21:52:21.9424554Z             compiled._ordered_columns,
2022-01-25T21:52:21.9424849Z             compiled._textual_ordered_columns,
2022-01-25T21:52:21.9425384Z             compiled._loose_column_name_matching,
2022-01-25T21:52:21.9425781Z         )
2022-01-25T21:52:21.9426010Z         self.isinsert = compiled.isinsert
2022-01-25T21:52:21.9426256Z         self.isupdate = compiled.isupdate
2022-01-25T21:52:21.9426686Z         self.isdelete = compiled.isdelete
2022-01-25T21:52:21.9426936Z         self.is_text = compiled.isplaintext
2022-01-25T21:52:21.9427152Z     
2022-01-25T21:52:21.9427393Z         if self.isinsert or self.isupdate or self.isdelete:
2022-01-25T21:52:21.9427801Z             self.is_crud = True
2022-01-25T21:52:21.9428103Z             self._is_explicit_returning = bool(compiled.statement._returning)
2022-01-25T21:52:21.9428399Z             self._is_implicit_returning = bool(
2022-01-25T21:52:21.9428711Z                 compiled.returning and not compiled.statement._returning
2022-01-25T21:52:21.9428974Z             )
2022-01-25T21:52:21.9429137Z     
2022-01-25T21:52:21.9429497Z         if not parameters:
2022-01-25T21:52:21.9429715Z             self.compiled_parameters = [
2022-01-25T21:52:21.9429966Z                 compiled.construct_params(
2022-01-25T21:52:21.9430229Z                     extracted_parameters=extracted_parameters
2022-01-25T21:52:21.9430459Z                 )
2022-01-25T21:52:21.9430639Z             ]
2022-01-25T21:52:21.9430808Z         else:
2022-01-25T21:52:21.9431025Z             self.compiled_parameters = [
2022-01-25T21:52:21.9431262Z                 compiled.construct_params(
2022-01-25T21:52:21.9431482Z                     m,
2022-01-25T21:52:21.9431674Z                     _group_number=grp,
2022-01-25T21:52:21.9432020Z                     extracted_parameters=extracted_parameters,
2022-01-25T21:52:21.9432268Z                 )
2022-01-25T21:52:21.9432484Z                 for grp, m in enumerate(parameters)
2022-01-25T21:52:21.9432704Z             ]
2022-01-25T21:52:21.9432862Z     
2022-01-25T21:52:21.9433092Z             self.executemany = len(parameters) > 1
2022-01-25T21:52:21.9433295Z     
2022-01-25T21:52:21.9433537Z         # this must occur before create_cursor() since the statement
2022-01-25T21:52:21.9433840Z         # has to be regexed in some cases for server side cursor
2022-01-25T21:52:21.9434072Z         if util.py2k:
2022-01-25T21:52:21.9434341Z             self.unicode_statement = util.text_type(compiled.string)
2022-01-25T21:52:21.9434579Z         else:
2022-01-25T21:52:21.9434818Z             self.unicode_statement = compiled.string
2022-01-25T21:52:21.9435026Z     
2022-01-25T21:52:21.9435238Z         self.cursor = self.create_cursor()
2022-01-25T21:52:21.9435449Z     
2022-01-25T21:52:21.9435707Z         if self.compiled.insert_prefetch or self.compiled.update_prefetch:
2022-01-25T21:52:21.9436003Z             if self.executemany:
2022-01-25T21:52:21.9436239Z                 self._process_executemany_defaults()
2022-01-25T21:52:21.9436462Z             else:
2022-01-25T21:52:21.9436682Z >               self._process_executesingle_defaults()
2022-01-25T21:52:21.9436844Z 
2022-01-25T21:52:21.9437695Z /home/runner/work/ihatemoney/ihatemoney/.tox/py/lib/python3.7/site-packages/sqlalchemy/engine/default.py:1019: 
2022-01-25T21:52:21.9438049Z _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
2022-01-25T21:52:21.9438202Z 
2022-01-25T21:52:21.9438661Z self = <sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb object at 0x7fc169e5ed10>
2022-01-25T21:52:21.9438957Z 
2022-01-25T21:52:21.9439175Z     def _process_executesingle_defaults(self):
2022-01-25T21:52:21.9439668Z         key_getter = self.compiled._key_getters_for_crud_column[2]
2022-01-25T21:52:21.9441522Z         self.current_parameters = (
2022-01-25T21:52:21.9441790Z             compiled_parameters
2022-01-25T21:52:21.9442068Z         ) = self.compiled_parameters[0]
2022-01-25T21:52:21.9442296Z     
2022-01-25T21:52:21.9442560Z         for c in self.compiled.insert_prefetch:
2022-01-25T21:52:21.9442908Z             if c.default and not c.default.is_sequence and c.default.is_scalar:
2022-01-25T21:52:21.9443232Z                 val = c.default.arg
2022-01-25T21:52:21.9443470Z             else:
2022-01-25T21:52:21.9443714Z >               val = self.get_insert_default(c)
2022-01-25T21:52:21.9443890Z 
2022-01-25T21:52:21.9444751Z /home/runner/work/ihatemoney/ihatemoney/.tox/py/lib/python3.7/site-packages/sqlalchemy/engine/default.py:1892: 
2022-01-25T21:52:21.9445506Z _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
2022-01-25T21:52:21.9445662Z 
2022-01-25T21:52:21.9446418Z self = <sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb object at 0x7fc169e5ed10>
2022-01-25T21:52:21.9447095Z column = Column('id', BigInteger(), table=<transaction>, primary_key=True, nullable=False, default=Sequence('transaction_id_seq', metadata=MetaData()))
2022-01-25T21:52:21.9447380Z 
2022-01-25T21:52:21.9447592Z     def get_insert_default(self, column):
2022-01-25T21:52:21.9448080Z         if column.default is None:
2022-01-25T21:52:21.9448321Z             return None
2022-01-25T21:52:21.9448501Z         else:
2022-01-25T21:52:21.9448771Z >           return self._exec_default(column, column.default, column.type)
2022-01-25T21:52:21.9449126Z 
2022-01-25T21:52:21.9449552Z /home/runner/work/ihatemoney/ihatemoney/.tox/py/lib/python3.7/site-packages/sqlalchemy/engine/default.py:1836: 
2022-01-25T21:52:21.9450091Z _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
2022-01-25T21:52:21.9450235Z 
2022-01-25T21:52:21.9450564Z self = <sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb object at 0x7fc169e5ed10>
2022-01-25T21:52:21.9451560Z column = Column('id', BigInteger(), table=<transaction>, primary_key=True, nullable=False, default=Sequence('transaction_id_seq', metadata=MetaData()))
2022-01-25T21:52:21.9452089Z default = Sequence('transaction_id_seq', metadata=MetaData())
2022-01-25T21:52:21.9452335Z type_ = BigInteger()
2022-01-25T21:52:21.9452466Z 
2022-01-25T21:52:21.9452689Z     def _exec_default(self, column, default, type_):
2022-01-25T21:52:21.9452927Z         if default.is_sequence:
2022-01-25T21:52:21.9453186Z >           return self.fire_sequence(default, type_)
2022-01-25T21:52:21.9453345Z 
2022-01-25T21:52:21.9453918Z /home/runner/work/ihatemoney/ihatemoney/.tox/py/lib/python3.7/site-packages/sqlalchemy/engine/default.py:1697: 
2022-01-25T21:52:21.9454282Z _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
2022-01-25T21:52:21.9454427Z 
2022-01-25T21:52:21.9454756Z self = <sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb object at 0x7fc169e5ed10>
2022-01-25T21:52:21.9455299Z seq = Sequence('transaction_id_seq', metadata=MetaData()), type_ = BigInteger()
2022-01-25T21:52:21.9455502Z 
2022-01-25T21:52:21.9455696Z     def fire_sequence(self, seq, type_):
2022-01-25T21:52:21.9456103Z         return self._execute_scalar(
2022-01-25T21:52:21.9456294Z             (
2022-01-25T21:52:21.9456701Z                 "select nextval(%s)"
2022-01-25T21:52:21.9457148Z                 % self.identifier_preparer.format_sequence(seq)
2022-01-25T21:52:21.9457371Z             ),
2022-01-25T21:52:21.9457554Z >           type_,
2022-01-25T21:52:21.9457722Z         )
2022-01-25T21:52:21.9457830Z 
2022-01-25T21:52:21.9458386Z /home/runner/work/ihatemoney/ihatemoney/.tox/py/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py:1153: 
2022-01-25T21:52:21.9459001Z _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
2022-01-25T21:52:21.9459149Z 
2022-01-25T21:52:21.9459495Z self = <sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb object at 0x7fc169e5ed10>
2022-01-25T21:52:21.9459999Z stmt = 'select nextval(transaction_id_seq)', type_ = BigInteger()
2022-01-25T21:52:21.9460265Z parameters = {}
2022-01-25T21:52:21.9460400Z 
2022-01-25T21:52:21.9460626Z     def _execute_scalar(self, stmt, type_, parameters=None):
2022-01-25T21:52:21.9460951Z         """Execute a string statement on the current cursor, returning a
2022-01-25T21:52:21.9461204Z         scalar result.
2022-01-25T21:52:21.9461400Z     
2022-01-25T21:52:21.9461664Z         Used to fire off sequences, default phrases, and "select lastrowid"
2022-01-25T21:52:21.9462001Z         types of statements individually or in the context of a parent INSERT
2022-01-25T21:52:21.9462289Z         or UPDATE statement.
2022-01-25T21:52:21.9462475Z     
2022-01-25T21:52:21.9462696Z         """
2022-01-25T21:52:21.9462856Z     
2022-01-25T21:52:21.9463058Z         conn = self.root_connection
2022-01-25T21:52:21.9463270Z         if (
2022-01-25T21:52:21.9463480Z             isinstance(stmt, util.text_type)
2022-01-25T21:52:21.9463781Z             and not self.dialect.supports_unicode_statements
2022-01-25T21:52:21.9464011Z         ):
2022-01-25T21:52:21.9464240Z             stmt = self.dialect._encoder(stmt)[0]
2022-01-25T21:52:21.9464445Z     
2022-01-25T21:52:21.9464688Z         if "schema_translate_map" in self.execution_options:
2022-01-25T21:52:21.9465000Z             schema_translate_map = self.execution_options.get(
2022-01-25T21:52:21.9465263Z                 "schema_translate_map", {}
2022-01-25T21:52:21.9465479Z             )
2022-01-25T21:52:21.9465639Z     
2022-01-25T21:52:21.9465893Z             rst = self.identifier_preparer._render_schema_translates
2022-01-25T21:52:21.9466195Z             stmt = rst(stmt, schema_translate_map)
2022-01-25T21:52:21.9466407Z     
2022-01-25T21:52:21.9466601Z         if not parameters:
2022-01-25T21:52:21.9466830Z             if self.dialect.positional:
2022-01-25T21:52:21.9467134Z                 parameters = self.dialect.execute_sequence_format()
2022-01-25T21:52:21.9467373Z             else:
2022-01-25T21:52:21.9467662Z                 parameters = {}
2022-01-25T21:52:21.9467879Z     
2022-01-25T21:52:21.9468124Z >       conn._cursor_execute(self.cursor, stmt, parameters, context=self)
2022-01-25T21:52:21.9468321Z 
2022-01-25T21:52:21.9468754Z /home/runner/work/ihatemoney/ihatemoney/.tox/py/lib/python3.7/site-packages/sqlalchemy/engine/default.py:1290: 
2022-01-25T21:52:21.9469110Z _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
2022-01-25T21:52:21.9469257Z 
2022-01-25T21:52:21.9469529Z self = <sqlalchemy.engine.base.Connection object at 0x7fc169e3ded0>
2022-01-25T21:52:21.9470028Z cursor = <pymysql.cursors.Cursor object at 0x7fc169ed06d0>
2022-01-25T21:52:21.9470433Z statement = 'select nextval(transaction_id_seq)', parameters = {}
2022-01-25T21:52:21.9470840Z context = <sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb object at 0x7fc169e5ed10>
2022-01-25T21:52:21.9471115Z 
2022-01-25T21:52:21.9471370Z     def _cursor_execute(self, cursor, statement, parameters, context=None):
2022-01-25T21:52:21.9471676Z         """Execute a statement + params on the given cursor.
2022-01-25T21:52:21.9471903Z     
2022-01-25T21:52:21.9472124Z         Adds appropriate logging and exception handling.
2022-01-25T21:52:21.9472353Z     
2022-01-25T21:52:21.9472677Z         This method is used by DefaultDialect for special-case
2022-01-25T21:52:21.9472966Z         executions, such as for sequences and column defaults.
2022-01-25T21:52:21.9473267Z         The path of statement execution in the majority of cases
2022-01-25T21:52:21.9473526Z         terminates at _execute_context().
2022-01-25T21:52:21.9473734Z     
2022-01-25T21:52:21.9473906Z         """
2022-01-25T21:52:21.9474218Z         if self._has_events or self.engine._has_events:
2022-01-25T21:52:21.9474517Z             for fn in self.dispatch.before_cursor_execute:
2022-01-25T21:52:21.9474817Z                 statement, parameters = fn(
2022-01-25T21:52:21.9475098Z >                   self, cursor, statement, parameters, context, False
2022-01-25T21:52:21.9475326Z                 )
2022-01-25T21:52:21.9475443Z 
2022-01-25T21:52:21.9475874Z /home/runner/work/ihatemoney/ihatemoney/.tox/py/lib/python3.7/site-packages/sqlalchemy/engine/base.py:1865: 
2022-01-25T21:52:21.9476210Z _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
2022-01-25T21:52:21.9476334Z 
2022-01-25T21:52:21.9476594Z conn = <sqlalchemy.engine.base.Connection object at 0x7fc169e3ded0>
2022-01-25T21:52:21.9476933Z cursor = <pymysql.cursors.Cursor object at 0x7fc169ed06d0>
2022-01-25T21:52:21.9477314Z statement = 'select nextval(transaction_id_seq)', parameters = {}
2022-01-25T21:52:21.9477734Z context = <sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb object at 0x7fc169e5ed10>
2022-01-25T21:52:21.9478081Z executemany = False
2022-01-25T21:52:21.9478207Z 
2022-01-25T21:52:21.9478403Z     def wrap_before_cursor_execute(
2022-01-25T21:52:21.9478671Z         conn, cursor, statement, parameters, context, executemany
2022-01-25T21:52:21.9478916Z     ):
2022-01-25T21:52:21.9479075Z         orig_fn(
2022-01-25T21:52:21.9479258Z             conn,
2022-01-25T21:52:21.9479443Z             cursor,
2022-01-25T21:52:21.9479617Z             statement,
2022-01-25T21:52:21.9479817Z             parameters,
2022-01-25T21:52:21.9479994Z             context,
2022-01-25T21:52:21.9480189Z >           executemany,
2022-01-25T21:52:21.9480358Z         )
2022-01-25T21:52:21.9480466Z 
2022-01-25T21:52:21.9480891Z /home/runner/work/ihatemoney/ihatemoney/.tox/py/lib/python3.7/site-packages/sqlalchemy/engine/events.py:134: 
2022-01-25T21:52:21.9481218Z _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
2022-01-25T21:52:21.9481362Z 
2022-01-25T21:52:21.9481672Z self = <ihatemoney.versioning.ConditionalVersioningManager object at 0x7fc16e1e63d0>
2022-01-25T21:52:21.9482066Z conn = <sqlalchemy.engine.base.Connection object at 0x7fc169e3ded0>
2022-01-25T21:52:21.9482400Z cursor = <pymysql.cursors.Cursor object at 0x7fc169ed06d0>
2022-01-25T21:52:21.9482884Z statement = 'select nextval(transaction_id_seq)', parameters = {}
2022-01-25T21:52:21.9483307Z context = <sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb object at 0x7fc169e5ed10>
2022-01-25T21:52:21.9483665Z executemany = False
2022-01-25T21:52:21.9483793Z 
2022-01-25T21:52:21.9483979Z     def track_association_operations(
2022-01-25T21:52:21.9484273Z         self, conn, cursor, statement, parameters, context, executemany
2022-01-25T21:52:21.9484502Z     ):
2022-01-25T21:52:21.9484673Z         """
2022-01-25T21:52:21.9484912Z         Track association operations and adds the generated history
2022-01-25T21:52:21.9485223Z         association operations to pending_statements list.
2022-01-25T21:52:21.9485468Z         """
2022-01-25T21:52:21.9485629Z         if (
2022-01-25T21:52:21.9485933Z             not self.options['versioning'] and
2022-01-25T21:52:21.9486250Z             not self.options['native_versioning']
2022-01-25T21:52:21.9486475Z         ):
2022-01-25T21:52:21.9486642Z             return
2022-01-25T21:52:21.9486818Z     
2022-01-25T21:52:21.9486992Z         op = None
2022-01-25T21:52:21.9487182Z         if context.isinsert:
2022-01-25T21:52:21.9487411Z             op = Operation.INSERT
2022-01-25T21:52:21.9487672Z         elif context.isdelete:
2022-01-25T21:52:21.9488002Z             op = Operation.DELETE
2022-01-25T21:52:21.9488192Z     
2022-01-25T21:52:21.9488378Z         if op is not None:
2022-01-25T21:52:21.9488700Z >           table_name = statement.split(' ')[2]
2022-01-25T21:52:21.9488949Z E           IndexError: list index out of range
2022-01-25T21:52:21.9489100Z 
2022-01-25T21:52:21.9489554Z /home/runner/work/ihatemoney/ihatemoney/.tox/py/lib/python3.7/site-packages/sqlalchemy_continuum/manager.py:454: IndexError

The log is quite extensive, so I asked the question to the SQLAlchemy community on https://github.com/sqlalchemy/sqlalchemy/discussions/7649

The maintainer answer led me to https://github.com/kvesteri/sqlalchemy-continuum/blame/2e53b5f927c7b601c1dab02b2c91c4dbb3b9ed15/sqlalchemy_continuum/transaction.py#L125 (but there is also https://github.com/kvesteri/sqlalchemy-continuum/blame/2e53b5f927c7b601c1dab02b2c91c4dbb3b9ed15/sqlalchemy_continuum/plugins/activity.py#L204) were explicit Sequence() are used, since the merge of https://github.com/kvesteri/sqlalchemy-continuum/commit/0bb36e02b9db7b65133c037ca993effbc78c299b. This was based on work in https://github.com/kvesteri/sqlalchemy-continuum/pull/118 that mentioned support for Oracle database.

According to the SQLAlchemy maintainer:

So somewhere you have a Sequence() object, if you aren't supporting Oracle database, I'd take that out entirely (and if you are supporting Oracle, Id look into using IDENTITY for most databases anyway, which we now support).

I tried to set optional=True as mentioned in https://github.com/kvesteri/sqlalchemy-continuum/pull/118#issuecomment-215553949 but this doesn't solve the issue. However, commenting the line solved this issue. So I have to request to change something here. Obviously the support for Oracle is needed, so maybe the sequence should be defined only when this backend is in use. I didn't look (yet?) into IDENTITY as mentioned, but I can't test Oracle by myself, so if someone has a clue on this, I'll be delighted.

In the meantime we may monkey-patch or use another transaction factory.

marksteward commented 2 years ago

Thanks for the investigation! I'm inclined to agree with zzzeek's comment that Sequence shouldn't be needed, but removing existing sequences will affect migrations, tests, etc. In the long-term, it might be better to wait for the MySQL dialect to support returning.

I think we might be able to reference context.invoked_statement.table.name and similar instead of parsing the generated SQL. However, we still ideally want to skip the nextval query (and any others) and it's not clear to me yet how to do that.

Glandos commented 2 years ago

As you already guessed, using table_name = context.invoked_statement.table.name makes our complete test suite pass. But I'm not sure that we are using "advanced" features like bulk insert. Do you think this small change could be integrated, as it obviously seems a better thing to do than splitting statement string?

marksteward commented 2 years ago

That's good to know! I just need to check whether we need to deduplicate the operations (we might be inserting twice per transaction with this change). I've run out of time this evening but will try to take a look tomorrow.

marksteward commented 2 years ago

Just to update here, substituting that line should be safe for typical uses, when the only table with a sequence is Continuum's Transaction table. The scenarios that may not be safe are when an association table (one used in a relationship with secondary) has an explicit sequence or schema. I think I need to write a test around this before I'm comfortable committing it.

almet commented 1 year ago

Hey, I'm not sure what to do about this. Is it fixed? Needs some work? Thanks!

Glandos commented 1 year ago

For now, I've monkeypatched, but this is quite ugly: https://github.com/spiral-project/ihatemoney/blob/master/ihatemoney/monkeypath_continuum.py#L40

I had to copy/paste the whole factory :/

marksteward commented 1 year ago

Having rewritten these tests for 2.0, I've realised that an association table with a sequence on shouldn't happen. So could you try the new sqla-2.0 branch? It should support 1.4 and above.

njoeres commented 1 year ago

We encountered a similar problem using Microsoft SQL Server. We are getting the following error message:

ProgrammingError: (pyodbc.ProgrammingError) ('42S02', "[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name 'transaction_id_seq'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")
[SQL: INSERT INTO [transaction] (id, remote_addr, issued_at) OUTPUT inserted.id VALUES (NEXT VALUE FOR transaction_id_seq, ?, ?)]

Are there any updates regarding this issue?

Versions we use: SQLAlchemy - 2.0.19 SQLAlchemy-Continuum - 1.4.0