Login security not SQL92 compatible [#770074]

Dear all,

Thanks for this great looking module.

Unfortunately, it contains some typical MySQL feature, which does not exist on any other database, including PostgreSQL:

query: INSERT INTO single_login_history
                 SET uid = 1, session_id =  
&amp;#039;89fc6702ab35**********bdf1f26423d&amp;#039;, date = 127*******32, ip =  
&amp;#039;88.160.168.33&amp;#039;, browser = &amp;#039;Mozilla/5.0 (X11; U;  
Linux x86_64; en-US; rv:1.9.1.9) Gecko/20100402 Iceweasel/3.5.9 (like  
Firefox/3.5.9)&amp;#039;, type = &amp;#039;cookie&amp;#039;
                 ON DUPLICATE KEY UPDATE date = 1271166632 in  
/home/html/*************/www/sites/all/modules/single_login/single_login.module on  
line 56.

There is a nice SQL developer guide here:
http://drupal.org/node/770068

I am trying to find a solution in one query and will report back.

Comments

Comment #1

grub3 CreditAttribution: grub3 commented 13 April 2010 at 14:25

I think you should simply add unique indexes on:
uid, session_id, date, ip, browser
to avoid duplicated.

I see there is no index on you tables.
No index, means terrible server load.

Comment #2

deekayen CreditAttribution: deekayen commented 13 April 2010 at 14:26

Title:	Login security not SQL92 compatible	» Single Login not SQL92 compatible
Project:	Login Security	» Single Login

Looks like you pegged the wrong module issue queue.

Comment #3

grub3 CreditAttribution: grub3 commented 13 April 2010 at 14:30

Title:	Single Login not SQL92 compatible	» Login security not SQL92 compatible
Project:	Single Login	» Login Security

Okay, some more:

$sql = "INSERT INTO {single_login} (uid, counter) VALUES (%d, %d) ON DUPLICATE KEY UPDATE counter = counter + 1";
        db_query(sprintf($sql, $user->uid, 1));