Re: Table not found: a bug? DERBY-1304 created.

[Piet Blok <pb...@wanadoo.nl>]

 DERBY-1304 created

Indeed, a caching problem seems likely.


Piet Blok
----- Original Message ----- 
From: "Rajesh Kartha" <ka...@gmail.com>
To: "Derby Discussion" <de...@db.apache.org>
Sent: Monday, May 08, 2006 8:57 PM
Subject: Re: Table not found: a bug?


> Piet Blok wrote:
>
>> Hi,
>>
>> I encountered a strange problem when trying to do the following:
>>
>> Create a schema
>> Create a table
>> Create a view
>>
>> Drop the view
>> Drop the table
>> Drop the schema
>>
>> The first time I execute this sequence all goes well.
>>
>> The second time however when I try to execute the code, it fails on the 
>> drop view statement:
>> ERROR X0X05: Table 'TEST_SCHEMA.V1' does not exist.
>>
>> When I leave out the create and drop of a view, all goes well, so it 
>> seems to have something to do with the view.
>>
>>
>> The following ij script demonstrates the problem:
>>
>> -- *******************************************************
>> -- Setup connection
>> -- *******************************************************
>> connect 'jdbc:derby://localhost/TestData;create=true;';
>> -- *******************************************************
>> -- The first time
>> -- *******************************************************
>> CREATE SCHEMA TEST_SCHEMA;
>> CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR);
>> CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM 
>> TEST_SCHEMA.T1;
>>
>> DROP VIEW TEST_SCHEMA.V1;
>> DROP TABLE TEST_SCHEMA.T1;
>> DROP SCHEMA TEST_SCHEMA RESTRICT;
>> -- *******************************************************
>> -- The second time
>> -- *******************************************************
>> CREATE SCHEMA TEST_SCHEMA;
>> CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR);
>> CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM 
>> TEST_SCHEMA.T1;
>>
>> DROP VIEW TEST_SCHEMA.V1; -- This statement fails
>> DROP TABLE TEST_SCHEMA.T1;
>> DROP SCHEMA TEST_SCHEMA RESTRICT;
>> -- *******************************************************
>> -- Cleanup
>> -- *******************************************************
>> disconnect;
>> connect 'jdbc:derby://localhost/TestData;shutdown=true;';
>>
>> I get the following console output from ij:
>>
>> ij version 10.1
>> ij> -- *******************************************************
>> -- Setup connection
>> -- *******************************************************
>> connect 'jdbc:derby://localhost/TestData;create=true;';
>> ij> -- *******************************************************
>> -- The first time
>> -- *******************************************************
>> CREATE SCHEMA TEST_SCHEMA;
>> 0 rows inserted/updated/deleted
>> ij> CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR);
>> 0 rows inserted/updated/deleted
>> ij> CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM 
>> TEST_SCHEMA.T1;
>> 0 rows inserted/updated/deleted
>> ij> DROP VIEW TEST_SCHEMA.V1;
>> 0 rows inserted/updated/deleted
>> ij> DROP TABLE TEST_SCHEMA.T1;
>> 0 rows inserted/updated/deleted
>> ij> DROP SCHEMA TEST_SCHEMA RESTRICT;
>> 0 rows inserted/updated/deleted
>> ij> -- *******************************************************
>> -- The second time
>> -- *******************************************************
>> CREATE SCHEMA TEST_SCHEMA;
>> 0 rows inserted/updated/deleted
>> ij> CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR);
>> 0 rows inserted/updated/deleted
>> ij> CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM 
>> TEST_SCHEMA.T1;
>> 0 rows inserted/updated/deleted
>> ij> DROP VIEW TEST_SCHEMA.V1;
>> ERROR X0X05: Table 'TEST_SCHEMA.V1' does not exist.
>> ij> -- This statement fails
>> DROP TABLE TEST_SCHEMA.T1;
>> ERROR X0Y23: Operation 'DROP TABLE' cannot be performed on object 'T1' 
>> because VIEW 'V1' is dependent on that object.
>> ij> DROP SCHEMA TEST_SCHEMA RESTRICT;
>> ERROR X0Y54: Schema 'TEST_SCHEMA' cannot be dropped because it is not 
>> empty.
>> ij> -- *******************************************************
>> -- Cleanup
>> -- *******************************************************
>> disconnect;
>> ij> connect 'jdbc:derby://localhost/TestData;shutdown=true;';
>> ERROR 08006: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08006, SQLERRMC: 
>> Database 'TestData' shutdown.
>> ij>
>>
>> This is the sysinfo output:
>>
>> ------------------ Java Information ------------------
>> Java Version:    1.5.0_06
>> Java Vendor:     Sun Microsystems Inc.
>> Java home:       C:\Program Files\Java\jre1.5.0_06
>> Java classpath: 
>> .;C:\PROGRA~1\JMF21~1.1E\lib\sound.jar;C:\PROGRA~1\JMF21~1.1E\lib\jmf.jar;C:\PROGRA~1\JMF21~1.1E\lib;C:\WINDOWS\java\classes;C:\WorkSpace\PB 
>> 3.0\bin;C:\WorkSpace\Filipe\bin;C:\Documents and Settings\Piet\My 
>> Documents\Resources\Jars\ImgrRdr130.jar;C:\Documents and Settings\Piet\My 
>> Documents\Resources\Jars\lucene-1.4.1.jar;C:\Documents and 
>> Settings\Piet\My 
>> Documents\Resources\Jars\Multivalent20040415.jar;C:\Workspace\Database\bin;
>> OS name:         Windows XP
>> OS architecture: x86
>> OS version:      5.1
>> Java user name:  Piet
>> Java user home:  C:\Documents and Settings\Piet
>> Java user dir:   C:\StickFolder
>> java.specification.name: Java Platform API Specification
>> java.specification.version: 1.5
>> --------- Derby Information --------
>> JRE - JDBC: J2SE 5.0 - JDBC 3.0
>> [/org/apache/derby/info/DBMS.properties] 10.1.2.1 - (330608)
>> [/org/apache/derby/info/tools.properties] 10.1.2.1 - (330608)
>> [/org/apache/derby/info/net.properties] 10.1.2.1 - (330608)
>> [/org/apache/derby/info/dnc.properties] 10.1.2.1 - (330608)
>> ------------------------------------------------------
>> ----------------- Locale Information -----------------
>> Current Locale :  [Nederlands/Nederland [nl_NL]]
>> Found support for locale: [de_DE]
>>  version: 10.1.2.1 - (330608)
>> Found support for locale: [es]
>>  version: 10.1.2.1 - (330608)
>> Found support for locale: [fr]
>>  version: 10.1.2.1 - (330608)
>> Found support for locale: [it]
>>  version: 10.1.2.1 - (330608)
>> Found support for locale: [ja_JP]
>>  version: 10.1.2.1 - (330608)
>> Found support for locale: [ko_KR]
>>  version: 10.1.2.1 - (330608)
>> Found support for locale: [pt_BR]
>>  version: 10.1.2.1 - (330608)
>> Found support for locale: [zh_CN]
>>  version: 10.1.2.1 - (330608)
>> Found support for locale: [zh_TW]
>>  version: 10.1.2.1 - (330608)
>> ------------------------------------------------------
>>
>> Piet Blok
>>
>>
>>
> Hi Peter,
>
> Typically after a CREATE SCHEMA I would do a SET SCHEMA. Using that 
> approach I did not see the  Exception:
>
> ij version 10.1
> ij> -- *******************************************************
> -- Setup connection
> -- *******************************************************
> connect 'jdbc:derby://localhost/TestData;create=true;';
> ij> -- *******************************************************
> -- The NEW First time
> -- *******************************************************
> CREATE SCHEMA TEST_SCHEMA;
> 0 rows inserted/updated/deleted
> ij> set schema TEST_SCHEMA;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE T1 (TABLE_COLUMN LONG VARCHAR);
> 0 rows inserted/updated/deleted
> ij> CREATE VIEW V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM 
> TEST_SCHEMA.T1;
> 0 rows inserted/updated/deleted
> ij> DROP VIEW V1;
> 0 rows inserted/updated/deleted
> ij> -- This statement fails
> DROP TABLE T1;
> 0 rows inserted/updated/deleted
> ij> DROP SCHEMA TEST_SCHEMA RESTRICT;
> 0 rows inserted/updated/deleted
> ij> -- *******************************************************
> -- The NEW Second time
> -- *******************************************************
> CREATE SCHEMA TEST_SCHEMA;
> 0 rows inserted/updated/deleted
> ij> set schema TEST_SCHEMA;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE T1 (TABLE_COLUMN LONG VARCHAR);
> 0 rows inserted/updated/deleted
> ij> CREATE VIEW V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM 
> TEST_SCHEMA.T1;
> 0 rows inserted/updated/deleted
> ij> DROP VIEW V1;
> 0 rows inserted/updated/deleted
> ij> -- This statement fails
> DROP TABLE T1;
> 0 rows inserted/updated/deleted
> ij> DROP SCHEMA TEST_SCHEMA RESTRICT;
> 0 rows inserted/updated/deleted
> ij> -- *******************************************************
> -- Cleanup
> -- *******************************************************
>
> Hope the above helps.
>
> Using your scripts does seem bring out some minor schema related (caching 
> ?) issue. You may choose to log a
> JIRA issue in this regard.
>
> -Rajesh
>