Mailing List Archive: Catalyst: Users

Database-backed sessions: Session::Store::DBI/DBIC and MSSQL

Index | Next | Previous | View Threaded

swatt at infobal

Jun 17, 2009, 1:34 PM

Post #1 of 3 (413 views)
Permalink

Database-backed sessions: Session::Store::DBI/DBIC and MSSQL

I don't know if anyone else has hit these problems, and I am working on
them, but there appear to be a set of significant issues around the DBI
and DBIC session stores using Microsoft SQL Server as the back-end.
These are mostly related to the fact that MSSQL only allows one active
statement at a time. Therefore, you get errors like:

[error] Caught exception in engine
"DBIx::Class::Storage::DBI::__ANON__(): DBI Exception: DBD::ODBC::st
execute failed: [Microsoft][ODBC SQL Server Driver]Connection is busy
with results for another hstmt (SQL-HY000) [for Statement "UPDATE
sessions SET expires = ? WHERE id = ?" with ParamValues: 1=1245271741,
2='session:6b263f72b22e53043beca20c3746dee2baa3bf35'] at
D:/perl510-20090611/site/lib/Catalyst/Plugin/Session/Store/DBI.pm line 52"

Since I was using Session::Store::DBI rather than Session::Store::DBIC I
thought that might be the problem, but I now suspect the session
handling needs its own connections. (On a side note, I got all sorts of
strange error messages trying to set up Session::Store::DBIC, and I am
not worrying about them just yet.)

Since Session::Store::DBI can be set to use its own connection, I
thought that might resolve it. Not completely, unfortunately, and now
the error is due to the recommended use of a text column for the data.
It seems the DBI+DBD::ODBC likes to require LongTruncLen set to a rather
larger value than is the default (apparently the default can even be
zero!), so you get truncation errors when reading any session data. The
way the statements are set up (in a batch) makes it hard to set this for
one statement independently, and supposedly it needs to be set before a
statement is prepared.

I had thought the transition to MSSQL would be as easy for session
handling as it had been for model data. Not so. And no, MSSQL was not my
idea!

All the best
Stuart
--
Stuart Watt
ARM Product Developer
Information Balance

bobtfish at bobtfish

Jun 18, 2009, 1:01 AM

Post #2 of 3 (375 views)
Permalink

Re: Database-backed sessions: Session::Store::DBI/DBIC and MSSQL [In reply to]

On 17 Jun 2009, at 21:34, Stuart Watt wrote:
>
> Since Session::Store::DBI can be set to use its own connection, I
> thought that might resolve it. Not completely, unfortunately, and
> now the error is due to the recommended use of a text column for
> the data. It seems the DBI+DBD::ODBC likes to require LongTruncLen
> set to a rather larger value than is the default (apparently the
> default can even be zero!), so you get truncation errors when
> reading any session data.

Any session data at all? Can you be more specific - I'd guess that
this should only hit you if the length is small, or if your sessions
get large.

Also, is there a different data type you can use without this
behavior? (One that at least fails rather than silently truncating
stuff would be good, for a start)..

> The way the statements are set up (in a batch) makes it hard to set
> this for one statement independently, and supposedly it needs to be
> set before a statement is prepared.

Why not just arrange for it to be set by all queries done by the
session handling? If you already have a dedicated DB connection for
session data, why not use it? :)

Cheers
t0m

_______________________________________________
List: Catalyst[at]lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst[at]lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/

swatt at infobal

Jun 18, 2009, 4:02 PM

Post #3 of 3 (367 views)
Permalink

Re: Database-backed sessions: Session::Store::DBI/DBIC and MSSQL [In reply to]

On 18-Jun-09, at 4:01 AM, Tomas Doran wrote:

>
> Any session data at all? Can you be more specific - I'd guess that
> this should only hit you if the length is small, or if your sessions
> get large.

No, the sessions were tiny, more or less all they held was the id for
which project the user was working on. The field allocated was "text"
type, and it looks like MSSQL basically throws a truncation error for
"long" type, no matter how much data was actually in there. DBI
permits this. This was something of a surprise. If I'd declared the
type as a VARCHAR of some kind, I'd have got away with it.
Unfortunately, you can't get away from the need to make a separate
connection as easily.

>
> Also, is there a different data type you can use without this
> behavior? (One that at least fails rather than silently truncating
> stuff would be good, for a start)..

Yes, a VARCHAR(4000) or so would probably do fine for my purposes.
Although my actual solution was C::P::Session::Store::Delegate with
some additional logic to delegate to my existing user data model,
which has a bunch of non-session stuff. This eventually worked well,
when I finally figured the requirement for this to support any
additional fields, and to keep user and session data separate. It even
seemed to handle the overlapping query issues. DBIC saved the day
(again!).

>
>> The way the statements are set up (in a batch) makes it hard to set
>> this for one statement independently, and supposedly it needs to be
>> set before a statement is prepared.
>
> Why not just arrange for it to be set by all queries done by the
> session handling? If you already have a dedicated DB connection for
> session data, why not use it? :)

That would have worked fine, but DBIC handles all this directly, so
delegating to that solved all the issues. I guess my comments should
really be read as "in anyone is ever patching
C::P::Session::Store::DBI, here are a some things that could probably
be added." In its out of the box form, it would have needed an
override or two to work. As it is, C::P::Session::Store::Delegate is
working very smoothly, and running stable with MSSQL.

>
> Cheers
> t0m
>

_______________________________________________
List: Catalyst[at]lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst[at]lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact lists@gossamer-threads.com