LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: kevin_chuah

Hi there,

Has anyone experienced this error before?

Current heap size setting ls.brain.node.javaMaxHeapSize=2048m

Using MySQL Connector/J v5.1.18

Box has 24Gb of RAM

Only seem to encounter this error when the JDBC Query node is trying to return data that is a few hundred megabytes in size.

thanks
Kev

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Hi,

I think that the problem here is that the MySQL JDBC driver will attempt to retrieve the entire result set for the query.
You can change the settings such that this is buffered somewhat, and the driver will return only the specified number of rows at a time.
The node will keep iterating over this until all rows have been retrieved, so it won't reduce the number of rows output by the node or anything like that.

You can do this by adding the following settings into your JDBC URL:

defaultFetchSize=<numRows>
useCursorFetch=true

Where <numRows> is obviously the number of rows returned by the driver per fetch.
A higher number of rows will use more memory, but execute faster, while a lower number of rows will use less memory, but be slower.

Tim.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: kevin_chuah

Hi Tim,

The query used in the node is below.


defaultFetchSize=200000
useCursorFetch=true
select msn_id
,msisdn
,service_type
,imsi
,owning_cost_center
,equipment_status_trfn
from nrs1p.abor_staging
where batch_no_id = 20120418
and substr(msn_id,1,1) = 4


....and I get the error below.

Exited with non zero status
2012-04-20 10:14:52.000; ERROR: error on row 0. com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: 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 'defaultFetchSize=200000 useCursorFetch=true select msn_id ,msisdn ' at line 1
id: 0 chain: 0 group: 0
cppDetail: context: nodeMain QueryDump.java@162

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: kevin_chuah

Success! Thanks Sam.

Was solved by invoking it below

jdbc:mysql://prdappl001:3306/rs1p?defaultFetchSize=200000&useCursorFetch=true

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: xathras

Thanks for this post. This helped me out today. I hit a hard limit on querying our AAA Server and this helped resolve the issue.