Problem with User Administration

Here is background to my problem. We had a stream of spammers login to the site. They created accounts, and spammed a lot. So, we instituted an approval process for membership. No problem. The spammers are gone. We deleted their accounts, and the problems started.

First, we had artifacts that remained in the database after deleted. Digging deep into the problem, I found that some items (especially in the elggentity_relationships table (along with others) had items still belonging to the deleted users. So! I did a HUGE database clean. I went through every table with a owner ID columns (of some sort or another), and I checked that to the now CLEAN elgguser_entities table. I deleted any database rows with owner IDs not in the cleaned elgguser_entities. This cleaned up 99% of my problems.

Second, I am still having a problem with the User Adminstration page. At an OFFSET=50, I run into problems with my user data. But, I can manually increase the offset to bypass the bad data. I know it is a data problem (I think ;) ) Here is what I did.

I went to the /admin/user.php, and changed the LIMIT to 1. I could then identify specific data that appeared to be causing problems. In my case OFFSETs 51, 52, and 53 are causing problems. So, I attempted to sort the elgguser_entities table to find the specific user. However, it doesn't seem to be in reverse (DESC) order. For example, 51 should be a particular user. But, when I go to User 54, that turns out to be the user record I expected in 51. Hmmm. Is this a caching issue? I turned off caching, and nothing changed. Is there somewhere to flush the cache in ELGG?

So, I went to /engine/lib/entities.php, since user.php called list_entities. List_entities calls get_entities. I put an echo $query; in the entities.php to look at the code. However, before I can get anything meaningful, the code fails with the following:

InvalidParameterException

File profile/small.jpg (0) is missing an owner!

Also, when I look at the generated queries from other offsets (that do work), the query doesn’t pass through to the database. I am using phpMyAdmin to pass through the query that is displayed, and it doesn’t process. So, I must have the echo in the wrong place or file.

I look at the users database row, and I can see nothing unusual about that data. But, I may not be looking at the right data. I have no way to tell. Any thoughts about how to track this down?

  • This seems insane, for someone to have to go through all of this, just to clean users out of the database, etc.  Is there no scrubbing tool, for Elgg?

  • Yakiv, this is the challenge of Open Source coding projects. After all, ELGG is free. The folks who are working on it are doing so because of their passion for the project. I accept that it can and does have quirks. If you are working on a production project that absolutely cannot have failures, then I suggest using something more commercial. In those cases, you'll pay for the code, the support, the hosting, the updates, or all of the above.

    What I am hopeful is that this discussion, along with what I have learned (also documented in this post - http://community.elgg.org/mod/groups/topicposts.php?topic=350111&group_guid=15815) will help both other users, and more importantly will help the developers who are actively working this project to dig into the solution.

    Perhaps that is a perspective you didn't consider. Perhaps you have. Either way, I think we all owe more thanks than ridicule to the developers of ELGG.

    My 2 cents.

  • this is rather o-l-d problem ;-) haven't y'all seen my research notes and fix for this ?

  • Dhrup, yes, old problem. ;) I worked and solved most of it. However, I cannot lock down this last bit. If I could get ELGG debugged to show me what record it is trying to load, I would be good. My gut is telling me that this is a caching issue. However, that doesn't seem to fit within what I have read of the ELGG dev. stuff.

    What kind of debugging tools are recommended for ELGG? Any thoughts?

  • What Elgg version ?
    This is old Elgg bug problem !
    Never delete users --> will always crash your site ;-(

  • Bill, I don't see how my post was ridicule for the developers.  I think you construed something out of my post that wasn't there.  The point is, the process you were going through is insane.  I asked if there was a scrubbing tool for Elgg.  I think you should be more careful in how you read into things and respond.

  • @Bill

    I hope you saw my expanded version of your earlier problemn's sql query ;-) I'm pasting it here in case you missed it. I think the same approach might fix your latest "deleted user" problem ==>

    As promised, here's the smoothed out code to do a JOIN extended from the mysql code you had posted which got your deleted user working again. I hope this helps if you ever have the same accident/ problem again. This JOIN mysql code is giving me some more ideas -- maybe the mysql code can be expanded to detect *any missing owner problem and direct one to the cause of the problem and maybe even do a self-correct... that might be nice

    `````````````````````````````````````````````````````
    SELECT    guid_one, relationship, guid_two,
              guid, username
    FROM      elgg_entity_relationships
    LEFT JOIN elgg_users_entity
           ON elgg_entity_relationships.guid_one = elgg_users_entity.guid
    WHERE     guid_two = {$problem_user_guid}
    ORDER  BY guid_one ASC
    LIMIT     0 , 99
    ;
    `````````````````````````````````````````````````````

  • There needs to be an actual administrative tool to clean data safely. Doing stuff like this directly in the DB, for the average Elgg site, is dangerous. Use extreme caution with any kind of DB actions like this.

  • @Yakiv ... Sorry for my misconstruing your comments. But when you say, "This seems insane, for someone to have to go through all of this...", I took that as a critique against the developers. If that is your opinion, fine. You are free to slam them as you choose. I was just offering a different opinion, and setting a different level of expectations.

     

    @Dhrup ... this is a 1.6.1 that was originally installed as 1.5 used for a while and upgraded when 1.6.1 came out.

    Also, Thanks for the better SQL query. The real issue is identifying the problem user. Any thoughts on which package and where I might put some debug code to highlight which user is actually causing me errors?

  • @Bill - again, you are suggestingly accusing me of slamming the developers, which I did not. I was referring to the effort of an Elgg site owner, doing all that you did, and that it is insane to get into the database and go through such cleaning processes as you did.  Again, I asked if there was a tool for Elgg, to clean up after deleting all of those users.  And again, quit putting words in my mouth.  Maybe it is you who has the attitude against the developers?  I surely don't.