Simba driver prepares an INSERT SQL statement incorrectly

Description

System details :  

  1. Client is 172.23.100.204

  2. cd /root/CASSANDRA-YCSB/YCSB_3

  3. run jdbcload.sh 

Server :

    1. server is 172.23.100.190 

    2. CB version 4.5.1

 

The problem here is that the ycsb client sends this query to JDBC:

INSERT INTO usertable (YCSB_KEY,field1,field0,field7,field6,field9,field8,field3,field2,field5,field4) VALUES(?,?,?,?,?,?,?,?,?,?,?)

This statement is correct SQL but not correct N1QL. The Simba driver tries to prepare this statement by converting it into this statement, as though it were N1QL:

prepare INSERT INTO usertable (YCSB_KEY,field1,field0,field7,field6,field9,field8,field3,field2,field5,field4) VALUES(?,?,?,?,?,?,?,?,?,?,?)

This is not correct N1QL. The Simba driver should convert the original statement to N1QL and then prepend a "prepare".

Here is the log output:

Jan 11 11:21:05.852 TRACE 24 com.simba.couchbase.dataengine.CBNativeQueryExecutor.CBNativeQueryExecutor("INSERT INTO usertable (YCSB_KEY,field1,field0,field7,field6,field9,field8,field3,field2,field5,field4) VALUES(?,?,?,?,?,?,?,?,?,?,?)", null, false): +++++ enter +++++ Jan 11 11:21:05.853 TRACE 24 com.simba.couchbase.core.CBClient.preparedStatement("INSERT INTO usertable (YCSB_KEY,field1,field0,field7,field6,field9,field8,field3,field2,field5,field4) VALUES(?,?,?,?,?,?,?,?,?,?,?)"): +++++ enter +++++ Jan 11 11:21:05.853 TRACE 24 com.simba.couchbase.client.N1QLClient.prepareStatement("INSERT INTO usertable (YCSB_KEY,field1,field0,field7,field6,field9,field8,field3,field2,field5,field4) VALUES(?,?,?,?,?,?,?,?,?,?,?)"): +++++ enter +++++ Jan 11 11:21:05.853 TRACE 24 com.simba.couchbase.client.N1QLClientUtil.GET("prepare INSERT INTO usertable (YCSB_KEY,field1,field0,field7,field6,field9,field8,field3,field2,field5,field4) VALUES(?,?,?,?,?,?,?,?,?,?,?)", "statement"): +++++ enter +++++ Jan 11 11:21:05.856 TRACE 24 com.simba.couchbase.client.N1QLClientUtil.GET("Error Detected in during GET operation"): +++++ enter +++++ Jan 11 11:21:05.862 ERROR 24 com.simba.exceptions.ExceptionConverter.toSQLException: [Simba][CouchbaseJDBCDriver](500207) Error preparing statement, prepare statement fail. Code: 3000, message syntax error - at YCSB_KEY java.sql.SQLException: [Simba][CouchbaseJDBCDriver](500207) Error preparing statement, prepare statement fail. Code: 3000, message syntax error - at YCSB_KEY         at com.simba.couchbase.utils.CBQueryUtils.buildServerErrorMessage(CBQueryUtils.java:114)         at com.simba.couchbase.core.CBClient.preparedStatement(CBClient.java:367)         at com.simba.couchbase.dataengine.CBNativeQueryExecutor.<init>(CBNativeQueryExecutor.java:189)         at com.simba.couchbase.dataengine.CBSQLDataEngine.prepare(CBSQLDataEngine.java:323)         at com.simba.jdbc.common.SPreparedStatement.<init>(Unknown Source)         at com.simba.jdbc.jdbc4.S4PreparedStatement.<init>(Unknown Source)         at com.simba.jdbc.jdbc4.JDBC4ObjectFactory.createPreparedStatement(Unknown Source)         at com.simba.couchbase.jdbc4.CBJDBC4ObjectFactory.createPreparedStatement(CBJDBC4ObjectFactory.java:115)         at com.simba.jdbc.common.SConnection.prepareStatement(Unknown Source)         at com.yahoo.ycsb.db.JdbcDBClient.createAndCacheInsertStatement(JdbcDBClient.java:263)         at com.yahoo.ycsb.db.JdbcDBClient.insert(JdbcDBClient.java:415)         at com.yahoo.ycsb.DBWrapper.insert(DBWrapper.java:240)         at com.yahoo.ycsb.workloads.CoreWorkload.doInsert(CoreWorkload.java:593)         at com.yahoo.ycsb.ClientThread.run(Client.java:475) Caused by: com.simba.support.exceptions.GeneralException: [Simba][CouchbaseJDBCDriver](500207) Error preparing statement, prepare statement fail. Code: 3000, message syntax error - at YCSB_KEY         ... 14 more

I will file a ticket with Simba for this problem.

Components

Affects versions

Fix versions

Labels

Environment

None

Link to Log File, atop/blg, CBCollectInfo, Core dump

