Bug 59363 - Bug in JDBC Request Sampler for Nightly Build (21.04.2016)
Summary: Bug in JDBC Request Sampler for Nightly Build (21.04.2016)
Status: RESOLVED FIXED
Alias: None
Product: JMeter
Classification: Unclassified
Component: Main (show other bugs)
Version: Nightly (Please specify date)
Hardware: PC All
: P2 blocker (vote)
Target Milestone: ---
Assignee: JMeter issues mailing list
URL:
Keywords:
: 59792 (view as bug list)
Depends on:
Blocks:
 
Reported: 2016-04-21 09:14 UTC by Andreas Hoppe
Modified: 2016-07-05 19:47 UTC (History)
2 users (show)



Attachments
Test plan (5.01 KB, text/xml)
2016-04-21 11:37 UTC, Andreas Hoppe
Details
JMeter log file (25.80 KB, text/plain)
2016-04-21 11:38 UTC, Andreas Hoppe
Details
Screenshot of result for 2.13 (51.83 KB, image/png)
2016-04-21 13:08 UTC, Andreas Hoppe
Details
Use jdbc isValid method, when no query is given (1.17 KB, patch)
2016-04-21 17:37 UTC, Felix Schumacher
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Andreas Hoppe 2016-04-21 09:14:10 UTC
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).
Comment 1 UbikLoadPack support 2016-04-21 09:16:14 UTC
(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
Comment 2 Andreas Hoppe 2016-04-21 09:39:00 UTC
- 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
Comment 3 UbikLoadPack support 2016-04-21 09:41:52 UTC
(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
Comment 4 Andreas Hoppe 2016-04-21 09:44:51 UTC
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.
Comment 5 UbikLoadPack support 2016-04-21 09:49:47 UTC
(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
Comment 6 Andreas Hoppe 2016-04-21 09:52:36 UTC
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.
Comment 7 Andreas Hoppe 2016-04-21 09:54:55 UTC
Sorry, was misunderstandable. The bud is in 1739623 (3.0 RC 2), 1740080 and 1740231 (21.04.2016) and NOT in 2.13.
Comment 8 UbikLoadPack support 2016-04-21 09:57:41 UTC
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
Comment 9 Sebb 2016-04-21 10:06:44 UTC
Also, have you tried any other (simpler) queries on the nightly build?

e.g.

SELECT COUNT(*) FROM DUAL
Comment 10 Andreas Hoppe 2016-04-21 11:37:31 UTC
Created attachment 33785 [details]
Test plan

Testplan with not working simple query
Comment 11 Andreas Hoppe 2016-04-21 11:38:04 UTC
Created attachment 33786 [details]
JMeter log file

JMeter log file
Comment 12 Andreas Hoppe 2016-04-21 11:38:58 UTC
I tried also simple queries, but the cause the same error.
Comment 13 Andreas Hoppe 2016-04-21 11:42:31 UTC
In case of the ora error the Statement didn't reach the database. This error comes from  from the jdbc driver.
Comment 14 Sebb 2016-04-21 12:26:34 UTC
Does the same test plan work for you on 2.13?

The Validation query looks as though it may be wrong for Oracle.
Comment 15 Andreas Hoppe 2016-04-21 12:59:57 UTC
Yes it works with JMeter 2.13 (see screenshot).
Comment 16 Andreas Hoppe 2016-04-21 13:08:02 UTC
Created attachment 33787 [details]
Screenshot of result for 2.13

Screenshot of query result with JMeter 2.13.
Comment 17 Sebb 2016-04-21 13:10:18 UTC
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.
Comment 18 Andreas Hoppe 2016-04-21 13:30:23 UTC
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.
Comment 19 Sebb 2016-04-21 13:42:38 UTC
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.
Comment 20 Andreas Hoppe 2016-04-21 13:52:57 UTC
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?
Comment 21 UbikLoadPack support 2016-04-21 14:00:59 UTC
(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.
Comment 22 Sebb 2016-04-21 14:32:36 UTC
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.
Comment 23 UbikLoadPack support 2016-04-21 15:03:39 UTC
(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
Comment 24 Felix Schumacher 2016-04-21 17:37:47 UTC
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).
Comment 25 Philippe Mouawad 2016-04-21 21:01:38 UTC
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
Comment 26 Philippe Mouawad 2016-04-21 21:05:26 UTC
(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.
Comment 28 Felix Schumacher 2016-07-05 19:16:32 UTC
*** Bug 59792 has been marked as a duplicate of this bug. ***
Comment 29 Felix Schumacher 2016-07-05 19:47:43 UTC
(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.