Same user listed multiple times in who's online block
webchick - January 5, 2007 - 10:04
| Project: | Drupal |
| Version: | 7.x-dev |
| Component: | user system |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | patch (code needs review) |
Description
To reproduce:
1. Enable the "who's online" block.
2. Open up browser #1 (say, Firefox) and login as some user.
3. Open up browser #2 (say, Safari) and login as the same user.
The user's name will be listed twice. Since this block is referring to users and not sessions, each username should only be listed once, even if multiple sessions are present.
#1
This patch adds a DISTINCT which fixes the problem.
#2
By the way, I'm using MySQL 5.0.19. I know I've seen this on other sites as well, but I'm not sure of the db versions running there.
#3
Is there a greater underlying problem since there is more than one session per user? What change caused this bad behavior all of a sudden?
#4
Ahh, nevermind. Just re-read the top post and it makes sense with multiple browsers. I'm an idiot. Patch looks good to me.
#5
I can reproduce the problem. We should check with Robert -- he removed that distinct, I believe.
#6
Having the same problem with D5 HEAD. The DISTINCT is not there.
I added it, and now it's fine.
Thanks
#7
The proper fix is to skip the join on the sessions table and use the access value in the users table fo rthe time info.
#8
@Gerhard: Take a look at http://drupal.org/node/93042 for why there is a join ... only using the access value is not sufficient, because users that log out are removed from the session table, but their access timestamp is still in the interval, so they were listed.
#9
Marked http://drupal.org/node/115112 a dupe of this.
#10
Marked http://drupal.org/node/116929 a dupe of this.
#11
Another dupe: http://drupal.org/node/117278.
Where does this stand. Is adding a DISTINCT really not the way to go? Any SQL gurus get a patch up for that JOIN then?
#12
Committed to CVS HEAD. Needs to be backported to Drupal 5.
#13
Committed to 5.
#14
#15
Worked fine for me. Thanks !!
#16
This one popped up again for 5.2
Someone added s.timestamp into the select query as such..
$authenticated_users = db_query('SELECT DISTINCT u.uid, u.name, s.timestamp FROM {users}
removing s.timestamp solved the issue.
#17
Yes, JHeffner is right. Who fix this problem in CVS?
#18
re-introduced by hunmonk at Revision 1.791 of user.module. CVS notes are
- Patch #148974 by hunmonk: fixed whois online block on PostgreSQL.
#19
Introduced again on 5.3. Removing select timestamp fixed problem again. There must be multiple timestamps for users that aren't distinct. I noticed this only seems to happen with the admin user now though.
#20
Possible duplicate: http://drupal.org/node/185202
#21
Duplicate of http://drupal.org/node/185202.
#22
Actually, I will leave this one open since there is more information.
Selecting distinct on the session timestamp is not good since it will probably be different for different sessions with the same user. The timestamp used should be the one from the user column as Gerhard mentioned, but still do the join to make sure the session is still open.
Make sure to test Postgres before committing.
#23
Affects new version 5.6. Any progress with getting this committed?
#24
Yes, it would be nice to know.
I think removing select timestamp will work fine. Other solutions are not.
#25
As mentioned before, selecting the timestamp will result in different rows, thus rendering the added DISTINCT useless.
I tested removing the timestamp from the query agains both MySQL and PostgreSQL. Unfortunately PostgreSQL will give the following error :
* warning: pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /var/www/httpdocs/drupal6-head/includes/database.pgsql.inc on line 138.
* user warning: query: SELECT DISTINCT u.uid, u.name FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.timestamp >= 1201459760 AND s.uid > 0 ORDER BY s.timestamp DESC in /var/www/httpdocs/drupal6-head/modules/user/user.module on line 758.
I made a patch for D6 head which solves the listing problem by keeping track of users added to the list in an array. It's definitely not an optimal solution imho because when the limit of number of users to display is set to a very large value, it will have some negative impact on performance. However, with or without this added code, having a large limit would result in negative performance anyway. I also removed the DISTINCT since it's not needed anymore.
If it could be solved by perhaps rewriting the query it would definitely be a better solution, but since I'm not that familiar with PostgreSQL, I don't know if it's possible at all.
#26
gdevlugt, please provide patches in unified diff format, see http://drupal.org/patch
#27
Hi,
Sorry, forgot the up switch. Here's an updated diff file attached.
#28
I'd tested your patch - it works well.
I'd tried to find more fast solution, but in_array is the fastest one.
I am not familiar with PostgreSQL either, but maybe this "sql-rewriting" will work on it:
<?php$authenticated_users = db_query(db_distinct_field('u', 'uid', 'SELECT u.uid, u.name FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.timestamp >= %d AND s.uid > 0 ORDER BY s.timestamp DESC', $interval));
?>
This works for me on MySQL.
I really doubt it will work on PostgreSQL, but what if...
#29
Happening with 5.6 running with postgres,
I'm not too bothered by it, but it would be nice to know that a fix for postgres is also being worked on.
#30
No - db_distinct_field is not working here.
It adds "uid" in ORDER BY as first field.
#31
@bfo:
What about patch from #27? It's not sqlify, but it clearly works.
I think there can be no suitable solutions:
http://www.tek-tips.com/viewthread.cfm?qid=1261181&page=1
"The best workaround is using sub-select"
Maybe it's time to write a distinct wrapper for Database abstraction layer like db_query_range() (called db_query_distinct).
#32
The patch #27 has a authenticated_count mistake.
So this is a new patch.
#33
Is there a particular reason this couldn't be done with a subquery? D6 supports them now.
#34
You are right - no reason at all:
http://drupal.org/requirements
Drupal requires MySQL 3.23.17 or higher. MySQL 4.1 or higher are strongly recommended. Drupal 6 will require MySQL 4.1 or higher.
But D5.x still hasn't subquery in MySQL before 4.1.
I'll try to make a patch with a subquery.
Is there any subqueries in core modues of Drupal6 ?
#35
Please remember that drupal is also supposed to work with postgres it says 7.1 or higher so I would assume that a core fix should work for both DB's
#36
afaik (which isn't much) postgresql 7.1 supports subqueries without issues.
#37
I added a patch using a subquery. Please test since I only did a short test which worked on two D6 head installs using MySQL and PostgreSQL. In both cases, after patching the username was only listed once.
Alternatively, a subquery is 'too advanced', a group by on uid,name and aggregating (say using count) timestamp might also work, but the query itself will be less readable.
#38
Ignore last patch and use the one attached to this comment.... Forgot something in the query.
#39
@gdevlugt
The patches #37 #38 are the same.
I think you forgot ORDER BY s.timestamp somewhere.
#40
The attached patch is used db_distinct_field() function.
I'd tested it on PostgreSQL - works well. the last logged user appears on the top.
#41
@bfo:
What about patch from #27? It's not sqlify, but it clearly works.
Will it work with drupal 5.6?
or is the problem only being fixed for drupal 6?
Thanks
#42
I couldn't get the patch at #40 to work under MySQL (5.0.41). I think it's because db_distinct_field() uses 'DISTINCT ON' which MySQL ignores. Perhaps someone else can verify this or has some more information on this?
As mentioned by vladimir in #39 i forgot to the ORDER BY s.timestamp DESC part in the subquery. I added it to the patch attached and hopefully it is now complete. Thanks for noticing it.
#43
#41 bfo: You're right, since this bug also exists under Drupal 5.x, it should of course also be fixed.
In a previous comment I mentioned that the problem could perhaps also be solved by a GROUP BY and use of an aggregrate function. Attached to this comment is a patch which uses an SQL query which seems to work, but naturally needs reviewing and testing. Basically, instead of selecting distinctly (which doesn't correctly work atm), the query selects the maximum timestamp, grouped by user (id and name). Perhaps the SQL seems a bit explicit (grouping on both id and name), it's because PostgreSQL, by the looks of it, is more strict than MySQL. I'm no SQL expert so again, review and test.
I also reworked the code from my previous patch for Drupal 6 head (using a php array). I will refrain from attaching it atm, since it might get confusing and handling it the SQL way is preferred. However, if the patch above doesn't work and won't work, I'll post the ported patch for Drupal 5 if you want.
Again the patch below is for Drupal 5 head only.
#44
The SQL in Patch #42 works on Drupal 5.7 with MySQL 4.1 .. The SQL in patch #43 didn't work. It didn't return any results from the query.
#45
Can we review 6.x to get that in first, then move to 5.x?
I have successfully tested #42 on DRUPAL-6 (MySQL 5.0.18) by logging in with two browsers. Pre-patch shows two users, post-patch shows one. Perhaps we can do with another review, preferably with the minimum required version of MySQL 4.1.
#46
We have been waiting over a year to get this patch into Drupal 5. The patch doesn't need to be the same for both, as long a patch does get into Drupal 5 for the version it was originally posted for.
#47
Yes, but still the issue is in the 6.x queue right now, and the usual policy is to commit to the newer branch first. I realize that the patch for D5 will be different and will look like #43, but I just wanted to suggest that we review #42 to get it into D6 before fixing #43 up for D5.
#48
Regarding the problem mentioned in #44 by JHefner, the patch attached to #43 only seems to work for later versions than 4.1. The problem is the 'ORDER BY max(s.timestamp)'. I tested it with 4.1 and indeed it didn't return any results and gave a warning. I found out that by aliasing max(s.timestamp), it does seem to work under at least MySQL 4.1 and 5.0.41 and PostgreSQL 7.4.19. At the moment I'm unable to test if it works under MySQL 3.23.17 but will test later tonight when I'm able to. Again, I added a patch for Drupal 5 head.
What would be interesting to know for Drupal 6 is which SQL query (GROUP BY using max() or the subquery) performs better and/or is more efficient. That is if both are equivalent in the results they give and correctly fix the problem of course. For Drupal 5 the subquery patch will work only if your MySQL version supports it, like 4.1 and 5.
Please test and review and post your results.
#49
gdevlugt patch #48 seems to work on Drupal 5.7 with MySQL 4.1. Thank you so much for working on the patch.
#50
I just fixed it!
#51
Here is a perfect example of the problems with the Drupal community. Why does it take a year and a half to fix and still not merge a fix for a bug into the core for 5? Why do the technical staff have to keep a list of 30 different patches that need to be checked every time a new minor version is released? We need more developers to take the maintenance of the code seriously, and not just always looking forward to writing new code.
#52
The problem is that there are too few people testing patches. This could have been solved a year ago otherwise.
Edit: A 6.x patch existed already, but I didn't spot it among all the 5.x patches. Oops.
#53
@JHeffner: report your testing results with postgres, and if the patch continues to work, mark it "reviewed and tested by the community" and it'll go in. Pretty simple stuff. While things are marked "code needs review", they still need, er, review. :P We're held up by someone testing in a pgsql environment (preferably both 7.x and 8.x). And looks like benchmarks wouldn't hurt either.
#54
@webchick I don't run in a postgres environment so can only test the fix for multiple users under MySQL 4/5 which I reported above. This seems unfortunate that this bug is held up by such a small group of testers. It was a long morning of patches to walk through in the 5.8 release, so I apologize for the outburst. This bug has been present in almost every release of version 5. This probably is the wrong place to bring this up, but there is a serious need for better testers/developers to help maintain patches so they can get committed across all current releases. I wish I had the time to commit, but don't due to my day job.
#55
Hm. I don't think you understand.
The entire Drupal community (you, me, that other dude there) are the testers. It is our responsibility to test patches so that they can get to a committable state. It is not the core committers' job; they're there to commit patches once the community has reviewed them. And they're certainly not going to commit patches that have not been fully tested, since that's how this bug came about in the first place.
Since it's already costing you time to deal with this each release, it's a little illogical to me why you don't spend just a little bit more time to do what's necessary to get this into core, and instead are expecting others (who also have day jobs, and families they'd like to see once in awhile, etc.) to drop what they're doing and perform this testing for you. Scratch your own itch.
#56
@webchick I don't think I was specific enough. The problem was originally introduced by by hunmonk at Revision 1.791 of user.module. The notes indicate this was a supposed fix for PostgreSQL. I took the time to track down all the information I could find to let the developers know the best detailed information I had. This patch was committed back to core without properly being tested. Shouldn't there be some way to either pull the change for further testing, or get other developers to test the change with postgres before commit? I understand this can be hard, look at this thread for instance.
I understand everyone has day jobs. Unfortunately we are now at this stand still. I tested the patch on MySQL 4.1, and MySQL 5 with success, but I do not run postgres nor have it installed on any servers. However the initial change introduced the problem in these environments and I have tested that the patch does work there.
I'm not trying to make any attacks, but rather trying to find a solution that would allow fixes not to get stalled completely. This may mean that I need to install a few development environments on postgres to test these patches for the community. I believed there were plenty of developers already running postgres that could test a small patch like this easily. I apologize, as I am wrong. I'll test this next week and get back to this thread. I'm familiar with MSSQL and MySQL 4/5, but not at all aware of the differences in PostgresSQL. Since the version number was changed to 6, do we need testing on 5, 6 and now 7 for every patch? or is it enough just to test patches on a single version, depending on it's complexity?
#57
Yeah, unfortunately that's been a big problem. :( The good news is that if you become one of those developers that does, you suddenly get a lot more leverage with your patch reviews. :D
Generally, you test patches against the latest development version of Drupal (which would be 7.x-dev) and any supported version of an OS/DB/PHP/etc. I think it would be sufficient to test this against 7.x-dev and pgsql 8.x. If we can prove it works for the latest stuff, then the patch simply gets back-ported to previous versions of Drupal. Then someone who uses pgsql 7.x can come back and supply another patch if it doesn't work.
Ideally, this would be accompanied by a test in the Drupal 7 branch so that this bug never gets reintroduced again, but I'm not sure off the top of my head how to simulate two user sessions, other than manual DB manipulation. Which, actually, might not be the worst thing. Hmmm...
Anyway, for now, let's focus on making sure that it doesn't break pgsql. :) Thanks for your help!
#58
No kidding. You can go "behold, I can test your patches on PostgreSQL" and get developers to kiss your feet. ;)
#59
I've added this issue to the List of issues which need a review on postgres.
#42 still applies to 7.x with a few lines offset, moving to there since we normally commit to the development branch first, then backport.
#60
This definitely works, thanks for this patch. I am using the latest drupal 6.4 and the bug still exists, then I tried this patch and boom, no more doubling of users.
#61
I mean #48 works for Drupal 6.4 release, thanks
#62
Patch from #42 works correctly on d7/postgresql install. Online user is only displayed once, even when logged in through two browsers. I guess with a postgresql successful test, this issue is RTBC?
#63
Tested on Drupal 5/postgres 7.4.19. Still haven't test on Drupal 6. To re-create the error log in on a different browser to the same site. Your username should appear twice. After applying the patch in #48 your username should appear only once.