Simba driver prepares an INSERT SQL statement incorrectly
Description
Components
Affects versions
Fix versions
Environment
Link to Log File, atop/blg, CBCollectInfo, Core dump
Release Notes Description
Attachments
- 31 Mar 2017, 10:56 PM
- 16 Feb 2017, 09:26 PM
Activity
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 @sandip nandi,
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 PMEdited
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.
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 PMEdited
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 PMEdited
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
System details :
Client is 172.23.100.204
cd /root/CASSANDRA-YCSB/YCSB_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.