Bug 59363

(In reply to Andreas Hoppe from comment #0)
> I tried to use the jdbc request sample for an oracle db and get the error
> ORA-00923.
> 
> I tried the statement before with sql developer and JMeter 2.13 and it works
> fine.
> 
> I used the jdbc oracle driver version 7 and Java JDK 1.8 u 77.
> 
> I used the nightly build versions  1739623 (3.0 RC 2), 1740080 and 1740231
> (21.04.2016).

Hello,
Thanks for testing nightly build.
Could you provide:
- Sample failing test plan
- JMeter.log file
- content of jmeter/lib (does it contain the driver ? which version ?)
Thanks

- Driver in lib tree: ojdbc7.jar

- Sample SQL-Statement:
select count(*)
from an
where filenumber = '${fn}'
and extractvalue (xml,'//an:fastMD/text()', 'xmlns:an="http://www.mm.de/MFA/v2/AN') = '${fn}'

- Response for jdbc request: Response message: java.sql.SQLException: Cannot create PoolableConnectionFactory (ORA-00923: Schlüsselwort FROM nicht an erwarteter Stelle gefunden)

- Test plan and log file cannot be provided. Log file doesn't contain error massages

(In reply to Andreas Hoppe from comment #2)
> - Driver in lib tree: ojdbc7.jar
> 
> - Sample SQL-Statement:
> select count(*)
> from an
> where filenumber = '${fn}'
> and extractvalue (xml,'//an:fastMD/text()',
> 'xmlns:an="http://www.mm.de/MFA/v2/AN') = '${fn}'

Are you sure ${fn} was replaced ? What is the exact SQL query that hits the db ? 
> 
> - Response for jdbc request: Response message: java.sql.SQLException: Cannot
> create PoolableConnectionFactory (ORA-00923: Schlüsselwort FROM nicht an
> erwarteter Stelle gefunden)
> 
> - Test plan and log file cannot be provided. Log file doesn't contain error
> massages


Did you check with JMeter 2.13 under exactly the same conditions ?
- Same JDK
- Same Driver ?
- Same  SQL query 

Can you try by playing with the query to see what part of the SQL is failing ?
Thanks

I check with JMeter 2.13 under same condition on same machine. Equal what I am doing with the query, I got the same error. The SQL statement is correct.

(In reply to Andreas Hoppe from comment #4)
> I check with JMeter 2.13 under same condition on same machine. Equal what I
> am doing with the query, I got the same error. The SQL statement is correct.

I understand you get an error with 2.13 right ? Do I misunderstand ?
Because you initially wrote:
"I tried the statement before with sql developer and JMeter 2.13 and it works fine."

Thanks

I get an error with the nightly builds 1739623 (3.0 RC 2), 1740080 and 1740231.

JMeter 2.13 works fine. Additionaly I tried SQL developer to check that the statement is correct.

Sorry, was misunderstandable. The bud is in 1739623 (3.0 RC 2), 1740080 and 1740231 (21.04.2016) and NOT in 2.13.

Ok,
Unless you provide those informations, it will be very hard if not impossible to see the problem:
- Exact query hitting the database (on oracle side) with JMeter 2.13 vs 3.0 RC2
- JMeter.log file removing all confidential information
- Sample failing test plan with only failing query

Thanks

Also, have you tried any other (simpler) queries on the nightly build?

e.g.

SELECT COUNT(*) FROM DUAL

Created attachment 33785 [details]
Test plan

Testplan with not working simple query

Created attachment 33786 [details]
JMeter log file

JMeter log file

I tried also simple queries, but the cause the same error.

In case of the ora error the Statement didn't reach the database. This error comes from  from the jdbc driver.

Does the same test plan work for you on 2.13?

The Validation query looks as though it may be wrong for Oracle.

Yes it works with JMeter 2.13 (see screenshot).

Created attachment 33787 [details]
Screenshot of result for 2.13

Screenshot of query result with JMeter 2.13.

Comment on attachment 33787 [details]
Screenshot of result for 2.13

Screenshot does not show the JDBC Config.

Try changing the validation query so it is valid for Oracle.

It should be 'SELECT 1 FROM DUAL' or similar.

I used the SAME test plan!!!!

It has the SAME JDBC configuration!!!

I also tried this simple statement and the SAME error appears!!!


To summarise:
-------------

1. The SQL statement is in ALL cases correct.
2. JDBC driver and Oracle connection configuration are correct.
3. With JMeter 2.13 the JDBC request sampler works correct.
4. With JMeter 3.0 RC2 or all other higher nightly build versions the JDBC request sampler causes ALWAYS the same bug.


My assumption:
--------------

1. The new JDBC request sampler generates an incorrect statement. This shows the error ORA-00923.

As I wrote:

Have you tried correcting the validation query?


There were some changes to the way JDBC works; it's possible that the query is now being used at a different point in the process.

You are talking about the configuration of the jdbc Connection where you configurae the Validation query. No this i haven't changed. Now I used another query and it works.

Is it possible to leave this field empty?

(In reply to Andreas Hoppe from comment #20)
> You are talking about the configuration of the jdbc Connection where you
> configurae the Validation query. No this i haven't changed. Now I used
> another query and it works.
> 
> Is it possible to leave this field empty?
Set Test While Idle to false.

Not a problem.

The original validation query was invalid; however this did not cause a problem as it was not being used previously. So the incorrect setting went unnoticed.

(In reply to Sebb from comment #22)
> Not a problem.
> 
> The original validation query was invalid; however this did not cause a
> problem as it was not being used previously. So the incorrect setting went
> unnoticed.

Hi Sebb,
Maybe changes should mention to take care about this as it seems Validation Query was not used by old pool even when keep alive.
Note there is something strange here, DBCP pool is created with:
dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);
        dataSource.setTestOnCreate(false);
        dataSource.setTestWhileIdle(false);

In this case only testWhileIdle is true:
            dataSource.setTestWhileIdle(true);

In my understanding, the validation query would have been executed while connection is idle after 1 minute while it appears test is done before execution.

Maybe there is an issue.
Regards

Created attachment 33790 [details]
Use jdbc isValid method, when no query is given

As asked by the reporter, with this patch it is possible to leave the validation query blank. In that case dbcp pool will use the isValid method of the jdbc driver (when testWhileIdle is selected).

I wonder, whether we should include this patch (with documentation) in 3.0 (rc3), or leave it open until 3.1 (probably a bit safer).

Author: pmouawad
Date: Thu Apr 21 21:00:29 2016
New Revision: 1740410

URL: http://svn.apache.org/viewvc?rev=1740410&view=rev
Log:
Bug 59363 - Bug in JDBC Request Sampler for Nightly Build (21.04.2016)
Document behaviour
Bugzilla Id: 59363

Modified:
    jmeter/trunk/xdocs/usermanual/component_reference.xml

(In reply to Felix Schumacher from comment #24)
> Created attachment 33790 [details]
> Use jdbc isValid method, when no query is given
> 
> As asked by the reporter, with this patch it is possible to leave the
> validation query blank. In that case dbcp pool will use the isValid method
> of the jdbc driver (when testWhileIdle is selected).
> 
> I wonder, whether we should include this patch (with documentation) in 3.0
> (rc3), or leave it open until 3.1 (probably a bit safer).

+1
But I think you should create a new Bugzilla in Improvement as this one is not a bug and does not need a fix.

For reference:
The dbcp2 pool uses the validationQuery to validate the pool on its creation:
https://github.com/apache/commons-dbcp/blob/trunk/src/main/java/org/apache/commons/dbcp2/BasicDataSource.java#L2301

Then:
https://github.com/apache/commons-dbcp/blob/trunk/src/main/java/org/apache/commons/dbcp2/PoolableConnection.java#L289

*** Bug 59792 has been marked as a duplicate of this bug. ***

(In reply to Philippe Mouawad from comment #26)
> (In reply to Felix Schumacher from comment #24)
> > Created attachment 33790 [details]
> > Use jdbc isValid method, when no query is given
> > 
> > As asked by the reporter, with this patch it is possible to leave the
> > validation query blank. In that case dbcp pool will use the isValid method
> > of the jdbc driver (when testWhileIdle is selected).
> > 
> > I wonder, whether we should include this patch (with documentation) in 3.0
> > (rc3), or leave it open until 3.1 (probably a bit safer).
> 
> +1
> But I think you should create a new Bugzilla in Improvement as this one is
> not a bug and does not need a fix.

Done with Bug 59803.

This issue has been migrated to GitHub: https://github.com/apache/jmeter/issues/3957