Opened 3 years ago

Closed 3 years ago

#3504 closed undecided (fixed)

PosgreSQL foreign key error upon reconfigure

Reported by: gracinet Owned by: gracinet
Priority: major Milestone: 0.9.0
Version: 0.9.0b7 Keywords:
Cc:

Description (last modified by rutsky)

Got this in a reconfigure (buildmaster DB is on PostgresSQL):

         File "/srv/buildbot9/buildbot-git/master/buildbot/data/builders.py", line 110, in updateBuilderList
            masterid=masterid, builderid=builderid)
          File "/srv/buildbot9/local/lib/python2.7/site-packages/twisted/python/threadpool.py", line 246, in inContext
            result = inContext.theWork()
          File "/srv/buildbot9/local/lib/python2.7/site-packages/twisted/python/threadpool.py", line 262, in <lambda>
            inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
          File "/srv/buildbot9/local/lib/python2.7/site-packages/twisted/python/context.py", line 118, in callWithContext
            return self.currentContext().callWithContext(ctx, func, *args, **kw)
          File "/srv/buildbot9/local/lib/python2.7/site-packages/twisted/python/context.py", line 81, in callWithContext
            return func(*args,**kw)
          File "/srv/buildbot9/buildbot-git/master/buildbot/db/pool.py", line 174, in __thd
            rv = callable(arg, *args, **kwargs)
          File "/srv/buildbot9/buildbot-git/master/buildbot/db/builders.py", line 96, in thd
            & (tbl.c.masterid == masterid))))
          File "/srv/buildbot9/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
            return meth(self, multiparams, params)
          File "/srv/buildbot9/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
            return connection._execute_clauseelement(self, multiparams, params)
          File "/srv/buildbot9/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
            compiled_sql, distilled_params
          File "/srv/buildbot9/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
            context)
          File "/srv/buildbot9/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
            exc_info
          File "/srv/buildbot9/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
            reraise(type(exception), exception, tb=exc_tb, cause=cause)
          File "/srv/buildbot9/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
            context)
          File "/srv/buildbot9/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
            cursor.execute(statement, parameters)
        sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) update or delete on table "builder_masters" violates foreign key constraint "configured_workers_buildermasterid_fkey" on table "configured_workers"
        DETAIL:  Key (id)=(157) is still referenced from table "configured_workers".
         [SQL: 'DELETE FROM builder_masters WHERE builder_masters.builderid = %(builderid_1)s AND builder_masters.masterid = %(masterid_1)s'] [parameters: {'builderid_1': 4, 'masterid_1': 1}]

2016-03-25 13:31:56+0100 [-] WARNING: reconfig partially applied; master may malfunction

The strange part is I don't believe this reconfig removed any builder, but it added some. On top of that, it seems that builder removal either needs to

  • use cascading deletes (but that would be apply in all cases)
  • better be scheduled in that case

Change History (8)

comment:1 Changed 3 years ago by gracinet

oops used the wrong block-quoting in traceback

comment:2 Changed 3 years ago by rutsky

  • Description modified (diff)

comment:3 Changed 3 years ago by tardyp

  • Milestone changed from undecided to 0.9.0

comment:4 Changed 3 years ago by gracinet

I can reproduce reliably, but this happens only with buildbot reconfig. Given that buildbot restart deletes the configured_workers row in that case, I'd wager we can simply use a foreign key with cascading delete option.

SQLAlchemy supports this, at least since version 0.7: http://docs.sqlalchemy.org/en/latest/core/constraints.html#on-update-and-on-delete http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html?highlight=foreignkey#on-update-and-on-delete

Here's the paragraph mentioning them in PostgreSQL documentation (it's been there forever): http://www.postgresql.org/docs/9.5/static/ddl-constraints.html#DDL-CONSTRAINTS-FK

I don't now how exactly to handle upgrade of the DB (if needed at all in these beta series)

comment:5 Changed 3 years ago by gracinet

To reproduce with the sample conf:

createdb bb_fktest`
buildbot create-master --db postgresql:///bb_fktest fktest
cd fktest
mv master.cfg.sample master.cfg
buildbot start
sed -i s/runtests/runtests2/g master.cfg
buildbot reconfig

comment:6 Changed 3 years ago by rutsky

  • Owner set to gracinet
  • Status changed from new to assigned

comment:7 Changed 3 years ago by gracinet

I'll add the cascading deletes on such many2many relationships (heard of something similar with changes)

comment:8 Changed 3 years ago by tardyp

  • Resolution set to fixed
  • Status changed from assigned to closed
Note: See TracTickets for help on using tickets.