Zend Application Session SaveHandler DB Table issue

[Gabriel Baez-2]

I'm having issues  trying to use  Zend Application Resource Session  with Save Handler DB Table.

I'm getting the following  error.

Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "session__seq" does not exist LINE 1: SELECT NEXTVAL('"session__seq"') ^' in C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement\Pdo.php:234 Stack trace: #0 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement.php(320): Zend_Db_Statement_Pdo->_execute(Array) #1 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Abstract.php(468): Zend_Db_Statement->execute(Array) #2 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT NEXTVAL(...', Array) #3 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Abstract.php(799): Zend_Db_Adapter_Pdo_Abstract->query('SELECT NEXTVAL(...', Array) #4 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Pdo\Pgsql.php(299): Zend_Db_Adapter_Abstract->fetchOne('SELECT NEXTVAL(...') #5 C:\Zend\Apache2\ht in C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement\Pdo.php on line 234



My configuration is the following:

obviously I removed the username and password for this post.


<resources>
            <modules>
                <!-- Placeholder to ensure an array is created -->
                <placeholder />
            </modules>
            <db>
                <adapter>Pdo_Pgsql</adapter>
                <params>
                    <host>localhost</host>
                    <username></username>
                    <password></password>
                    <dbname>test_db</dbname>
                    <port>5432</port>
                </params>
                <isDefaultTableAdapter>true</isDefaultTableAdapter>
            </db>
            <session>
                <save_path><zf:const zf:name="APPLICATION_PATH" />/../data/sessions</save_path>
                <use_only_cookies>true</use_only_cookies>
                <remember_me_seconds>864000</remember_me_seconds>
                <saveHandler>
                    <class>Zend_Session_SaveHandler_DbTable</class>
                    <options>
                        <name>session</name>
                        <primary>
                            <session_id>session_id</session_id>
                            <save_path>save_path</save_path>
                            <name>name</name>
                        </primary>
                        <primaryAssignment>
                            <sessionId>sessionId</sessionId>
                            <sessionSavePath>sessionSavePath</sessionSavePath>
                            <sessionName>sessionName</sessionName>
                        </primaryAssignment>
                        <modifiedColumn>modified</modifiedColumn>
                        <dataColumn>session_data</dataColumn>
                        <lifetimeColumn>lifetime</lifetimeColumn>
                    </options>
                </saveHandler>
            </session>


Table Structure:

I added the column "session" to see if it fixes  it but it did not help.



CREATE TABLE "session"
(
  "session" serial NOT NULL,
  session_id character(32) NOT NULL,
  save_path character varying(32) NOT NULL,
  "name" character varying(32) NOT NULL DEFAULT ''::character varying,
  modified integer,
  lifetime integer,
  session_data text,
  CONSTRAINT session_pk PRIMARY KEY (session, session_id)
)
WITH (
  OIDS=FALSE
);



-- Index: session_idx

-- DROP INDEX session_idx;

CREATE UNIQUE INDEX session_idx
  ON "session"
  USING btree
  (session);

[conf]

I'm having issues  trying to use  Zend Application Resource Session  with Save Handler DB Table.

I'm getting the following  error.

Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "session__seq" does not exist LINE 1: SELECT NEXTVAL('"session__seq"') ^' in C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement\Pdo.php:234 Stack trace: #0 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement.php(320): Zend_Db_Statement_Pdo->_execute(Array) #1 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Abstract.php(468): Zend_Db_Statement->execute(Array) #2 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT NEXTVAL(...', Array) #3 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Abstract.php(799): Zend_Db_Adapter_Pdo_Abstract->query('SELECT NEXTVAL(...', Array) #4 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Pdo\Pgsql.php(299): Zend_Db_Adapter_Abstract->fetchOne('SELECT NEXTVAL(...') #5 C:\Zend\Apache2\ht in C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement\Pdo.php on line 234

Your database configuration required sequence with the name session__seq for generating auto-increment values, which doesn't exist.

It's written just here:
Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message
'SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "session__seq" does not exist
LINE 1: SELECT NEXTVAL('"session__seq"') ^'


--
Regards,
Shein Alexey

[Gabriel Baez-2]

Sorry  I replied  off the mailing list earlier.


 I created  the sequence just as suggested  and  it didn't fix  the  issue

