Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-1327

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

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.3.1, 10.2.1.6
    • Fix Version/s: 10.1.3.1, 10.2.1.6
    • Component/s: SQL
    • Labels:
      None

      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++)

      { 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]

        Attachments

        1. Derby1327WrongStartKeyPatch1CodelineTrunk.txt
          483 kB
          Mamta A. Satoor
        2. Derby1327WrongStartKeyPatch1SvnDiff101.txt
          474 kB
          Mamta A. Satoor
        3. Derby1327WrongStartKeyPatch1SvnStat101.txt
          2 kB
          Mamta A. Satoor

          Activity

            People

            • Assignee:
              mamtas Mamta A. Satoor
              Reporter:
              kmarsden Katherine Marsden
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: