[Buildbot] #3220: sqlalchemy.exc.IntegrityError: (IntegrityError) on pushes to github when moving from launchpad

[Buildbot trac]

#3220: sqlalchemy.exc.IntegrityError: (IntegrityError) on pushes to github when
moving from launchpad
----------------------+-----------------------
Reporter:  serg       |      Owner:
    Type:  undecided  |     Status:  new
Priority:  major      |  Milestone:  undecided
 Version:  0.8.10     |   Keywords:
----------------------+-----------------------
 There are two tables (involved in this bug): users and users_info
 The first looks like
 {{{
 +-----+------------+-------------+-------------+
 | uid | identifier | bb_username | bb_password |
 +-----+------------+-------------+-------------+
 |   1 | foo at bar.com | NULL        | NULL       |
 ...
 }}}
 The other looks like
 {{{
 +-----+-----------+-------------+
 | uid | attr_type | attr_data   |
 +-----+-----------+-------------+
 |   1 | bzr       | foo at bar.com |
 ....
 }}}
 The method {{{UsersConnectorComponent.findUserByAttr}}} does the
 following:
 {{{
 #!python
     # try to find the user
     q = sa.select([ tbl_info.c.uid ],
                 whereclause=and_(tbl_info.c.attr_type == attr_type,
                         tbl_info.c.attr_data == attr_data))
 }}}
 which corresponds to, for example,
 {{{
   SELECT uid from users_info where attr='git' and attr_data='foo at bar.com';
 }}}

 That fails, it assumes that the user does not exists and tries to create
 it:
 {{{
 #!python
       r = conn.execute(tbl.insert(), dict(identifier=identifier))
       uid = r.inserted_primary_key[0]

       conn.execute(tbl_info.insert(),
               dict(uid=uid, attr_type=attr_type,
                    attr_data=attr_data))
 }}}
 which corresponds to
 {{{
   INSERT users (identifier) VALUES ('foo at bar.com');
   INSERT users_info VALUES (LAST_INSERT_ID(), 'git', 'foo at bar.com');
 }}}

 This obviously fails, because the user '''does''' exist, but only for
 '''bzr''' attr_type,
 not for '''git'''. And there's {{{UNIQUE(identifier)}}} constraint in the
 first table.

 Here's the patch:
 {{{
 --- /usr/lib/python2.7/dist-packages/buildbot/users.py  2015-03-04
 12:13:42.067065164 +0200
 +++ /usr/lib/python2.7/dist-packages/buildbot/users.py  2015-03-04
 12:13:07.943241931 +0200
 @@ -49,8 +49,17 @@
              # time from the perspective of other masters.
              transaction = conn.begin()
              try:
 -                r = conn.execute(tbl.insert(),
 dict(identifier=identifier))
 -                uid = r.inserted_primary_key[0]
 +                try:
 +                    r = conn.execute(tbl.insert(),
 dict(identifier=identifier))
 +                    uid = r.inserted_primary_key[0]
 +                except (sa.exc.IntegrityError):
 +                    # user already exists
 +                    q = tbl.select(whereclause=(tbl.c.identifier ==
 identifier))
 +                    row = conn.execute(q).fetchone()
 +                    if row:
 +                        uid = row.uid
 +                    else:
 +                        raise

                  conn.execute(tbl_info.insert(),
                          dict(uid=uid, attr_type=attr_type,
 }}}

 It's for 0.8.8. The latest 0.8.10 code is slightly different, but the
 logic
 is the same and the bug is still there.

--
Ticket URL: <http://trac.buildbot.net/ticket/3220>
Buildbot <http://buildbot.net/>
Buildbot: build/test automation