[ http://issues.apache.org/jira/browse/DERBY-1327?page=comments#action_12412448 ]
Kathey Marsden commented on DERBY-1327: --------------------------------------- The problem seems to be in the compilation of this constructor call. The parameter for autoincStart gets compiled incorrectly. This is a long value and even hard coded as 0L it will have a bad value maybe an address, since the 0L value is much less than the variable. I still have not been able to isolate completely from Derby. Constructor call code in CreateTAbleConstantAction.executeConstantAction() columnDescriptor = new ColumnDescriptor( columnInfo[ix].name, index++, columnInfo[ix].dataType, columnInfo[ix].defaultValue, columnInfo[ix].defaultInfo, td, defaultUUID, // below is the problem parameter. // Occurs also with 0L hard coded. // The value when printed is ok. columnInfo[ix].autoincStart, columnInfo[ix].autoincInc, columnInfo[ix].autoincInc != 0, columnInfo[ix].autoinc_create_or_modify_Start_Increment ); Constructor definition: public ColumnDescriptor(String columnName, int columnPosition, DataTypeDescriptor columnType, DataValueDescriptor columnDefault, DefaultInfo columnDefaultInfo, TableDescriptor table, UUID defaultUUID, long autoincStart, long autoincInc, boolean autoinc, long userChangedWhat) > Identity column can be created with wrong and very large start with value > with "J2RE 1.5.0 IBM Windows 32 build pwi32dev-20060412 (SR2)" with JIT on > ------------------------------------------------------------------------------------------------------------------------------------------------------ > > Key: DERBY-1327 > URL: http://issues.apache.org/jira/browse/DERBY-1327 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.1.2.4, 10.2.0.0 > Reporter: Kathey Marsden > > 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++) > { > String tableName = "APP.MYTABLE" + i; > String createTableSQL = "CREATE TABLE " + tableName > + " (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))"; > > stmt.executeUpdate(createTableSQL); > System.out.println(createTableSQL); > System.out.println("Check before commit"); > numBadStartWith = checkBadStartWithCols(conn,2); > conn.commit(); > System.out.println("Check after commit"); > numBadStartWith = checkBadStartWithCols(conn,2); > if (numBadStartWith > 0) > break; > } > } 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()) > { > System.out.println("Unexpected start value: " + > > rs.getLong(3) + > " on column > " + rs.getString(1) + > "(" + > rs.getString(2) + ")"); > > > } > } > 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] -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
