[Buildbot] #3504: PosgreSQL foreign key error upon reconfigure

[Buildbot trac]

#3504: PosgreSQL foreign key error upon reconfigure
----------------------+------------------------
Reporter:  gracinet   |       Owner:
    Type:  undecided  |      Status:  new
Priority:  major      |   Milestone:  undecided
 Version:  0.9.0b7    |  Resolution:
Keywords:             |
----------------------+------------------------
Description changed by rutsky:

Old description:

> 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

New description:

 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

--

--
Ticket URL: <http://trac.buildbot.net/ticket/3504#comment:2>
Buildbot <http://buildbot.net/>
Buildbot: build/test automation