Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <11822983.1149781650634.JavaMail.jira@brutus>
Date: Thu, 8 Jun 2006 15:47:30 +0000 (GMT+00:00)
From: "Mamta A. Satoor (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (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: quoted-printable

     [ http://issues.apache.org/jira/browse/DERBY-1327?page=3Dall ]

Mamta A. Satoor updated DERBY-1327:
-----------------------------------

    Attachment: Derby1327WrongStartKeyPatch1CodelineTrunk.txt

The fix for this issue would be to reduce the number of parameters required=
 by the constructor to <=3D10 in org.apache.derby.iapi.sql.dictionary.Colum=
nDescriptor class. While researching into this, I found that all the 3 cons=
tructors in the class have a parameter named autoinc and it is defined as a=
 boolean. This parameter is always equal to (parameter named autoincInc !=
=3D 0). In my patch (Derby1327WrongStartKeyPatch1CodelineTrunk.txt) which i=
s attached to this JIRA, I have removed the autoinc parameter and inside th=
e constructors, I use (parameter named autoincInc !=3D 0) instead of relyin=
g on autoinc. This cleans up the constructor parameter passing for all the =
3 constructors and also brings down the number of parameters to <=3D10. The=
 test program from the JIRA entry runs fine with this change and I have cre=
ated a new test JitTest.java based on that test program. Hopefully this tes=
t can be a place holder for any future JIT issues. I also ran the test suit=
es and there were no new failures.

If the changes looks good, can a committer please commit it? As a cleanup, =
I also removed the import of org.apache.derby.iapi.sql.dictionary.ColumnDes=
criptor from some classes which didn't really use ColumnDescriptor.

svn stat
M java\engine\org\apache\derby\impl\sql\compile\FromSubquery.java
M java\engine\org\apache\derby\impl\sql\compile\CallStatementNode.java
M java\engine\org\apache\derby\impl\sql\compile\UpdateNode.java
M java\engine\org\apache\derby\impl\sql\compile\FromList.java
M java\engine\org\apache\derby\impl\sql\compile\InsertNode.java
M java\engine\org\apache\derby\impl\sql\compile\SubqueryList.java
M java\engine\org\apache\derby\impl\sql\compile\ResultColumnList.java
M java\engine\org\apache\derby\impl\sql\execute\WriteCursorConstantAction.j=
ava
M java\engine\org\apache\derby\impl\sql\execute\GenericResultSetFactory.jav=
a
M java\engine\org\apache\derby\impl\sql\execute\DropSchemaConstantAction.ja=
va
M java\engine\org\apache\derby\impl\sql\execute\CreateTableConstantAction.j=
ava
M java\engine\org\apache\derby\impl\sql\execute\CreateViewConstantAction.ja=
va
M java\engine\org\apache\derby\impl\sql\execute\IndexConstantAction.java
M java\engine\org\apache\derby\impl\sql\execute\DropConstraintConstantActio=
n.java
M java\engine\org\apache\derby\impl\sql\execute\SetConstraintsConstantActio=
n.java
M java\engine\org\apache\derby\impl\sql\execute\InsertConstantAction.java
M java\engine\org\apache\derby\impl\sql\execute\DropViewConstantAction.java
M java\engine\org\apache\derby\impl\sql\execute\ConstraintConstantAction.ja=
va
M java\engine\org\apache\derby\impl\sql\execute\DropIndexConstantAction.jav=
a
M java\engine\org\apache\derby\impl\sql\execute\AlterTableConstantAction.ja=
va
M java\engine\org\apache\derby\impl\sql\catalog\SYSCOLUMNSRowFactory.java
M java\engine\org\apache\derby\impl\sql\catalog\DataDictionaryImpl.java
M java\engine\org\apache\derby\impl\sql\catalog\SYSFILESRowFactory.java
M java\engine\org\apache\derby\iapi\sql\conn\LanguageConnectionContext.java
M java\engine\org\apache\derby\iapi\sql\dictionary\ColumnDescriptor.java
A java\testing\org\apache\derbyTesting\functionTests\tests\lang\JitTest.jav=
a
A java\testing\org\apache\derbyTesting\functionTests\master\JitTest.out
M java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.runal=
l


> Identity column can be  created with wrong and very large start with valu=
e 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
>  Attachments: Derby1327WrongStartKeyPatch1CodelineTrunk.txt
>
> Using the following JRE with JIT on  an identity column may be created wi=
th a wrong and very large START WITH value.  When the problem occurs it aff=
ects not only the table being created, but also other tables that were crea=
ted in previous transactions.
> For example attempting to create 1000 tables with identity columns the 12=
6th table creation changes the start with value in sys.syscolumns to 416288=
50257395713 for ALL 125 tables.  Attempts to insert into any of the tables =
cause
> "SQL Exception: A truncation error was encountered trying to=20
> shrink ... to length 12."=20
> This program will create up to 1000 tables until the problem=20
> occurs
> Note:
> - The problem does not occur with -Xnojit (JIT OFF)
> - The problem, when it occurs, changes not only the table being=20
> created but all previous tables created.  See output below.=20
> Every thing was fine up until mytable126 and then all the=20
> tables got changed to start with 41628850257395713=20
> - Problem occurs with autocommit on/off.
> - The problem occurs after the create table but before the=20
> commit.
> - If the non-identity columns are removed the problem does not=20
> reproduce.
> import java.sql.DatabaseMetaData;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> import java.sql.DriverManager;
> public class BadStartWith
> {
>    =20
>     public static void main (String args [])throws Exception=20
>     {
>     =09testBadStartWith();
>     }
>    =20
>     /**
>      * After some number of table creations with JIT turned on, the START=
 WITH value=20
>      * for the table being created and all the ones already created gets =
mysteriously
>      * changed with pwi32dev-20060412 (SR2)
>      *=20
>      * @throws Exception
>      */
>     public static void testBadStartWith() throws Exception
>     {
> =09=09
> =09=09Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance()=
;
> =09=09Connection conn =3D DriverManager.getConnection("jdbc:derby:wombat;=
create=3Dtrue");
>                conn.setAutoCommit(false);
> =09=09Statement stmt =3D null;
> =09=09
> =09=09DatabaseMetaData md =3D conn.getMetaData() ;
> =09=09System.out.println(md.getDatabaseProductVersion());
>                System.out.println(md.getDatabaseProductName());
>                 System.out.println(md.getDriverName());
> =09=09dropAllAppTables(conn);
> =09=09System.out.println("Create tables until we get a wrong Start with v=
alue");
> =09=09stmt =3D conn.createStatement();
> =09=09// numBadStartWith will be changed if any columns get a bad start w=
ith value.
> =09=09int numBadStartWith =3D 0;=20
> =09=09try {
> =09=09=09// create 1000 tables.  Break out if we get a table that has a b=
ad
> =09=09=09// start with value.
> =09=09=09for (int i =3D 0; (i < 1000) && (numBadStartWith =3D=3D 0); i++)
> =09=09=09{
> =09=09=09=09String tableName =3D "APP.MYTABLE" + i;
> =09=09=09    String createTableSQL =3D "CREATE TABLE " + tableName + "  (=
ROLEID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2, INCREME=
NT BY 1), INSTANCEID INTEGER, STATUS INTEGER, LOGICAL_STATE INTEGER, LSTATE=
_TSTAMP  TIMESTAMP, UPDT_TSTAMP TIMESTAMP, TSTAMP TIMESTAMP, CLALEVEL1_CLAL=
EVEL2_CLALEVEL2ID VARCHAR(255),  CLALEVEL1_CLALEVEL2_CLALEVEL3_CLALEVEL3ID =
VARCHAR(255))";
> =09=09=09=09
> =09=09=09=09stmt.executeUpdate(createTableSQL);
> =09=09=09=09System.out.println(createTableSQL);
>                 System.out.println("Check before commit");
>                 numBadStartWith =3D checkBadStartWithCols(conn,2);
>                 conn.commit();
>                 System.out.println("Check after commit");
> =09=09=09=09numBadStartWith =3D checkBadStartWithCols(conn,2);
> =09=09=09=09if (numBadStartWith > 0)
> =09=09=09=09=09break;
> =09=09=09}
> =09=09} catch (SQLException se)
> =09=09{
> =09=09=09se.printStackTrace();
> =09=09}
> =09=09if (numBadStartWith =3D=3D 0)
> =09=09=09System.out.println("PASS: All 1000 tables created without proble=
ms");
> =09=09stmt.close();
> =09=09conn.rollback();
>         conn.close();
> =09}
> /**
>  * Check that all tables in App do not have a an autoincrementstart value
>  * greater tan maxautoincrementstart
>  * @param conn
>  * @param maxautoincrementstart  Maximum expected autoincrementstart valu=
e
>  * @return number of columns with bad autoincrementstart value
>  */
> =09private static int checkBadStartWithCols(Connection conn, int
> =09=09=09=09=09=09=09=09=09=09=09 maxautoincrementstart) throws Exception
> =09{
> =09=09Statement stmt =3D conn.createStatement();
> =09=09ResultSet rs =3Dstmt.executeQuery("select count(autoincrementstart)=
 from sys.syscolumns c, sys.systables t, sys.sysschemas s WHERE t.schemaid =
=3D  s.schemaid and s.schemaname =3D 'APP' and autoincrementstart > " +  ma=
xautoincrementstart);
> =09=09rs.next();
> =09=09int numBadStartWith =3D rs.getInt(1);
> =09=09System.out.println(numBadStartWith + " columns have bad START WITH =
VALUE");
> =09=09rs.close();
> =09=09
> =09=09if (numBadStartWith > 0)
> =09=09{
> =09=09=09rs =3Dstmt.executeQuery("select tablename, columnname, autoincre=
mentstart from sys.syscolumns c, sys.systables t, sys.sysschemas s WHERE t.=
schemaid =3D s.schemaid and s.schemaname =3D 'APP' and autoincrementstart >=
 2 ORDER BY tablename");
> =09=09=09while (rs.next())
> =09=09=09{
> =09=09=09=09System.out.println("Unexpected start value: " +
> =09=09=09=09=09=09=09=09   rs.getLong(3) +=20
> =09=09=09=09=09=09=09=09   " on column " + rs.getString(1) +
> =09=09=09=09=09=09=09=09   "(" + rs.getString(2) + ")");
> =09=09=09=09
> =09=09=09=09
> =09=09=09}
> =09=09}
>        return numBadStartWith;
> =09}
> =09
>       /**
>         * Drop all tables in schema APP
> =09 * @param conn
> =09 * @throws SQLException
> =09 */
> =09private  static void dropAllAppTables(Connection conn) throws SQLExcep=
tion
> =09{
> =09=09Statement stmt1 =3D conn.createStatement();
> =09=09Statement stmt2 =3D conn.createStatement();
> =09=09System.out.println("Drop all tables in APP schema");
> =09=09ResultSet rs =3D stmt1.executeQuery("SELECT tablename from sys.syst=
ables t, sys.sysschemas s where t.schemaid =3D s.schemaid and s.schemaname =
=3D 'APP'");
> =09=09while (rs.next())
> =09=09{
> =09=09=09String tableName =3D rs.getString(1);
> =09=09=09
> =09=09=09try {
> =09=09=09=09stmt2.executeUpdate("DROP TABLE " + tableName);
> =09=09=09}
> =09=09=09catch (SQLException se)
> =09=09=09{
> =09=09=09=09System.out.println("Error dropping table:" + tableName);
> =09=09=09=09se.printStackTrace();
> =09=09=09=09continue;
> =09=09=09}
> =09=09}
> =09}
> =09
> }
> 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 I=
DENTITY
>  (START WITH 2, INCREMENT BY 1), INSTANCEID INTEGER, STATUS INTEGER, LOGI=
CAL_STA
> TE INTEGER, LSTATE_TSTAMP  TIMESTAMP, UPDT_TSTAMP TIMESTAMP, TSTAMP TIMES=
TAMP, C
> LALEVEL1_CLALEVEL2_CLALEVEL2ID VARCHAR(255),  CLALEVEL1_CLALEVEL2_CLALEVE=
L3_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 INTEGE=
R, 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 INTEGE=
R, 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]

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