snappydata icon indicating copy to clipboard operation
snappydata copied to clipboard

Published 20 hours ago • verified publisher icon TIBCOSoftware

SELECT with LIMIT works find on snappy-shell but not working with JDBC

Open obaidcuet opened this issue 7 years ago • 14 comments

Issue: SELECT with LIMIT works fine on snappy-shell but not working with JDBC.

Workaround: Use JDBC method Statement.setMaxRows(int max) This workaround will work fine for inhouse codes, but may cause problem for 3rd party tools.

Environment: Snappydata version: version 0.9 on docker image Client: snappydata-client-1.5.5.jar Target table: a row store table

Code:

import java.sql.*;

public class Main {

    public static void main(String[] args) {
        try{
            Class.forName("io.snappydata.jdbc.ClientDriver");
            Connection con=DriverManager.getConnection(
                    "jdbc:snappydata://192.168.99.100:1527/route-query=false","ycsb","ycsb");
            Statement stmt=con.createStatement();
            // stmt.setMaxRows(10); // <<< workaround, use this instead of putting LIMIT in query
            ResultSet rs=stmt.executeQuery("SELECT * FROM usertable LIMIT 10");
            while(rs.next())
                System.out.println(rs.getString(1)+"  "+rs.getString(2)+"  "+rs.getString(3));
            con.close();
        }catch(Exception e){
            System.out.println(e);
        }
    }
}

Error:

Jun 18, 2017 3:15:24 PM java.util.logging.LogManager$RootLogger log
INFO: Starting client on 'DESKTOP-555B11O' with ID='2812|2017/06/18 15:15:24.485 SGT' Source-Revision=5d896818f67dd9c6ba1de2bc027384c4991cd1a8
java.sql.SQLException: (SQLState=42X01 Severity=20000) (Server=/0.0.0.0[1528] Thread=pool-3-thread-7) Syntax error: Encountered "10" at line 1, column 32.
Was expecting one of:
    <EOF> 
    ...
    "except" ...
    "fetch" ...
    "for" ...
    "group" ...
    "having" ...
    "intersect" ...
    "order" ...
    "union" ...
    .

Process finished with exit code 0

obaidcuet avatar Jun 18 '17 07:06 obaidcuet

@obaidcuet It seems the typo in query - LIMKIT (should be LIMIT)

ymahajan avatar Jun 18 '17 10:06 ymahajan

@ymahajan Thanks for pointing out the typo, I have fixed that accordingly and run it. Issue still the same. So, the problem statement still unchanged. Note: I have updated in my first post with type correction.

obaidcuet avatar Jun 18 '17 12:06 obaidcuet

Why are you setting route-query=false? That is more of a debugging property and is not recommended for production.

If you start snappy shell, and connect using the following command :

snappy>connect client 'localhost:1527;route-query=false';

You will start getting the same error on snappy shell as well.

hbhanawat avatar Jun 18 '17 12:06 hbhanawat

@hbhanawat

Thanks, without "route-query=false" false it is successful. But if I use bind variables it show below error: Code:

    public static void main(String[] args) {
        try{
            Class.forName("io.snappydata.jdbc.ClientDriver");
            Connection con=DriverManager.getConnection(
                    "jdbc:snappydata://192.168.99.100:1527/route-query=false","ycsb","ycsb");
            String sql = "SELECT * FROM usertable LIMIT ?";
            PreparedStatement prepStat = con.prepareStatement(sql);
            prepStat.setInt(1, 10);
            ResultSet rs=prepStat.executeQuery();
            while(rs.next())
                System.out.println(rs.getString(1));
            con.close();
        }catch(Exception e){
            System.out.println(e);
        }
    }

Error:

Jun 18, 2017 9:57:06 PM java.util.logging.LogManager$RootLogger log
INFO: Starting client on 'DESKTOP-555B11O' with ID='7780|2017/06/18 21:57:06.683 SGT' Source-Revision=5d896818f67dd9c6ba1de2bc027384c4991cd1a8
java.sql.SQLException: (SQLState=XJ001 Severity=0) (Server=/0.0.0.0[1528] Thread=pool-3-thread-8) Java exception: 'com.gemstone.gemfire.distributed.internal.ReplyException: From 192.168.99.100(453)<v2>:47605: com.pivotal.gemfirexd.internal.engine.distributed.FunctionExecutionException: Function execution exception on member [192.168.99.100(453)<v2>:47605]: com.gemstone.gemfire.cache.execute.FunctionException'.

