[Catalyst] Duplicate session problem? (LONG)

[Dr. Jennifer Nussbaum]

--- On Sun, 7/6/08, J. Shirley <jshirley at gmail.com> wrote:

> From: J. Shirley <jshirley at gmail.com>
> Subject: Re: [Catalyst] Duplicate session problem? (LONG)
> To: bg271828 at yahoo.com, "The elegant MVC web framework" <catalyst at lists.scsys.co.uk>
> Cc: "Tomas Doran" <bobtfish at bobtfish.net>
> Date: Sunday, July 6, 2008, 6:57 AM
> On Sun, Jul 6, 2008 at 6:30 AM, Dr. Jennifer Nussbaum
> <bg271828 at yahoo.com> wrote:
> >
> >
> > --- On Sun, 7/6/08, Tomas Doran
> <bobtfish at bobtfish.net> wrote:
> >
> >> From: Tomas Doran <bobtfish at bobtfish.net>
> >> Subject: Re: [Catalyst] Duplicate session problem?
> >> To: bg271828 at yahoo.com, "The elegant MVC web
> framework" <catalyst at lists.scsys.co.uk>
> >> Date: Sunday, July 6, 2008, 4:05 AM
> >> On 5 Jul 2008, at 22:21, Dr. Jennifer Nussbaum
> wrote:
> >>
> >> > Now that i got my authentication routines
> working, im
> >> running into
> >> > another problem.
> >> >
> >> > Anything i do, i am getting elaborate
> "duplicate
> >> session" errors;
> >> > in my error
> >> > log they show up as things like:
> >> >
> >> > DBI Exception: DBD::mysql::st execute failed:
> >> Duplicate entry
> >> > 'session:12a7c108ba07843441d5bed0'
> for key 1
> >> [for Statement "INSERT
> >> > INTO sessions (id) VALUES (?)" with
> ParamValues:
> >> 0='session:
> >> >
> 12a7c108ba07843441d5bed0467e3398a8e7c3f1'] at
> >> /usr/share/perl5/DBIx/
> >> > Class/Schema.pm line 954
> >> >
> >> > This happens just about constnatly. Im the
> only one
> >> using the system,
> >> > its just for testing now, so i dont expect
> any
> >> collisions.
> >>
> >> <snip>
> > <snip>
> 
> I'm actually more inclined to think that there is
> something wrong with
> the database.  Due to your previous problem of the
> '0' vs. 0 returning
> different data, which shouldn't happen, there is
> something else going
> on with MySQL.

I did try this with a different version of MySQL--a 4.x version on
FreeBSD (this is a 5.x version on Linux), and there was also a 
difference between the "deleted = 0" and "deleted = '0'" search.

> If you'll note, DBIC uses bind parameters which takes
> care of quoting:
> SELECT me.id, me.login, me.password, me.access_level,
> me.email,
> me.last_login, me.created, me.deleted FROM user me WHERE (
> ( ( ( ( (
> me.id = ? ) ) ) ) AND ( ( ( deleted IS NULL ) OR ( deleted
> = ? ) ) ) )
> ) ORDER BY login: '3', '0'
> 
> The ? mark means it is a bind param, and the quoting on
> '3' and '0' is
> for display purposes.  From your previous message about the
> auth
> framework, if that query isn't working out of the box
> there is some
> other problem.
> 
> Is your MySQL database configured strangely, if you run
> these queries
> do you get a valid record back?
> 
> SELECT me.id, me.session_data, me.expires, me.t FROM
> sessions me WHERE
> ( ( me.id = ?
> 'session:4f540dcae6da5bf8bc91b15f65bf6a34e16733a5'
> ) )

If I remove your "?", then I get:

mysql> SELECT me.id, me.session_data, me.expires, me.t FROM
    -> sessions me WHERE ( ( me.id = 'session:4f540dcae6da5bf8bc91b15f65bf6a34e16733a5' ) );
Empty set (0.00 sec)


> INSERT INTO sessions (id) VALUES
> ('session:4f540dcae6da5bf8bc91b15f65bf6a34e16733a5')

mysql> INSERT INTO sessions (id) VALUES
    -> ('session:4f540dcae6da5bf8bc91b15f65bf6a34e16733a5');
ERROR 1062 (23000): Duplicate entry 'session:4f540dcae6da5bf8bc91b15f' for key 1


> SELECT me.id, me.session_data, me.expires, me.t FROM
> sessions me WHERE
> ( ( me.id =
> 'session:4f540dcae6da5bf8bc91b15f65bf6a34e16733a5'
> ))

mysql> SELECT me.id, me.session_data, me.expires, me.t FROM
    -> sessions me WHERE ( ( me.id =
    -> 'session:4f540dcae6da5bf8bc91b15f65bf6a34e16733a5' ) );
Empty set (0.00 sec)

What does all this mean?

Heres the sessions table, btw (the a_session is legacy):
mysql> desc sessions;
+--------------+-------------+------+-----+-------------------+-------+
| Field        | Type        | Null | Key | Default           | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| id           | varchar(32) | NO   | PRI |                   |       | 
| t            | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
| a_session    | text        | YES  |     | NULL              |       | 
| session_data | text        | YES  |     | NULL              |       | 
| expires      | int(10)     | YES  |     | NULL              |       | 
+--------------+-------------+------+-----+-------------------+-------+
5 rows in set (0.00 sec)

Thanks.

Jen