Thread: question about rollback and SQLException
I received this exception when trying to rollback a transaction through the
Postgres JDBC driver (build106) used by a webapp running in Resin (2.0.6). I
have tried setting up the connection pool in my web.xml to use both
javax.sql.DataSource and javax.sql.XADataSource, but both give me this
exception:
java.sql.SQLException: ERROR: parser: parse error at or near "qrollback"
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
.java:482)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
.java:461)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio
n.java:1031)
at
com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470
)
at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132)
Any idea what that means and how I can prevent it from occuring?
thx
andy kriger
can you send us logs from the server? or a test case which demonstrates this? Dave On Thu, 2003-01-23 at 12:07, Andy Kriger wrote: > I received this exception when trying to rollback a transaction through the > Postgres JDBC driver (build106) used by a webapp running in Resin (2.0.6). I > have tried setting up the connection pool in my web.xml to use both > javax.sql.DataSource and javax.sql.XADataSource, but both give me this > exception: > > java.sql.SQLException: ERROR: parser: parse error at or near "qrollback" > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) > at > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > .java:482) > at > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > .java:461) > at > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio > n.java:1031) > at > com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470 > ) > at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132) > > Any idea what that means and how I can prevent it from occuring? > > thx > andy kriger > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Dave Cramer <Dave@micro-automation.net>
On Thu, Jan 23, 2003 at 12:07:31PM -0500, Andy Kriger wrote: > I received this exception when trying to rollback a transaction through the > Postgres JDBC driver (build106) used by a webapp running in Resin (2.0.6). I > have tried setting up the connection pool in my web.xml to use both > javax.sql.DataSource and javax.sql.XADataSource, but both give me this > exception: > > java.sql.SQLException: ERROR: parser: parse error at or near "qrollback" "qrollback"? Sure looks like a typo in your code somewhere, to me. Ross
That's what I thought too, except I'm not sending the rollback cmd as a String, I'm using JDBC's Connection.rollback(), which would seem to suggest that the error is in the driver (which is why I'm posting to pgsql-jdbc -> hoping a driver developer can comment on this). -----Original Message----- From: Ross J. Reedstrom [mailto:reedstrm@rice.edu] Sent: Thursday, January 23, 2003 12:32 To: Andy Kriger Cc: Pgsql-Jdbc Subject: Re: [JDBC] question about rollback and SQLException On Thu, Jan 23, 2003 at 12:07:31PM -0500, Andy Kriger wrote: > I received this exception when trying to rollback a transaction through the > Postgres JDBC driver (build106) used by a webapp running in Resin (2.0.6). I > have tried setting up the connection pool in my web.xml to use both > javax.sql.DataSource and javax.sql.XADataSource, but both give me this > exception: > > java.sql.SQLException: ERROR: parser: parse error at or near "qrollback" "qrollback"? Sure looks like a typo in your code somewhere, to me. Ross
I don't really have something I can easily reduce out of my code. I started a transaction with Connection.setAutoCommit(false) Ran some SQL queries using PreparedStatements Ran a query that I knew would fail, throwing a SQLException Then I rolledback the transaction (just to be sure) with Connection.rollback() It was during Connection.rollback() that I received the exception that's in my first email. The stack trace picks up from my code as it enters the Resin & driver code. -----Original Message----- From: Dave Cramer [mailto:Dave@micro-automation.net] Sent: Thursday, January 23, 2003 12:32 To: Andy Kriger Cc: Resin-Interest; Pgsql-Jdbc Subject: Re: [JDBC] question about rollback and SQLException can you send us logs from the server? or a test case which demonstrates this? Dave On Thu, 2003-01-23 at 12:07, Andy Kriger wrote: > I received this exception when trying to rollback a transaction through the > Postgres JDBC driver (build106) used by a webapp running in Resin (2.0.6). I > have tried setting up the connection pool in my web.xml to use both > javax.sql.DataSource and javax.sql.XADataSource, but both give me this > exception: > > java.sql.SQLException: ERROR: parser: parse error at or near "qrollback" > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) > at > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > .java:482) > at > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > .java:461) > at > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio > n.java:1031) > at > com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470 > ) > at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132) > > Any idea what that means and how I can prevent it from occuring? > > thx > andy kriger > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Dave Cramer <Dave@micro-automation.net>
The "q" in "qrollback" is part of the fronted/backend protocol that is saying, "here's a query". How it got into the query string itself is a driver bug in all likelihood, probably the driver got confused and put two q's into the upstream message. I have seen this problem before with batch prepared statements, but I thought those cases had been fixed. Andy, can you give us an idea of what your actually doing? In code form ideally. Kris Jurka On 23 Jan 2003, Dave Cramer wrote: > can you send us logs from the server? > > or a test case which demonstrates this? > > Dave > > On Thu, 2003-01-23 at 12:07, Andy Kriger wrote: > > I received this exception when trying to rollback a transaction through the > > Postgres JDBC driver (build106) used by a webapp running in Resin (2.0.6). I > > have tried setting up the connection pool in my web.xml to use both > > javax.sql.DataSource and javax.sql.XADataSource, but both give me this > > exception: > > > > java.sql.SQLException: ERROR: parser: parse error at or near "qrollback" > > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) > > at > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > .java:482) > > at > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > .java:461) > > at > > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio > > n.java:1031) > > at > > com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470 > > ) > > at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132) > > > > Any idea what that means and how I can prevent it from occuring? > > > > thx > > andy kriger > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > -- > Dave Cramer <Dave@micro-automation.net> > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Andy, The logs from the server would be good, I just tested rollback and it works, but if there was something some how left in the query buffer, this would be a problem DAve On Thu, 2003-01-23 at 12:44, Andy Kriger wrote: > I don't really have something I can easily reduce out of my code. > > I started a transaction with Connection.setAutoCommit(false) > Ran some SQL queries using PreparedStatements > Ran a query that I knew would fail, throwing a SQLException > Then I rolledback the transaction (just to be sure) with > Connection.rollback() > > It was during Connection.rollback() that I received the exception that's in > my first email. The stack trace picks up from my code as it enters the Resin > & driver code. > > -----Original Message----- > From: Dave Cramer [mailto:Dave@micro-automation.net] > Sent: Thursday, January 23, 2003 12:32 > To: Andy Kriger > Cc: Resin-Interest; Pgsql-Jdbc > Subject: Re: [JDBC] question about rollback and SQLException > > > can you send us logs from the server? > > or a test case which demonstrates this? > > Dave > > On Thu, 2003-01-23 at 12:07, Andy Kriger wrote: > > I received this exception when trying to rollback a transaction through > the > > Postgres JDBC driver (build106) used by a webapp running in Resin (2.0.6). > I > > have tried setting up the connection pool in my web.xml to use both > > javax.sql.DataSource and javax.sql.XADataSource, but both give me this > > exception: > > > > java.sql.SQLException: ERROR: parser: parse error at or near "qrollback" > > at > org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) > > at > > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > .java:482) > > at > > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > .java:461) > > at > > > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio > > n.java:1031) > > at > > > com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470 > > ) > > at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132) > > > > Any idea what that means and how I can prevent it from occuring? > > > > thx > > andy kriger > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > -- > Dave Cramer <Dave@micro-automation.net> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Dave Cramer <Dave@micro-automation.net>
[ moving this off the Resin mailing list as it is not about Resin ] I think I figured out what is going on. Below is the pgsql log (at level 2) You can see the problem in line 2: the query is being mangled. The original query was insert into purchase (customer_id,item_id,quantity) values (?,?,?,?); I realize this query is bad since there aren't as many columns as values. It was only meant to trigger a SQLException so I could verify rollback. However, my PreparedStatment was setting only 3 of the 4 values. And doing that caused the mangling. If I set all 4 values, I get a SQLException (INSERT has more expressions than target columns) followed by a successful rollback. If I use the same number of values and columns but don't set the last value, I get a SQLException (No value specified for parameter 3) followed by an unsucessful rollback (the original problem). So, it looks like the JDBC driver is not handling the error condition where the last value is not set. And this is mangling the rollback query (though I'm guessing it would mangle the next query regardless of what it was). I was not able to isolate it to whether you will get the same problem if any value is not set (the 1st or 2nd, for example). Hopefully, one of the JDBC driver developers can take it from here. -a === PGSQL log === 2003-01-23 13:52:43 DEBUG: StartTransactionCommand 2003-01-23 13:52:43 DEBUG: query: insert into purchase (customer_id,item_id,quantity) values (8,2,2Qrollback; begin; 2003-01-23 13:52:43 ERROR: parser: parse error at or near "qrollback" 2003-01-23 13:52:43 DEBUG: AbortCurrentTransaction 2003-01-23 13:52:43 DEBUG: StartTransactionCommand 2003-01-23 13:52:43 DEBUG: query: rollback; begin; 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin; 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand 2003-01-23 13:52:43 DEBUG: StartTransactionCommand 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin; 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand 2003-01-23 13:52:43 DEBUG: StartTransactionCommand 2003-01-23 13:52:43 DEBUG: query: end 2003-01-23 13:52:43 DEBUG: ProcessUtility: end 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand 2003-01-23 13:52:43 DEBUG: proc_exit(0) 2003-01-23 13:52:43 DEBUG: shmem_exit(0) 2003-01-23 13:52:43 DEBUG: exit(0) 2003-01-23 13:52:43 DEBUG: reaping dead processes 2003-01-23 13:52:43 DEBUG: child process (pid 31273) exited with exit code 0 -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer Sent: Thursday, January 23, 2003 13:06 To: Andy Kriger Cc: Resin-Interest; Pgsql-Jdbc Subject: Re: [JDBC] question about rollback and SQLException Andy, The logs from the server would be good, I just tested rollback and it works, but if there was something some how left in the query buffer, this would be a problem DAve On Thu, 2003-01-23 at 12:44, Andy Kriger wrote: > I don't really have something I can easily reduce out of my code. > > I started a transaction with Connection.setAutoCommit(false) > Ran some SQL queries using PreparedStatements > Ran a query that I knew would fail, throwing a SQLException > Then I rolledback the transaction (just to be sure) with > Connection.rollback() > > It was during Connection.rollback() that I received the exception that's in > my first email. The stack trace picks up from my code as it enters the Resin > & driver code. > > -----Original Message----- > From: Dave Cramer [mailto:Dave@micro-automation.net] > Sent: Thursday, January 23, 2003 12:32 > To: Andy Kriger > Cc: Resin-Interest; Pgsql-Jdbc > Subject: Re: [JDBC] question about rollback and SQLException > > > can you send us logs from the server? > > or a test case which demonstrates this? > > Dave > > On Thu, 2003-01-23 at 12:07, Andy Kriger wrote: > > I received this exception when trying to rollback a transaction through > the > > Postgres JDBC driver (build106) used by a webapp running in Resin (2.0.6). > I > > have tried setting up the connection pool in my web.xml to use both > > javax.sql.DataSource and javax.sql.XADataSource, but both give me this > > exception: > > > > java.sql.SQLException: ERROR: parser: parse error at or near "qrollback" > > at > org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) > > at > > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > .java:482) > > at > > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > .java:461) > > at > > > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio > > n.java:1031) > > at > > > com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470 > > ) > > at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132) > > > > Any idea what that means and how I can prevent it from occuring? > > > > thx > > andy kriger > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > -- > Dave Cramer <Dave@micro-automation.net> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Dave Cramer <Dave@micro-automation.net> ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Andy, Now that you have a reproducable test case, can you send a code sample that shows the problem. After reading this thread, I am still not sure when the error message is being given. A code example would help a lot. Also, what version of the driver are you using? --Barry Andy Kriger wrote: > [ moving this off the Resin mailing list as it is not about Resin ] > > I think I figured out what is going on. Below is the pgsql log (at level 2) > You can see the problem in line 2: the query is being mangled. > > The original query was > insert into purchase (customer_id,item_id,quantity) values (?,?,?,?); > > I realize this query is bad since there aren't as many columns as values. It > was only meant to trigger a SQLException so I could verify rollback. > However, my PreparedStatment was setting only 3 of the 4 values. And doing > that caused the mangling. If I set all 4 values, I get a SQLException > (INSERT has more expressions than target columns) followed by a successful > rollback. If I use the same number of values and columns but don't set the > last value, I get a SQLException (No value specified for parameter 3) > followed by an unsucessful rollback (the original problem). > > So, it looks like the JDBC driver is not handling the error condition where > the last value is not set. And this is mangling the rollback query (though > I'm guessing it would mangle the next query regardless of what it was). I > was not able to isolate it to whether you will get the same problem if any > value is not set (the 1st or 2nd, for example). > > Hopefully, one of the JDBC driver developers can take it from here. > > -a > > === PGSQL log === > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > 2003-01-23 13:52:43 DEBUG: query: insert into purchase > (customer_id,item_id,quantity) values (8,2,2Qrollback; begin; > 2003-01-23 13:52:43 ERROR: parser: parse error at or near "qrollback" > 2003-01-23 13:52:43 DEBUG: AbortCurrentTransaction > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > 2003-01-23 13:52:43 DEBUG: query: rollback; begin; > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin; > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin; > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > 2003-01-23 13:52:43 DEBUG: query: end > 2003-01-23 13:52:43 DEBUG: ProcessUtility: end > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand > 2003-01-23 13:52:43 DEBUG: proc_exit(0) > 2003-01-23 13:52:43 DEBUG: shmem_exit(0) > 2003-01-23 13:52:43 DEBUG: exit(0) > 2003-01-23 13:52:43 DEBUG: reaping dead processes > 2003-01-23 13:52:43 DEBUG: child process (pid 31273) exited with exit code > 0 > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer > Sent: Thursday, January 23, 2003 13:06 > To: Andy Kriger > Cc: Resin-Interest; Pgsql-Jdbc > Subject: Re: [JDBC] question about rollback and SQLException > > > Andy, > > The logs from the server would be good, I just tested rollback and it > works, but if there was something some how left in the query buffer, > this would be a problem > > DAve > On Thu, 2003-01-23 at 12:44, Andy Kriger wrote: > >>I don't really have something I can easily reduce out of my code. >> >>I started a transaction with Connection.setAutoCommit(false) >>Ran some SQL queries using PreparedStatements >>Ran a query that I knew would fail, throwing a SQLException >>Then I rolledback the transaction (just to be sure) with >>Connection.rollback() >> >>It was during Connection.rollback() that I received the exception that's > > in > >>my first email. The stack trace picks up from my code as it enters the > > Resin > >>& driver code. >> >>-----Original Message----- >>From: Dave Cramer [mailto:Dave@micro-automation.net] >>Sent: Thursday, January 23, 2003 12:32 >>To: Andy Kriger >>Cc: Resin-Interest; Pgsql-Jdbc >>Subject: Re: [JDBC] question about rollback and SQLException >> >> >>can you send us logs from the server? >> >>or a test case which demonstrates this? >> >>Dave >> >>On Thu, 2003-01-23 at 12:07, Andy Kriger wrote: >> >>>I received this exception when trying to rollback a transaction through >> >>the >> >>>Postgres JDBC driver (build106) used by a webapp running in Resin > > (2.0.6). > >>I >> >>>have tried setting up the connection pool in my web.xml to use both >>>javax.sql.DataSource and javax.sql.XADataSource, but both give me this >>>exception: >>> >>>java.sql.SQLException: ERROR: parser: parse error at or near > > "qrollback" > >>> at >> >>org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) >> >>> at >>> >> > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > >>>.java:482) >>> at >>> >> > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > >>>.java:461) >>> at >>> >> > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio > >>>n.java:1031) >>> at >>> >> > com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470 > >>>) >>> at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132) >>> >>>Any idea what that means and how I can prevent it from occuring? >>> >>>thx >>>andy kriger >>> >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 6: Have you searched our list archives? >>> >>>http://archives.postgresql.org >> >>-- >>Dave Cramer <Dave@micro-automation.net> >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 3: if posting/reading through Usenet, please send an appropriate >>subscribe-nomail command to majordomo@postgresql.org so that your >>message can get through to the mailing list cleanly > > -- > Dave Cramer <Dave@micro-automation.net> > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Barry, I haven't looked at the code, but it appears that the driver is not dealing well with a ill formed prepared statement. There are 3 variables, and 4 ? marks. Dave On Fri, 2003-01-24 at 00:45, Barry Lind wrote: > Andy, > > Now that you have a reproducable test case, can you send a code sample > that shows the problem. After reading this thread, I am still not sure > when the error message is being given. A code example would help a lot. > > Also, what version of the driver are you using? > > --Barry > > Andy Kriger wrote: > > [ moving this off the Resin mailing list as it is not about Resin ] > > > > I think I figured out what is going on. Below is the pgsql log (at level 2) > > You can see the problem in line 2: the query is being mangled. > > > > The original query was > > insert into purchase (customer_id,item_id,quantity) values (?,?,?,?); > > > > I realize this query is bad since there aren't as many columns as values. It > > was only meant to trigger a SQLException so I could verify rollback. > > However, my PreparedStatment was setting only 3 of the 4 values. And doing > > that caused the mangling. If I set all 4 values, I get a SQLException > > (INSERT has more expressions than target columns) followed by a successful > > rollback. If I use the same number of values and columns but don't set the > > last value, I get a SQLException (No value specified for parameter 3) > > followed by an unsucessful rollback (the original problem). > > > > So, it looks like the JDBC driver is not handling the error condition where > > the last value is not set. And this is mangling the rollback query (though > > I'm guessing it would mangle the next query regardless of what it was). I > > was not able to isolate it to whether you will get the same problem if any > > value is not set (the 1st or 2nd, for example). > > > > Hopefully, one of the JDBC driver developers can take it from here. > > > > -a > > > > === PGSQL log === > > > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > > 2003-01-23 13:52:43 DEBUG: query: insert into purchase > > (customer_id,item_id,quantity) values (8,2,2Qrollback; begin; > > 2003-01-23 13:52:43 ERROR: parser: parse error at or near "qrollback" > > 2003-01-23 13:52:43 DEBUG: AbortCurrentTransaction > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > > 2003-01-23 13:52:43 DEBUG: query: rollback; begin; > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin; > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin; > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand > > 2003-01-23 13:52:43 DEBUG: query: end > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: end > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand > > 2003-01-23 13:52:43 DEBUG: proc_exit(0) > > 2003-01-23 13:52:43 DEBUG: shmem_exit(0) > > 2003-01-23 13:52:43 DEBUG: exit(0) > > 2003-01-23 13:52:43 DEBUG: reaping dead processes > > 2003-01-23 13:52:43 DEBUG: child process (pid 31273) exited with exit code > > 0 > > > > -----Original Message----- > > From: pgsql-jdbc-owner@postgresql.org > > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer > > Sent: Thursday, January 23, 2003 13:06 > > To: Andy Kriger > > Cc: Resin-Interest; Pgsql-Jdbc > > Subject: Re: [JDBC] question about rollback and SQLException > > > > > > Andy, > > > > The logs from the server would be good, I just tested rollback and it > > works, but if there was something some how left in the query buffer, > > this would be a problem > > > > DAve > > On Thu, 2003-01-23 at 12:44, Andy Kriger wrote: > > > >>I don't really have something I can easily reduce out of my code. > >> > >>I started a transaction with Connection.setAutoCommit(false) > >>Ran some SQL queries using PreparedStatements > >>Ran a query that I knew would fail, throwing a SQLException > >>Then I rolledback the transaction (just to be sure) with > >>Connection.rollback() > >> > >>It was during Connection.rollback() that I received the exception that's > > > > in > > > >>my first email. The stack trace picks up from my code as it enters the > > > > Resin > > > >>& driver code. > >> > >>-----Original Message----- > >>From: Dave Cramer [mailto:Dave@micro-automation.net] > >>Sent: Thursday, January 23, 2003 12:32 > >>To: Andy Kriger > >>Cc: Resin-Interest; Pgsql-Jdbc > >>Subject: Re: [JDBC] question about rollback and SQLException > >> > >> > >>can you send us logs from the server? > >> > >>or a test case which demonstrates this? > >> > >>Dave > >> > >>On Thu, 2003-01-23 at 12:07, Andy Kriger wrote: > >> > >>>I received this exception when trying to rollback a transaction through > >> > >>the > >> > >>>Postgres JDBC driver (build106) used by a webapp running in Resin > > > > (2.0.6). > > > >>I > >> > >>>have tried setting up the connection pool in my web.xml to use both > >>>javax.sql.DataSource and javax.sql.XADataSource, but both give me this > >>>exception: > >>> > >>>java.sql.SQLException: ERROR: parser: parse error at or near > > > > "qrollback" > > > >>> at > >> > >>org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) > >> > >>> at > >>> > >> > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > > >>>.java:482) > >>> at > >>> > >> > > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection > > > >>>.java:461) > >>> at > >>> > >> > > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio > > > >>>n.java:1031) > >>> at > >>> > >> > > com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470 > > > >>>) > >>> at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132) > >>> > >>>Any idea what that means and how I can prevent it from occuring? > >>> > >>>thx > >>>andy kriger > >>> > >>> > >>> > >>>---------------------------(end of broadcast)--------------------------- > >>>TIP 6: Have you searched our list archives? > >>> > >>>http://archives.postgresql.org > >> > >>-- > >>Dave Cramer <Dave@micro-automation.net> > >> > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 3: if posting/reading through Usenet, please send an appropriate > >>subscribe-nomail command to majordomo@postgresql.org so that your > >>message can get through to the mailing list cleanly > > > > -- > > Dave Cramer <Dave@micro-automation.net> > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Dave Cramer <Dave@micro-automation.net>
Here you go - sample code. You'll need to set the url/usr/pass/query to make
sense for your db (a dummy table with 3 int cols will do the trick).
The key is the setInt methods. One is commented out, this will trigger the
exceptions. You can comment any of them out and get the same exception (it
doesn't just have to be the last one).
No value specified for parameter 2
at
org.postgresql.core.QueryExecutor.sendQuery(QueryExecutor.java:148)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:70)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
.java:505)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
ava:320)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:48)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1State
ment.java:197)
at DriverBug.main(DriverBug.java:26)
java.sql.SQLException: ERROR: parser: parse error at or near "qrollback"
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
.java:482)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
.java:461)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio
n.java:1031)
at DriverBug.main(DriverBug.java:31)
---TEST CODE---
import java.sql.*;
public class DriverBug
{
public static void main(String[] args)
{
String url = "";
String user = "";
String pass = "";
String query = "insert into table (col1,col2,col3) values (?, ?, ?)";
try {
Class.forName("org.postgresql.Driver");
} catch(Exception e) {
e.printStackTrace();
System.exit(1);
}
Connection cnx = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
cnx = DriverManager.getConnection(url,user,pass);
cnx.setAutoCommit(false);
System.out.println("connection obtained");
stmt = cnx.prepareStatement(query);
stmt.setInt(1,3);
//stmt.setInt(2,53);
stmt.setInt(3,10);
System.out.println("statement initialized");
stmt.executeUpdate();
System.out.println("update done");
} catch(SQLException se) {
try {
se.printStackTrace();
cnx.rollback();
} catch(Exception e) {
e.printStackTrace();
}
} finally {
try {
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(cnx != null) cnx.close();
} catch(Exception e) {
e.printStackTrace();
}
}
System.out.println("FIN");
}
}
-----Original Message-----
From: Dave Cramer [mailto:Dave@micro-automation.net]
Sent: Friday, January 24, 2003 11:44
To: Barry Lind
Cc: Andy Kriger; Pgsql-Jdbc; Kevin Tung
Subject: Re: [JDBC] question about rollback and SQLException
Barry,
I haven't looked at the code, but it appears that the driver is not
dealing well with a ill formed prepared statement.
There are 3 variables, and 4 ? marks.
Dave
On Fri, 2003-01-24 at 00:45, Barry Lind wrote:
> Andy,
>
> Now that you have a reproducable test case, can you send a code sample
> that shows the problem. After reading this thread, I am still not sure
> when the error message is being given. A code example would help a lot.
>
> Also, what version of the driver are you using?
>
> --Barry
>
> Andy Kriger wrote:
> > [ moving this off the Resin mailing list as it is not about Resin ]
> >
> > I think I figured out what is going on. Below is the pgsql log (at level
2)
> > You can see the problem in line 2: the query is being mangled.
> >
> > The original query was
> > insert into purchase (customer_id,item_id,quantity) values (?,?,?,?);
> >
> > I realize this query is bad since there aren't as many columns as
values. It
> > was only meant to trigger a SQLException so I could verify rollback.
> > However, my PreparedStatment was setting only 3 of the 4 values. And
doing
> > that caused the mangling. If I set all 4 values, I get a SQLException
> > (INSERT has more expressions than target columns) followed by a
successful
> > rollback. If I use the same number of values and columns but don't set
the
> > last value, I get a SQLException (No value specified for parameter 3)
> > followed by an unsucessful rollback (the original problem).
> >
> > So, it looks like the JDBC driver is not handling the error condition
where
> > the last value is not set. And this is mangling the rollback query
(though
> > I'm guessing it would mangle the next query regardless of what it was).
I
> > was not able to isolate it to whether you will get the same problem if
any
> > value is not set (the 1st or 2nd, for example).
> >
> > Hopefully, one of the JDBC driver developers can take it from here.
> >
> > -a
> >
> > === PGSQL log ===
> >
> > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > 2003-01-23 13:52:43 DEBUG: query: insert into purchase
> > (customer_id,item_id,quantity) values (8,2,2Qrollback; begin;
> > 2003-01-23 13:52:43 ERROR: parser: parse error at or near "qrollback"
> > 2003-01-23 13:52:43 DEBUG: AbortCurrentTransaction
> > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > 2003-01-23 13:52:43 DEBUG: query: rollback; begin;
> > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin;
> > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand
> > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin;
> > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand
> > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > 2003-01-23 13:52:43 DEBUG: query: end
> > 2003-01-23 13:52:43 DEBUG: ProcessUtility: end
> > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand
> > 2003-01-23 13:52:43 DEBUG: proc_exit(0)
> > 2003-01-23 13:52:43 DEBUG: shmem_exit(0)
> > 2003-01-23 13:52:43 DEBUG: exit(0)
> > 2003-01-23 13:52:43 DEBUG: reaping dead processes
> > 2003-01-23 13:52:43 DEBUG: child process (pid 31273) exited with exit
code
> > 0
> >
> > -----Original Message-----
> > From: pgsql-jdbc-owner@postgresql.org
> > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> > Sent: Thursday, January 23, 2003 13:06
> > To: Andy Kriger
> > Cc: Resin-Interest; Pgsql-Jdbc
> > Subject: Re: [JDBC] question about rollback and SQLException
> >
> >
> > Andy,
> >
> > The logs from the server would be good, I just tested rollback and it
> > works, but if there was something some how left in the query buffer,
> > this would be a problem
> >
> > DAve
> > On Thu, 2003-01-23 at 12:44, Andy Kriger wrote:
> >
> >>I don't really have something I can easily reduce out of my code.
> >>
> >>I started a transaction with Connection.setAutoCommit(false)
> >>Ran some SQL queries using PreparedStatements
> >>Ran a query that I knew would fail, throwing a SQLException
> >>Then I rolledback the transaction (just to be sure) with
> >>Connection.rollback()
> >>
> >>It was during Connection.rollback() that I received the exception that's
> >
> > in
> >
> >>my first email. The stack trace picks up from my code as it enters the
> >
> > Resin
> >
> >>& driver code.
> >>
> >>-----Original Message-----
> >>From: Dave Cramer [mailto:Dave@micro-automation.net]
> >>Sent: Thursday, January 23, 2003 12:32
> >>To: Andy Kriger
> >>Cc: Resin-Interest; Pgsql-Jdbc
> >>Subject: Re: [JDBC] question about rollback and SQLException
> >>
> >>
> >>can you send us logs from the server?
> >>
> >>or a test case which demonstrates this?
> >>
> >>Dave
> >>
> >>On Thu, 2003-01-23 at 12:07, Andy Kriger wrote:
> >>
> >>>I received this exception when trying to rollback a transaction through
> >>
> >>the
> >>
> >>>Postgres JDBC driver (build106) used by a webapp running in Resin
> >
> > (2.0.6).
> >
> >>I
> >>
> >>>have tried setting up the connection pool in my web.xml to use both
> >>>javax.sql.DataSource and javax.sql.XADataSource, but both give me this
> >>>exception:
> >>>
> >>>java.sql.SQLException: ERROR: parser: parse error at or near
> >
> > "qrollback"
> >
> >>> at
> >>
> >>org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
> >>
> >>> at
> >>>
> >>
> >
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> >
> >>>.java:482)
> >>> at
> >>>
> >>
> >
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> >
> >>>.java:461)
> >>> at
> >>>
> >>
> >
org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio
> >
> >>>n.java:1031)
> >>> at
> >>>
> >>
> >
com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470
> >
> >>>)
> >>> at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132)
> >>>
> >>>Any idea what that means and how I can prevent it from occuring?
> >>>
> >>>thx
> >>>andy kriger
> >>>
> >>>
> >>>
> >>>---------------------------(end of
broadcast)---------------------------
> >>>TIP 6: Have you searched our list archives?
> >>>
> >>>http://archives.postgresql.org
> >>
> >>--
> >>Dave Cramer <Dave@micro-automation.net>
> >>
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 3: if posting/reading through Usenet, please send an appropriate
> >>subscribe-nomail command to majordomo@postgresql.org so that your
> >>message can get through to the mailing list cleanly
> >
> > --
> > Dave Cramer <Dave@micro-automation.net>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Dave Cramer <Dave@micro-automation.net>
The following patch fixes this problem. The QueryExecutor was detecting
this error halfway through the sendQuery process without resetting the
query state. I have moved this check to before any processing occurs.
Kris Jurka
On Fri, 24 Jan 2003, Andy Kriger wrote:
> Here you go - sample code. You'll need to set the url/usr/pass/query to make
> sense for your db (a dummy table with 3 int cols will do the trick).
>
> The key is the setInt methods. One is commented out, this will trigger the
> exceptions. You can comment any of them out and get the same exception (it
> doesn't just have to be the last one).
>
> No value specified for parameter 2
> at
> org.postgresql.core.QueryExecutor.sendQuery(QueryExecutor.java:148)
> at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:70)
> at
> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> .java:505)
> at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
> ava:320)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
> ava:48)
> at
> org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1State
> ment.java:197)
> at DriverBug.main(DriverBug.java:26)
> java.sql.SQLException: ERROR: parser: parse error at or near "qrollback"
> at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
> at
> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> .java:482)
> at
> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> .java:461)
> at
> org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio
> n.java:1031)
> at DriverBug.main(DriverBug.java:31)
>
> ---TEST CODE---
>
> import java.sql.*;
> public class DriverBug
> {
> public static void main(String[] args)
> {
> String url = "";
> String user = "";
> String pass = "";
> String query = "insert into table (col1,col2,col3) values (?, ?, ?)";
>
> try {
> Class.forName("org.postgresql.Driver");
> } catch(Exception e) {
> e.printStackTrace();
> System.exit(1);
> }
>
> Connection cnx = null;
> PreparedStatement stmt = null;
> ResultSet rs = null;
> try {
> cnx = DriverManager.getConnection(url,user,pass);
> cnx.setAutoCommit(false);
> System.out.println("connection obtained");
> stmt = cnx.prepareStatement(query);
> stmt.setInt(1,3);
> //stmt.setInt(2,53);
> stmt.setInt(3,10);
> System.out.println("statement initialized");
> stmt.executeUpdate();
> System.out.println("update done");
> } catch(SQLException se) {
> try {
> se.printStackTrace();
> cnx.rollback();
> } catch(Exception e) {
> e.printStackTrace();
> }
> } finally {
> try {
> if(rs != null) rs.close();
> if(stmt != null) stmt.close();
> if(cnx != null) cnx.close();
> } catch(Exception e) {
> e.printStackTrace();
> }
> }
> System.out.println("FIN");
> }
> }
>
> -----Original Message-----
> From: Dave Cramer [mailto:Dave@micro-automation.net]
> Sent: Friday, January 24, 2003 11:44
> To: Barry Lind
> Cc: Andy Kriger; Pgsql-Jdbc; Kevin Tung
> Subject: Re: [JDBC] question about rollback and SQLException
>
>
> Barry,
>
> I haven't looked at the code, but it appears that the driver is not
> dealing well with a ill formed prepared statement.
>
> There are 3 variables, and 4 ? marks.
>
> Dave
> On Fri, 2003-01-24 at 00:45, Barry Lind wrote:
> > Andy,
> >
> > Now that you have a reproducable test case, can you send a code sample
> > that shows the problem. After reading this thread, I am still not sure
> > when the error message is being given. A code example would help a lot.
> >
> > Also, what version of the driver are you using?
> >
> > --Barry
> >
> > Andy Kriger wrote:
> > > [ moving this off the Resin mailing list as it is not about Resin ]
> > >
> > > I think I figured out what is going on. Below is the pgsql log (at level
> 2)
> > > You can see the problem in line 2: the query is being mangled.
> > >
> > > The original query was
> > > insert into purchase (customer_id,item_id,quantity) values (?,?,?,?);
> > >
> > > I realize this query is bad since there aren't as many columns as
> values. It
> > > was only meant to trigger a SQLException so I could verify rollback.
> > > However, my PreparedStatment was setting only 3 of the 4 values. And
> doing
> > > that caused the mangling. If I set all 4 values, I get a SQLException
> > > (INSERT has more expressions than target columns) followed by a
> successful
> > > rollback. If I use the same number of values and columns but don't set
> the
> > > last value, I get a SQLException (No value specified for parameter 3)
> > > followed by an unsucessful rollback (the original problem).
> > >
> > > So, it looks like the JDBC driver is not handling the error condition
> where
> > > the last value is not set. And this is mangling the rollback query
> (though
> > > I'm guessing it would mangle the next query regardless of what it was).
> I
> > > was not able to isolate it to whether you will get the same problem if
> any
> > > value is not set (the 1st or 2nd, for example).
> > >
> > > Hopefully, one of the JDBC driver developers can take it from here.
> > >
> > > -a
> > >
> > > === PGSQL log ===
> > >
> > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG: query: insert into purchase
> > > (customer_id,item_id,quantity) values (8,2,2Qrollback; begin;
> > > 2003-01-23 13:52:43 ERROR: parser: parse error at or near "qrollback"
> > > 2003-01-23 13:52:43 DEBUG: AbortCurrentTransaction
> > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG: query: rollback; begin;
> > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin;
> > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin;
> > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG: query: end
> > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: end
> > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand
> > > 2003-01-23 13:52:43 DEBUG: proc_exit(0)
> > > 2003-01-23 13:52:43 DEBUG: shmem_exit(0)
> > > 2003-01-23 13:52:43 DEBUG: exit(0)
> > > 2003-01-23 13:52:43 DEBUG: reaping dead processes
> > > 2003-01-23 13:52:43 DEBUG: child process (pid 31273) exited with exit
> code
> > > 0
> > >
> > > -----Original Message-----
> > > From: pgsql-jdbc-owner@postgresql.org
> > > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> > > Sent: Thursday, January 23, 2003 13:06
> > > To: Andy Kriger
> > > Cc: Resin-Interest; Pgsql-Jdbc
> > > Subject: Re: [JDBC] question about rollback and SQLException
> > >
> > >
> > > Andy,
> > >
> > > The logs from the server would be good, I just tested rollback and it
> > > works, but if there was something some how left in the query buffer,
> > > this would be a problem
> > >
> > > DAve
> > > On Thu, 2003-01-23 at 12:44, Andy Kriger wrote:
> > >
> > >>I don't really have something I can easily reduce out of my code.
> > >>
> > >>I started a transaction with Connection.setAutoCommit(false)
> > >>Ran some SQL queries using PreparedStatements
> > >>Ran a query that I knew would fail, throwing a SQLException
> > >>Then I rolledback the transaction (just to be sure) with
> > >>Connection.rollback()
> > >>
> > >>It was during Connection.rollback() that I received the exception that's
> > >
> > > in
> > >
> > >>my first email. The stack trace picks up from my code as it enters the
> > >
> > > Resin
> > >
> > >>& driver code.
> > >>
> > >>-----Original Message-----
> > >>From: Dave Cramer [mailto:Dave@micro-automation.net]
> > >>Sent: Thursday, January 23, 2003 12:32
> > >>To: Andy Kriger
> > >>Cc: Resin-Interest; Pgsql-Jdbc
> > >>Subject: Re: [JDBC] question about rollback and SQLException
> > >>
> > >>
> > >>can you send us logs from the server?
> > >>
> > >>or a test case which demonstrates this?
> > >>
> > >>Dave
> > >>
> > >>On Thu, 2003-01-23 at 12:07, Andy Kriger wrote:
> > >>
> > >>>I received this exception when trying to rollback a transaction through
> > >>
> > >>the
> > >>
> > >>>Postgres JDBC driver (build106) used by a webapp running in Resin
> > >
> > > (2.0.6).
> > >
> > >>I
> > >>
> > >>>have tried setting up the connection pool in my web.xml to use both
> > >>>javax.sql.DataSource and javax.sql.XADataSource, but both give me this
> > >>>exception:
> > >>>
> > >>>java.sql.SQLException: ERROR: parser: parse error at or near
> > >
> > > "qrollback"
> > >
> > >>> at
> > >>
> > >>org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
> > >>
> > >>> at
> > >>>
> > >>
> > >
> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> > >
> > >>>.java:482)
> > >>> at
> > >>>
> > >>
> > >
> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> > >
> > >>>.java:461)
> > >>> at
> > >>>
> > >>
> > >
> org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio
> > >
> > >>>n.java:1031)
> > >>> at
> > >>>
> > >>
> > >
> com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470
> > >
> > >>>)
> > >>> at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132)
> > >>>
> > >>>Any idea what that means and how I can prevent it from occuring?
> > >>>
> > >>>thx
> > >>>andy kriger
> > >>>
> > >>>
> > >>>
> > >>>---------------------------(end of
> broadcast)---------------------------
> > >>>TIP 6: Have you searched our list archives?
> > >>>
> > >>>http://archives.postgresql.org
> > >>
> > >>--
> > >>Dave Cramer <Dave@micro-automation.net>
> > >>
> > >>
> > >>
> > >>---------------------------(end of broadcast)---------------------------
> > >>TIP 3: if posting/reading through Usenet, please send an appropriate
> > >>subscribe-nomail command to majordomo@postgresql.org so that your
> > >>message can get through to the mailing list cleanly
> > >
> > > --
> > > Dave Cramer <Dave@micro-automation.net>
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> --
> Dave Cramer <Dave@micro-automation.net>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Attachment
Kris,
This has been applied to HEAD, and 7.2.3
Dave
On Mon, 2003-01-27 at 13:29, Kris Jurka wrote:
> The following patch fixes this problem. The QueryExecutor was detecting
> this error halfway through the sendQuery process without resetting the
> query state. I have moved this check to before any processing occurs.
>
> Kris Jurka
>
>
>
>
> On Fri, 24 Jan 2003, Andy Kriger wrote:
>
> > Here you go - sample code. You'll need to set the url/usr/pass/query to make
> > sense for your db (a dummy table with 3 int cols will do the trick).
> >
> > The key is the setInt methods. One is commented out, this will trigger the
> > exceptions. You can comment any of them out and get the same exception (it
> > doesn't just have to be the last one).
> >
> > No value specified for parameter 2
> > at
> > org.postgresql.core.QueryExecutor.sendQuery(QueryExecutor.java:148)
> > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:70)
> > at
> > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> > .java:505)
> > at
> > org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
> > ava:320)
> > at
> > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
> > ava:48)
> > at
> > org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1State
> > ment.java:197)
> > at DriverBug.main(DriverBug.java:26)
> > java.sql.SQLException: ERROR: parser: parse error at or near "qrollback"
> > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
> > at
> > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> > .java:482)
> > at
> > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> > .java:461)
> > at
> > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio
> > n.java:1031)
> > at DriverBug.main(DriverBug.java:31)
> >
> > ---TEST CODE---
> >
> > import java.sql.*;
> > public class DriverBug
> > {
> > public static void main(String[] args)
> > {
> > String url = "";
> > String user = "";
> > String pass = "";
> > String query = "insert into table (col1,col2,col3) values (?, ?, ?)";
> >
> > try {
> > Class.forName("org.postgresql.Driver");
> > } catch(Exception e) {
> > e.printStackTrace();
> > System.exit(1);
> > }
> >
> > Connection cnx = null;
> > PreparedStatement stmt = null;
> > ResultSet rs = null;
> > try {
> > cnx = DriverManager.getConnection(url,user,pass);
> > cnx.setAutoCommit(false);
> > System.out.println("connection obtained");
> > stmt = cnx.prepareStatement(query);
> > stmt.setInt(1,3);
> > //stmt.setInt(2,53);
> > stmt.setInt(3,10);
> > System.out.println("statement initialized");
> > stmt.executeUpdate();
> > System.out.println("update done");
> > } catch(SQLException se) {
> > try {
> > se.printStackTrace();
> > cnx.rollback();
> > } catch(Exception e) {
> > e.printStackTrace();
> > }
> > } finally {
> > try {
> > if(rs != null) rs.close();
> > if(stmt != null) stmt.close();
> > if(cnx != null) cnx.close();
> > } catch(Exception e) {
> > e.printStackTrace();
> > }
> > }
> > System.out.println("FIN");
> > }
> > }
> >
> > -----Original Message-----
> > From: Dave Cramer [mailto:Dave@micro-automation.net]
> > Sent: Friday, January 24, 2003 11:44
> > To: Barry Lind
> > Cc: Andy Kriger; Pgsql-Jdbc; Kevin Tung
> > Subject: Re: [JDBC] question about rollback and SQLException
> >
> >
> > Barry,
> >
> > I haven't looked at the code, but it appears that the driver is not
> > dealing well with a ill formed prepared statement.
> >
> > There are 3 variables, and 4 ? marks.
> >
> > Dave
> > On Fri, 2003-01-24 at 00:45, Barry Lind wrote:
> > > Andy,
> > >
> > > Now that you have a reproducable test case, can you send a code sample
> > > that shows the problem. After reading this thread, I am still not sure
> > > when the error message is being given. A code example would help a lot.
> > >
> > > Also, what version of the driver are you using?
> > >
> > > --Barry
> > >
> > > Andy Kriger wrote:
> > > > [ moving this off the Resin mailing list as it is not about Resin ]
> > > >
> > > > I think I figured out what is going on. Below is the pgsql log (at level
> > 2)
> > > > You can see the problem in line 2: the query is being mangled.
> > > >
> > > > The original query was
> > > > insert into purchase (customer_id,item_id,quantity) values (?,?,?,?);
> > > >
> > > > I realize this query is bad since there aren't as many columns as
> > values. It
> > > > was only meant to trigger a SQLException so I could verify rollback.
> > > > However, my PreparedStatment was setting only 3 of the 4 values. And
> > doing
> > > > that caused the mangling. If I set all 4 values, I get a SQLException
> > > > (INSERT has more expressions than target columns) followed by a
> > successful
> > > > rollback. If I use the same number of values and columns but don't set
> > the
> > > > last value, I get a SQLException (No value specified for parameter 3)
> > > > followed by an unsucessful rollback (the original problem).
> > > >
> > > > So, it looks like the JDBC driver is not handling the error condition
> > where
> > > > the last value is not set. And this is mangling the rollback query
> > (though
> > > > I'm guessing it would mangle the next query regardless of what it was).
> > I
> > > > was not able to isolate it to whether you will get the same problem if
> > any
> > > > value is not set (the 1st or 2nd, for example).
> > > >
> > > > Hopefully, one of the JDBC driver developers can take it from here.
> > > >
> > > > -a
> > > >
> > > > === PGSQL log ===
> > > >
> > > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > > > 2003-01-23 13:52:43 DEBUG: query: insert into purchase
> > > > (customer_id,item_id,quantity) values (8,2,2Qrollback; begin;
> > > > 2003-01-23 13:52:43 ERROR: parser: parse error at or near "qrollback"
> > > > 2003-01-23 13:52:43 DEBUG: AbortCurrentTransaction
> > > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > > > 2003-01-23 13:52:43 DEBUG: query: rollback; begin;
> > > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin;
> > > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand
> > > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: rollback; begin;
> > > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand
> > > > 2003-01-23 13:52:43 DEBUG: StartTransactionCommand
> > > > 2003-01-23 13:52:43 DEBUG: query: end
> > > > 2003-01-23 13:52:43 DEBUG: ProcessUtility: end
> > > > 2003-01-23 13:52:43 DEBUG: CommitTransactionCommand
> > > > 2003-01-23 13:52:43 DEBUG: proc_exit(0)
> > > > 2003-01-23 13:52:43 DEBUG: shmem_exit(0)
> > > > 2003-01-23 13:52:43 DEBUG: exit(0)
> > > > 2003-01-23 13:52:43 DEBUG: reaping dead processes
> > > > 2003-01-23 13:52:43 DEBUG: child process (pid 31273) exited with exit
> > code
> > > > 0
> > > >
> > > > -----Original Message-----
> > > > From: pgsql-jdbc-owner@postgresql.org
> > > > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> > > > Sent: Thursday, January 23, 2003 13:06
> > > > To: Andy Kriger
> > > > Cc: Resin-Interest; Pgsql-Jdbc
> > > > Subject: Re: [JDBC] question about rollback and SQLException
> > > >
> > > >
> > > > Andy,
> > > >
> > > > The logs from the server would be good, I just tested rollback and it
> > > > works, but if there was something some how left in the query buffer,
> > > > this would be a problem
> > > >
> > > > DAve
> > > > On Thu, 2003-01-23 at 12:44, Andy Kriger wrote:
> > > >
> > > >>I don't really have something I can easily reduce out of my code.
> > > >>
> > > >>I started a transaction with Connection.setAutoCommit(false)
> > > >>Ran some SQL queries using PreparedStatements
> > > >>Ran a query that I knew would fail, throwing a SQLException
> > > >>Then I rolledback the transaction (just to be sure) with
> > > >>Connection.rollback()
> > > >>
> > > >>It was during Connection.rollback() that I received the exception that's
> > > >
> > > > in
> > > >
> > > >>my first email. The stack trace picks up from my code as it enters the
> > > >
> > > > Resin
> > > >
> > > >>& driver code.
> > > >>
> > > >>-----Original Message-----
> > > >>From: Dave Cramer [mailto:Dave@micro-automation.net]
> > > >>Sent: Thursday, January 23, 2003 12:32
> > > >>To: Andy Kriger
> > > >>Cc: Resin-Interest; Pgsql-Jdbc
> > > >>Subject: Re: [JDBC] question about rollback and SQLException
> > > >>
> > > >>
> > > >>can you send us logs from the server?
> > > >>
> > > >>or a test case which demonstrates this?
> > > >>
> > > >>Dave
> > > >>
> > > >>On Thu, 2003-01-23 at 12:07, Andy Kriger wrote:
> > > >>
> > > >>>I received this exception when trying to rollback a transaction through
> > > >>
> > > >>the
> > > >>
> > > >>>Postgres JDBC driver (build106) used by a webapp running in Resin
> > > >
> > > > (2.0.6).
> > > >
> > > >>I
> > > >>
> > > >>>have tried setting up the connection pool in my web.xml to use both
> > > >>>javax.sql.DataSource and javax.sql.XADataSource, but both give me this
> > > >>>exception:
> > > >>>
> > > >>>java.sql.SQLException: ERROR: parser: parse error at or near
> > > >
> > > > "qrollback"
> > > >
> > > >>> at
> > > >>
> > > >>org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
> > > >>
> > > >>> at
> > > >>>
> > > >>
> > > >
> > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> > > >
> > > >>>.java:482)
> > > >>> at
> > > >>>
> > > >>
> > > >
> > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection
> > > >
> > > >>>.java:461)
> > > >>> at
> > > >>>
> > > >>
> > > >
> > org.postgresql.jdbc1.AbstractJdbc1Connection.rollback(AbstractJdbc1Connectio
> > > >
> > > >>>n.java:1031)
> > > >>> at
> > > >>>
> > > >>
> > > >
> > com.caucho.sql.UserConnectionAdapter.rollback(UserConnectionAdapter.java:470
> > > >
> > > >>>)
> > > >>> at com.gto.db.SqlTransaction.rollback(SqlTransaction.java:132)
> > > >>>
> > > >>>Any idea what that means and how I can prevent it from occuring?
> > > >>>
> > > >>>thx
> > > >>>andy kriger
> > > >>>
> > > >>>
> > > >>>
> > > >>>---------------------------(end of
> > broadcast)---------------------------
> > > >>>TIP 6: Have you searched our list archives?
> > > >>>
> > > >>>http://archives.postgresql.org
> > > >>
> > > >>--
> > > >>Dave Cramer <Dave@micro-automation.net>
> > > >>
> > > >>
> > > >>
> > > >>---------------------------(end of broadcast)---------------------------
> > > >>TIP 3: if posting/reading through Usenet, please send an appropriate
> > > >>subscribe-nomail command to majordomo@postgresql.org so that your
> > > >>message can get through to the mailing list cleanly
> > > >
> > > > --
> > > > Dave Cramer <Dave@micro-automation.net>
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 4: Don't 'kill -9' the postmaster
> > > >
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 4: Don't 'kill -9' the postmaster
> > > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > --
> > Dave Cramer <Dave@micro-automation.net>
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
> ______________________________________________________________________
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Dave Cramer <Dave@micro-automation.net>