Process finished with exit code 0

In fact, I was performing load testing using YCSB. All test went through, but it was keep failing with below error for "Workload E". I was assuming that it is shifting execution as spark job. So, wanted to avoid that using "route-query=false" and after that end up with testing JDBC with bind variable.

Error in processing scan of table: usertablejava.sql.SQLException: (SQLState=XJ001 Severity=0) (Server=/0.0.0.0[1528] Thread=pool-3-thread-4) Java exception: 'com.gemstone.gemfire.distributed.internal.ReplyException: From 192.168.99.100(526)<v2>:65047: com.pivotal.gemfirexd.internal.engine.distributed.FunctionExecutionException: Function execution exception on member [192.168.99.100(526)<v2>:65047]: com.gemstone.gemfire.cache.execute.FunctionException'.
Error in processing scan of table: usertablejava.sql.SQLException: (SQLState=40XD0 Severity=30000) ([192.168.99.100/0.0.0.0[1528]]) Container has been closed or has moved from '192.168.99.100[1527]' {failed after trying all available servers: [192.168.99.100/0.0.0.0[1528], 192.168.99.100/0.0.0.0[1527]] with: io.snappydata.thrift.SnappyException}.

Command used to run YSCB "Workload E": .\bin\ycsb run jdbc -P workloads/workloade -p db.driver=io.snappydata.jdbc.ClientDriver -p db.url=jdbc:snappydata://192.168.99.100:1527/route-query=false -p db.user=ycsb -p db.passwd=ycsb -p operationcount=1000000 -s -threads 2

Anyways, it will be enough for me, if I know how to run YCSB workload E successfully.

obaidcuet avatar Jun 18 '17 14:06 obaidcuet

I had a cursory look at the YCSB tests. It seems the usertable that it is looking for is not created. Check server logs to confirm. I assume you were not able to run the load phase (or any other workload) as well?

Secondly, with jdbc, the usertable that it creates has a column with name 'key'. This may not work with Snappy as key is a reserved keyword. You may have to work around that issue with YCSB.

hbhanawat avatar Jun 18 '17 15:06 hbhanawat

Only "Workload E"(workloads/workloade) is showing error, others workloads with same target table working fine.

The table exists and created as below:

create schema ycsb;

CREATE TABLE ycsb.usertable(YCSB_KEY VARCHAR (255) PRIMARY KEY,
  FIELD0 VARCHAR(1000), FIELD1 VARCHAR(1000),
  FIELD2 VARCHAR(1000), FIELD3 VARCHAR(1000),
  FIELD4 VARCHAR(1000), FIELD5 VARCHAR(1000),
  FIELD6 VARCHAR(1000), FIELD7 VARCHAR(1000),
  FIELD8 VARCHAR(1000), FIELD9 VARCHAR(1000)
)USING ROW OPTIONS (PERSISTENT 'asynchronous' );

by the way, I have found a this, but seems like they skipped workload E.

obaidcuet avatar Jun 18 '17 21:06 obaidcuet

@obaidcuet I am one of authors of that paper. We didn't run Workload E at that time because Snappy's row tables weren't optimized for scan queries (workload e is the only workload that has scan queries). With latest releases of Snappy, scan queries should work fine on row tables but we haven't tried YCSB in some time. Regarding the create table command, I was looking at JdbcDBCreateTable for creating the table and hence the confusion of the primary key name.

Coming back to your problem, I could reproduce the problem. Do your server logs have a similar exception trace? This would confirm if I am seeing the same issue as yours.

