You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 => Wrong query ?
Posted by: Romain Froger
Date: January 28, 2009 04:30PM

Hi,
i'm not used to post on forums but i'm getting mad with the configuration of my Tomcat / Mysql connection.

Servers :
= OS : Kubuntu 8.04
= Tomcat 5.5
- Mysql 5.0.51
- Connector/J : mysql-connector-java-5.0.8-bin


Definition of connection pool :
=================================
inside context.xml :

<Resource
name="jdbc/TutoPool"
auth="Container"
type="javax.sql.DataSource"
username="root"
password=""
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/astreinte?autoReconnect=true"
maxActive="8"
maxIdle="8"
maxWait="10000"
validationQuery="1"
removeAbandoned="true"
removeAbandonedTimeout="20"
logAbandoned="true" />


in web.xml :
web-app>
<display-name>TutoPool</display-name>
<servlet>
<servlet-name>TutoPool</servlet-name>
<servlet-class>tutorial.TutoPool</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>TutoPool</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<resource-ref>
<description>
reference a la ressource BDD pour le pool
</description>
<res-ref-name>jdbc/TutoPool</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>



My java code is :
================
Context initCtx = new InitialContext();
Context envContext = (Context)initCtx.lookup("java:/comp/env");
ds = (DataSource) envContext.lookup("jdbc/TutoPool");
Connection con=ds.getConnection();
Statement s=con.createStatement();
ResultSet rs=s.executeQuery("select * from lrp_users where active=1 order by firstname asc");


When i execute this code i raise the following exception :

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1232)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:332)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1249)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
... 18 more


and the log of mysql.log is :

26 Connect root@localhost on astreinte
26 Query SHOW SESSION VARIABLES
26 Query SHOW COLLATION
26 Query SET character_set_results = NULL
26 Query SET autocommit=1
26 Query SET sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES'
26 Query 1
26 Quit


There is defintively a problem with the query. We can see the "query 1" that raises the exception but i don't know why the true query is not passed.


I ran the same query using PHP and i have this log :
23 Connect root@localhost on
23 Init DB astreinte
23 Query select * from lrp_users where active=1 order by firstname asc
23 Quit




Do you have an idea ?
I'm completely lost !!!

Best

Options: ReplyQuote


Subject
Written By
Posted
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 => Wrong query ?
January 28, 2009 04:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.