[ http://issues.apache.org/jira/browse/DERBY-1327?page=all ]
Kathey Marsden updated DERBY-1327:
----------------------------------
Derby Info: (was: [Patch Available])
Taking off Patch Available as this has been committed to trunk and 10.1
> 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.2.0.0, 10.1.2.4
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Attachments: Derby1327WrongStartKeyPatch1CodelineTrunk.txt,
> Derby1327WrongStartKeyPatch1SvnDiff101.txt,
> Derby1327WrongStartKeyPatch1SvnStat101.txt
>
> 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