java.lang.UnsupportedOperationException: This query is unsupported for prepared statement at Remote Member '192.168.1.104(26319):8747' in io.snappydata.gemxd.SparkSQLPrepareImpl.packRows(SparkSQLPrepareImpl.scala:88) at Remote Member '192.168.1.104(26319):8747' in com.pivotal.gemfirexd.internal.engine.distributed.SnappyResultHolder.prepareSend(SnappyResultHolder.java:178) at Remote Member '192.168.1.104(26319):8747' in com.pivotal.gemfirexd.internal.engine.distributed.message.LeadNodeExecutorMsg.execute(LeadNodeExecutorMsg.java:148) at Remote Member '192.168.1.104(26319):8747' in com.pivotal.gemfirexd.internal.engine.distributed.message.GfxdFunctionMessage.processMessage(GfxdFunctionMessage.java:739) ... 9 more ============= end nested exception, level (3) ===========

If yes, it looks like a bug to me. @vivekwiz can you please have a look?

hbhanawat avatar Jun 19 '17 04:06 hbhanawat

Prepared statement do not support bind variable in Limit clause. It should only be given for where clause. The following error is expected for such queries: java.lang.UnsupportedOperationException: This query is unsupported for prepared statement

Regards, Vivek

On Mon, Jun 19, 2017 at 10:26 AM, hbhanawat [email protected] wrote:

@obaidcuet https://github.com/obaidcuet I am one of authors of that paper. We didn't run Workload E at that time because Snappy's row tables weren't optimized for scan queries (workload e is the only workload that has scan queries). With latest releases of Snappy, scan queries should work fine on row tables but we haven't tried YCSB in some time. Regarding the create table command, I was looking at JdbcDBCreateTable for creating the table and hence the confusion of the primary key name.

Coming back to your problem, I could reproduce the problem. Do your server logs have a similar exception trace? This would confirm if I am seeing the same issue as yours.

java.lang.UnsupportedOperationException: This query is unsupported for prepared statement at Remote Member '192.168.1.104(26319):8747' in io.snappydata.gemxd. SparkSQLPrepareImpl.packRows(SparkSQLPrepareImpl.scala:88) at Remote Member '192.168.1.104(26319):8747' in com.pivotal.gemfirexd. internal.engine.distributed.SnappyResultHolder.prepareSend( SnappyResultHolder.java:178) at Remote Member '192.168.1.104(26319):8747' in com.pivotal.gemfirexd. internal.engine.distributed.message.LeadNodeExecutorMsg. execute(LeadNodeExecutorMsg.java:148) at Remote Member '192.168.1.104(26319):8747' in com.pivotal.gemfirexd. internal.engine.distributed.message.GfxdFunctionMessage.processMessage( GfxdFunctionMessage.java:739) ... 9 more ============= end nested exception, level (3) ===========

If yes, it looks like a bug to me. @vivekwiz https://github.com/vivekwiz can you please have a look?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/SnappyDataInc/snappydata/issues/662#issuecomment-309338584, or mute the thread https://github.com/notifications/unsubscribe-auth/AGD75DgMQ82m1ZVyazB4RvB3rshTXWHwks5sFf-KgaJpZM4N9bU_ .

vibhaska avatar Jun 19 '17 05:06 vibhaska

@hbhanawat

Yes, while running "Workload E", which consists of bind variable for LIMIT, I am getting "java.lang.UnsupportedOperationException: This query is unsupported for prepared statement". Please find below. Is there any way I an run this "Workload E" ?

Running Workload E of YCSB: .\bin\ycsb run jdbc -P workloads/workloade -p db.driver=io.snappydata.jdbc.ClientDriver -p db.url=jdbc:snappydata://192.168.99.100:1527 -p db.user=ycsb -p db.passwd=ycsb -p operationcount=1000000 -s -threads 2

log from localhost-server-1

