I'm running a multi-site installation of 4.6.9 with shared users.

I thought it was running smoothly, until today when I discovered a problem with my current set-up. Basically, users who've created an account on one site have had a NULL .rid value inserted in the users_roles table on the other sites.

Note that this isn't a bug in Drupal ... I know exactly what I did wrong in the set-up ... it was my fault ... I'll admit it.

Anyway, my question is, how can I do a mass update of users_roles.rid using MySQL?

What I'd like to do is search each users.uid to see if the corresponding users_roles.rid is NULL. If it is, I want to change it to "2" (Authenticated User).

My SQL skills are limited. I've been able to do a SELECT FROM that properly pulls out all the NULL users, but as for changing the values? I'm lost.

Any help would be appreciated.

Comments

GWL’s picture

I thought this was a fairly basic SQL question. Maybe I wasn't clear.

Here's the basic database struture ...

Table - users
Row - uid
Table - users_roles
Row - uid
Row - rid

Due to a configuration error on my part, many of the rows in users_roles are NULL when users_roles.uid should be populated by the appropriate uid and users_roles.rid by the value "2".

In phpMyAdmin, I can easily bring up a list of those NULL rows with this SQL query:

SELECT users.uid, users_roles.rid
FROM users
LEFT JOIN users_roles
ON users.uid=users_roles.uid
WHERE users_roles.rid is NULL

What I need, though, is a way to take those results and INSERT the proper values into the users_roles table.

Can anyone help me? This is kind of urgent, and nothing I've found on Google has helped.

pwolanin’s picture

I'm not too familiar with the 4.6 schema. Are you sure there is supposed to be a "2" for all users? In the 4.7 schema, only roles with ID > 2 are stored in the database and all users get role 2 when the user object is loaded.

---
Work: Acquia

GWL’s picture

Thanks for the help, pwolanin, but after working on this all day, I finally came up with a solution.

BTW, I am sure I needed it. The way I had things configured, the user table was shared across three sites, but I'd wanted the roles to be separate, so that a project manager on one site wouldn't be granted the same status on all sites.

It was a nice idea, but in practice, a user who registered on Site A would become an "authorized user" (role 2) on that site. But if that person logged in to Site B or Site C, the row corresponding to their uid in the users_roles table was undefined. The end result is that they would lose all access to the site (except their own profile) by logging in. They actually had fewer permissions than "anonymous."

It sounds like a problem that should've been caught months ago. But due to the nature of Site B, few people log in to both sites except me, and I use the Admin login for that.

It wasn't until I ran across MAJOR problems on the newly launched Site C this week that I realized my mistake.

Anyway, I wound up writing a php script to check for undefined rows on users_roles. If a user already has permissions, the script does nothing. If the rows are undefined, it sets them to "authorized user." The script runs this check for all three sites.

Not the most elegant solution, but it worked.

pwolanin’s picture

see INSERT ... SELECT:

http://dev.mysql.com/doc/refman/4.1/en/insert.html

if actually need to do this, maybe you could do something like:

INSERT INTO users_roles (uid, rid)
SELECT users.uid, 2
FROM users
LEFT JOIN users_roles
ON users.uid=users_roles.uid
WHERE users_roles.rid is NULL

---
Work: Acquia