FAQ
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20090617/372b77e8/attachment.htm

Search Discussions

  • Tomas Doran at Jun 18, 2009 at 8:01 am

    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
  • Stuart Watt at Jun 18, 2009 at 11:02 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcatalyst @
categoriescatalyst, perl
postedJun 17, '09 at 8:34p
activeJun 18, '09 at 11:02p
posts3
users2
websitecatalystframework.org
irc#catalyst

2 users in discussion

Stuart Watt: 2 posts Tomas Doran: 1 post

People

Translate

site design / logo © 2022 Grokbase