[phplib] PHPLib sessions - problem with ac_store() in ct_sql.inc From: John Bafford (dshadow <email protected>)
Date: 06/30/00

In attempting to use PHPLib with Horde and IMP, I came across a problem
with Horde displaying many error messages about being unable to do
something with sessions.

I tracked the problem down to ac_store() in ct_sql.inc. The problem also
exists with PHPLib 7.3; the code for functions is unchanged between the
two versions.

I am using mySQL. This problem may or may not exist with other databases.

This is the problem:

ac_store() does an update, counts the number of affected rows, and if the
number of affected rows is zero, does an insert query.

This fails when the update causes no rows to be updated.

Example:
        ac_store() is called twice in the same second with the same data.

Results:
        The update for the first ac_store() call succeeds as expected,
with one row changed.
        The update for the second ac_store() call succeeds, but because no
data is changed (the 'changed' value storing the time is the same), no
rows are updated, so there are no affected rows.
        Since there are no affected rows, ac_store does an insert.
        The insert fails: sid and name are primary keys; attempting to
insert another row with the same values will fail.
        This causes ac_store() to return false.

Solution:
        There are two solutions to this problem. The first is to change
the value that goes into changed.

        My solution was to rewrite the portion of code that does the
actual SQL queries. It locks the active sessions table, and does the
update. If there are no affected rows, it does a select to see if the
session is in the table. If it is not, it inserts it, otherwise, it does
nothing. It then unlocks the table.

The drawback to this is that it requires table locking from inside of
PHPLib. This may not be desirable, as making the lock might cancel another
lock that that particular thread has active. A solution to this problem
could be using other kinds of locking; for example, the mySQL
GETLOCK() and RELEASE_LOCK() functions. If this is applied to all the
accesses to the sessions table, then the session table is protected by
locks (and therefore, accesses surrounded by locks are done
atomically) without compromising any locks set by users of PHPLib.

Also, a possible solution to the problems of multiple databases:

mySQL allows you to select from tables in other databases. For example,
you can do the following regardless of the active database.
        select * from horde.active_sessions where sid='foo';

This way, it is not necessary to make calls to change the current
database and you then work better with PHPLib clients that need to use
multiple databases.

My Fix:

Replace the code at the bottom of ac_store() (from the comment block to
the the statment before the return with the following:

        $this->db->lock($this->database_table);
        $this->db->query($uquery);
        if($this->db->affected_rows() == 0)
        {
                //select it and see if it already exists
                $this->db->query("select count(*) as cnt from
$this->database_table where sid='$id'");
                $this->db->next_record();
                if($this->db->f('cnt') == 0)
                        if(!$this->db->query($iquery))
                                $ret = false;
        }
        $this->db->unlock();

Hope this helps..

-John 

---
John Bafford
dshadow <email protected>
http://www.dshadow.com/

---------------------------------------------------------------------
To unsubscribe, e-mail: phplib-unsubscribe <email protected>
For additional commands, e-mail: phplib-help <email protected>