sync user.id and drupal_users.uid

[jywarren]

... thought it was synced!

[btbonval]

How does this out of sync express itself (symptoms) and where is one id preferred over another in code?

On Thu, Jul 18, 2013 at 10:02 AM, Jeffrey Warren notifications@github.comwrote:

... thought it was synced!

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/153 .

[jywarren]

user.id is native Rails, drupal_user.uid is native Drupal. I believed that we had set them up to be the same, (probably in models/drupal_users.rb but i'm seeing some users (cfastie) where this seems not to be the case. we can run a migration to re-sync them in retrospect but should double check the syncing logic

On Thu, Jul 18, 2013 at 3:18 PM, Bryan Bonvallet notifications@github.comwrote:

How does this out of sync express itself (symptoms) and where is one id preferred over another in code?

On Thu, Jul 18, 2013 at 10:02 AM, Jeffrey Warren notifications@github.comwrote:

... thought it was synced!

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/153> .

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/153#issuecomment-21207468 .

[btbonval]

I'm pretty experienced at writing that sort of checking and sync stuff ;)

I'll take a crack at it. On Jul 18, 2013 3:38 PM, "Jeffrey Warren" notifications@github.com wrote:

user.id is native Rails, drupal_user.uid is native Drupal. I believed that we had set them up to be the same, (probably in models/drupal_users.rb but i'm seeing some users (cfastie) where this seems not to be the case. we can run a migration to re-sync them in retrospect but should double check the syncing logic

On Thu, Jul 18, 2013 at 3:18 PM, Bryan Bonvallet notifications@github.comwrote:

How does this out of sync express itself (symptoms) and where is one id preferred over another in code?

On Thu, Jul 18, 2013 at 10:02 AM, Jeffrey Warren notifications@github.comwrote:

... thought it was synced!

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/153> .

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/153#issuecomment-21207468> .

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/153#issuecomment-21208556 .

[jywarren]

cool. the longer-term ideal is of course to just ditch drupal_users, since once the drupal site is dead, it's totally redundant

On Thu, Jul 18, 2013 at 3:40 PM, Bryan Bonvallet notifications@github.comwrote:

I'm pretty experienced at writing that sort of checking and sync stuff ;)

I'll take a crack at it. On Jul 18, 2013 3:38 PM, "Jeffrey Warren" notifications@github.com wrote:

user.id is native Rails, drupal_user.uid is native Drupal. I believed that we had set them up to be the same, (probably in models/drupal_users.rb but i'm seeing some users (cfastie) where this seems not to be the case. we can run a migration to re-sync them in retrospect but should double check the syncing logic

On Thu, Jul 18, 2013 at 3:18 PM, Bryan Bonvallet notifications@github.comwrote:

How does this out of sync express itself (symptoms) and where is one id preferred over another in code?

On Thu, Jul 18, 2013 at 10:02 AM, Jeffrey Warren notifications@github.comwrote:

... thought it was synced!

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/153> .

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/153#issuecomment-21207468> .

— Reply to this email directly or view it on GitHub< https://github.com/jywarren/plots2/issues/153#issuecomment-21208556> .

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/153#issuecomment-21209118 .

[btbonval]

Database notes:

• user.id => rusers.id
• drupal_user.uid => users.uid

If the ids are out of sync, how else does the identity problem get solved? Looks like rusers.username == users.name.

SELECT rusers.username, rusers.id, users.uid FROM rusers, users WHERE rusers.username = users.name AND rusers.id <> users.uid;
...
46 rows in set (0.07 sec)

[btbonval]

full output for good measure:

mysql> SELECT rusers.username, rusers.id, users.uid FROM rusers, users WHERE rusers.username = users.name AND rusers.id <> users.uid;
+----------------+----+-------+
| username       | id | uid   |
+----------------+----+-------+
| mathew         |  3 |     4 |
| gonzoearth     |  4 |    12 |
| liz            |  5 |     7 |
| Shannon        |  6 |     9 |
| Adam-Griffith  |  7 |     3 |
| Becki          |  8 | 46673 |
| cfastie        |  9 |   554 |
| ives           | 11 |   403 |
| nadya          | 12 |   704 |
| hagitkeysar    | 13 |   152 |
| patcoyle       | 14 |    77 |
| btbonval       | 15 | 43229 |
| cbreuer        | 16 | 49682 |
| shaief         | 17 |   151 |
| eustatic       | 18 |   379 |
| donblair       | 19 | 43651 |
| itdaniher      | 20 | 46964 |
| Sara           | 21 |     8 |
| RichardCWinkel | 22 | 51998 |
| stoft          | 23 | 54025 |
| Larzag         | 24 |  1225 |
| xxup           | 25 | 57263 |
| straylight     | 26 | 43047 |
| ebrelsford     | 27 |  1057 |
| LimnoRR        | 28 | 45309 |
| Timoti         | 29 | 58354 |
| dan.beavers    | 30 |    51 |
| oscarbrett     | 31 |   409 |
| dusjagr        | 32 | 46795 |
| j0hn           | 33 | 57430 |
| Ioan           | 34 | 44551 |
| jmsaavedra     | 35 | 46929 |
| dorncox        | 37 |  1205 |
| simone         | 38 |   164 |
| james1be2      | 39 | 56693 |
| osbock         | 40 | 44836 |
| bdebaque       | 41 | 57052 |
| veroacosta     | 42 | 45662 |
| rjm            | 43 |   163 |
| sonofaquark    | 44 | 54330 |
| brad           | 45 | 59609 |
| virdi          | 46 | 60478 |
| Cindy_ExCites  | 47 |  1083 |
| geobduffy      | 48 | 60509 |
| cversek        | 49 | 48275 |
| Jessica_Helm   | 50 | 60514 |
+----------------+----+-------+
46 rows in set (0.07 sec)

Which id should be set as the correct id? Since drupal_users will be deprecated, it seems like that one is expendable, however, posts might be preferring one over the other (in which case we'd want to keep posts synchronized).

[jywarren]

Yep, that was a stopgap solution and not pretty On Jul 18, 2013 4:08 PM, "Bryan Bonvallet" notifications@github.com wrote:

Database notes:

• user.id => rusers.id
• drupal_user.uid => users.uid

If the ids are out of sync, how else does the identity problem get solved? Looks like rusers.username == users.name.

SELECT rusers.username, rusers.id, users.uid FROM rusers, users WHERE rusers.username = users.name AND rusers.id <> users.uid; ... 46 rows in set (0.07 sec)

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/153#issuecomment-21210874 .

[btbonval]

Looks like the out of sync is only effecting the first 50 or so users (only 46 are effected and all have rusers.id under 50.

[jywarren]

Uid will be what all the relationships depend on. On Jul 18, 2013 4:10 PM, "Bryan Bonvallet" notifications@github.com wrote:

full output for good measure:

mysql> SELECT rusers.username, rusers.id, users.uid FROM rusers, users WHERE rusers.username = users.name AND rusers.id <> users.uid; +----------------+----+-------+ | username | id | uid | +----------------+----+-------+ | mathew | 3 | 4 | | gonzoearth | 4 | 12 | | liz | 5 | 7 | | Shannon | 6 | 9 | | Adam-Griffith | 7 | 3 | | Becki | 8 | 46673 | | cfastie | 9 | 554 | | ives | 11 | 403 | | nadya | 12 | 704 | | hagitkeysar | 13 | 152 | | patcoyle | 14 | 77 | | btbonval | 15 | 43229 | | cbreuer | 16 | 49682 | | shaief | 17 | 151 | | eustatic | 18 | 379 | | donblair | 19 | 43651 | | itdaniher | 20 | 46964 | | Sara | 21 | 8 | | RichardCWinkel | 22 | 51998 | | stoft | 23 | 54025 | | Larzag | 24 | 1225 | | xxup | 25 | 57263 | | straylight | 26 | 43047 | | ebrelsford | 27 | 1057 | | LimnoRR | 28 | 45309 | | Timoti | 29 | 58354 | | dan.beavers | 30 | 51 | | oscarbrett | 31 | 409 | | dusjagr | 32 | 46795 | | j0hn | 33 | 57430 | | Ioan | 34 | 44551 | | jmsaavedra | 35 | 46929 | | dorncox | 37 | 1205 | | simone | 38 | 164 | | james1be2 | 39 | 56693 | | osbock | 40 | 44836 | | bdebaque | 41 | 57052 | | veroacosta | 42 | 45662 | | rjm | 43 | 163 | | sonofaquark | 44 | 54330 | | brad | 45 | 59609 | | virdi | 46 | 60478 | | Cindy_ExCites | 47 | 1083 | | geobduffy | 48 | 60509 | | cversek | 49 | 48275 | | Jessica_Helm | 50 | 60514 | +----------------+----+-------+ 46 rows in set (0.07 sec)

Which id should be set as the correct id? Since drupal_users will be deprecated, it seems like that one is expendable, however, posts might be preferring one over the other (in which case we'd want to keep posts synchronized).

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/153#issuecomment-21211028 .

[btbonval]

For rusers.id, only id 1 (El Jeffe) and 36 (David Gifford) are properly synced.

I'm in the top 50. It looks like rusers.id is getting changed based on admin criteria or some kind of importance. Most of these names look familiar, including me, and I know I wasn't one of the first 50 to sign up. The loss of synchronization seems to be related to that.

We could force a synchronization with users.uid overriding rusers.id, but it might async itself again if we don't find the cause of this modification.

[btbonval]

This might be bad mojo. I decided to look up whose place I took, assuming the id and uid were once synced.

rusers.id 15 is now me, but assuming sync, it was the person who has users.uid 15 which is ncraig. Doing a search for ncraig in rusers yielded nothing. I think his spot got overwritten when I was moved to rusers.id 15.

If I'm right, this is messy.

[jywarren]

ah, is this because .id is the primary key?

remember that not everyone will have a rusers entry, since they get one only upon registering.

we can also do this all later...

On Thu, Jul 18, 2013 at 4:26 PM, Bryan Bonvallet notifications@github.comwrote:

This might be bad mojo. I decided to look up whose place I took, assuming the id and uid were once synced.

rusers.id 15 is now me, but assuming sync, it was the person who has users.uid 15 which is ncraig. Doing a search for ncraig in rusers yielded nothing. I think his spot got overwritten when I was moved to rusers.id15.

If I'm right, this is messy.

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/153#issuecomment-21212193 .

[jywarren]

I think the 50 in question were those who signed up for the beta plots2 site before we had syncing set up. This may indeed be messy, but basically I think we have to do something where we "make space" for them to get the correct ruser.id, and if we can't figure it out, just use one high enough to have an acceptable uid and id.

[btbonval]

Assigning new uids sounds like a good compromise, but unfortunately the id/uid is an extremely integral number in the database. Changing it can be very difficult to keep straight.

At any rate, I think only drupal_user.uid is used across the board. We could probably replace user.id without any far reaching consequences. On Apr 28, 2014 3:13 PM, "Jeffrey Warren" notifications@github.com wrote:

I think the 50 in question were those who signed up for the beta plots2 site before we had syncing set up. This may indeed be messy, but basically I think we have to do something where we "make space" for them to get the correct ruser.id, and if we can't figure it out, just use one high enough to have an acceptable uid and id.

— Reply to this email directly or view it on GitHubhttps://github.com/jywarren/plots2/issues/153#issuecomment-41600597 .

[btbonval]

We'll have to very carefully, and by hand, reassign those ids not only in the User table but across all tables the user could have potentially touched. Since there are no foreign keys in the database, the only way to know all places a user'd id can appear is to review all the models and find which ones reference DrupalUser, I think.

[btbonval]

primary keys aren't meant to be changed in a relational database, ever :(

[btbonval]

You know what, it might be worth deleting the User entries for misaligned users in Rails console. The console supports cascade, so we should see if we can delete only when there is nothing to cascade.

As long as there is nothing to cascade, we can delete the User entry and then generate a new User via a migration.

Since DrupalUser.uid is authoritative, we would want to remove any User where User.id does not correspond to the DrupalUser.uid.

For those cases where a cascade is needed ... well let's just hope that never comes up :(

[jywarren]

We can probably list which tables depend on uid:

DrupalNode DrupalNodeRevision DrupalNodeCommunityTag TagSelection DrupalComment DrupalFile Image DrupalProfileValue

I think that's it -- i searched https://github.com/publiclab/plots2/blob/master/db/schema.rb

Hopefully (but not surely) all of these include dependencies in their models, and many include :dependent => :destroy

On Mon, Jun 9, 2014 at 2:36 PM, Bryan Bonvallet notifications@github.com wrote:

You know what, it might be worth deleting the User entries for misaligned users in Rails console. The console supports cascade, so we should see if we can delete only when there is nothing to cascade.

As long as there is nothing to cascade, we can delete the User entry and then generate a new User via a migration.

Since DrupalUser.uid is authoritative, we would want to remove any User where User.id does not correspond to the DrupalUser.uid.

For those cases where a cascade is needed ... well let's just hope that never comes up :(

— Reply to this email directly or view it on GitHub https://github.com/jywarren/plots2/issues/153#issuecomment-45526970.

[jywarren]

moving this monster over to https://github.com/publiclab/plots2/issues/227