Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Links SQL v1.11 --> Duplicate Sessions

Quote Reply
Links SQL v1.11 --> Duplicate Sessions
I know this has been discussed before...and I remember Jerry Su addressing this in the past (but I can't seem to find the relevant Thread) and I did search the Links SQL FAQ site, but couldn't find the relevant link.

Here is the problem....

Users are logging in twice to my site and thus creating duplicate Session records in the Sessions table. I know this because I wrote a script over the weekend that shows online users:

http://vlib.anthrotech.com/bin/online.cgi

and as you can see by clicking on either link, User with the ID of 143 is logged in twice.

Would somebody direct me to the relevant Thread or link in the FAQ site that may provide a fix for this? I thought about adding in codes in the sub login_user routine in the user.cgi script that checks the Session table first with the Username used to login and if there is a duplicate, then ignore the Session creation codes. But if this has already been addressed before, then it would save me time hacking the script.

Thanks in advance.

Regards,

Eliot Lee

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Eliot...

I really don't remember anything on that specifically, at least anything that worked.

FWIW... if a user logs in again, any other sessions by that user _could_ be killed, but _should_ they? You'd need to do a check.

What, if for example, a user is working in two areas of the site with two different browsers on different computers. Would this log out the other session?

Could you get around this? Yes, by adding a permanent ID_Number to each cookie such that each browser instance got a unique serial number, so that the session data contained Username/Password/ID_Number. Then, you'd check not only for an expired session, but whether this 'instance' of connection was expired.

On any "who's logged in" displays, stuff the users into a Hash, and if a user is already logged in, you can either increment the 'sessions' count, or ignore the 'duplicate key' error.

Make sense?

Since much of this code is roll your own, taking into account multiple instances of user-sessions is important, not just an absolute # of sessions.

You'd need to check, and follow both 'session' and 'ID_Number'. That way, if you had two unexpired sessions with a different 'ID_Number' but the same Username, you could assume the user was logged in twice. If both unexpired sessions had the same 'ID_Number' then you'd be able to assume the most current cookie was the valid one, and delete the duplicates.

(The same thing would occur with a user on two computers, or a user checking the site using NN and MSIE, each with it's own cookie-sheet <G>).

There may be a better way to do this, but without putting the information in the 'Sessions' table, there is no way to communicate between different browser instances, so you need some sort of 'ID_Number' field to store 'identity_instance' not just 'currency' and 'user'. Two browsers should never get the same 'ID_Number' with a valid session, since if the sessions database has 6 valid, unexpired sessions for a Username, the next attempted logon (session ID doesn't match or is expired) would get a 7th 'ID_Number'. If 5 of those sessions expire, the browser attempting to log on again could get a new number, or reuse an expired one (it's a "new" logon). This could even be used to track the number of user logons for any account.




http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Okay...how about another approach...when I print the last and all sessions through the script I linked...how do you suggest I ignore "duplicate" sesssions?? I guess that is the most important issue...I can play around with the Sessions table at a later time.

Any suggestions are welcome. Thanks.

Regards,

Eliot Lee

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Hi Eliot,

here's the piece of Jerry's code (user.cgi) I think you are looking for:

# Create and insert a Session, but first remove sessions older then 3 hours.
$db->do (qq!
DELETE FROM Sessions
WHERE UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(Created) > 10800 or
Username = '$user'
!);

this code deletes all session being older than 3h and all sessions of the user logging-in. (it keeps the db clean Smile)

regards, alexander

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Thanks...Alexander...The codes look logical. The time period of the sessions is not really a concern.

Although I am heeding pugdog's advice regarding multiple sessions...I know that for me...I tend to use multiple sessions to do different things via different browsers. So, his advice is right on...

I will try the codes you provided though and see what happens. If they don't work...what I may work on is editing the online.cgi that only prints UNIQUE Contact Names (derived from the Username field in the Sessions table) in the web page. (The challenge is perserving the relations that I've set-up between the Sessions and Users table.)

Regards,

Eliot Lee

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Going with DISTINCT will be a much better solution, it should be fairly easy, just change your query to something like:

SELECT DISTINCT Username
FROM Sessions

and that will weed out duplicates.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Alexander,

Thanks! It worked. Those were the codes I was looking for. I appreciate you re-posting them.

Alex,

I may play around some more with the DISTINCT function. For some reason, it didn't work with the queries in my script. I did research this function a bit via the MySQL web site (http://www.mysql.com) and the only documents I found regarding DISTINCT contained the following syntax example:

Code:

DISTINCT(Count)


I tried using DISTINCT(Username) and I got no users found.

Regards,

Eliot Lee

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
yep.. i made that little mysql statement addon a long time ago..

it sucks though.. ;)

