Forum OpenACS Q&A: Response to "insert a duplicate key into unique index referer_log

I've found a similar problem, and i think i've found the reason.

Look at the error i found in my server.log. It tries to register a hit from another machine's bboard page:

[20/Jun/2001:00:03:38][4418.6151][-conn2-] Error: dbinit: error(localhost::test6db,
ERROR:  Cannot insert a duplicate key into unique index referer_log_pkey):
'insert into referer_log (local_url, foreign_url, entry_date, click_count)
select '/documentacion/informes/',
'http://xxx/bboard/q-and-a-fetch-msg.tcl?msg_id=00005l&topic_id=6&topic=proyecto%3a%20b2c', trunc(sysdate()), 1
from dual
where 0 = (select count(*)
       from referer_log
       where local_url = '/documentacion/informes/'
       and foreign_url = 'http://xxx/bboard/q-and-a-fetch-msg.tcl?msg_id=00005l&topic_id=6&topic=Proyecto%3a%20B2C'
       and trunc(entry_date) = trunc(sysdate()))'

Have you seen the problem? It's trying to insert the URL in lowercase, but it's checking that the real URL is not in th DB.

Here you have the code at tcl/ad-referer.tcl:

set insert_sql "insert into referer_log (local_url, foreign_url, entry_date, click_count)
select '[DoubleApos [ns_conn url]]', '[string tolower [DoubleApos $foreign_url]]', trunc(sysdate()), 1
from dual
where 0 = (select count(*)
           from referer_log
           where local_url = '[DoubleApos [ns_conn url]]'
           and foreign_url = '[DoubleApos $foreign_url]'
           and trunc(entry_date) = trunc(sysdate()))"
               ns_db dml $db $insert_sql

I think this is a genuine bug. Should i post a patch in the SDM? (it's not a big change, you only have to change a bit the query:

...
...
where local_url = '[DoubleApos [ns_conn url]]'
           and foreign_url = '[string tolower [DoubleApos $foreign_url]]'
...

I don't know what's the problem in John's case (http://www.servername.com/ is lowercase, but maybe he has changed the URL to post it here), and Jeff's URLs are all lowercase.

I hope that helps.

Forum OpenACS Q&A: Response to "insert a duplicate key into unique index referer_log_pkey" error