CREATE SEQUENCE session__seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 5
  CACHE 1;



Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42601]: Syntax error: 7 ERROR: zero-length delimited identifier at or near """" LINE 1: ..., "name", "modified", "session_data", "lifetime", "") VALUES... ^' in C:\Zend\Apache2\htdocs\

projects\genscript\library\Zend\Db\Statement\Pdo.php:234 Stack trace: #0 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement.php(320): Zend_Db_Statement_Pdo->_execute(Array) #1 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Abstract.php(468): Zend_Db_Statement->execute(Array) #2 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO "se...', Array) #3 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Abstract.php(546): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO "se...', Array) #4 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Table\Abstract.php(1056): Z in C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement\Pdo.php on line 234


I recreated the table  according to the documentation   http://framework.zend.com/manual/en/zend.session.savehandler.dbtable.html


CREATE TABLE "session"

(
  session_id character(32) NOT NULL,
  save_path character varying(32) NOT NULL,
  "name" character varying(32) NOT NULL DEFAULT ''::character varying,
  modified integer,
  lifetime integer,
  session_data text,

  CONSTRAINT session_pk PRIMARY KEY (session_id, save_path)
)
WITH (
  OIDS=FALSE
);

however with this same table structure,   if  I remove the resource session  config  off the resources   and  configured it per the example  at  http://framework.zend.com/manual/en/zend.session.savehandler.dbtable.html   on  the boostrap.    Then  it works  fine  I see an entry in the database.



public  function _initSessions()
{
    $config = array(
    'name'              => 'session', //table name as per Zend_Db_Table
    'primary'           => array(
        'session_id',   //the sessionID given by PHP
        'save_path',    //session.save_path
        'name',         //session name
    ),
    'primaryAssignment' => array(
        //you must tell the save handler which columns you
        //are using as the primary key. ORDER IS IMPORTANT
        'sessionId', //first column of the primary key is of the sessionID
        'sessionSavePath', //second column of the primary key is the save path
        'sessionName', //third column of the primary key is the session name
    ),
    'modifiedColumn'    => 'modified',     //time the session should expire
    'dataColumn'        => 'session_data', //serialized data
    'lifetimeColumn'    => 'lifetime',     //end of life for a specific record
);

//Tell Zend_Session to use your Save Handler
Zend_Session::setSaveHandler(new Zend_Session_SaveHandler_DbTable($config));

//start your session
Zend_Session::start();   

}



Server [localhost]:
Database [postgres]: bohiti
Port [5432]:
Username [postgres]:
psql (8.4.0)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

bohiti=# select * from session
bohiti-# ;
            session_id            | save_path |   name    |  modified  | lifetim
e | session_data
----------------------------------+-----------+-----------+------------+--------
--+--------------
 kv72hog6jiqk02653qlul20kc7       |           | PHPSESSID | 1257339780 |     144
0 |
 pu5p31gtbouls2acd1ntlbm564       |           | PHPSESSID | 1257339788 |     144
0 |
(2 rows)


bohiti=#




Why does it not work  with  Zend_Application_Resource_Db  ?   I think it has something  to do with it trying to insert the data differently ?  double quotes?  instead of quotes  ? 

On Tue, Nov 3, 2009 at 9:09 PM, Gabriel Baez <[hidden email]> wrote:

I'm having issues  trying to use  Zend Application Resource Session  with Save Handler DB Table.

I'm getting the following  error.

Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "session__seq" does not exist LINE 1: SELECT NEXTVAL('"session__seq"') ^' in C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement\Pdo.php:234 Stack trace: #0 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement.php(320): Zend_Db_Statement_Pdo->_execute(Array) #1 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Abstract.php(468): Zend_Db_Statement->execute(Array) #2 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT NEXTVAL(...', Array) #3 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Abstract.php(799): Zend_Db_Adapter_Pdo_Abstract->query('SELECT NEXTVAL(...', Array) #4 C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Adapter\Pdo\Pgsql.php(299): Zend_Db_Adapter_Abstract->fetchOne('SELECT NEXTVAL(...') #5 C:\Zend\Apache2\ht in C:\Zend\Apache2\htdocs\projects\genscript\library\Zend\Db\Statement\Pdo.php on line 234



My configuration is the following:

obviously I removed the username and password for this post.


<resources>
            <modules>
                <!-- Placeholder to ensure an array is created -->
                <placeholder />
            </modules>
            <db>
                <adapter>Pdo_Pgsql</adapter>
                <params>
                    <host>localhost</host>
                    <username></username>
                    <password></password>
                    <dbname>test_db</dbname>
                    <port>5432</port>
                </params>
                <isDefaultTableAdapter>true</isDefaultTableAdapter>
            </db>
            <session>
                <save_path><zf:const zf:name="APPLICATION_PATH" />/../data/sessions</save_path>
                <use_only_cookies>true</use_only_cookies>
                <remember_me_seconds>864000</remember_me_seconds>
                <saveHandler>
                    <class>Zend_Session_SaveHandler_DbTable</class>
                    <options>
                        <name>session</name>
                        <primary>
                            <session_id>session_id</session_id>
                            <save_path>save_path</save_path>
                            <name>name</name>
                        </primary>
                        <primaryAssignment>
                            <sessionId>sessionId</sessionId>
                            <sessionSavePath>sessionSavePath</sessionSavePath>
                            <sessionName>sessionName</sessionName>
                        </primaryAssignment>
                        <modifiedColumn>modified</modifiedColumn>
                        <dataColumn>session_data</dataColumn>
                        <lifetimeColumn>lifetime</lifetimeColumn>
                    </options>
                </saveHandler>
            </session>


Table Structure:

I added the column "session" to see if it fixes  it but it did not help.



CREATE TABLE "session"
(
  "session" serial NOT NULL,
  session_id character(32) NOT NULL,
  save_path character varying(32) NOT NULL,
  "name" character varying(32) NOT NULL DEFAULT ''::character varying,
  modified integer,
  lifetime integer,
  session_data text,
  CONSTRAINT session_pk PRIMARY KEY (session, session_id)
)
WITH (
  OIDS=FALSE
);



-- Index: session_idx

-- DROP INDEX session_idx;

CREATE UNIQUE INDEX session_idx
  ON "session"
  USING btree
  (session);