Обсуждение: JDBC parse error
Hi, I'm having an intermittent problem with the PostGres JDBC Driver I recently downloaded from http://jdbc.postgresql.org/download.html. I am running a Java Servlet application under Tomcat 4.0 and Solaris 8. On some occasions it seems that the String passed to a Statement object gets altered when executed. I get a SQLException with the text "parse error at or near selec". It always seems to happen on a select. On some occasions it works but it seems to happen on some queries more than others. The text in the parse error is different at times for the same query (another example was selecaria). Here is an example stack trace: java.sql.SQLException: ERROR: parser: parse error at or near "selec" at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94) at org.postgresql.Connection.ExecSQL(Connection.java:398) at org.postgresql.jdbc2.Statement.execute(Statement.java:130) at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54) at org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.java:9 9) at com.conquestnet.database.entity.EducationDegree.getAllEducationDegrees(Educa tionDegree.java:211) at org.apache.jsp.edit$jsp._jspService(edit$jsp.java:218) at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(Unknown Source) at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown Source) at org.apache.jasper.servlet.JspServlet.service(Unknown Source) Any help that can be provided is much appriciated. I've searched the FAQ but could not find anything that seemed like the same problem. Thanks, John Metz
John, Can you turn on query logging on the server to see what the actual queries sent from the jdbc driver to the server actually are? That might give some indication of where the problem is. Finally are you sure that your own code isn't munging the string before it is passed to the jdbc driver? I haven't heard of anyone else seeing this problem. We need to track down what is different in your environment that is causing it. Also if you would mention what version of the server and jdbc driver you are using, that would be helpful. thanks, --Barry John Metz wrote: >Hi, > >I'm having an intermittent problem with the PostGres JDBC Driver I recently >downloaded from http://jdbc.postgresql.org/download.html. I am running a >Java Servlet application under Tomcat 4.0 and Solaris 8. On some occasions >it seems that the String passed to a Statement object gets altered when >executed. I get a SQLException with the text "parse error at or near selec". >It always seems to happen on a select. On some occasions it works but it >seems to happen on some queries more than others. The text in the parse >error is different at times for the same query (another example was >selecaria). Here is an example stack trace: > >java.sql.SQLException: ERROR: parser: parse error at or near "selec" > > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94) > at org.postgresql.Connection.ExecSQL(Connection.java:398) > at org.postgresql.jdbc2.Statement.execute(Statement.java:130) > at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54) > at >org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.java:9 >9) > at >com.conquestnet.database.entity.EducationDegree.getAllEducationDegrees(Educa >tionDegree.java:211) > at org.apache.jsp.edit$jsp._jspService(edit$jsp.java:218) > at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) > at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(Unknown >Source) > at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown Source) > at org.apache.jasper.servlet.JspServlet.service(Unknown Source) > >Any help that can be provided is much appriciated. I've searched the FAQ >but could not find anything that seemed like the same problem. > >Thanks, > >John Metz > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > > >
Barry,
You SA upgraded us to PostGres 7.2.1, we had been using 7.1.2, and we are
able to recreate the problem. We are using the JDBC driver that came with
the PostGres 7.2.1 distribution. We are running under Java 1.3.1_01 on
Solaris 8. We did turn debugging on and the output is below. I included a
successful query executed before the one that failed. I also included the
output from a psql -V command. I was able to run the exact same operation
minutes before and it worked. Also, I can execute the query (as it is in
our Java code) in psql and it works. Please let me know if there's anything
else I can do to help you find the problem.
bash-2.03$ psql -V
psql (PostgreSQL) 7.2.1
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996, Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.
--- DEBUG log ---
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SELECT id, name FROM clearance ORDER BY name
DEBUG: parse tree: { QUERY :command 1 :utility <> :resultRelation 0 :into <>
:
isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks
false :
rtable ({ RTE :relname clearance :relid 24755 :subquery <> :alias <> :eref
{ AT
TR :relname clearance :attrs ( "id" "name" )} :inh true :inFromCl true
:checkF
orRead true :checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR
:fromlist
({ RANGETBLREF 1 }) :quals <>} :rowMarks () :targetList ({ TARGETENTRY
:resdom
{ RESDOM :resno 1 :restype 23 :restypmod -1 :resname id :reskey 0 :reskeyop
0 :r
essortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype
23 :
vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom
{
RESDOM :resno 2 :restype 1043 :restypmod 34 :resname name :reskey 0
:reskeyop 0
:ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 2
:vartype 10
43 :vartypmod 34 :varlevelsup 0 :varnoold 1 :varoattno 2}}) :groupClause <>
:ha
vingQual <> :distinctClause <> :sortClause ({ SORTCLAUSE :tleSortGroupRef 1
:sor
top 1066 }) :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()
}
DEBUG: rewritten parse tree:
DEBUG: { QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal
fal
se :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable
({ RT
E :relname clearance :relid 24755 :subquery <> :alias <> :eref { ATTR
:relname
clearance :attrs ( "id" "name" )} :inh true :inFromCl true :checkForRead
true
:checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({
RANGETBL
REF 1 }) :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM
:re
sno 1 :restype 23 :restypmod -1 :resname id :reskey 0 :reskeyop 0
:ressortgroupr
ef 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM
:resn
o 2 :restype 1043 :restypmod 34 :resname name :reskey 0 :reskeyop 0
:ressortgrou
pref 1 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1043
:vartypmo
d 34 :varlevelsup 0 :varnoold 1 :varoattno 2}}) :groupClause <> :havingQual
<>
:distinctClause <> :sortClause ({ SORTCLAUSE :tleSortGroupRef 1 :sortop
1066 })
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG: plan: { SORT :startup_cost 1.03 :total_cost 1.03 :rows 2 :width 37
:qpta
rgetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:res
name id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
VAR :v
arno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoat
tno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 1043 :restypmod 34
:res
name name :reskey 1 :reskeyop 1066 :ressortgroupref 1 :resjunk false } :expr
{ V
AR :varno 1 :varattno 2 :vartype 1043 :vartypmod 34 :varlevelsup 0 :varnoold
1
:varoattno 2}}) :qpqual <> :lefttree { SEQSCAN :startup_cost 0.00
:total_cost 1.
02 :rows 2 :width 37 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1
:res
type 23 :restypmod -1 :resname id :reskey 0 :reskeyop 0 :ressortgroupref 0
:resj
unk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varleve
lsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2
:resty
pe 1043 :restypmod 34 :resname name :reskey 0 :reskeyop 0 :ressortgroupref 1
:re
sjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 34
:var
levelsup 0 :varnoold 1 :varoattno 2}}) :qpqual <> :lefttree <> :righttree <>
:ex
tprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 } :righttree <> :extprm
()
:locprm () :initplan <> :nprm 0 :keycount 1 }
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SELEC.1 2, name, description, type_id FROM education_degree
ORDER
BY name
ERROR: parser: parse error at or near "selec"
DEBUG: AbortCurrentTransaction
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
DEBUG: reaping dead processes
DEBUG: child process (pid 23821) exited with exit code 0
----- Original Message -----
From: "Barry Lind" <barry@xythos.com>
To: "John Metz" <johnmetz@excite.com>
Cc: <pgsql-jdbc@postgresql.org>
Sent: Tuesday, June 18, 2002 2:00 PM
Subject: Re: [JDBC] JDBC parse error
> John,
>
> Can you turn on query logging on the server to see what the actual
> queries sent from the jdbc driver to the server actually are? That
> might give some indication of where the problem is. Finally are you
> sure that your own code isn't munging the string before it is passed to
> the jdbc driver? I haven't heard of anyone else seeing this problem.
> We need to track down what is different in your environment that is
> causing it. Also if you would mention what version of the server and
> jdbc driver you are using, that would be helpful.
>
> thanks,
> --Barry
>
> John Metz wrote:
>
> >Hi,
> >
> >I'm having an intermittent problem with the PostGres JDBC Driver I
> recently
> >downloaded from http://jdbc.postgresql.org/download.html. I am running
a
> >Java Servlet application under Tomcat 4.0 and Solaris 8. On some
> occasions
> >it seems that the String passed to a Statement object gets altered when
> >executed. I get a SQLException with the text "parse error at or near
> selec".
> >It always seems to happen on a select. On some occasions it works but
it
> >seems to happen on some queries more than others. The text in the parse
> >error is different at times for the same query (another example was
> >selecaria). Here is an example stack trace:
> >
> >java.sql.SQLException: ERROR: parser: parse error at or near "selec"
> >
> > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94)
> > at org.postgresql.Connection.ExecSQL(Connection.java:398)
> > at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
> > at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
> > at
>
>org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.java:
9
> >9)
> > at
>
>com.conquestnet.database.entity.EducationDegree.getAllEducationDegrees(Educ
a
> >tionDegree.java:211)
> > at org.apache.jsp.edit$jsp._jspService(edit$jsp.java:218)
> > at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source)
> > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > at
org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(Unknown
> >Source)
> > at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown Source)
> > at org.apache.jasper.servlet.JspServlet.service(Unknown Source)
> >
> >Any help that can be provided is much appriciated. I've searched the
FAQ
> >but could not find anything that seemed like the same problem.
> >
> >Thanks,
> >
> >John Metz
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
> >
> >
> >
>
>
>
>
John,
The only thing that will help solve the problem is going to be a
reproducable test case. Can you come up with a small test case that
shows the problem? If you can't, then you may need to run a java
debugger (or print statements) on your app to see what is going on.
In your output below I only see this one failed query:
SELEC.1 2, name, description, type_id FROM education_degree
ORDER BY name
I don't see a successful version of this query. What would this query
look like if it hadn't been corrupted? Also any idea where in your app
data like '.1 2' might be created? Is this a PreparedStatement query or
a regular Statement?
thanks,
--Barry
John Metz wrote:
>Barry,
>
>You SA upgraded us to PostGres 7.2.1, we had been using 7.1.2, and we are
>able to recreate the problem. We are using the JDBC driver that came with
>the PostGres 7.2.1 distribution. We are running under Java 1.3.1_01 on
>Solaris 8. We did turn debugging on and the output is below. I
included a
>successful query executed before the one that failed. I also included the
>output from a psql -V command. I was able to run the exact same operation
>minutes before and it worked. Also, I can execute the query (as it is in
>our Java code) in psql and it works. Please let me know if there's
anything
>else I can do to help you find the problem.
>
>bash-2.03$ psql -V
>psql (PostgreSQL) 7.2.1
>Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
>Portions Copyright (c) 1996, Regents of the University of California
>Read the file COPYRIGHT or use the command \copyright to see the
>usage and distribution terms.
>
>--- DEBUG log ---
>
>DEBUG: ProcessQuery
>DEBUG: CommitTransactionCommand
>DEBUG: StartTransactionCommand
>DEBUG: query: SELECT id, name FROM clearance ORDER BY name
>DEBUG: parse tree: { QUERY :command 1 :utility <> :resultRelation 0
:into <>
>:
>isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks
>false :
>rtable ({ RTE :relname clearance :relid 24755 :subquery <> :alias <> :eref
>{ AT
>TR :relname clearance :attrs ( "id" "name" )} :inh true :inFromCl true
>:checkF
>orRead true :checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR
>:fromlist
>({ RANGETBLREF 1 }) :quals <>} :rowMarks () :targetList ({ TARGETENTRY
>:resdom
>{ RESDOM :resno 1 :restype 23 :restypmod -1 :resname id :reskey 0
:reskeyop
>0 :r
>essortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1
:vartype
>23 :
>vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY
:resdom
>{
>RESDOM :resno 2 :restype 1043 :restypmod 34 :resname name :reskey 0
>:reskeyop 0
>:ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 2
>:vartype 10
>43 :vartypmod 34 :varlevelsup 0 :varnoold 1 :varoattno 2}})
:groupClause <>
>:ha
>vingQual <> :distinctClause <> :sortClause ({ SORTCLAUSE
:tleSortGroupRef 1
>:sor
>top 1066 }) :limitOffset <> :limitCount <> :setOperations <>
>:resultRelations ()
>}
>DEBUG: rewritten parse tree:
>DEBUG: { QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal
>fal
>se :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable
>({ RT
>E :relname clearance :relid 24755 :subquery <> :alias <> :eref { ATTR
>:relname
>clearance :attrs ( "id" "name" )} :inh true :inFromCl true :checkForRead
>true
>:checkForWrite false :checkAsUser 0}) :jointree { FROMEXPR :fromlist ({
>RANGETBL
>REF 1 }) :quals <>} :rowMarks () :targetList ({ TARGETENTRY :resdom {
RESDOM
>:re
>sno 1 :restype 23 :restypmod -1 :resname id :reskey 0 :reskeyop 0
>:ressortgroupr
>ef 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23
>:vartypmod -1
>:varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM
>:resn
>o 2 :restype 1043 :restypmod 34 :resname name :reskey 0 :reskeyop 0
>:ressortgrou
>pref 1 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1043
>:vartypmo
>d 34 :varlevelsup 0 :varnoold 1 :varoattno 2}}) :groupClause <>
:havingQual
><>
>:distinctClause <> :sortClause ({ SORTCLAUSE :tleSortGroupRef 1 :sortop
>1066 })
>:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
>DEBUG: plan: { SORT :startup_cost 1.03 :total_cost 1.03 :rows 2 :width 37
>:qpta
>rgetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1
>:res
>name id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
>VAR :v
>arno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
>:varoat
>tno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 1043
:restypmod 34
>:res
>name name :reskey 1 :reskeyop 1066 :ressortgroupref 1 :resjunk false }
:expr
>{ V
>AR :varno 1 :varattno 2 :vartype 1043 :vartypmod 34 :varlevelsup 0
:varnoold
>1
>:varoattno 2}}) :qpqual <> :lefttree { SEQSCAN :startup_cost 0.00
>:total_cost 1.
>02 :rows 2 :width 37 :qptargetlist ({ TARGETENTRY :resdom { RESDOM
:resno 1
>:res
>type 23 :restypmod -1 :resname id :reskey 0 :reskeyop 0 :ressortgroupref 0
>:resj
>unk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
>:varleve
>lsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2
>:resty
>pe 1043 :restypmod 34 :resname name :reskey 0 :reskeyop 0
:ressortgroupref 1
>:re
>sjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 34
>:var
>levelsup 0 :varnoold 1 :varoattno 2}}) :qpqual <> :lefttree <>
:righttree <>
>:ex
>tprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 } :righttree <>
:extprm
>()
>:locprm () :initplan <> :nprm 0 :keycount 1 }
>DEBUG: ProcessQuery
>DEBUG: CommitTransactionCommand
>DEBUG: StartTransactionCommand
>DEBUG: query: SELEC.1 2, name, description, type_id FROM education_degree
>ORDER
>BY name
>ERROR: parser: parse error at or near "selec"
>DEBUG: AbortCurrentTransaction
>DEBUG: proc_exit(0)
>DEBUG: shmem_exit(0)
>DEBUG: exit(0)
>DEBUG: reaping dead processes
>DEBUG: child process (pid 23821) exited with exit code 0
>
>----- Original Message -----
>From: "Barry Lind" <barry@xythos.com>
>To: "John Metz" <johnmetz@excite.com>
>Cc: <pgsql-jdbc@postgresql.org>
>Sent: Tuesday, June 18, 2002 2:00 PM
>Subject: Re: [JDBC] JDBC parse error
>
>
>
>
>>John,
>>
>>Can you turn on query logging on the server to see what the actual
>>queries sent from the jdbc driver to the server actually are? That
>>might give some indication of where the problem is. Finally are you
>>sure that your own code isn't munging the string before it is passed to
>>the jdbc driver? I haven't heard of anyone else seeing this problem.
>> We need to track down what is different in your environment that is
>>causing it. Also if you would mention what version of the server and
>>jdbc driver you are using, that would be helpful.
>>
>>thanks,
>>--Barry
>>
>>John Metz wrote:
>>
>> >Hi,
>> >
>> >I'm having an intermittent problem with the PostGres JDBC Driver I
>>recently
>> >downloaded from http://jdbc.postgresql.org/download.html. I am running
>>
>>
>a
>
>
>> >Java Servlet application under Tomcat 4.0 and Solaris 8. On some
>>occasions
>> >it seems that the String passed to a Statement object gets altered when
>> >executed. I get a SQLException with the text "parse error at or near
>>selec".
>> >It always seems to happen on a select. On some occasions it works but
>>
>>
>it
>
>
>> >seems to happen on some queries more than others. The text in the
parse
>> >error is different at times for the same query (another example was
>> >selecaria). Here is an example stack trace:
>> >
>> >java.sql.SQLException: ERROR: parser: parse error at or near "selec"
>> >
>> > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94)
>> > at org.postgresql.Connection.ExecSQL(Connection.java:398)
>> > at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
>> > at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
>> > at
>>
>>org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.java:
>>
>>
>9
>
>
>> >9)
>> > at
>>
>>com.conquestnet.database.entity.EducationDegree.getAllEducationDegrees(Educ
>>
>>
>a
>
>
>> >tionDegree.java:211)
>> > at org.apache.jsp.edit$jsp._jspService(edit$jsp.java:218)
>> > at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source)
>> > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
>> > at
>>
>>
>org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(Unknown
>
>
>> >Source)
>> > at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown Source)
>> > at org.apache.jasper.servlet.JspServlet.service(Unknown Source)
>> >
>> >Any help that can be provided is much appriciated. I've searched the
>>
>>
>FAQ
>
>
>> >but could not find anything that seemed like the same problem.
>> >
>> >Thanks,
>> >
>> >John Metz
>> >
>> >
>> >---------------------------(end of
broadcast)---------------------------
>> >TIP 4: Don't 'kill -9' the postmaster
>> >
>> >
>> >
>>
>>
>>
>>
>>
>>
>
>
>
>