17/06/19 06:02:46.791 EDT pool-3-thread-8<tid=0x96> INFO snappystore: com.gemstone.gemfire.cache.execute.FunctionException: com.gemstone.gemfire.distributed.internal.ReplyException: From 192.168.99.100(515)<v2>:45091: com.pivotal.gemfirexd.internal.engine.distributed.FunctionExecutionException: Function execution exception on member [192.168.99.100(515)<v2>:45091]
        at com.pivotal.gemfirexd.internal.engine.distributed.GfxdQueryResultCollector.addExceptionFromMember(GfxdQueryResultCollector.java:274)
        at com.pivotal.gemfirexd.internal.engine.distributed.GfxdQueryResultCollector.addException(GfxdQueryResultCollector.java:260)
        at com.pivotal.gemfirexd.internal.engine.distributed.GfxdQueryResultCollector.addResult(GfxdQueryResultCollector.java:130)
        at com.pivotal.gemfirexd.internal.engine.distributed.message.GfxdFunctionMessage$GfxdFunctionReplyMessageProcessor.addResult(GfxdFunctionMessage.java:1335)
        at com.pivotal.gemfirexd.internal.engine.distributed.message.GfxdFunctionMessage$GfxdFunctionOrderedReplyMessageProcessor.addResult(GfxdFunctionMessage.java:1759)
        at com.pivotal.gemfirexd.internal.engine.distributed.message.GfxdFunctionMessage$GfxdFunctionReplyMessageProcessor.process(GfxdFunctionMessage.java:1221)
        at com.gemstone.gemfire.distributed.internal.ReplyMessage.process(ReplyMessage.java:372)
        at com.gemstone.gemfire.distributed.internal.ReplyMessage.dmProcess(ReplyMessage.java:344)
        at com.gemstone.gemfire.distributed.internal.ReplyMessage.process(ReplyMessage.java:332)
        at com.gemstone.gemfire.distributed.internal.DistributionMessage.scheduleAction(DistributionMessage.java:431)
        at com.gemstone.gemfire.distributed.internal.DistributionMessage$1.run(DistributionMessage.java:503)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at com.gemstone.gemfire.distributed.internal.DistributionManager.runUntilShutdown(DistributionManager.java:730)
        at com.gemstone.gemfire.distributed.internal.DistributionManager$6$1.run(DistributionManager.java:1062)
        at java.lang.Thread.run(Thread.java:748)
============= begin nested exception, level (1) ===========
com.gemstone.gemfire.distributed.internal.ReplyException: From 192.168.99.100(515)<v2>:45091: com.pivotal.gemfirexd.internal.engine.distributed.FunctionExecutionException: Function execution exception on member [192.168.99.100(515)<v2>:45091]
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.pivotal.gemfirexd.internal.engine.distributed.GfxdMessage.basicProcess(GfxdMessage.java:412)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.gemstone.gemfire.internal.cache.AbstractOperationMessage.process(AbstractOperationMessage.java:221)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.gemstone.gemfire.distributed.internal.DistributionMessage.scheduleAction(DistributionMessage.java:431)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.gemstone.gemfire.distributed.internal.DistributionMessage$1.run(DistributionMessage.java:503)
        at Remote Member '192.168.99.100(515)<v2>:45091' in java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at Remote Member '192.168.99.100(515)<v2>:45091' in java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.gemstone.gemfire.distributed.internal.DistributionManager.runUntilShutdown(DistributionManager.java:730)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.gemstone.gemfire.distributed.internal.DistributionManager$9$1.run(DistributionManager.java:1180)
        at Remote Member '192.168.99.100(515)<v2>:45091' in java.lang.Thread.run(Thread.java:748)
        at com.pivotal.gemfirexd.internal.iapi.error.StandardException.fixUpRemoteException(StandardException.java:162)
        at com.pivotal.gemfirexd.internal.engine.distributed.GfxdQueryResultCollector.addException(GfxdQueryResultCollector.java:258)
        ... 14 more
============= end nested exception, level (1) ===========
============= begin nested exception, level (2) ===========
com.pivotal.gemfirexd.internal.engine.distributed.FunctionExecutionException: Function execution exception on member [192.168.99.100(515)<v2>:45091]
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.pivotal.gemfirexd.internal.engine.distributed.message.GfxdFunctionMessage.processMessage(GfxdFunctionMessage.java:767)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.pivotal.gemfirexd.internal.engine.distributed.GfxdMessage.basicProcess(GfxdMessage.java:406)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.gemstone.gemfire.internal.cache.AbstractOperationMessage.process(AbstractOperationMessage.java:221)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.gemstone.gemfire.distributed.internal.DistributionMessage.scheduleAction(DistributionMessage.java:431)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.gemstone.gemfire.distributed.internal.DistributionMessage$1.run(DistributionMessage.java:503)
        at Remote Member '192.168.99.100(515)<v2>:45091' in java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at Remote Member '192.168.99.100(515)<v2>:45091' in java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.gemstone.gemfire.distributed.internal.DistributionManager.runUntilShutdown(DistributionManager.java:730)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.gemstone.gemfire.distributed.internal.DistributionManager$9$1.run(DistributionManager.java:1180)
        at Remote Member '192.168.99.100(515)<v2>:45091' in java.lang.Thread.run(Thread.java:748)
============= end nested exception, level (2) ===========
============= begin nested exception, level (3) ===========
java.lang.UnsupportedOperationException: This query is unsupported for prepared statement
        at Remote Member '192.168.99.100(515)<v2>:45091' in io.snappydata.gemxd.SparkSQLPrepareImpl.packRows(SparkSQLPrepareImpl.scala:88)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.pivotal.gemfirexd.internal.engine.distributed.SnappyResultHolder.prepareSend(SnappyResultHolder.java:178)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.pivotal.gemfirexd.internal.engine.distributed.message.LeadNodeExecutorMsg.execute(LeadNodeExecutorMsg.java:148)
        at Remote Member '192.168.99.100(515)<v2>:45091' in com.pivotal.gemfirexd.internal.engine.distributed.message.GfxdFunctionMessage.processMessage(GfxdFunctionMessage.java:739)
        ... 9 more
============= end nested exception, level (3) ===========

obaidcuet avatar Jun 19 '17 10:06 obaidcuet

@obaidcuet As @vivekwiz has clarified, there is no support for bind variables in the LIMIT clause. You need to change the query to embed the constant in query string and not use bind.

sumwale avatar Jun 19 '17 13:06 sumwale

@sumwale I have tested with both String concat and constant value for LIMIT, seems like whenever using LIMIT in JDBC, I am getting Exceptions. So far solution is using "setMaxRows(int max)" as I mentioned start of this topic.

By the way, workload E of YCSB, which fires queries like "SELECT * FROM usertable WHERE YCSB_KEY >=? ORDER BY YCSB_KEY LIMIT ?", is super slow on snappydata ( about 10 quires per second, as per my manual testing). On the other hand mysql, other inmemory DB and even nosql DBs are far better then Snappydata for this test.

So, with this test(because ORDER BY along with LIMIT will be common queries from dashboards) along with lack of geospatial capabilities, are two things I want to highlight snappydata team to resolve in coming next version.

obaidcuet avatar Jun 21 '17 16:06 obaidcuet

@obaidcuet Please check with latest 1.0 RC release (the final 1.0 release will be out later this week). The '?' in LIMIT is still not recommended rather should use like "SELECT * FROM usertable WHERE YCSB_KEY >=? ORDER BY YCSB_KEY LIMIT 100" which works in current releases just fine. setMaxRows route will just limit on the client-side so is not recommended (equivalent to stopping ResultSet loop after those many iterations).

Apart from above, for getting good performance with row tables use appropriate indexes e.g. on YCSB_KEY like above. In our previous testing, GemFireXD has consistently beaten MySQL and others in YCSB load by quite some margin so the low performance you see is likely due to one of these two reasons or both.

You can also try with column tables that now support update/delete. Their scan speed is orders of magnitude higher than row tables. Index support for those is still WIP so may not be very efficient for lots of point updates/deletes, but still if table is partitioned on the key column then in-built partition pruning and efficient scan space elimination can still give decent performance.

sumwale avatar Sep 19 '17 16:09 sumwale

Is there support for bind variables in the LIMIT clause now? @sumwale

fire avatar Aug 26 '18 23:08 fire

@fire No. It will be difficult to support it due to inherent way that Spark engine works for LIMIT. It assumes an integer literal -- see CollectLimitExec and the actual execution in SparkPlan.executeTake.

sumwale avatar Aug 27 '18 10:08 sumwale