Major Description
Using the following JRE with JIT on an identity column may be created with a wrong and very large START WITH value. When the problem occurs it affects not only the table being created, but also other tables that were created in previous transactions.
For example attempting to create 1000 tables with identity columns the 126th table creation changes the start with value in sys.syscolumns to 41628850257395713 for ALL 125 tables. Attempts to insert into any of the tables cause
"SQL Exception: A truncation error was encountered trying to
shrink ... to length 12."
This program will create up to 1000 tables until the problem
occurs
Note:
- The problem does not occur with -Xnojit (JIT OFF)
- The problem, when it occurs, changes not only the table being
created but all previous tables created. See output below.
Every thing was fine up until mytable126 and then all the
tables got changed to start with 41628850257395713 - Problem occurs with autocommit on/off.
- The problem occurs after the create table but before the
commit. - If the non-identity columns are removed the problem does not
reproduce.
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DriverManager;
public class BadStartWith
{
public static void main (String args [])throws Exception
{ testBadStartWith(); }/**
- After some number of table creations with JIT turned on, the START WITH value
- for the table being created and all the ones already created gets mysteriously
- changed with pwi32dev-20060412 (SR2)
- @throws Exception
*/
public static void testBadStartWith() throws Exception
{
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:derby:wombat;create=true");
conn.setAutoCommit(false);
Statement stmt = null;
DatabaseMetaData md = conn.getMetaData() ;
System.out.println(md.getDatabaseProductVersion());
System.out.println(md.getDatabaseProductName());
System.out.println(md.getDriverName());
dropAllAppTables(conn);
System.out.println("Create tables until we get a wrong Start with value");
stmt = conn.createStatement();
// numBadStartWith will be changed if any columns get a bad start with value.
int numBadStartWith = 0;
try {
// create 1000 tables. Break out if we get a table that has a bad
// start with value.
for (int i = 0; (i < 1000) && (numBadStartWith == 0); i++)
} catch (SQLException se)
{ se.printStackTrace(); } if (numBadStartWith == 0)
System.out.println("PASS: All 1000 tables created without problems");
stmt.close();
conn.rollback();
conn.close();
}
/**
- Check that all tables in App do not have a an autoincrementstart value
- greater tan maxautoincrementstart
- @param conn
- @param maxautoincrementstart Maximum expected autoincrementstart value
- @return number of columns with bad autoincrementstart value
*/
private static int checkBadStartWithCols(Connection conn, int
maxautoincrementstart) throws Exception
{
Statement stmt = conn.createStatement();
ResultSet rs =stmt.executeQuery("select count(autoincrementstart) from sys.syscolumns c, sys.systables t, sys.sysschemas s WHERE t.schemaid = s.schemaid and s.schemaname = 'APP' and autoincrementstart > " + maxautoincrementstart);
rs.next();
int numBadStartWith = rs.getInt(1);
System.out.println(numBadStartWith + " columns have bad START WITH VALUE");
rs.close();
if (numBadStartWith > 0)
{
rs =stmt.executeQuery("select tablename, columnname, autoincrementstart from sys.syscolumns c, sys.systables t, sys.sysschemas s WHERE t.schemaid = s.schemaid and s.schemaname = 'APP' and autoincrementstart > 2 ORDER BY tablename");
while (rs.next())
}
return numBadStartWith;
}
/**
- Drop all tables in schema APP
- @param conn
- @throws SQLException
*/
private static void dropAllAppTables(Connection conn) throws SQLException
{
Statement stmt1 = conn.createStatement();
Statement stmt2 = conn.createStatement();
System.out.println("Drop all tables in APP schema");
ResultSet rs = stmt1.executeQuery("SELECT tablename from sys.systables t, sys.sysschemas s where t.schemaid = s.schemaid and s.schemaname = 'APP'");
while (rs.next())
{
String tableName = rs.getString(1);
try
{ stmt2.executeUpdate("DROP TABLE " + tableName); }catch (SQLException se)
{ System.out.println("Error dropping table:" + tableName); se.printStackTrace(); continue; } }
}
}
Relevant output:
$java BadStartWith
10.2.0.0 alpha
Apache Derby
Apache Derby Embedded JDBC Driver
Drop all tables in APP schema
Create tables until we get a wrong Start with value
CREATE TABLE APP.MYTABLE0 (ROLEID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 2, INCREMENT BY 1), INSTANCEID INTEGER, STATUS INTEGER, LOGICAL_STA
TE INTEGER, LSTATE_TSTAMP TIMESTAMP, UPDT_TSTAMP TIMESTAMP, TSTAMP TIMESTAMP, C
LALEVEL1_CLALEVEL2_CLALEVEL2ID VARCHAR(255), CLALEVEL1_CLALEVEL2_CLALEVEL3_CLAL
EVEL3ID VARCHAR(255))
Check before commit
0 columns have bad START WITH VALUE
Check after commit
0 columns have bad START WITH VALUE
[snip MYTABLE1 ... MYTABLE124]
CREATE TABLE APP.MYTABLE125 (ROLEID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2, INCREMENT BY 1), INSTANCEID INTEGER, STATUS INTEGER, LOGICAL_STATE INTEGER, LSTATE_TSTAMP TIMESTAMP,
UPDT_TSTAMP TIMESTAMP, TSTAMP TIMESTAMP, CLALEVEL1_CLALEVEL2_CLALEVEL2ID VARCHAR(255), CLALEVEL1_CLALEVEL2_CLALEVEL3_CLALEVEL3ID VARCHAR(255))
Check before commit
0 columns have bad START WITH VALUE
Check after commit
0 columns have bad START WITH VALUE
CREATE TABLE APP.MYTABLE126 (ROLEID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2, INCREMENT BY 1), INSTANCEID INTEGER, STATUS INTEGER, LOGICAL_STATE INTEGER, LSTATE_TSTAMP TIMESTAMP,
UPDT_TSTAMP TIMESTAMP, TSTAMP TIMESTAMP, CLALEVEL1_CLALEVEL2_CLALEVEL2ID VARCHAR(255), CLALEVEL1_CLALEVEL2_CLALEVEL3_CLALEVEL3ID VARCHAR(255))
Check before commit
127 columns have bad START WITH VALUE
Unexpected start value: 41628850257395713 on column MYTABLE0(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE1(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE10(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE100(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE101(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE102(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE103(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE104(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE105(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE106(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE107(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE108(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE109(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE11(ROLEID)
Unexpected start value: 41628850257395713 on column MYTABLE110(ROLEID)
[snip the rest of the tables have unexpected START WITH value too]