wiki:BwUserEmailChange

If you ever need to change a user's email address, you will need to hot-fix this on the live database, because Rox does not have a GUI for this (as far as we know). This is how to do it.

The email address is stored in two places:

  1. BW_MAIN.user.email (plain text)
  2. BW_MAIN.members.email (as foreign ID from BW_CRYPTED.cryptedfields)

Steps for updating email address

  1. Check existing contents
    mysql> SELECT email FROM BW_MAIN.user WHERE handle = 'ThatUser';
    
    +------------------+
    | email            |
    +------------------+
    | user@example.org |
    +------------------+
    
    mysql> SELECT email FROM BW_MAIN.members WHERE username = 'ThatUser';
    
    +-------+
    | email |
    +-------+
    |   666 |
    +-------+
    
    mysql> SELECT AdminCryptedValue, MemberCryptedValue, temporary_uncrypted_buffer, isCrypted FROM BW_CRYPTED.cryptedfields WHERE id = 666;
    
    +-------------------------------------------------+---------------------------------------------------+----------------------------+-----------+
    | AdminCryptedValue                               | MemberCryptedValue                                | temporary_uncrypted_buffer | isCrypted |
    +-------------------------------------------------+---------------------------------------------------+----------------------------+-----------+
    | <admincrypted>user%40example.org</admincrypted> | <membercrypted>user%40example.org</membercrypted> | user@example.org           | always    |
    +-------------------------------------------------+---------------------------------------------------+----------------------------+-----------+
    # Note: Depending in "isCrypted" setting, "MemberCryptedValue" might be equal to "temporary_uncrypted_buffer" (no wrapping tag).
    
  2. Update email address
    mysql> UPDATE BW_MAIN.user SET email = 'that.user@example.org' WHERE handle = 'ThatUser';
    mysql> UPDATE BW_CRYPTED.cryptedfields SET AdminCryptedValue = '<admincrypted>that.user%40example.org</admincrypted>' WHERE id = 666;
    
    # If isCrypted == "always":
    mysql> UPDATE BW_CRYPTED.cryptedfields SET MemberCryptedValue = '<membercrypted>that.user%40example.org</membercrypted>' WHERE id = 666;
    mysql> UPDATE BW_CRYPTED.cryptedfields SET temporary_uncrypted_buffer = NULL WHERE id = 666;
    
    # If isCrypted == "not crypted":
    mysql> UPDATE BW_CRYPTED.cryptedfields SET MemberCryptedValue = 'that.user@example.org' WHERE id = 666;
    mysql> UPDATE BW_CRYPTED.cryptedfields SET temporary_uncrypted_buffer = 'that.user@example.org' WHERE id = 666;
    
Last modified 4 years ago Last modified on Oct 15, 2012 6:46:12 AM