Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <11780321.1150230391117.JavaMail.jira@brutus>
Date: Tue, 13 Jun 2006 20:26:31 +0000 (GMT+00:00)
From: "Kathey Marsden (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_12416072 ] 

Kathey Marsden commented on DERBY-1327:
---------------------------------------

I confirmed with the released IBM JDK Service release 2 that this issue does reproduce without Mamta's change and was fixed afterward.   I am awaiting clarification on what is the minimum number of parameters to trigger the JVM bug but since this is a compile time issue I think that Mamta's fix is enough to resolve DERBY-1327 in both trunk and 10.1 as I do not think there are any runtime configurations that could cause different behaviour.  Below is a summary of this issue from a user perspective.


PROBLEM

After creating a table, with JIT engaged, identity column start with values for any column in the database gets changed to an incorrect value.

SYMPTOM

The symptom is typically that an insert to the table will fail with the Exception:
SQLSTATE 22001: "A truncation error was encountered trying to shrink ... to length 12."

CAUSE
A JIT issue in IBM JDK 1.5 Service Release 2 can cause corruption of the start with value.  The affected version is:
java version "1.5.0"
Java(TM) 2 Runtime Environment, Standard Edition (build pwi32dev-20060511 (SR2))
IBM J9 VM (build 2.3, J2RE 1.5.0 IBM J9 2.3 Windows XP x86-32 j9vmwi3223-20060504 (JIT enabled)
J9VM - 20060501_06428_lHdSMR
JIT  - 20060428_1800_r8
GC   - 20060501_AA)
JCL  - 20060511a

SOLUTION:

A fix to resolve the Derby symptom is available in 10.1 builds after 10.1.2.5.413354 and will be included in the Derby 10.1.3.0 release.  

Once corruption has occurred there is no way in 10.1 to correct the START WITH value.   The only way to recover date is to recreate the database and use export/import to transfer the data.

WORKAROUND:
If  users wish to wait  for the 10.1.3.0 official release,  The following JIT options can be specified for the JVM to prevent corruption in identity columns but this does allow recovery after the problem has occured.

-Xjit:exclude={org/apache/derby/impl/sql/execute/CreateTableCon
stantAction.executeConstantAction\(Lorg/apache/derby/iapi/sql/Ac
tivation\;\)V}


> 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
>     Assignee: Mamta A. Satoor
>      Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.5
>  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