if you try logging into the same user on a different computer while the user is on.. the user gets logged out..

when i was developing some sites for some people.. this got annoying.. because i would be logged into it as a user to test user functions and then the other person would log in and i need to relogin and we just kept logging each other in and out for awhile.. haha


Jerry Su
Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Thanks, Jerry...it is at least a temporary fix before I can get the DISTINCT function to work properly in the queries I've written in the online.cgi script. It is more annoying at this point to have duplicate users show up in the Online User web pages.

Regards,

Eliot Lee

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
What about using it the way Alex suggested:

Code:
SELECT DISTINCT Username
FROM Sessions
I tried it, and got a list of unique Usernames in the sessions table.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Uh...pugdog...as I stated, I already used the codes that Alex provided and guess what...no go! Sorry.

Thanks for your reply though.

Regards,

Eliot Lee

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Eliot,

You show it being used as a function, not a qualifier.

If you use it as:

SELECT DISTINCT Username FROM Sessions

It works.

You might want to try it from the SQL Monitor, and then in your query.

In MySQLMan, the output is formatted, into a table, so the results _can_ be picked off and manipulated.

In fact, if you just cut/paste the above line into the SQL Monitor, it works. I've tried it on a dozen different databases.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Well, I am tried using the following codes in the script:

Code:

my $USERDB = new Links::DBSQL $LINKS{admin_root_path} . "/defs/Sessions.def";
my $query = qq!
SELECT DISTINCT Username
FROM Sessions
ORDER BY Username
LIMIT 1000
!;

my $sth = $USERDB->prepare($query);
$sth->execute();
my $hits = $sth->rows();
my ($user, $output);
while ($user = $sth->fetchrow_hashref) {
$users .= &site_html_users_link ($user, $dynamic);
}


Yes...I tried it via telnet...and it worked...however, in the Perl scripts I've written...it unfortunately does not work with the above query codes.

Regards,

Eliot Lee


Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Well, it works via MySQLMan, so it does work via HTTP:

I tried this, and it works for me.

I've tried the codes in both MySQLMan and in Links SQL Monitor.

Check your program, make sure you don't have some reciprocal sort of error.

Did you run a debug effor to see if you are getting any $hits or if it's failing somewhere else?

In a Select statement, 'DISTINCT' is a a keyword, that alters the default
behavior of 'ALL' when returning rows.

What 'DISTINCT' does, is limit the return to only ONE row for each group of
lines that are identical. The default behavior is 'ALL' where all matches
are returned.

Code:
SELECT [STRAIGHT_JOIN] [DISTINCT|ALL] value[, value2, ...]
[INTO OUTFILE 'filename' delimiters]
FROM table[, table2,...]
[CLAUSE]
http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
In Reply To:
Did you run a debug effor to see if you are getting any $hits or if it's failing somewhere else?
Yes...I used the following codes after the query codes:

Code:

if ($hits) {
# Span pages if more than 10 hits.
($USERDB->hits > $LINKS{modify_span}) and ($span = $USERDB->toolbar(alpha => '1'));

# Print out the HTML results.
my $title_linked = &build_linked_cgi_title ("List All Online Users");
&site_html_users ( { Users => $users, span => $span, title_linked => $title_linked}, $dynamic );
}
else {
my $title_linked = &build_linked_cgi_title ("List All Online Users/Error: Unable to Process Form");
&site_html_error ( { error => "No Online Users at this time.", title_linked => $title_linked}, $dynamic);
}


and all I got was the error page stating that there are no online users even when I logged in a bunch of times...when I took out DISTINCT...my user account was listed along with other users online.

I also used if ($USERDB->hits > 0)...and it didn't make any difference.

Regards,

Eliot Lee

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Code:
my $users;
my $sth = $db->prepare ("SELECT DISTINCT *.u FROM Users as u, Sessions as s WHERE Username.u = Username.s FROM Sessions ORDER BY Username.u");
$sth->execute() or die $DBI::errstr;
while (my $user = $sth->fetchrow_hashref) {
$users .= &site_html_online ($user);
}
Jerry Su
Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
What about (and I know it's a small thing).

my $hits = $sth->rows();

Becomes:

my $hits = $sth->rows;

No value was passed to ->execute() so, maybe the () is confusing the compiler on the type of return value?

I'm actually curious what the value of $hits is right after that assignment.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
In Reply To:
SELECT DISTINCT *.u FROM Users as u, Sessions as s
I think you mean u.*? It's tablename.columname.

My questiong would be, what are you passing to site_html_online()? What is $user meant to be. Is it the full user info? If it is, the only thing you are getting in the query I suggested is the username, so your hash looks like:

Username => 'MyUser'

Nothing else. If you need the full user info, try:

Code:
SELECT Users.*
FROM Users,Sessions
WHERE Users.Username = Sessions.Username
GROUP BY Sessions.Username
ORDER BY Users.Username
That will give you all the user info of logged on users. If all you want is the Username:

Code:
SELECT DISTINCT(Username)
FROM Sessions
ORDER BY Username
will do.

Hope that helps,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Thanks, Alex...it worked...

And also, thanks to pugdog...removing the () codes for the following line of codes:

Code:

my $hits = $sth->rows();


also helped the script to work.

Thanks to all for your helpful ideas...one of these days I will learn more about MySQL and strict Perl.

Regards,

Eliot Lee

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
oh.. my Users table gets logged where they are at..

i just modded my sub authenticate so that it puts $ENV{REQUEST_URI} into a field in the Users table..

so basically.. I needed stuff from the user table..

and yes.. I meant it the way you put it.. I just really messed up typing it without checking to see if it was right..

Jerry Su
Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Okay...one more thing...I want to pull the Created field in the table of online user information. I have tried a bunch of codes with the DISTINCT(Username) and nothing works....

here are the codes I've tried so far:

Code:

SELECT DISTINCT(Username), Created


This pulls all users from the Sessions table and duplicates show. However, the Created field is pulled and shows up in the online users web page.

Code:

SELECT DISTINCT(Username, Created)


No users are shown.

Code:

SELECT DISTINCT('Username','Created')


Same thing...no users show.

Anyone know how to select the Created column, but not show duplicate records.

I did search the MySQL documentation and I am really confused about how the DISTINCT function works.

Thanks in advance.

Regards,

Eliot Lee

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
I think I see what you are trying to do.

You want to show distinct users, plus the created field. The problem is, if there is more than one user, "WHICH" created field do you want to show?

You might want to try:

Select DISTINCT Username, Created
From Sessions
Group by Username

That should/may limit it to one record.

I've tried a variety of queries in my copy of MySQLMan, but I don't have an active enough Sessions table to try these specific queries.

I tried it on my Hits_Track, and I got the results I hoped -- 202 distinct IP's, 202 records returned from the above query, and 303 records in the table.



http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Links SQL v1.11 --> Duplicate Sessions In reply to
Works great, pugdog!

It pulls the most current record and shows the time created.

Thanks!

Regards,

Eliot Lee