"duplicate entry in user_badges_user" when using a badge to more than one role. [#906862]

And here it is.
1. Create a badge (with image).
2. Set this badge for 2 roles.

-> error message
user warning: Duplicate entry '2-11-role' for key 1 query: INSERT INTO user_badges_user (uid, bid, type) (SELECT uid, 2, 'role' FROM users_roles WHERE rid=13) in .../sites/all/modules/user_badges/user_badges.module on line 612.

-> info:
"There was a problem saving roles to the database"

--------

Possible solutions:
- "REPLACE INTO" (only on mysql, I know..), or
- "INSERT ... ON DUPLICATE KEY UPDATE" (cross-db compatible?), or
- check if the table rows already exist, and add only those that are missing yet, or
- wrap the stuff in a try/catch block, and just ignore the warnings.. (didn't think much about that, actually)

Comments

Comment #1

donquixote CreditAttribution: donquixote commented 9 September 2010 at 19:21

This works:
in user_badges_save_roles()
user_badges.module, line 610 ff.

<?php
          $select = "
            SELECT ur.uid, %d, 'role'
            FROM {users_roles} ur
            LEFT JOIN {user_badges_user} ubu ON (ubu.uid=ur.uid AND ubu.bid=%d)
            WHERE ur.rid=%d AND ubu.uid IS NULL
          ";
          $success = $success && db_query("
            INSERT INTO {user_badges_user} (uid, bid, type) 
            ($select)
          ", $bid, $bid, $rid);
?>

Comment #2

NancyDru

she/her/hers

English

Boston

CreditAttribution: NancyDru commented 30 November 2010 at 21:08

Status:

Active

» Postponed (maintainer needs more info)

Please try the latest and see if this is resolved. Let me know.

Comment #3

NancyDru

she/her/hers

English

Boston

CreditAttribution: NancyDru commented 17 February 2011 at 17:49

Status:

Postponed (maintainer needs more info)

» Fixed

I have committed a fix to 6.x-1.x-dev.

Comment #4

donquixote CreditAttribution: donquixote commented 17 February 2011 at 19:26

Sorry if I did not reply. The project is full of these little features, where we are just happy if they work, and then move on to other tasks. I think we still have the patched file as mentioned in #1, and so far it seems to work.

I might give more feedback if one day this moves back into our "locus of attention"..