[teiid-issues] [JBoss JIRA] (TEIID-1933) 0 size CLOBs in teiid cause unexpected problems

0 size CLOBs in teiid cause unexpected problems ----------------------------------------------- Key: TEIID-1933 URL: https://issues.jboss.org/browse/TEIID-1933 Project: Teiid Issue Type: Bug Affects Versions: 7.6 Environment: java version "1.6.0_20" OpenJDK Runtime Environment (IcedTea6 1.9.10) (rhel-1.23.1.9.10.el5_7-x86_64) OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode) Running on RHEL 5, teiid 7.6 inside of JBoss EAP 5.1.1 teiid has had the fix in TEIID-1927 applied Reporter: Graeme Gillies Assignee: Steven Hawkins Hi, this issue is similar to the issue in TEIID-1927, but this time, we are experiencing similar problems when other code is inadvertently calling getSubString on 0 sized CLOB objects from teiid An example is below that takes some data from teiid and tries to put it into a mysql database {code} import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Iterator; import org.teiid.jdbc.TeiidDataSource; public class TestMain { private static final String host = "vdb.example.com"; private static final String port = "31000"; private static final String vdb = "EngVDBR"; private static final String user = "user"; private static final String password = "user"; private static final int batchSize = 50000; public static void main(String[] args) throws Exception { new TestMain(); } public TestMain() { try { System.out.println("Executing using the TeiidDriver"); Connection connSrc = getSrcConnection(); Connection connTar = getTarConnection(); ArrayList<String> batches = getBatches(getCount(connSrc, "Bugzilla.bugs", "1 = 1")); System.out.println(batches.size()); Iterator<String> it = batches.iterator(); while (it.hasNext()) { String str = "select keywords from Bugzilla.bugs order by bug_id " + it.next(); System.out.println(str); execute(connSrc, str, connTar); } } catch (Exception e) { e.printStackTrace(); } } static Connection getSrcConnection() throws Exception { String url = "jdbc:teiid:" + vdb + "@mms://" + host + ":" + port + ";showplan=on"; Class.forName("org.teiid.jdbc.TeiidDriver"); return DriverManager.getConnection(url, user, password); } static Connection getTarConnection() throws Exception { String url = "jdbc:mysql://localhost:3306/test"; Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection(url, "test", "test"); } static Connection getDataSourceConnection() throws Exception { TeiidDataSource ds = new TeiidDataSource(); ds.setDatabaseName(vdb); ds.setUser(user); ds.setPassword(password); ds.setServerName(host); ds.setPortNumber(Integer.valueOf(port)); ds.setShowPlan("on"); return ds.getConnection(); } public static void execute(Connection connSrc, String sql, Connection connTar) throws Exception { try { Statement statement = connSrc.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); // statement.setFetchSize(5000); ResultSet results = statement.executeQuery(sql); ResultSetMetaData metadata = results.getMetaData(); int columns = metadata.getColumnCount(); connTar.setAutoCommit(false); PreparedStatement ps = connTar .prepareStatement("insert into bugs values(" + getColStr(columns) + ")"); System.out.println("insert into bugs values(" + getColStr(columns) + ")"); for (int row = 1; results.next(); row++) { for (int i = 1; i <= columns; i++) { // System.out.println(results.getObject(i)); ps.setObject(i, results.getObject(i)); } ps.executeUpdate(); } connTar.commit(); results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); System.exit(1); } finally { } } public ArrayList<String> getBatches(int tabCnt) { ArrayList<String> batches = new ArrayList<String>(); int batchLen = 0; int init = 0; int endNum = 0; if (tabCnt % batchSize == 0) { batchLen = tabCnt / batchSize; } else { batchLen = tabCnt / batchSize + 1; } for (int i = 0; i < batchLen; i++) { batches.add(" limit " + init + ", " + batchSize); endNum = init + batchSize; init = endNum; } return batches; } public int getCount(Connection conn, String table, String condition) { try { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select count(1) from " + table + " where " + condition); if (rs.next()) { return rs.getInt(1); } } catch (Exception e) { } return 0; } public static String getColStr(int columnCount) { StringBuffer sb = new StringBuffer(); for (int i = 0; i < columnCount; i++) { sb.append("?,"); } String str = sb.toString(); return str.substring(0, str.length() - 1); } } {code} Which throws the following error {noformat} Executing using the TeiidDriver 13 select keywords from Bugzilla.bugs order by bug_id limit 0, 50000 insert into bugs values(?) javax.sql.rowset.serial.SerialException: Invalid position in BLOB object set at javax.sql.rowset.serial.SerialClob.getSubString(SerialClob.java:246) at com.mysql.jdbc.PreparedStatement.setClob(PreparedStatement.java:3553) at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3979) at com.redhat.com.hss.etl.util.TestMain.execute(TestMain.java:93) at com.redhat.com.hss.etl.util.TestMain.<init>(TestMain.java:40) at com.redhat.com.hss.etl.util.TestMain.main(TestMain.java:25) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.apache.tools.ant.taskdefs.ExecuteJava.run(ExecuteJava.java:217) at org.apache.tools.ant.taskdefs.ExecuteJava.execute(ExecuteJava.java:152) at org.apache.tools.ant.taskdefs.Java.run(Java.java:764) at org.apache.tools.ant.taskdefs.Java.executeJava(Java.java:218) at org.apache.tools.ant.taskdefs.Java.executeJava(Java.java:132) at org.apache.tools.ant.taskdefs.Java.execute(Java.java:105) at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106) at org.apache.tools.ant.Task.perform(Task.java:348) at org.apache.tools.ant.Target.execute(Target.java:357) at org.apache.tools.ant.Target.performTasks(Target.java:385) at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1337) at org.apache.tools.ant.Project.executeTarget(Project.java:1306) at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41) at org.apache.tools.ant.Project.executeTargets(Project.java:1189) at org.apache.tools.ant.Main.runBuild(Main.java:758) at org.apache.tools.ant.Main.startAnt(Main.java:217) at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257) at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104) {noformat} On closer inspection it looks like inside the mysql driver it's calling getSubString(1, 0) on the object because the CLOB object is size 0, which is the problem in the first place.