db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <karth...@gmail.com>
Subject Re: Table not found: a bug?
Date Mon, 08 May 2006 18:57:23 GMT
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

Mime
View raw message