Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <10476021.1149833551042.JavaMail.jira@brutus>
Date: Fri, 9 Jun 2006 06:12:31 +0000 (GMT+00:00)
From: "Mamta A. Satoor (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (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
In-Reply-To: <14931134.1147761305951.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/DERBY-1327?page=comments#action_12415491 ] 

Mamta A. Satoor commented on DERBY-1327:
----------------------------------------

I ran the new test JitTest.java with Sun JDK 1.3.1 and it ran fine. I have also started derbyall run with Sun JDK 1.3.1 and so far no failures. This is all on main codeline.

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