None

Release Notes Description

None

Attachments

2
  • 31 Mar 2017, 10:56 PM
  • 16 Feb 2017, 09:26 PM

Activity

Show:

sandip nandi April 7, 2017 at 3:32 AM

We have created schema file properly and we are able to insert data using YCSB.   This issue can be closed.  To make it run through YCSB  code modification is done.  YCSB

YCSB jdbc code creates a preparestatement and store into cache. It is done to avoid one layer of extra function call to create insert command.   Currently the code path need to be avoided to make simba driver  work with YCSB. check https://couchbasecloud.atlassian.net/browse/MB-23793#icft=MB-23793 .   The working code flow with simba driver  is expensive as it adds one extra layer.  

 

Prasad Varakur March 31, 2017 at 11:45 PM

Hi ,

Looks like there is small bug in your code. You have ? in quotes.  Hence, it was throwing error for you.

 

String selectSQL = "INSERT INTO airline (PK, type, name) values ('?', '?', '?')";

It should be:

String selectSQL = "INSERT INTO airline (PK, type, name) values (?, ?, ?)";

Following code works:

 

import java.sql.*; public class sampleJDBC {   static String ConnectionURL = "jdbc:couchbase://localhost:8093;QueryMode=0;SchemaMapOperation=0;LocalSchemaFile=/Users/varakurprasad/Downloads/Simba_Couchbase_JDBC_Desktop/myschMap.json;AuthMech=1;CredFile=/Users/varakurprasad/Downloads/Simba_Couchbase_JDBC_Desktop/credFile.json;TypeNameList=`travel-sample`:`type`";   public static void main(String[] args) throws Exception {     Class.forName("com.simba.couchbase.jdbc41.Driver");     System.out.println("DONE1");     Connection con = DriverManager.getConnection(ConnectionURL, "Administrator", "password");     System.out.println("DONE2");     Statement stmt = con.createStatement();     //ResultSet rs = stmt.executeQuery("select name from `travel-sample` where name = $1 limit 2");     //ResultSet rs = stmt.executeQuery("select name from `travel-sample` where type = 'airline' limit 2");     //int ret = stmt.executeUpdate("INSERT INTO airline (PK, type, name) values ('prasad', 'airline', 'varakur') ");     String selectSQL = "INSERT INTO airline (PK, type, name) values (?, ?, ?)";     PreparedStatement preparedStatement = con.prepareStatement(selectSQL);     preparedStatement.setString(1, "prasad2");     preparedStatement.setString(2, "airline");     preparedStatement.setString(3, "varakur");     int ret=0;     try {         ret = preparedStatement.executeUpdate();     } catch (SQLException e) {         System.out.println(e);     }     System.out.println("DONE3");     ResultSet rs = stmt.executeQuery("select PK, name from airline where type = 'airline' and name = 'varakur'");     while (rs.next()) {        String name = rs.getString("name");        String pk = rs.getString("PK");        System.out.println("PK: " + pk + ", name: " + name);     }     System.out.println("DONE, ret = " + ret);     con.close();   } // End of main() }

Output:

 

couchbases-MacBook-Pro-2:Simba_Couchbase_JDBC_Desktop varakurprasad$ java sampleJDBC DONE1 DONE2 DONE3 PK: prasad, name: varakur PK: prasad2, name: varakur DONE, ret = 0

 

 

 

sandip nandi March 31, 2017 at 10:44 PM
Edited

Here the problem is with Simba driver.   Preparedstatement  for CB driver is not working properly.

All JDBC drivers execute SQL query in two way . Either you create preparedstatement or you use statement.   The example presented above works because it did not use Preparedstatement. YCSB use preparedstatement, so it fails.  I have attached the java file to reproduce and simba will be able to repro it.

This line is enough to repro:

String selectSQL = "INSERT INTO airline (PK, type, name) values (?, ?, ?)";
PreparedStatement preparedStatement = con.prepareStatement(selectSQL);

The following one works:

int ret = stmt.executeUpdate("INSERT INTO airline (PK, type, name) values ('pr', 'airline', 'varakur') ");

For YCSB , it follows the same code path, create a SQL string and create preparedstatement.

https://github.com/brianfrankcooper/YCSB/blob/master/jdbc/src/main/java/com/yahoo/ycsb/db/JdbcDBClient.java#L262

https://github.com/brianfrankcooper/YCSB/blob/master/jdbc/src/main/java/com/yahoo/ycsb/db/JdbcDBClient.java#L410

 

For reference check the following link how  simba driver works for other vendors :

https://help.thoughtspot.com/Integration_Guides/Data_Integration_Guide_4.2/020/020/010

From orcale preparedstatement :

http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html 

java sampleJDBCSIMBA

Exception in thread "main" java.sql.SQLException: [Simba][CouchbaseJDBCDriver](500207) Error preparing statement, prepare statement fail. Code: 3000, message syntax error - at PK
at com.simba.couchbase.utils.CBQueryUtils.buildServerErrorMessage(CBQueryUtils.java:114)
at com.simba.couchbase.core.CBClient.preparedStatement(CBClient.java:367)
at com.simba.couchbase.dataengine.CBNativeQueryExecutor.<init>(CBNativeQueryExecutor.java:189)
at com.simba.couchbase.dataengine.CBSQLDataEngine.prepare(CBSQLDataEngine.java:323)
at com.simba.jdbc.common.SPreparedStatement.<init>(Unknown Source)
at com.simba.jdbc.jdbc41.S41PreparedStatement.<init>(Unknown Source)
at com.simba.jdbc.jdbc41.JDBC41ObjectFactory.createPreparedStatement(Unknown Source)
at com.simba.couchbase.jdbc41.CBJDBC41ObjectFactory.createPreparedStatement(CBJDBC41ObjectFactory.java:114)
at com.simba.jdbc.common.SConnection.prepareStatement(Unknown Source)
Caused by: com.simba.support.exceptions.GeneralException: [Simba][CouchbaseJDBCDriver](500207) Error preparing statement, prepare statement fail. Code: 3000, message syntax error - at PK
... 9 more

 

Prasad Varakur March 31, 2017 at 6:12 PM
Edited

I have verified the INSERT works with Simba driver. Here is a sample program. Note that:

  - first time you run it with  SchemaMapOperation=1 in the ConnectionURL variable. This creates the schema.

  - On subsequent runs, set SchemaMapOperation=0, to read the schema from the specified schema file.

  - write your credentials in JSON format in the specified CredFile parameter. Something like:

 

couchbases-MacBook-Pro-2:Simba_Couchbase_JDBC_Desktop varakurprasad$ cat credFile.json  [{"user": "Administrator", "pass":"password"}]

 

 

import java.sql.*; public class sampleJDBC {   static String ConnectionURL = "jdbc:couchbase://localhost:8093;QueryMode=0;SchemaMapOperation=0;LocalSchemaFile=/Users/varakurprasad/Downloads/Simba_Couchbase_JDBC_Desktop/myschMap.json;AuthMech=1;CredFile=/Users/varakurprasad/Downloads/Simba_Couchbase_JDBC_Desktop/credFile.json;TypeNameList=`travel-sample`:`type`";   public static void main(String[] args) throws Exception {     Class.forName("com.simba.couchbase.jdbc41.Driver");    Connection con = DriverManager.getConnection(ConnectionURL, "Administrator", "password"); Statement stmt = con.createStatement();     //ResultSet rs = stmt.executeQuery("select name from `travel-sample` where type = 'airline' limit 2");     int ret = stmt.executeUpdate("INSERT INTO airline (PK, type, name) values ('prasad', 'airline', 'varakur') ");     ResultSet rs = stmt.executeQuery("select name from airline where type = 'airline' and name = 'varakur'");     while (rs.next()) {        String name = rs.getString("name");        System.out.println("name: " + name);     }     System.out.println("DONE, ret = " + ret);     con.close();   } // End of main() }

 

 

Output:

 

couchbases-MacBook-Pro-2:Simba_Couchbase_JDBC_Desktop varakurprasad$ javac sampleJDBC.java   couchbases-MacBook-Pro-2:Simba_Couchbase_JDBC_Desktop varakurprasad$ java sampleJDBC name: varakur DONE, ret = 1

 

 

 

sandip nandi March 2, 2017 at 10:54 PM
Edited

Experiment CDATA driver.

  • stand Alone program : Select works. Insert does not work, no exception thrown.

  • YCSB: YCSB data load fails.
    YCSB Workloada : get operation works. setops is 0


Prasad is going to check the Insert syntax with CDATA.

Run the YCSB with read only Workload. It throws error.

Error in processing read of table usertable: XcoreXcouchbaseX160X6254.vic: 5000:"6b2fdf08-48aa-407a-adea-604800e3042d" backfill exceeded limit 5120, 6469 - cause: "6b2fdf08-48aa-407a-adea-604800e3042d" backfill exceeded limit 5120, 6469
Error in processing read of table usertable: XcoreXcouchbaseX160X6254.vic: 5000:"0c9d0d88-7fbb-47cb-90b8-6828f61b7243" backfill exceeded limit 5120, 6469 - cause: "0c9d0d88-7fbb-47cb-90b8-6828f61b7243" backfill exceeded limit 5120, 6469

Error in processing read of table

Fixed
Pinned fields
Click on the next to a field label to start pinning.

Details

Assignee

Prasad Varakur(Deactivated)

Reporter

Is this a Regression?

Unknown

Triage

Triaged

Sprint

Priority

Instabug

Open Instabug

PagerDuty

Sentry

Zendesk Support

Created January 11, 2017 at 7:49 PM
Updated April 7, 2017 at 3:33 AM
Resolved April 7, 2017 at 3:33 AM
Instabug