Re: Help needed to understand deadlock

[Rick Hillegas]

With that change, the script fails further on with the creation of the 
registrations table:


ERROR X0Y46: Constraint 'REGISTRNS_1' is invalid: referenced table USERS 
does not exist.



On 2/11/24 3:48 AM, John English wrote:

Either delete the constraint, or add "studentid integer" to the table
definition.

On Sat, 10 Feb 2024, 23:13 Rick Hillegas,  wrote:


I get the following error when I run this DDL:

ERROR 42X93: Table 'ACTIVE_LIST' contains a constraint definition with
column 'STUDENTID' which is not in the table.


On 2/10/24 6:03 AM, John English wrote:

CREATE TABLE active_list (
   usernameVARCHAR(15)   NOT NULL,
   surname VARCHAR(255)  NOT NULL,
   initialsVARCHAR(255)  NOT NULL DEFAULT '',
   email   VARCHAR(255)  NOT NULL DEFAULT '',
   settingsCLOB,
   CONSTRAINT active_pk  PRIMARY KEY (username),
   CONSTRAINT active_1   UNIQUE (studentid)
)

Re: Help needed to understand deadlock

[Rick Hillegas]

I get the following error when I run this DDL:

ERROR 42X93: Table 'ACTIVE_LIST' contains a constraint definition with 
column 'STUDENTID' which is not in the table.



On 2/10/24 6:03 AM, John English wrote:

CREATE TABLE active_list (
  username    VARCHAR(15)   NOT NULL,
  surname VARCHAR(255)  NOT NULL,
  initials    VARCHAR(255)  NOT NULL DEFAULT '',
  email   VARCHAR(255)  NOT NULL DEFAULT '',
  settings    CLOB,
  CONSTRAINT active_pk  PRIMARY KEY (username),
  CONSTRAINT active_1   UNIQUE (studentid)
)

Re: Help needed to understand deadlock

[Rick Hillegas]

I would need to see your full schema before speculating about why these 
statements grab these locks.


On 2/2/24 11:46 AM, John English wrote:

My system recently reported the following:

java.sql.SQLTransactionRollbackException: A lock could not be obtained 
due to a deadlock, cycle of locks and waiters is:

Lock : ROW, ACTION_LOG, (2,4800)
  Waiting XID : {255785240, U} , APP, SELECT * FROM action_log WHERE 
status='A' AND endtime>=CURRENT_TIMESTAMP FOR UPDATE

  Granted XID : {255785232, U}
Lock : TABLE, REGISTRATIONS, Tablelock
  Waiting XID : {255785232, IS} , APP, SELECT * FROM registrations 
WHERE activity=?

  Granted XID : {255785263, IX}
Lock : TABLE, ACTION_LOG, Tablelock
  Waiting XID : {255785263, X} , APP, DELETE FROM active_list
    WHERE username=CAST 
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(1) 
AS VARCHAR(15))

    AND   username NOT IN (SELECT username FROM current_list)
  Granted XID : {255785232, IX} , {255785233, IX} , {255785236, IX} , 
{255785240, IX} , {255785247, IX} , {255785249, IX}

Lock : ROW, ACTION_LOG, (2,4800)
  Waiting XID : {255785249, U} , APP, SELECT * FROM action_log WHERE 
username=? AND action=? AND seed=? FOR UPDATE

. The selected victim is XID : 255785240.

Users can register to carry out particular activities, and there is an 
action log which is updated about once a minute while an activity is 
in progress. The deadlock happened when I tried to delete an activity 
and the associated registrations.


The "DELETE FROM active_list" is fired by the following trigger:

CREATE TRIGGER delete_user
  AFTER DELETE ON registrations
  REFERENCING OLD AS del
  FOR EACH ROW MODE DB2SQL
  DELETE FROM active_list
    WHERE username=del.username
    AND   username NOT IN (SELECT username FROM current_list)

I am trying to understand why there is a table lock on the action log 
(updating this is much more important and urgent than purging inactive 
users!). I am not clear what U, X, IS and IX are -- uodate, exclusive, 
shared something, exclusive something?


And of course I'd like to prevent it happening again. Would changing 
the isolation level for one of the queries perhaps help? Any ideas 
gratefully received!


Thanks,

Re: Help needed to understand deadlock

[Rick Hillegas]

The following information may be helpful:

o Developer Guide material on Derby locking: 
https://db.apache.org/derby/docs/10.17/devguide/cdevconcepts30291.html


o Reference Guide material on the SYSCS_DIAG.LOCK_TABLE diagnostic 
table: 
https://db.apache.org/derby/docs/10.17/ref/rrefsyscsdiaglocktable.html


o A wiki page on debugging locking problems: 
https://cwiki.apache.org/confluence/display/DERBY/LockDebugging



On 2/2/24 11:46 AM, John English wrote:

My system recently reported the following:

java.sql.SQLTransactionRollbackException: A lock could not be obtained 
due to a deadlock, cycle of locks and waiters is:

Lock : ROW, ACTION_LOG, (2,4800)
  Waiting XID : {255785240, U} , APP, SELECT * FROM action_log WHERE 
status='A' AND endtime>=CURRENT_TIMESTAMP FOR UPDATE

  Granted XID : {255785232, U}
Lock : TABLE, REGISTRATIONS, Tablelock
  Waiting XID : {255785232, IS} , APP, SELECT * FROM registrations 
WHERE activity=?

  Granted XID : {255785263, IX}
Lock : TABLE, ACTION_LOG, Tablelock
  Waiting XID : {255785263, X} , APP, DELETE FROM active_list
    WHERE username=CAST 
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(1) 
AS VARCHAR(15))

    AND   username NOT IN (SELECT username FROM current_list)
  Granted XID : {255785232, IX} , {255785233, IX} , {255785236, IX} , 
{255785240, IX} , {255785247, IX} , {255785249, IX}

Lock : ROW, ACTION_LOG, (2,4800)
  Waiting XID : {255785249, U} , APP, SELECT * FROM action_log WHERE 
username=? AND action=? AND seed=? FOR UPDATE

. The selected victim is XID : 255785240.

Users can register to carry out particular activities, and there is an 
action log which is updated about once a minute while an activity is 
in progress. The deadlock happened when I tried to delete an activity 
and the associated registrations.


The "DELETE FROM active_list" is fired by the following trigger:

CREATE TRIGGER delete_user
  AFTER DELETE ON registrations
  REFERENCING OLD AS del
  FOR EACH ROW MODE DB2SQL
  DELETE FROM active_list
    WHERE username=del.username
    AND   username NOT IN (SELECT username FROM current_list)

I am trying to understand why there is a table lock on the action log 
(updating this is much more important and urgent than purging inactive 
users!). I am not clear what U, X, IS and IX are -- uodate, exclusive, 
shared something, exclusive something?


And of course I'd like to prevent it happening again. Would changing 
the isolation level for one of the queries perhaps help? Any ideas 
gratefully received!


Thanks,

Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime

[Rick Hillegas]

On 12/3/23 11:34 AM, Steven Saunders wrote:

Hi Rick,

Can you confirm or not that Before Update and Before Insert triggers can or
cannot be used to modify column values on a table?


A trigger fires a triggeredStatement. The triggeredStatement can modify 
columns in a table.


The triggeredStatement cannot modify a row which has not been INSERTed 
yet. So I do not see how a BEFORE INSERT trigger can solve your problem.


Similarly, if the triggeredStatement UPDATEs a row in the triggering 
table, then you will run the risk of infinite recursion--regardless of 
whether the UPDATE trigger is a BEFORE or AFTER trigger.




The answer to this question will help me understand the limitations in
an attempt to migrate the software as-is codewise to work with Derby.

The only way to get it to work with Derby if still desired might be to move
the logic into the code vs. relying on the DB to keep the data as needed.
It was done the way it is because it was available in all the target DBMSs
and the DB handling the data in the correct state being centralized kept
data and behavior consistent over any modifications inside or outside the
scope of the software.

We are also looking at HyperSQL to fill this need for a lighter weight,
transportable file based DBMS.  So far it appears to work with Before
Update and Insert they way needed and as the other working DBMS options
(again Oracle DB, SQL Server, DB2 LUW and DB2 zOS).   MySQL is another DBMS
that might be evaluated but not for this particular need.

Moving to using Procedures for rights seems off the path of feasibly
solving the need to intercepting DML statements to control column values
the application needs.

Thanks for trying to help, it is very much appreciated,
Steve

On Fri, Dec 1, 2023 at 1:42 PM Rick Hillegas 
wrote:


Is there some reason that you have to solve this problem with triggers?

An alternative solution would be to perform your integrity checks in a
database procedure which runs with DEFINERS rights and to restrict
UPDATE privilege on the table to the table owner.

On 12/1/23 10:15 AM, Steven Saunders wrote:

Hi Rick,

I guess the first question of most importance is:
  Can we use Before Update or Before Insert triggers to intercept and
modify column values (timestamps or otherwise)?

The over simplified example was to show that the before triggers were not
functioning as expected so attempts as solutions are not
necessarily solving the real schema issues found.

I have now done some experimenting with HypberSQL DB and it appears to
handle the Before triggers we need well so far.  We may have to use that
instead if we can't use Before triggers the way we need in DerbyDB.

Thanks for your help,
Steve



On Wed, Nov 29, 2023 at 12:35 PM Rick Hillegas 
wrote:


You could replace the INSERT trigger with a generated column. I don't

see

how to eliminate the UPDATE trigger and preserve the behavior you want.

Here's how to eliminate the INSERT trigger. First make the following

class

visible on the JVM's classpath:

import java.sql.Timestamp;

public class TimeFunctions

{

  public static Timestamp currentTimestamp() { return new

Timestamp(System.currentTimeMillis()); }

}


Then declare the following user-defined function. Note that you have to
lie and say that the function is deterministic:

CREATE FUNCTION currentTimestamp() RETURNS TIMESTAMP

LANGUAGE JAVA

DETERMINISTIC

PARAMETER STYLE JAVA

NO SQL

EXTERNAL NAME 'TimeFunctions.currentTimestamp';


Then declare your table with a generated column. No INSERT trigger

should

be needed:

CREATE TABLE TEST1

(

ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 1,

INCREMENT BY 1 ) PRIMARY KEY,

UNIQUE_ID VARCHAR (47) NOT NULL ,

CREATETIME TIMESTAMP GENERATED ALWAYS AS (currentTimestamp()),

MODIFYTIME TIMESTAMP,

ENDTIME TIMESTAMP ,

STATUS NUMERIC (10) WITH DEFAULT 0

);



On 11/29/23 7:44 AM, Steven Saunders wrote:

Hi Rick,

Thanks for the alternative, it looks like you switched from Before

Insert

and Before Update to After Insert and After Update, respectfully.

That will add multiple updates for one Insert or Update inturn causing
unwanted triggers to fire in a slightly more complex schema I am trying

to

port from DB2 z/OS, DB2 LUW, Oracle DB and SQL Server to Derby.  I have
actually tried this switch on the target schema which caused unwanted
trigger firing and worse case scenario an exception for trigger depth.

Is it true then that we are not able to use any Before Update or Before
Insert triggers to intercept Inserts and Updates and affect column

values

such as adding timestamps or other data type values?   That would be
unfortunate if it were the case as there would be no opportunity to
manipulate the data before it is put in a table unless the only way is
calling via java classes in the Before triggers.

Thanks very much,
-Steve

On Tue, Nov 28, 2023 at 6:33 PM Rick Hillegas 



wrote:


Hi Steven,

Derby hews fairly closely

Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime

[Rick Hillegas]

Is there some reason that you have to solve this problem with triggers?

An alternative solution would be to perform your integrity checks in a 
database procedure which runs with DEFINERS rights and to restrict 
UPDATE privilege on the table to the table owner.


On 12/1/23 10:15 AM, Steven Saunders wrote:

Hi Rick,

I guess the first question of most importance is:
 Can we use Before Update or Before Insert triggers to intercept and
modify column values (timestamps or otherwise)?

The over simplified example was to show that the before triggers were not
functioning as expected so attempts as solutions are not
necessarily solving the real schema issues found.

I have now done some experimenting with HypberSQL DB and it appears to
handle the Before triggers we need well so far.  We may have to use that
instead if we can't use Before triggers the way we need in DerbyDB.

Thanks for your help,
Steve



On Wed, Nov 29, 2023 at 12:35 PM Rick Hillegas 
wrote:


You could replace the INSERT trigger with a generated column. I don't see
how to eliminate the UPDATE trigger and preserve the behavior you want.

Here's how to eliminate the INSERT trigger. First make the following class
visible on the JVM's classpath:

import java.sql.Timestamp;

public class TimeFunctions

{

 public static Timestamp currentTimestamp() { return new 
Timestamp(System.currentTimeMillis()); }

}


Then declare the following user-defined function. Note that you have to
lie and say that the function is deterministic:

CREATE FUNCTION currentTimestamp() RETURNS TIMESTAMP

LANGUAGE JAVA

DETERMINISTIC

PARAMETER STYLE JAVA

NO SQL

EXTERNAL NAME 'TimeFunctions.currentTimestamp';


Then declare your table with a generated column. No INSERT trigger should
be needed:

CREATE TABLE TEST1

(

   ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT 
BY 1 ) PRIMARY KEY,

   UNIQUE_ID VARCHAR (47) NOT NULL ,

   CREATETIME TIMESTAMP GENERATED ALWAYS AS (currentTimestamp()),

   MODIFYTIME TIMESTAMP,

   ENDTIME TIMESTAMP ,

   STATUS NUMERIC (10) WITH DEFAULT 0

);



On 11/29/23 7:44 AM, Steven Saunders wrote:

Hi Rick,

Thanks for the alternative, it looks like you switched from Before Insert
and Before Update to After Insert and After Update, respectfully.

That will add multiple updates for one Insert or Update inturn causing
unwanted triggers to fire in a slightly more complex schema I am trying to
port from DB2 z/OS, DB2 LUW, Oracle DB and SQL Server to Derby.  I have
actually tried this switch on the target schema which caused unwanted
trigger firing and worse case scenario an exception for trigger depth.

Is it true then that we are not able to use any Before Update or Before
Insert triggers to intercept Inserts and Updates and affect column values
such as adding timestamps or other data type values?   That would be
unfortunate if it were the case as there would be no opportunity to
manipulate the data before it is put in a table unless the only way is
calling via java classes in the Before triggers.

Thanks very much,
-Steve

On Tue, Nov 28, 2023 at 6:33 PM Rick Hillegas  

wrote:


Hi Steven,

Derby hews fairly closely to SQL Standard syntax. Your triggers look wrong
to me. Your triggered SQL statements are VALUES statements, which simply
manufacture some values and throw them into the void. I think that is why
you had to include MODE DB2SQL in your syntax. I don't think that MODE
DB2SQL causes Derby to actually behave like (some dialect of) DB2 in this
case. It just allows the syntax to compile both on Derby and on the
originating DB2 system.

See if the following alternative syntax gives you what you need:

CONNECT 'jdbc:derby:memory:db;create=true';

CREATE TABLE TEST1 ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS

IDENTITY(START WITH 1, INCREMENT BY 1 ) , UNIQUE_ID VARCHAR (47) NOT NULL ,

CREATETIME TIMESTAMP WITH DEFAULT CURRENT_TIMESTAMP, MODIFYTIME TIMESTAMP

, ENDTIME TIMESTAMP , STATUS NUMERIC (10) WITH DEFAULT 0 ); ALTER TABLE

TEST1 ADD CONSTRAINT TEST1_PK PRIMARY KEY ( ID ) ;

CREATE TRIGGER TEST1_BINS_TRG1 AFTER INSERT ON TEST1

REFERENCING NEW AS NEW FOR EACH ROW

   UPDATE TEST1

   SET CREATETIME = CURRENT_TIMESTAMP

   WHERE ID = NEW.ID;

CREATE TRIGGER TEST1_BUPD_TRG1

AFTER UPDATE OF STATUS ON TEST1

REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

WHEN (NEW.STATUS >= 0 AND OLD.STATUS <> 9 )

   UPDATE TEST1

 SET STATUS = 9 , MODIFYTIME = CURRENT_TIMESTAMP, ENDTIME = 
CURRENT_TIMESTAMP

   WHERE ID = OLD.ID;

---

INSERT INTO TEST1 (UNIQUE_ID) VALUES ('1');

SELECT * FROM TEST1;

UPDATE TEST1 SET STATUS=1 WHERE UNIQUE_ID='1';

SELECT * FROM TEST1;

Hope this helps,

-Rick

Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime

[Rick Hillegas]

You could replace the INSERT trigger with a generated column. I don't 
see how to eliminate the UPDATE trigger and preserve the behavior you want.


Here's how to eliminate the INSERT trigger. First make the following 
class visible on the JVM's classpath:


import java.sql.Timestamp;

public class TimeFunctions

{

    public static Timestamp currentTimestamp() { return new 
Timestamp(System.currentTimeMillis()); }

}


Then declare the following user-defined function. Note that you have to 
lie and say that the function is deterministic:


CREATE FUNCTION currentTimestamp() RETURNS TIMESTAMP

LANGUAGE JAVA

DETERMINISTIC

PARAMETER STYLE JAVA

NO SQL

EXTERNAL NAME 'TimeFunctions.currentTimestamp';


Then declare your table with a generated column. No INSERT trigger 
should be needed:


CREATE TABLE TEST1

(

  ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT 
BY 1 ) PRIMARY KEY,

  UNIQUE_ID VARCHAR (47) NOT NULL ,

  CREATETIME TIMESTAMP GENERATED ALWAYS AS (currentTimestamp()),

  MODIFYTIME TIMESTAMP,

  ENDTIME TIMESTAMP ,

  STATUS NUMERIC (10) WITH DEFAULT 0

);



On 11/29/23 7:44 AM, Steven Saunders wrote:

Hi Rick,

Thanks for the alternative, it looks like you switched from Before Insert
and Before Update to After Insert and After Update, respectfully.

That will add multiple updates for one Insert or Update inturn causing
unwanted triggers to fire in a slightly more complex schema I am trying to
port from DB2 z/OS, DB2 LUW, Oracle DB and SQL Server to Derby.  I have
actually tried this switch on the target schema which caused unwanted
trigger firing and worse case scenario an exception for trigger depth.

Is it true then that we are not able to use any Before Update or Before
Insert triggers to intercept Inserts and Updates and affect column values
such as adding timestamps or other data type values?   That would be
unfortunate if it were the case as there would be no opportunity to
manipulate the data before it is put in a table unless the only way is
calling via java classes in the Before triggers.

Thanks very much,
-Steve

On Tue, Nov 28, 2023 at 6:33 PM Rick Hillegas
wrote:


Hi Steven,

Derby hews fairly closely to SQL Standard syntax. Your triggers look wrong
to me. Your triggered SQL statements are VALUES statements, which simply
manufacture some values and throw them into the void. I think that is why
you had to include MODE DB2SQL in your syntax. I don't think that MODE
DB2SQL causes Derby to actually behave like (some dialect of) DB2 in this
case. It just allows the syntax to compile both on Derby and on the
originating DB2 system.

See if the following alternative syntax gives you what you need:

CONNECT 'jdbc:derby:memory:db;create=true';

CREATE TABLE TEST1 ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS

IDENTITY(START WITH 1, INCREMENT BY 1 ) , UNIQUE_ID VARCHAR (47) NOT NULL ,

CREATETIME TIMESTAMP WITH DEFAULT CURRENT_TIMESTAMP, MODIFYTIME TIMESTAMP

, ENDTIME TIMESTAMP , STATUS NUMERIC (10) WITH DEFAULT 0 ); ALTER TABLE

TEST1 ADD CONSTRAINT TEST1_PK PRIMARY KEY ( ID ) ;

CREATE TRIGGER TEST1_BINS_TRG1 AFTER INSERT ON TEST1

REFERENCING NEW AS NEW FOR EACH ROW

   UPDATE TEST1

   SET CREATETIME = CURRENT_TIMESTAMP

   WHERE ID = NEW.ID;

CREATE TRIGGER TEST1_BUPD_TRG1

AFTER UPDATE OF STATUS ON TEST1

REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

WHEN (NEW.STATUS >= 0 AND OLD.STATUS <> 9 )

   UPDATE TEST1

 SET STATUS = 9 , MODIFYTIME = CURRENT_TIMESTAMP, ENDTIME = 
CURRENT_TIMESTAMP

   WHERE ID = OLD.ID;

---

INSERT INTO TEST1 (UNIQUE_ID) VALUES ('1');

SELECT * FROM TEST1;

UPDATE TEST1 SET STATUS=1 WHERE UNIQUE_ID='1';

SELECT * FROM TEST1;

Hope this helps,

-Rick

Re: Apache Derby CURRENT_TIMESTAMP not working in BEFORE INSERT and BEFORE UPDATE triggers at runtime

[Rick Hillegas]

Hi Steven,

Derby hews fairly closely to SQL Standard syntax. Your triggers look 
wrong to me. Your triggered SQL statements are VALUES statements, which 
simply manufacture some values and throw them into the void. I think 
that is why you had to include MODE DB2SQL in your syntax. I don't think 
that MODE DB2SQL causes Derby to actually behave like (some dialect of) 
DB2 in this case. It just allows the syntax to compile both on Derby and 
on the originating DB2 system.


See if the following alternative syntax gives you what you need:

CONNECT 'jdbc:derby:memory:db;create=true';

CREATE TABLE TEST1 ( ID BIGINT NOT NULL GENERATED BY DEFAULT AS

IDENTITY(START WITH 1, INCREMENT BY 1 ) , UNIQUE_ID VARCHAR (47) NOT NULL ,

CREATETIME TIMESTAMP WITH DEFAULT CURRENT_TIMESTAMP, MODIFYTIME TIMESTAMP

, ENDTIME TIMESTAMP , STATUS NUMERIC (10) WITH DEFAULT 0 ); ALTER TABLE

TEST1 ADD CONSTRAINT TEST1_PK PRIMARY KEY ( ID ) ;

CREATE TRIGGER TEST1_BINS_TRG1 AFTER INSERT ON TEST1

REFERENCING NEW AS NEW FOR EACH ROW

  UPDATE TEST1

  SET CREATETIME = CURRENT_TIMESTAMP

  WHERE ID = NEW.ID;

CREATE TRIGGER TEST1_BUPD_TRG1

AFTER UPDATE OF STATUS ON TEST1

REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

WHEN (NEW.STATUS >= 0 AND OLD.STATUS <> 9 )

  UPDATE TEST1

    SET STATUS = 9 , MODIFYTIME = CURRENT_TIMESTAMP, ENDTIME = CURRENT_TIMESTAMP

  WHERE ID = OLD.ID;

---

INSERT INTO TEST1 (UNIQUE_ID) VALUES ('1');

SELECT * FROM TEST1;

UPDATE TEST1 SET STATUS=1 WHERE UNIQUE_ID='1';

SELECT * FROM TEST1;

Hope this helps,

-Rick

[ANNOUNCE] Apache Derby 10.17.1.0 released

[Rick Hillegas]

The Apache Derby project is pleased to announce feature release 10.17.1.0.

Apache Derby is a sub-project of the Apache DB project. Derby is a pure 
Java relational database engine which conforms to the ISO/ANSI SQL and 
JDBC standards. Derby aims to be easy for developers and end-users to 
work with.


The chief feature of this release is the removal of calls to deprecated 
Java APIs. Derby 10.17.1.0 has been built and tested on the Java SE 21 
platform, and will run only on Java SE 21 and newer Java platforms. 
Derby 10.17.1.0 cannot be used with older Java platforms. Please see 
http://db.apache.org/derby/derby_downloads.html for more details 
regarding which Derby releases are compatible with which Java platforms.


In addition, Derby 10.17.1.0 fixes a flaw in Derby's LDAP authentication 
logic (CVE-2022-46337).


Derby 10.17.1.0 contains other bug and documentation fixes. The release 
can be obtained from the Derby download site:


http://db.apache.org/derby/derby_downloads.html.

Please try out this new release.

Re: Doubts about encoding, using the database across different OS/encoding

[Rick Hillegas]

Character data is always stored and retrieved with UTF8 encoding. If you 
move your database from a Windows to a Linux platform, the character 
encoding will remain UTF8 and everything should work. This assumes that 
you are using character-based methods 
(java.sql.PreparedStatement.setString()/setClob()) to store data and 
character-based methods (java.sql.ResultSet.getString()/getClob()) to 
retrieve it.


Hope this helps,
-Rick

On 10/17/23 10:18 AM, fed wrote:

Hi,

I have some doubts about encoding, I am not an expert about encodings so I
prefer to ask.

Let's say I create and use the database on a Windows pc (so encoding is
like Windows-1252 but not UTF-8, and the application is running with
default OS encoding), I store some text data in tables etc. then I move and
use the database on a linux pc that has encoding UTF-8 and the application
will still run with default OS encoding.

Can this cause problems with text data?
Can the data stored when I was using the database on Windows read wrong on
the Linux system (and vice versa if I move the db again) ?

Thanks for the help
- fed

Re: disable creations of new databases when running the network server

[Rick Hillegas]

Yes, that's what I meant. The valid users are the users declared in your 
network-wide authentication scheme, which in your case is NATIVE 
authentication using a credentials database.


On 9/29/23 12:30 PM, fed wrote:

Hi Rick,

thanks for the answer, I know it seems an obvious question but what do you
mean with "valid user" ?
I try to explain: my setup is this one (I asked help for it time ago in the
mailing list):

a dbs/ dir (that is derby.system.home) where I have all of my db (db1, db2,
db3 etc) every one with their own native authentication and a credentials
db used by the network server, with derby,properties that is

derby.authentication.provider=NATIVE:credentials

the valid users that can create a new db are the one defined in the
credentials db, right?

Thanks for the help.
- fed



On Sat, 16 Sept 2023 at 20:04, Rick Hillegas 
wrote:


You are correct. Any valid user can create as many databases as they
want, provided that the databases are created in a part of the file
system which is write-accessible to the engine jar and to the account
which runs the server. There is no way to prevent a valid user from
creating databases.

On 9/15/23 11:51 PM, fed wrote:

Hi,

My doubt is that, if I am not wrong, every user that can connect to the
network server can create a new database and so indirectly a new

directory

in the same places where the user that runs the network server have write
permissions.
I would prefer to create the database not directly on the network server
but with the embedded driver and then later make it available on the
network server.
Maybe it is possible to limit this behaviour via the security manager

but I

don't think this can change a lot of the behaviour.

Is it possible to disable/prevent/limit this?

Thanks for the help
- fed

Re: Writing hex integer constants?

[Rick Hillegas]

Derby INTEGER constants are signed decimal numbers, that is, legal 
inputs to the java.lang.Integer.valueOf(String s) method. The radix is 
always 10. Hex/octal constants won't work, as you've discovered.


On 9/24/23 9:05 AM, John English wrote:

Is there a way to use hex constants as integers in Derby?

I tried using X'7FFF' and I got "Comparisons between 'SMALLINT' and 
'CHAR () FOR BIT DATA' are not supported".
I tried using CAST(X'7FFF' AS INTEGER) and got "Cannot convert types 
CHAR () FOR BIT DATA to INTEGER".


I can of course use 32767 in this case, but for other hex values it's 
a real pain to work out what it corresponds to. Or I could define a 
function to convert a string hex value to an integer, but that also 
seems like overkill.


Thanks,

Re: disable creations of new databases when running the network server

[Rick Hillegas]

You are correct. Any valid user can create as many databases as they 
want, provided that the databases are created in a part of the file 
system which is write-accessible to the engine jar and to the account 
which runs the server. There is no way to prevent a valid user from 
creating databases.


On 9/15/23 11:51 PM, fed wrote:

Hi,

My doubt is that, if I am not wrong, every user that can connect to the
network server can create a new database and so indirectly a new directory
in the same places where the user that runs the network server have write
permissions.
I would prefer to create the database not directly on the network server
but with the embedded driver and then later make it available on the
network server.
Maybe it is possible to limit this behaviour via the security manager but I
don't think this can change a lot of the behaviour.

Is it possible to disable/prevent/limit this?

Thanks for the help
- fed

Re: Doubts about online backups using freeze / unfreeze

[Rick Hillegas]

I never use the freeze and unfreeze commands but your understanding of 
them sounds correct and is consistent with the following ij experiment:


ij version 10.17

ij> CONNECT 'jdbc:derby:zdb;create=true';

ij> CREATE TABLE t(a INT);

0 rows inserted/updated/deleted

ij> INSERT INTO t VALUES (1);

1 row inserted/updated/deleted

ij> CALL SYSCS_UTIL.SYSCS_FREEZE_DATABASE();

0 rows inserted/updated/deleted

ij> SELECT * FROM t;

A

---

1

1 row selected

ij> DISCONNECT;

ij> CONNECT 'jdbc:derby:zdb';

ij> SELECT * FROM t;

A

---

1

1 row selected

ij> CALL SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE();

0 rows inserted/updated/deleted

ij> INSERT INTO t VALUES (2);

1 row inserted/updated/deleted

ij> SELECT * FROM t;

A

---

1

2

2 rows selected

Hope this helps,
-Rick

On 8/7/23 2:55 PM, fed wrote:

Hi,

I have a derby network server running with some databases connected and I
want to backup the databases directory with my backup utility.
My idea is to connect to every database in the network server and call the
freeze procedure, execute the backup of the databases directories and then
call the unfreeze procedure on every db, but I have some doubts.

Q1:
I am going for every database to get a connection, execute

CALL SYSCS_UTIL.SYSCS_FREEZE_DATABASE()

and then close the connection. After this I execute the backup with my
backup utility and then later after the backup is finished for every
database get a new connection and execute:

CALL SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE()   , is this correct?

>From the example in the documentation it seems that I have to use the same
connection to call the two stored procedures, but this is not required,
right?

public static void backUpDatabaseWithFreeze(Connection conn)
throws SQLException {
 Statement s = conn.createStatement();
 s.executeUpdate(
 "CALL SYSCS_UTIL.SYSCS_FREEZE_DATABASE()");
 *//copy the database directory during this interval*
 s.executeUpdate(
 "CALL SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE()");
 s.close();
}


Q2:
If I stop the network server while the database is in "freeze" state when I
restart it, it is still in "freeze" state and I have to call unfreeze on it?
My concern is that It is not possible that I get stuck in the "freeze"
state of the database, I can always "unfreeze" it, right?

Q3:
Last question, sorry for lots of questions: just to confirm even if in a
freeze state I can acquire a connection and execute a read operation
(select) on the database even in a transaction, right?

Thanks for the help
- fed

Re: Custom sorting in ORDER BY

[Rick Hillegas]

I can think of two solutions to your problem:

1) Write a custom Java collator and install it as the default sort order 
for all string data in your database. See the following sections in the 
Derby Developer's Guide: 
https://db.apache.org/derby/docs/10.16/devguide/cdevcollation.html and 
https://db.apache.org/derby/docs/10.16/devguide/tdevdvlpcustomcollation.html#tdevdvlpcustomcollation


2) The second approach, as you suggested, is to create a Derby function 
which recodes string types into some other type like VARBINARY. The 
function would need to divide the string into its alphabetic and numeric 
components and then reassemble them in a binary order which sorts the 
way you want. Your query would then look like:


  SELECT ... ORDER BY (recodingFunction(alphanumericColumn))

There are a lot of fiddly subtleties to both approaches. I can't give 
more advice because I haven't tackled this problem myself.


Hope this helps,
-Rick


On 2/2/23 12:35 AM, John English wrote:
I would like to be able to specify an ORDER BY which will give me the 
same sort of ordering as produced by my file browser, where embedded 
numbers in a string with the same prefix are ordered numerically.


For example: if I have strings 'x1','foo1','x2','foo10','foo5', a 
normal text sort will give 'foo1','foo10','foo5','x1','x2' (that is, 
'foo10' will come before 'foo5'). I would like to be able to produce 
the order 'foo1','foo5','foo10','x1','x2' instead.


I could do this in Java by defining a custom comparator, and then 
import this for use in Derby with CREATE FUNCTION but how would I then 
use it in an ORDER BY clause to give me the desired sort order?


Thanks for any tips,

Re: not getting proper database shutdown message in embedded mode

[Rick Hillegas]

Since it only happens with Derby 10.15 onward, the problem is probably 
caused by a misconfigured classpath: Derby 10.15 requires more jar 
files. I would take this up with the Netbeans community and point them 
at the release notes for 10.15.1.3: 
https://db.apache.org/derby/releases/release-10.15.1.3.html


On 12/21/22 9:29 PM, Orange Trees wrote:

Hello,

Any further suggestions please where and what should I check?

Regards,
Orange

From: Orange Trees 
Sent: 27 November 2022 12:35
To: Derby Discussion 
Subject: Re: not getting proper database shutdown message in embedded mode

Hi,

I investigated this even further. Derby DB version 10.14.2.0 is not having this 
problem. So in the same environment (LinuxMint 19, Java 17, NetBeans14) 
problems are at least with Derby DB 10.15.2.0 and 10.16.1.1.

Thus I still unsure where exactly the problem is, is it NetBeans or Derby DB? 
Any help or hints appreciated.

Anyway, as a workaround, I will stick with 10.14.2.0 for now until the issue is 
resolved.

Regards,
Orange

Re: Network server with different java (or derby) version between client and server

[Rick Hillegas]

Some responses inline...

On 12/1/22 4:15 AM, fed wrote:

Hi,

As you suggested, the issue is the security manager, to be honest I am used
to java security manager, I read and tested a bit to understand how it
works.
My initial setup was server service in a dir, let’s say /dir1 and database
in another dir let’s say /dir2 so the dirs are distinct to each other.

Considering version 10.15.2.0 running the server without security manager
works:

java -jar derbyrun.jar server -noSecurityManager start

but reading from documentation I know it is not recommended and advisable
so I want to use the security manager.

So then I put my db dir inside the lib dir where derbyrun.jar and other
jars are, so something like lib/db/mydb and running it with
java -jar derbyrun.jar server start
and I can access the db even using relative path on jdbc url, db/mydb.

I am not used to derby in server mode, I read the documentation but sorry
it’s not all clear to me how to use it.
So from my test I suppose the recommended setup is to create a db dir
inside lib and put all the dbs inside it ? lib/db/db1, lib/db/db2 etc ?
right?
Typically, people separate code-bearing directories from data-bearing 
directories. This makes it easier to upgrade to a new version of Derby 
since the database is not located with the old version of Derby code. 
Something like the following:


/Users/me/derbyInstallation/

/Users/me/derbyInstallation/derbyVersions/10.15.2.0

/Users/me/derbyInstallation/derbyHome

/Users/me/derbyInstallation/derbyHome/db1

/Users/me/derbyInstallation/derbyHome/db2




Just as note I tested again with 10.12.1.1 and I found that forcing the
security policy with the server template one (I suppose the default
behaviour is changed with the newer version) it behaves the same like
10.15.2.0, I used:
java
-Djava.security.policy=/tmp/db-derby-10.12.1.1-bin/demo/templates/server.policy
-jar derbyrun server start
Note that the supplied policies are just templates. You need to edit 
them in order to use them. See 
https://db.apache.org/derby/docs/10.15/security/csecjavasecurity.html 
That is, at a minimum, you need to copy the template server policy (for 
10.15.2.0 it's demo/templates/serverTemplate.policy) to some other 
location and edit lines like the following...


grant codeBase "${derby.install.url}derbyshared.jar"


...replacing the ${derby.install.url} symbol with the url handle of your 
Derby code library (file:///Users/me/derbyInstallation/10.15.2.0/) so 
that the lines look like this:


grant codeBase"file:///Users/me/derbyInstallation/10.15.2.0/derbyshared.jar"

Hope this helps,

-Rick



Thanks for the help.

On Fri, 25 Nov 2022 at 21:02, Rick Hillegas  wrote:


Check that your 10.15 classpath is correct. You need a couple more jar
files compared to previous releases. Your 10.15 server classpath must
contain the following jars:

derby.jar
derbyshared.jar
derbytools.jar
derbynet.jar

See

https://db.apache.org/derby/docs/10.15/adminguide/tadminappschangingyourclasspath.html
and

https://db.apache.org/derby/docs/10.15/publishedapi/org.apache.derby.server/module-summary.html


On 11/25/22 9:51 AM, Rick Hillegas wrote:

This indicates that the server is running with a Java SecurityManager
and that the policy file does not grant read permission on that
file--and probably all files in the database directory.

On 11/25/22 12:30 AM, fed wrote:

Hi,

testing with 10.15.2.0 from derby.log, server side, it complains about a
read permission on service.properties, some part of the file:

java.sql.SQLException: Impossibile avviare il database
'/home/user/db/' con
il caricatore di classi
jdk.internal.loader.ClassLoaders$AppClassLoader@277050dc. Per i
dettagli,
vedere l'eccezione successiva.
...
Caused by: java.security.AccessControlException: access denied
("java.io.FilePermission" "/home/user/db/service.properties" "read")
...
ERROR XBM0C: Privilegio mancante per l'operazione 'exists' sul file
'service.properties': access denied ("java.io.FilePermission"
"/home/user/db/service.properties" "read")


There are several errors like these ones but I have read permission
on this
file.
The user that starts the server is the same that owns the file, the
permissions on the file are 664.

As I said, same setup but using 10.12.1.1 for the server, I have no
problems.

Best Regards
-fed

On Thu, 24 Nov 2022 at 19:52, Rick Hillegas
wrote:


The SQLState indicates that the server was not able to boot the
database. Look in the server-side derby.log to see if there is a
detailed error message describing why the boot failed.

On 11/23/22 4:42 PM, fed wrote:

Hi,

Sorry for the late answer but I lost your reply.

Two tests:

I have a database updated to version 10.12.1.1, the server is running

with

the 10.12.1.1 too and the client is using 10.12.1.1 too, the
connection

is

OK, I can use this setup.

But another test:
still the same database up

Re: Network server with different java (or derby) version between client and server

[Rick Hillegas]

Check that your 10.15 classpath is correct. You need a couple more jar 
files compared to previous releases. Your 10.15 server classpath must 
contain the following jars:


  derby.jar
  derbyshared.jar
  derbytools.jar
  derbynet.jar

See 
https://db.apache.org/derby/docs/10.15/adminguide/tadminappschangingyourclasspath.html 
and 
https://db.apache.org/derby/docs/10.15/publishedapi/org.apache.derby.server/module-summary.html



On 11/25/22 9:51 AM, Rick Hillegas wrote:
This indicates that the server is running with a Java SecurityManager 
and that the policy file does not grant read permission on that 
file--and probably all files in the database directory.


On 11/25/22 12:30 AM, fed wrote:

Hi,

testing with 10.15.2.0 from derby.log, server side, it complains about a
read permission on service.properties, some part of the file:

java.sql.SQLException: Impossibile avviare il database 
'/home/user/db/' con

il caricatore di classi
jdk.internal.loader.ClassLoaders$AppClassLoader@277050dc. Per i 
dettagli,

vedere l'eccezione successiva.
...
Caused by: java.security.AccessControlException: access denied
("java.io.FilePermission" "/home/user/db/service.properties" "read")
...
ERROR XBM0C: Privilegio mancante per l'operazione 'exists' sul file
'service.properties': access denied ("java.io.FilePermission"
"/home/user/db/service.properties" "read")


There are several errors like these ones but I have read permission 
on this

file.
The user that starts the server is the same that owns the file, the
permissions on the file are 664.

As I said, same setup but using 10.12.1.1 for the server, I have no
problems.

Best Regards
-fed

On Thu, 24 Nov 2022 at 19:52, Rick Hillegas  
wrote:



The SQLState indicates that the server was not able to boot the
database. Look in the server-side derby.log to see if there is a
detailed error message describing why the boot failed.

On 11/23/22 4:42 PM, fed wrote:

Hi,

Sorry for the late answer but I lost your reply.

Two tests:

I have a database updated to version 10.12.1.1, the server is running

with
the 10.12.1.1 too and the client is using 10.12.1.1 too, the 
connection

is

OK, I can use this setup.

But another test:
still the same database updated to version 10.12.1.1, the server is

running

10.15.2.0 so a newer version and the client is using 10.12.1.1: I have
problems in this case the client can't connect to the database with 
this

error:

Caused by: org.apache.derby.client.am.SqlException: DERBY SQL error:
ERRORCODE: 4, SQLSTATE: XJ040, SQLERRMC: Impossibile avviare il
database '/home/user/some_db_path/' con il caricatore di classi
jdk.internal.loader.ClassLoaders$AppClassLoader@277050dc. Per i

dettagli,

vedere l'eccezione successiva.::SQLSTATE: XBM0C

Thanks for the help



On Sun, 13 Nov 2022 at 15:26, Bryan Pendleton <

bpendleton.de...@gmail.com>

wrote:


I'm not aware of client-server version incompatibilities. Have you
done any experiments with different versions?

thanks,

bryan

On Thu, Nov 10, 2022 at 4:16 AM fed  wrote:

Hi,

using derby with network server setup is there any problem if the

server

and the client are running on different java versions?
Still on this, considering the database created/updated with the 
apache
derby version that the client uses, is there any problem if the 
server

will

use a newer version of apache derby?

Thanks for the help

-fed

Re: not getting proper database shutdown message in embedded mode

[Rick Hillegas]

This indicates that Derby cannot find the localized messages for your 
environment. Only the English messages are bundled inside derby.jar. 
What happens when you run the following program:


import java.sql.*;

import java.util.*;

public class Z

{

    public static void main(String... args) throws Exception

    {

    println("Default locale = " + Locale.getDefault());

    Connection conn = 
DriverManager.getConnection("jdbc:derby:memory:db;create=true");

    conn.prepareStatement("CREATE TABLE t (a INT, b INT)").execute();

    try

    {

    DriverManager.getConnection("jdbc:derby:memory:db;shutdown=true");

    }

    catch (Exception ex)

    {

    println(ex.getMessage());

    }

    }

    private static void println(String text) { System.out.println(text); }

}



I see the following output when I run a script which prints out the java 
version and the classpath and then runs the program:


openjdk version "17" 2021-09-14
OpenJDK Runtime Environment (build 17+35-2724)
OpenJDK 64-Bit Server VM (build 17+35-2724, mixed mode, sharing)
CLASSPATH = 
/Users/rhillegas/derby/upgradeReleases/10.15.2.0/derby.jar:/Users/rhillegas/derby/upgradeReleases/10.15.2.0/derbyshared.jar:/Users/rhillegas/derby/upgradeReleases/10.15.2.0/derbytools.jar:/Users/rhillegas/src

Default locale = en_US
Database 'memory:db' shutdown.


On 11/25/22 4:33 AM, Orange Trees wrote:

Hello,

For some reason for my newly created database in embedded mode after issuing shutdown command 
getConnection("jdbc:derby:" +  + ";shutdown=true")  I'm not getting 
the message 'Database  shutdown.' (as it was for my previous projects) but instead I'm 
getting this message:

08006.D : [0] 

SQL State and error codes are inline (08006 and 45000). I tried with Derby 
versions 10.15.2.0 and 10.16.1.1. Also executing the included SimpleApp.java 
demo program gives the same result. For my project I'm including these jars:

derby.jar
derbyshared.jar
derbytools.jar

Also tried derbyoptionaltools.jar with no help.

Tried several times, deleting and recreating databases etc. Using Linux Mint 
with OpenJDK Runtime Environment Temurin-17.0.5+8 (build 17.0.5+8) (by the way 
in this environment my old databases return the correct/expected message)

What could be the issue?

Regards,
Orange

Re: Network server with different java (or derby) version between client and server

[Rick Hillegas]

This indicates that the server is running with a Java SecurityManager 
and that the policy file does not grant read permission on that 
file--and probably all files in the database directory.


On 11/25/22 12:30 AM, fed wrote:

Hi,

testing with 10.15.2.0 from derby.log, server side, it complains about a
read permission on service.properties, some part of the file:

java.sql.SQLException: Impossibile avviare il database '/home/user/db/' con
il caricatore di classi
jdk.internal.loader.ClassLoaders$AppClassLoader@277050dc. Per i dettagli,
vedere l'eccezione successiva.
...
Caused by: java.security.AccessControlException: access denied
("java.io.FilePermission" "/home/user/db/service.properties" "read")
...
ERROR XBM0C: Privilegio mancante per l'operazione 'exists' sul file
'service.properties': access denied ("java.io.FilePermission"
"/home/user/db/service.properties" "read")


There are several errors like these ones but I have read permission on this
file.
The user that starts the server is the same that owns the file, the
permissions on the file are 664.

As I said, same setup but using 10.12.1.1 for the server, I have no
problems.

Best Regards
-fed

On Thu, 24 Nov 2022 at 19:52, Rick Hillegas  wrote:


The SQLState indicates that the server was not able to boot the
database. Look in the server-side derby.log to see if there is a
detailed error message describing why the boot failed.

On 11/23/22 4:42 PM, fed wrote:

Hi,

Sorry for the late answer but I lost your reply.

Two tests:

I have a database updated to version 10.12.1.1, the server is running

with

the 10.12.1.1 too and the client is using 10.12.1.1 too, the connection

is

OK, I can use this setup.

But another test:
still the same database updated to version 10.12.1.1, the server is

running

10.15.2.0 so a newer version and the client is using 10.12.1.1: I have
problems in this case the client can't connect to the database with this
error:

Caused by: org.apache.derby.client.am.SqlException: DERBY SQL error:
ERRORCODE: 4, SQLSTATE: XJ040, SQLERRMC: Impossibile avviare il
database '/home/user/some_db_path/' con il caricatore di classi
jdk.internal.loader.ClassLoaders$AppClassLoader@277050dc. Per i

dettagli,

vedere l'eccezione successiva.::SQLSTATE: XBM0C

Thanks for the help



On Sun, 13 Nov 2022 at 15:26, Bryan Pendleton <

bpendleton.de...@gmail.com>

wrote:


I'm not aware of client-server version incompatibilities. Have you
done any experiments with different versions?

thanks,

bryan

On Thu, Nov 10, 2022 at 4:16 AM fed  wrote:

Hi,

using derby with network server setup is there any problem if the

server

and the client are running on different java versions?

Still on this, considering the database created/updated with the apache

derby version that the client uses, is there any problem if the server

will

use a newer version of apache derby?

Thanks for the help

-fed

Re: Network server with different java (or derby) version between client and server

[Rick Hillegas]

The SQLState indicates that the server was not able to boot the 
database. Look in the server-side derby.log to see if there is a 
detailed error message describing why the boot failed.


On 11/23/22 4:42 PM, fed wrote:

Hi,

Sorry for the late answer but I lost your reply.

Two tests:

I have a database updated to version 10.12.1.1, the server is running with
the 10.12.1.1 too and the client is using 10.12.1.1 too, the connection is
OK, I can use this setup.

But another test:
still the same database updated to version 10.12.1.1, the server is running
10.15.2.0 so a newer version and the client is using 10.12.1.1: I have
problems in this case the client can't connect to the database with this
error:

Caused by: org.apache.derby.client.am.SqlException: DERBY SQL error:
ERRORCODE: 4, SQLSTATE: XJ040, SQLERRMC: Impossibile avviare il
database '/home/user/some_db_path/' con il caricatore di classi
jdk.internal.loader.ClassLoaders$AppClassLoader@277050dc. Per i dettagli,
vedere l'eccezione successiva.::SQLSTATE: XBM0C

Thanks for the help



On Sun, 13 Nov 2022 at 15:26, Bryan Pendleton 
wrote:


I'm not aware of client-server version incompatibilities. Have you
done any experiments with different versions?

thanks,

bryan

On Thu, Nov 10, 2022 at 4:16 AM fed  wrote:

Hi,

using derby with network server setup is there any problem if the server

and the client are running on different java versions?

Still on this, considering the database created/updated with the apache

derby version that the client uses, is there any problem if the server will
use a newer version of apache derby?

Thanks for the help

-fed

Re: deadlock when running SYSCS_UTIL.SYSCS_COMPRESS_TABLE

[Rick Hillegas]

ge_metadata

2022-09-30T20:49:32,682Z ERROR [IO-/147.18.139.173:44710]
(o.a.q.s.u.ServerScopedRuntimeException) - Exception on enqueuing message
into message store25904045
java.sql.SQLTransactionRollbackException: A lock could not be obtained due
to a deadlock, cycle of locks and waiters is:
Lock : TABLE, QPID_MESSAGE_METADATA, Tablelock
   Waiting XID : {627716335, IX} , APP, INSERT INTO QPID_MESSAGE_METADATA(
message_id , meta_data ) values (?, ?)
   Granted XID : {627716296, X}
Lock : ROW, SYSCONGLOMERATES, (7,8)
   Waiting XID : {627716296, X} , APP, alter table
"APP"."QPID_MESSAGE_METADATA" compress
   Granted XID : {627716296, S} , {627716335, S}
. The selected victim is XID : 627716335.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:100)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:230)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:431)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:360)
at
org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2405)
at
org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:88)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.(EmbedPreparedStatement.java:152)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement42.(EmbedPreparedStatement42.java:41)
at
org.apache.derby.iapi.jdbc.Driver42.newEmbedPreparedStatement(Driver42.java:56)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1740)
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java:1568)
at
org.apache.qpid.server.store.jdbc.AbstractJDBCMessageStore.storeMetaData(AbstractJDBCMessageStore.java:981)
at
org.apache.qpid.server.store.jdbc.AbstractJDBCMessageStore.access$1200(AbstractJDBCMessageStore.java:81)
at
org.apache.qpid.server.store.jdbc.AbstractJDBCMessageStore$StoredJDBCMessage.store(AbstractJDBCMessageStore.java:1635)
at
org.apache.qpid.server.store.jdbc.AbstractJDBCMessageStore$JDBCTransaction.lambda$enqueueMessage$1(AbstractJDBCMessageStore.java:1240)
at
org.apache.qpid.server.store.jdbc.AbstractJDBCMessageStore$JDBCTransaction.doPreCommitActions(AbstractJDBCMessageStore.java:1289)
at
org.apache.qpid.server.store.jdbc.AbstractJDBCMessageStore$JDBCTransaction.commitTranAsync(AbstractJDBCMessageStore.java:1278)
at
org.apache.qpid.server.txn.AsyncAutoCommitTransaction.enqueue(AsyncAutoCommitTransaction.java:294)
at org.apache.qpid.server.message.RoutingResult.send(RoutingResult.java:124)

On Fri, Sep 30, 2022 at 2:46 PM Rick Hillegas 
wrote:


I don't have any theories yet about what is going on. Can you try
issuing the following statement just before you compress the table:

SELECT * FROM SYSCS_DIAG.LOCK_TABLE;

I don't understand why the INSERT transaction grabs a lock on a
SYSCONGLOMERATES row.

Thanks,
-Rick


On 9/29/22 1:24 PM, Nathan Jensen wrote:

Thanks for the response!  To clarify a few things, we're using Derby
10.15.2.0 and Java 11.  Our project is sticking with Java 11 for the time
being so I can't upgrade to Derby 10.16 at this time.  The version of

Qpid

we're using at present is 7.1.12.

I don't see anything suspicious regarding the INSERT statement, but maybe
you could see something that I don't.  I believe the relevant code is

here:
https://github.com/apache/qpid-broker-j/blob/7.1.12/broker-plugins/jdbc-store/src/main/java/org/apache/qpid/server/store/jdbc/AbstractJDBCMessageStore.java#L675

That does not commit the transaction, the transaction is committed
elsewhere in the same file, here:


https://github.com/apache/qpid-broker-j/blob/7.1.12/broker-plugins/jdbc-store/src/main/java/org/apache/qpid/server/store/jdbc/AbstractJDBCMessageStore.java#L1264

I should also note that one time I saw the deadlock happen with a DELETE
statement instead of an INSERT, so I don't think the unexpected row

locking

in SYSCONGLOMERATES is limited to INSERT.

Here is the DDL as produced by Derby's dblook:
-- --
-- DDL Statements for tables
-- --

CREATE TABLE "APP"."QPID_QUEUE_ENTRIES" ("QUEUE_ID" VARCHAR(36) NOT NULL,
"MESSAGE_ID" BIGINT NOT NULL);

CREATE TABLE "APP"."QPID_MESSAGE_CONTENT" ("MESSAGE_ID" BIGINT NOT NULL,
"CONTENT" BLOB(2147483647));

CREATE TABLE "APP"."QPID_XID_ACTIONS" ("FORMAT" BIGINT NOT NULL,
"GLOBAL_ID" VARCHAR (64) FOR BIT DATA NOT NULL, "BRANCH_ID" VARCHAR (64)
FOR BIT DATA NOT NULL, "ACTION_TYPE" CHAR(1) NOT NULL, "QUEUE_ID"
VARCHAR(36) NOT NULL, "MESSAGE_ID" BIGINT NOT NULL);

CREATE TABLE "APP"."AMQP_1_0_LINKS" ("LINK_KEY" VARCHAR(44) NOT NULL,
"REMOTE_CONTAINER_ID" BLOB(2147

Re: deadlock when running SYSCS_UTIL.SYSCS_COMPRESS_TABLE

[Rick Hillegas]

I don't have any theories yet about what is going on. Can you try 
issuing the following statement just before you compress the table:


SELECT * FROM SYSCS_DIAG.LOCK_TABLE;

I don't understand why the INSERT transaction grabs a lock on a 
SYSCONGLOMERATES row.


Thanks,
-Rick


On 9/29/22 1:24 PM, Nathan Jensen wrote:

Thanks for the response!  To clarify a few things, we're using Derby
10.15.2.0 and Java 11.  Our project is sticking with Java 11 for the time
being so I can't upgrade to Derby 10.16 at this time.  The version of Qpid
we're using at present is 7.1.12.

I don't see anything suspicious regarding the INSERT statement, but maybe
you could see something that I don't.  I believe the relevant code is here:
https://github.com/apache/qpid-broker-j/blob/7.1.12/broker-plugins/jdbc-store/src/main/java/org/apache/qpid/server/store/jdbc/AbstractJDBCMessageStore.java#L675
That does not commit the transaction, the transaction is committed
elsewhere in the same file, here:
https://github.com/apache/qpid-broker-j/blob/7.1.12/broker-plugins/jdbc-store/src/main/java/org/apache/qpid/server/store/jdbc/AbstractJDBCMessageStore.java#L1264
I should also note that one time I saw the deadlock happen with a DELETE
statement instead of an INSERT, so I don't think the unexpected row locking
in SYSCONGLOMERATES is limited to INSERT.

Here is the DDL as produced by Derby's dblook:
-- --
-- DDL Statements for tables
-- --

CREATE TABLE "APP"."QPID_QUEUE_ENTRIES" ("QUEUE_ID" VARCHAR(36) NOT NULL,
"MESSAGE_ID" BIGINT NOT NULL);

CREATE TABLE "APP"."QPID_MESSAGE_CONTENT" ("MESSAGE_ID" BIGINT NOT NULL,
"CONTENT" BLOB(2147483647));

CREATE TABLE "APP"."QPID_XID_ACTIONS" ("FORMAT" BIGINT NOT NULL,
"GLOBAL_ID" VARCHAR (64) FOR BIT DATA NOT NULL, "BRANCH_ID" VARCHAR (64)
FOR BIT DATA NOT NULL, "ACTION_TYPE" CHAR(1) NOT NULL, "QUEUE_ID"
VARCHAR(36) NOT NULL, "MESSAGE_ID" BIGINT NOT NULL);

CREATE TABLE "APP"."AMQP_1_0_LINKS" ("LINK_KEY" VARCHAR(44) NOT NULL,
"REMOTE_CONTAINER_ID" BLOB(2147483647), "LINK_NAME" BLOB(2147483647),
"LINK_ROLE" INTEGER, "SOURCE" BLOB(2147483647), "TARGET" BLOB(2147483647));

CREATE TABLE "APP"."QPID_MESSAGE_METADATA" ("MESSAGE_ID" BIGINT NOT NULL,
"META_DATA" BLOB(2147483647));

CREATE TABLE "APP"."QPID_DB_VERSION" ("VERSION" INTEGER NOT NULL);

CREATE TABLE "APP"."QPID_XIDS" ("FORMAT" BIGINT NOT NULL, "GLOBAL_ID"
VARCHAR (64) FOR BIT DATA NOT NULL, "BRANCH_ID" VARCHAR (64) FOR BIT DATA
NOT NULL);

CREATE TABLE "APP"."AMQP_1_0_LINKS_VERSION" ("VERSION" VARCHAR(10) NOT
NULL, "VERSION_TIME" TIMESTAMP);

-- --
-- DDL Statements for keys
-- --

-- PRIMARY/UNIQUE
ALTER TABLE "APP"."AMQP_1_0_LINKS" ADD CONSTRAINT
"SQL86-b2280141-0183-5b33-aec6-16c47528" PRIMARY KEY
("LINK_KEY");

ALTER TABLE "APP"."QPID_MESSAGE_METADATA" ADD CONSTRAINT
"SQL82-e333810d-0183-5b33-aec6-16c47528" PRIMARY KEY
("MESSAGE_ID");

ALTER TABLE "APP"."QPID_QUEUE_ENTRIES" ADD CONSTRAINT
"SQL81-61228105-0183-5b33-aec6-16c47528" PRIMARY KEY
("QUEUE_ID", "MESSAGE_ID");

ALTER TABLE "APP"."QPID_XID_ACTIONS" ADD CONSTRAINT
"SQL85-69c68125-0183-5b33-aec6-16c47528" PRIMARY KEY ("FORMAT",
"GLOBAL_ID", "BRANCH_ID", "ACTION_TYPE", "QUEUE_ID", "MESSAGE_ID");

ALTER TABLE "APP"."QPID_XIDS" ADD CONSTRAINT
"SQL84-e785811d-0183-5b33-aec6-16c47528" PRIMARY KEY ("FORMAT",
"GLOBAL_ID", "BRANCH_ID");

ALTER TABLE "APP"."QPID_MESSAGE_CONTENT" ADD CONSTRAINT
"SQL83-65548115-0183-5b33-aec6-16c47528" PRIMARY KEY
("MESSAGE_ID");

ALTER TABLE "APP"."AMQP_1_0_LINKS_VERSION" ADD CONSTRAINT
"SQL87-c14b-0183-5b33-aec6-16c47528" PRIMARY KEY
("VERSION");


And just in case I'm doing something stupid, here is the current version of
my code.  conn.getAutoCommit() is false, but I also tried it with an
autocommit connection and without the commit statements that time.  The
code is inside a try-catch-finally that is inside a TimerTask.

conn = newConnection();
LOGGER.info("connection.getAutoCommit() is " + conn.getAutoCommit());
LOGGER.info("Compressin

Re: deadlock when running SYSCS_UTIL.SYSCS_COMPRESS_TABLE

[Rick Hillegas]

I'm not an expert on the table compression code, but it does need an 
overhaul (see https://issues.apache.org/jira/browse/DERBY-3683).


If I'm interpreting the error correctly, the table compressor is blocked 
trying to escalate its lock on the SYSCONGLOMERATES row from shared to 
exclusive. But it can't do this because the INSERT transaction holds a 
shared lock on the SYSCONGLOMERATES row. I don't understand why the 
INSERT transaction would need to lock a SYSCONGLOMERATES row. The 
following simple experiment shows that, by itself, a simple INSERT 
should not lock any rows in SYSCONGLOMERATES:


ij version 10.17

ij> CONNECT 'jdbc:derby:memory:db;create=true';

ij> AUTOCOMMIT OFF;

ij> CREATE TABLE t(a INT);

0 rows inserted/updated/deleted

ij> CREATE INDEX t_idx ON t(a);

0 rows inserted/updated/deleted

ij> CREATE TABLE s(a INT);

0 rows inserted/updated/deleted

ij> INSERT INTO s VALUES (1);

1 row inserted/updated/deleted

ij> COMMIT;

ij> INSERT INTO t SELECT * FROM s;

1 row inserted/updated/deleted

ij> SELECT * FROM SYSCS_DIAG.LOCK_TABLE;

XID    |TYPE |MODE|TABLENAME
   |LOCKNAME
    |STATE|TABLETYPE|LOCK&|INDEXNAME

---

186    |TABLE|IX  |T
   
|Tablelock   |GRANT|T    |2    |NULL

186    |ROW  |X   |T
   
|(1,7)   |GRANT|T    |1    |NULL

2 rows selected

ij> COMMIT;

ij> SELECT * FROM SYSCS_DIAG.LOCK_TABLE;

XID    |TYPE |MODE|TABLENAME
   |LOCKNAME
    |STATE|TABLETYPE|LOCK&|INDEXNAME

---

0 rows selected


Is there anything else going on in the INSERT transaction? Can you share 
the table DDL with us, including indexes, constraints, auto-generated 
columns, etc.?


-Rick


On 9/28/22 2:12 PM, Nathan Jensen wrote:

Hi, we are using Derby as the backing message store for Qpid-Broker-J.  We
have a continuous data flow into Qpid so each message gets persisted to the
Derby message store as data comes in and then it is deleted from the Derby
message store as data is picked up and processed.  The data flow is pretty
constant, there are peaks and valleys but basically data is always coming
in so there are LOTS of inserts and deletes.  Over time we found that the
Derby message store fills up the disk drive until it runs out of space and
in general the workaround to that has been to stop Qpid and remove the
message store (i.e. the Derby directory), which loses data.

My attempted solution to this was to extend Qpid's DerbyMessageStore with a
DerbyRepackingMessageStore that executes the
SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure based on a timer.  However, on a
test system with flowing data, this always has a deadlock exception and
some data (the aborted transaction) is lost.  An example is:

Caused by: org.apache.derby.shared.common.error.StandardException: A lock
could not be obtained due to a deadlock, cycle of locks and waiters is:
Lock : TABLE, QPID_MESSAGE_METADATA, Tablelock
   Waiting XID : {501214885, IX} , APP, INSERT INTO QPID_MESSAGE_METADATA(
message_id , meta_data ) values (?, ?)
   Granted XID : {501214829, X}
Lock : ROW, SYSCONGLOMERATES, (7,8)
   Waiting XID : {501214829, X} , APP, alter table
"APP"."QPID_MESSAGE_METADATA" compress
   Granted XID : {501214829, S} , {501214865, S} , {501214885, S}
. The selected victim is XID : 501214885.
 at
org.apache.derby.shared.common.error.StandardException.newException(StandardException.java:300)
 at
org.apache.derby.shared.common.error.StandardException.newException(StandardException.java:295)
 at
org.apache.derby.impl.services.locks.Deadlock.buildException(Deadlock.java:557)
 at
org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(ConcurrentLockSet.java:647)

My understanding of that is that the deadlock is occurring because the
insert has a shared lock on the sysconglomerates row and wants an exclusive
lock on 

Re: ResultSetMetaData question

[Rick Hillegas]

On 7/7/22 2:03 AM, John English wrote:

On 05/07/2022 17:26, Rick Hillegas wrote:
In any event, as you've noticed, getLabelName() returns the same 
value as getColumnName() in Derby.


So basically I need to write my own SQL parser for a sequence of 
SelectItems between SELeCT and FROM if I want to get the unaliased item?


If you want getColumnName() and getLabelName() to return different 
values, then you will have to write some custom code.

Re: ResultSetMetaData question

[Rick Hillegas]

On 7/4/22 11:50 AM, John English wrote:

On 04/07/2022 16:21, Rick Hillegas wrote:
I'm afraid I don't understand your results. When I run your 
experiment, "2" is the name and label of the second column of the 
query "SELECT country,count(*) FROM customer GROUP BY country ORDER 
BY country". Does the following give you what you want:


   SELECT country,count(*) AS "count(*)" FROM customer GROUP BY 
country ORDER BY country


What I'm after if I have "x AS y" is a way of getting "x". I 
understood (incorrectly as it seems) that getColumnName() would give 
"x" and getColumnLabel() would give "y".


As far as I can tell from experiments, both methods ALWAYS produce 
identical results. So I'm no longer sure why two different methods exist.


As I understand the SQL Standard, the columns in query expressions (like 
SELECT statements) have "derived column names". The SQL Standard has no 
concept of a separate label name. The AS clause simply overrides the 
derived column name. The original authors of the ResultSetMetaData 
interface clearly imagined some distinction between getColumnName() and 
getColumnLabel(), but that distinction is not in the SQL Standard and it 
isn't clarified by either the ResultSetMetaData javadoc or the JDBC 4.3 
spec. Maybe there is some corresponding distinction in the older ODBC 
spec which inspired the first JDBC spec.


In any event, as you've noticed, getLabelName() returns the same value 
as getColumnName() in Derby.

Re: ResultSetMetaData question

[Rick Hillegas]

I'm afraid I don't understand your results. When I run your experiment, 
"2" is the name and label of the second column of the query "SELECT 
country,count(*) FROM customer GROUP BY country ORDER BY country". Does 
the following give you what you want:


  SELECT country,count(*) AS "count(*)" FROM customer GROUP BY country 
ORDER BY country



On 7/4/22 6:01 AM, John English wrote:

I have a query SELECT COUNT(*) AS X FROM ...

If I use ResultMetaData.getColumnLabel(), I get "X".
If I use ResultMetaData.getColumnName(), I also get "X".

Here is the output from a test program:

Query: SELECT country,count(*) FROM customer GROUP BY country ORDER BY 
country

Name = COUNTRY
Label = COUNTRY
Name = COUNT(*)
Label = COUNT(*)
columns = COUNT(*), COUNTRY

Query: select country, count(*) as customers from customer group by 
country order by country asc

Name = COUNTRY
Label = COUNTRY
Name = CUSTOMERS
Label = CUSTOMERS
columns = COUNTRY, CUSTOMERS

I was hoping/expecting that getColumnName() would return "COUNT(*)" in 
the second case.


Is there any way to get COUNT(*) short of parsing the query myself?

Thanks,

[ANNOUNCE] Apache Derby 10.16.1.1 released

[Rick Hillegas]

The Apache Derby project is pleased to announce feature release 10.16.1.1.

Apache Derby is a sub-project of the Apache DB project. Derby is a pure Java 
relational database engine which conforms to the ISO/ANSI SQL and
JDBC standards. Derby aims to be easy for developers and end-users to work with.

The chief feature of this release is the removal of support for the Java 
SecurityManager, anticipating its removal from the Open JDK as part of
https://openjdk.org/jeps/411. Derby 10.16.1.1 runs on JDK 17 and up. Users who 
need to run Derby-powered applications on older JVMs should select an earlier 
version of Derby, as described on the Derby download site.

Derby 10.16.1.1 also contains bug and documentation fixes and it can be 
obtained from the Derby download site:

 http://db.apache.org/derby/derby_downloads.html.

Please try out this new release.

Re: Strange sorting behaviour

[Rick Hillegas]

Sorting behavior is only defined for the columns in the ORDER BY clause. 
If a column is not included in the ORDER BY clause, then its sort order 
can be arbitrary and not even consistent across executions of the query.


Hope this helps,
-Rick

On 4/27/22 4:35 AM, John English wrote:
I have a Java method which displays a view as an HTML table. The 
method has parameters to specify the sort column (default is the first 
column ascending), filters to select specific rows, and so on.


Recently I came across an oddity which I can't figure out. Consider 
the following table and view:


create table t1 (
  a integer,
  b integer,
  c integer,
  d integer
);
insert into t1 values
  (1,2,3,4),
  (1,4,5,6),
  (2,3,4,5),
  (1,3,4,5);
create view v1 as
  select a,b,c from t1;

If I select from the view like this:

select * from v1;

this is what I get:

A B C
-
1 2 3
1 4 5
2 3 4
1 3 4

This is the same as the order of insertion. However, as soon as I sort 
on column A, the sort order for column B changes:


select * from v1 order by a;

A B C
-
1 3 4
1 4 5
1 2 3
2 3 4

B is now shown in the reverse of the order of insertion.

If I add an "order by" to the view:

create view v1 as
  select a,b,c from t1 order by a,b;

I get this:

select * from v1;

A B C
-
1 2 3
1 3 4
1 4 5
2 3 4

select * from v1 order by a;

A B C
-
1 4 5
1 3 4
1 2 3
2 3 4

Again, column B is reversed.

Since my table display method automatically inserts an "order by" as 
in this example, my displayed table always ends up with the second 
column in descending order no matter what I do.


Can anyone explain why this is, and what I can do to fix it so that B 
comes out sorted in the "natural" order (order of insertion, or as 
specified by "order by" in the view)?


TIA,

Re: query timeout

[Rick Hillegas]

That suggests to me that the problem is not in the Derby layer. The 
problem is in JPA's support for Derby. JPA should be able to take 
advantage of java.sql.Statement.setQueryTimeout(). Have you brought this 
issue to the JPA community?


On 4/20/22 7:52 AM, Marco Ferretti wrote:

Hi Rick,

thanks for taking the time to reply.
I have looked at the link you provide: the method that sets the values in persistence.xml 
should affect all queries attached to that persistence unit; the second ("Setting 
the Query timeout on the single Query") method is the one I am using, while the 
third option is, AFAIK, out of scope in my case.

Marco.

On Apr 20 2022, at 4:46 pm, Rick Hillegas  wrote:

I'm not an expert on using JPA. The following link suggests that there
is a way to configure query timeout in an xml-formatted JPA
configuration file:
http://www.mastertheboss.com/hibernate-jpa/jpa-configuration/3-ways-to-set-a-query-timeout-for-jpa-hibernate-applications/

On 4/20/22 5:59 AM, Marco Ferretti wrote:

Ok I have an update.

I have tested on PostgreSQL and I do get the timeout.
In order to create a simple case I have created a simple stored procedure on pg 
:

CREATE OR REPLACE PROCEDURE test_timeout("test" integer)
LANGUAGE SQL
AS $$
select count(*) from pg_sleep("test")
$$;

and the call
em.createStoredProcedureQuery("test_timeout")
.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
.setParameter(1, 5)
.setHint("javax.persistence.query.timeout", 1)
.execute();

actually throws the exception.
I have then created a simple Derby database (empty) in which I have created my 
procedure
CREATE SCHEMA TEST;
CALL 
SQLJ.INSTALL_JAR('file:///home/marco/devel/stored_procedures/target/storedprocedure-1.0.jar',
 'TEST.test', 0);
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 
'TEST.test');

DROP PROCEDURE APP.test_timeout;
CREATE PROCEDURE TEST.test_timeout (IN WAIT BIGINT)
PARAMETER STYLE JAVA
NO SQL
LANGUAGE JAVA
EXTERNAL NAME 'org.me.storedprocedure.Procedures.db_wait';

Here's the super dummy procedure if you want to try:
public static void db_wait(long wait) throws Exception {
Thread.sleep(wait*1000);
}

Running this code :
public boolean testStoredProcedure(EntityManager em ) throws Exception {
em.createStoredProcedureQuery("test.test_timeout")
.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
.setParameter(1, 5)
.setHint("javax.persistence.query.timeout", 1)
.execute();
return false;
}

Against Postgresql :
[EL Warning]: 2022-04-20 14:52:29.152--UnitOfWork(392289808)--Exception 
[EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.v20180807-4be1041): 
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: canceling 
statement due to user request
Where: SQL statement "select count(*) from pg_sleep("test")"
PL/pgSQL function test.test_timeout(integer) line 5 at SQL statement
Error Code: 0
Call: SELECT * FROM test.test_timeout(?)
bind => [1 parameter bound]
Query: ResultSetMappingQuery()
javax.persistence.PersistenceException:Exception [EclipseLink-4002] (Eclipse 
Persistence Services - 2.7.3.v20180807-4be1041): 
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: canceling 
statement due to user request
Where: SQL statement "select count(*) from pg_sleep("test")"
PL/pgSQL function test.test_timeout(integer) line 5 at SQL statement
Error Code: 0
Call: SELECT * FROM test.test_timeout(?)
bind => [1 parameter bound]
Query: ResultSetMappingQuery()
[EL Info]: connection: 2022-04-20 
14:52:29.162--ServerSession(1406848276)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu
 logout successful

Against Derby (Embedded):
[EL Info]: 2022-04-20 14:48:40.742--ServerSession(256346753)--EclipseLink, 
version: Eclipse Persistence Services - 2.7.3.v20180807-4be1041
[EL Info]: connection: 2022-04-20 
14:48:41.028--ServerSession(256346753)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu
 login successful
[EL Info]: connection: 2022-04-20 
14:48:46.144--ServerSession(256346753)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu
 logout successful

As you can see the call is cancelled after 1 millisecond when run against 
postgresql while it is not while run against derby
The two jdbc drivers I used :

org.apache.derby
derby
10.14.2.0


org.postgresql
postgresql
42.3.4


Am I hitting a derby/derby-jdbc limitation or am I missing some configuration ?
Thanks in advance for any help you can provide

On Apr 19 2022, at 11:57 pm, Marco Ferretti  wrote:

Hi Brian,
Thanks for your reply and attempt to help.

Here's what I do:
Within the database (preparation of the test)
CALL 
SQLJ.replace_jar('file:///home/marco/devel/stored_procedures/target/storedprocedure-1.0.jar',
 'APP.STORED_PROCS');

CREATE PROCEDURE AP

Re: query timeout

[Rick Hillegas]

the stored procedure i do, before 
doing anything else, a TimeUnit.SECONDS.wait(10);
On point 1 : I am lost. I think I am using the hint correctly (at least the API 
docs say so)
On point 2: I am setting up the datasource with the minimum configuration 
possible :
 JdbcDriver 
org.apache.derby.jdbc.ClientDriver JdbcUrl jdbc:derby://database:1527/dbpromo;create=false UserName DBPROMO 
Password dbpromo 
Could it be that I am failing to activate something on the Derby side ?

Thanks in advance
Marco

On Apr 19 2022, at 4:57 pm, Rick Hillegas mailto:rick.hille...@gmail.com)> wrote:

java.sql.Statement.setQueryTimeout(int) should do the trick.

On 4/19/22 3:30 AM, Marco Ferretti wrote:

Hi all,
I am trying to simulate a query timeout in a stored procedure by simply adding 
a delay in my (test) jar.
I then am launching the stored procedure in my java code via JPA and try to set 
a timeout hint by adding
.setHint("javax.persistence.query.timeout", milliseconds)
but I am not able to register a timeout.
I am wondering if there is some derby property or jdbc property I should use to 
activate such behavior. I have tried to google for it but I am having extremely 
bad results... but according to this 
(https://docs.oracle.com/cd/E25178_01/apirefs./e13952/pagehelp/J2EEkodojdbcconfdescriptorDerbyDictionaryBeantitle.html)
 I have to activate it somehow.

Does Derby supports query timeout at all? If so, can you please point me to 
some references ?
Thanks in advance for any help,
Marco

Re: query timeout

[Rick Hillegas]

java.sql.Statement.setQueryTimeout(int) should do the trick.

On 4/19/22 3:30 AM, Marco Ferretti wrote:

Hi all,
I am trying to simulate a query timeout in a stored procedure by simply adding 
a delay in my (test) jar.
I then am launching the stored procedure in my java code via JPA and try to set 
a timeout hint by adding
.setHint("javax.persistence.query.timeout", milliseconds)
but I am not able to register a timeout.
I am wondering if there is some derby property or jdbc property I should use to 
activate such behavior. I have tried to google for it but I am having extremely 
bad results... but according to this 
(https://docs.oracle.com/cd/E25178_01/apirefs./e13952/pagehelp/J2EEkodojdbcconfdescriptorDerbyDictionaryBeantitle.html)
 I have to activate it somehow.

Does Derby supports query timeout at all? If so, can you please point me to 
some references ?
Thanks in advance for any help,
Marco

Re: Problem with FOR UPDATE

[Rick Hillegas]

On 2/5/22 10:52 AM, John English wrote:

On 05/02/2022 20:10, Rick Hillegas wrote:
I don't think you need the FOR UPDATE clause. The following simpler 
code works for me:


 try (Statement updatable = 
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
ResultSet.CONCUR_UPDATABLE))

 {
 try (ResultSet rs = updatable.executeQuery(SELECT_ALL))
 {
 while(rs.next())
 {
 rs.updateInt(1, 10 * rs.getInt(1));
 rs.updateRow();
 }
 }
What if you want to update just one specific row? Can you use the same 
technique for that?



Yes. You just have to position yourself on that row. Cheers

Re: Problem with FOR UPDATE

[Rick Hillegas]

I don't think you need the FOR UPDATE clause. The following simpler code 
works for me:


import java.sql.*;

public class Z

{

    private static final String SELECT_ALL = "SELECT * FROM t";

    public static void main(String... args) throws Exception

    {

    Connection conn = 
DriverManager.getConnection("jdbc:derby:memory:db;create=true");

    conn.prepareStatement("CREATE TABLE t (a int)").execute();

    conn.prepareStatement("INSERT INTO t VALUES (1), (2)").execute();

    printResultSet(conn, "Table before update...");

    // now update the rows in place using an updatable ResultSet

    try (Statement updatable = 
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE))

    {

    try (ResultSet rs = updatable.executeQuery(SELECT_ALL))

    {

    while(rs.next())

    {

    rs.updateInt(1, 10 * rs.getInt(1));

    rs.updateRow();

    }

    }

    }

    printResultSet(conn, "\nTable after update...");

    }

    private static void printResultSet(Connection conn, String banner) throws 
SQLException

    {

    println(banner);

    try (PreparedStatement ps = conn.prepareStatement(SELECT_ALL))

    {

    try (ResultSet rs = ps.executeQuery())

    {

    printResultSet(rs);

    }

    }

    }

    private static void printResultSet(ResultSet rs) throws SQLException

    {

    ResultSetMetaData    rsmd = rs.getMetaData();

    int                    count = rsmd.getColumnCount();

    StringBuffer buffer = new StringBuffer();

    for (int idx = 1; idx <= count; idx++)

    {

    buffer.append("| ").append(rsmd.getColumnName(idx)).append(" ");

    }

    buffer.append(" |\n");

    while (rs.next())

    {

    for (int idx = 1; idx <= count; idx++)

    {

    buffer.append("| ").append(rs.getString(idx)).append(" ");

    }

    buffer.append(" |\n");

    }

    println(buffer.toString());

    }

    private static void println(String text) { System.out.println(text); }

}



On 2/5/22 7:19 AM, John English wrote:

I've got a problem with using a SELECT ... FOR UPDATE.

The code basically looks like this (stripped down for simplicity):

  PreparedStatement stat = conn.prepareStatement(
    "SELECT ... FOR UPDATE",
    ResultSet.TYPE_FORWARD_ONLY,
    ResultSet.CONCUR_UPDATABLE
   );
  try (ResultSet r = stat.executeQuery()) {
    if (!r.next()) {
  throw new Exception();
    }
    ...
    r.moveToCurrentRow();
    r.updateString(column,value);
    r.updateRow();    // throws SQLException
  }

The call to updateRow() is throwing the following SQLException:

  class java.sql.SQLException: Invalid cursor state - no current row.

I cannot for the life of me see why this is happening, since the test 
on r.next() shows that I've found the row I want to update.


Can anyone suggest a reason what might be going on here?

Thanks,

Re: Column is in more than one table?

[Rick Hillegas]

On 9/28/21 4:51 AM, John English wrote:

If I issue the following query:

SELECT t_time,facility FROM system_log,(SELECT time FROM system_log 
ORDER BY facility) AS x WHERE system_log.time=x.time


I get an error: "Column name 'FACILITY' is in more than one table in 
the FROM list."


Why does it consider Facility to be a column in table X? Is this 
correct behaviour, or is it a Derby bug?



This looks like a bug to me, but one with an easy workaround:

SELECT s.time,s.facility FROM system_log s,(SELECT time FROM system_log ORDER 
BY facility) AS x WHERE s.time=x.time;

Re: Using indexes

[Rick Hillegas]

Glad that you have made progress on this puzzle. One comment inline...

On 9/28/21 4:10 AM, John English wrote:
A couple more data points, from testing different variants of the 
inner select:


1) SELECT time FROM system_log
 ORDER BY time DESC NULLS LAST FETCH NEXT 20 ROWS ONLY;

419ms, "Index Scan ResultSet for SYSTEM_LOG using index LOG_INDEX at 
read uncommitted isolation level using share row locking chosen by the 
optimizer"


2) SELECT time FROM system_log
 WHERE username='foo'
 ORDER BY time DESC NULLS LAST FETCH NEXT 20 ROWS ONLY;

2245ms, "Table Scan ResultSet for SYSTEM_LOG at read uncommitted 
isolation level using share row locking chosen by the optimizer"


Removing "FETCH NEXT 20 ROWS ONLY":

1) 388,237 rows in 3859ms, otherwise same as above (uses index).
2) 23,211 rows in 2199ms, otherwise same as above (doesn't use index).

The WHERE clause seems to prevent the index from being used.
The index is not usable in this query because username is not the 
leading column in the index.

Re: Using indexes

[Rick Hillegas]

On 9/25/21 11:59 AM, John English wrote:

On 25/09/2021 21:14, Rick Hillegas wrote:

On 9/25/21 7:39 AM, John English wrote:
SELECT id,DateTimeFormat(time,null) AS 
t_time,name,username,facility,event,details

FROM system_log
ORDER BY id DESC
NULLS LAST
FETCH FIRST 20 ROWS ONLY;


I can remember whether you tried to rewrite the query to use a 
subquery. Something like this:


SELECT id, time AS t_time,name,username,facility,event,details
FROM
   system_log s,
   (
 SELECT id AS log_id
 FROM system_log
 ORDER BY id DESC
 NULLS LAST
 FETCH FIRST 20 ROWS ONLY
   ) t
WHERE s.id = t.log_id
;

Does that help?

-Rick


No, when I tried it, it made it even worse if anything. The subquery 
still fetches all 400,000 rows, sorts them without using the index, 
and then uses the results to select again in the outer query. I really 
don't understand why the index is ignored.


The support for FETCH/OFFSET is pretty minimal. Probably, the optimizer 
isn't smart enough to know that the subquery returns only 20 small rows.


What happens if you dump the results of the subquery into a temporary 
table and then join that with system_log?

Re: Using indexes

[Rick Hillegas]

On 9/25/21 7:39 AM, John English wrote:
SELECT id,DateTimeFormat(time,null) AS 
t_time,name,username,facility,event,details

FROM system_log
ORDER BY id DESC
NULLS LAST
FETCH FIRST 20 ROWS ONLY;


I can remember whether you tried to rewrite the query to use a subquery. 
Something like this:


SELECT id, time AS t_time,name,username,facility,event,details
FROM
  system_log s,
  (
    SELECT id AS log_id
    FROM system_log
    ORDER BY id DESC
    NULLS LAST
    FETCH FIRST 20 ROWS ONLY
  ) t
WHERE s.id = t.log_id
;

Does that help?

-Rick

Re: Case insensitive ORDER BY?

[Rick Hillegas]

Hm. I don't think that UPPER operates on numeric data:

ij> CONNECT 'jdbc:derby:memory:db;create=true';

ij> CREATE TABLE t(a int);

0 rows inserted/updated/deleted

ij> INSERT INTO t VALUES (2), (10), (21);

3 rows inserted/updated/deleted

ij> SELECT * FROM t ORDER BY UPPER(a);

ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'.



On 8/21/21 2:45 PM, Bryan Pendleton wrote:

I think he was saying that doing "ORDER BY UPPER(x)", where x is a
column of type INT, did something strange:


" for numberical columns I will end up sorting textually: values 1,2,10 will be 
sorted as 1,10,2."

On Sat, Aug 21, 2021 at 8:34 AM Rick Hillegas  wrote:

Some responses inline...

On 8/21/21 8:03 AM, John English wrote:

On 20/08/2021 20:13, Rick Hillegas wrote:

You could solve this problem with a custom character collation. See
https://db.apache.org/derby/docs/10.15/devguide/cdevcollation.html

Great!


If you don't need to sort the embedded numbers, then the simplest
solution is to create a database which uses a case-insensitive sort
order. See
https://db.apache.org/derby/docs/10.15/devguide/tdevdvlpcollation.html

I need to think a bit about whether I ever need case-sensitivity. I
suspect not, but I'll need to go through the tables, and if I can't
find any problems, this sounds like it might be the best solution.

Assuming this is a viable solution, is there a way to convert a live
database from case-sensitive to case-insensitive (from
collation=TERRITORY_BASED:TERTIARY to
collation=TERRITORY_BASED:PRIMARY, if I understand correctly), which I
assume will involve rebuilding all the indexes?

Unfortunately, you have to create a new database and copy your old data
into the new database. I would recommend creating a fresh database which
has the correct, case-insensitive collation. Then copy the old data into
the new database using the foreign views optional tool. See
https://db.apache.org/derby/docs/10.15/tools/rtoolsoptforeignviews.html

If you need to sort the embedded numbers too, then you have to supply
a custom collator. See
https://db.apache.org/derby/docs/10.15/devguide/tdevdvlpcustomcollation.html

OK, this bit I didn't understand. Sometimes I want to sort on columns
of numbers, sometimes dates, sometimes strings. Is that what you mean
by needing to "sort the embedded numbers"?

Or I don't understand your problem. I thought that you needed a string
like abc2def to sort before abc10def. Sort order should be correct for
numeric and date/time datatypes. It's just the character typed data
which sorts incorrectly.

It's hard to imagine that you are the first person who needs the sort
order you have described. Maybe a little googling will discover that
someone has open-sourced a collator which does the right thing. If
you can't find one but you end up writing your own, please consider
open-sourcing it.

OK, will do.

Many thanks,

Re: Case insensitive ORDER BY?

[Rick Hillegas]

Some responses inline...

On 8/21/21 8:03 AM, John English wrote:

On 20/08/2021 20:13, Rick Hillegas wrote:
You could solve this problem with a custom character collation. See 
https://db.apache.org/derby/docs/10.15/devguide/cdevcollation.html


Great!

If you don't need to sort the embedded numbers, then the simplest 
solution is to create a database which uses a case-insensitive sort 
order. See 
https://db.apache.org/derby/docs/10.15/devguide/tdevdvlpcollation.html


I need to think a bit about whether I ever need case-sensitivity. I 
suspect not, but I'll need to go through the tables, and if I can't 
find any problems, this sounds like it might be the best solution.


Assuming this is a viable solution, is there a way to convert a live 
database from case-sensitive to case-insensitive (from 
collation=TERRITORY_BASED:TERTIARY to 
collation=TERRITORY_BASED:PRIMARY, if I understand correctly), which I 
assume will involve rebuilding all the indexes?
Unfortunately, you have to create a new database and copy your old data 
into the new database. I would recommend creating a fresh database which 
has the correct, case-insensitive collation. Then copy the old data into 
the new database using the foreign views optional tool. See 
https://db.apache.org/derby/docs/10.15/tools/rtoolsoptforeignviews.html


If you need to sort the embedded numbers too, then you have to supply 
a custom collator. See 
https://db.apache.org/derby/docs/10.15/devguide/tdevdvlpcustomcollation.html


OK, this bit I didn't understand. Sometimes I want to sort on columns 
of numbers, sometimes dates, sometimes strings. Is that what you mean 
by needing to "sort the embedded numbers"?
Or I don't understand your problem. I thought that you needed a string 
like abc2def to sort before abc10def. Sort order should be correct for 
numeric and date/time datatypes. It's just the character typed data 
which sorts incorrectly.


It's hard to imagine that you are the first person who needs the sort 
order you have described. Maybe a little googling will discover that 
someone has open-sourced a collator which does the right thing. If 
you can't find one but you end up writing your own, please consider 
open-sourcing it.


OK, will do.

Many thanks,

Re: Case insensitive ORDER BY?

[Rick Hillegas]

You could solve this problem with a custom character collation. See 
https://db.apache.org/derby/docs/10.15/devguide/cdevcollation.html


If you don't need to sort the embedded numbers, then the simplest 
solution is to create a database which uses a case-insensitive sort 
order. See 
https://db.apache.org/derby/docs/10.15/devguide/tdevdvlpcollation.html


If you need to sort the embedded numbers too, then you have to supply a 
custom collator. See 
https://db.apache.org/derby/docs/10.15/devguide/tdevdvlpcustomcollation.html 
It's hard to imagine that you are the first person who needs the sort 
order you have described. Maybe a little googling will discover that 
someone has open-sourced a collator which does the right thing. If you 
can't find one but you end up writing your own, please consider 
open-sourcing it.


Hope this helps,
-Rick

On 8/20/21 6:02 AM, John English wrote:
Is there any way to ORDER BY case-insensitively if you don't know the 
column type?


I have a method in a webapp which displays tables with clickable 
column headings which sort by the clicked-on column. I give it a 
table/view name and a list of column names, and it does the rest. The 
method is completely general and knows nothing about what it is 
displaying.


My problem is that I want to sort case insensitively. I can of course 
modify the method to generate ORDER BY UPPER(x) instead of ORDER BY x, 
which will work for text columns, but for numberical columns I will 
end up sorting textually: values 1,2,10 will be sorted as 1,10,2.


Any ideas?
--
John English

Re: Difficulty enforcing a table invariant

[Rick Hillegas]

Triggers sound like the right tool for the job. I can't offer any more 
specific advice. I don't understand why item_count and 
item_location_count are hard-coded into the tables rather than being 
constructed on the fly by queries.


On 8/11/21 7:31 AM, Mark Raynsford wrote:

Hello.

I have the following tables:

---
create table cardant.items (
   item_id char(16) for bit data not null primary key,
   item_name   varchar(128)  not null,
   item_count  bigintnot null,

   constraint check_natural_count check (item_count >= 0)
)

create table cardant.locations (
   location_id   char(16) for bit data not null primary key,
   location_description  varchar(256)  not null
)

create table cardant.item_locations (
   item_location_id   char(16) for bit data not null,
   item_location_item_id  char(16) for bit data not null,
   item_location_countbigintnot null,

   constraint location_exists foreign key (item_location_id)  references 
cardant.locations (location_id),
   constraint item_exists foreign key (item_location_item_id) references 
cardant.items (item_id),
   constraint check_location_natural_count check (item_location_count >= 0)
)
---

These are tables for an inventory system. Each row of the
items table describes a collection of items, so an example
row might be ("c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", "4.7Ω Resistor", 100).

Each row of the locations table describes a storage location,
so an example row might be ("a7e0ab099d18b104dd673b01325053ba", "Box 1A").

Each row of the item_locations table associates items with
storage locations. The relationship is defined such that
items may be split between storage locations, so:

Given items:

   ("c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", "4.7Ω Resistor", 100)

... and storage locations:

   ("a7e0ab099d18b104dd673b01325053ba", "Box 1A")
   ("f45a88bb90d7cc1e492d26f7a34c3041", "Box 1B")

We might have this in the item_locations table:

   ("a7e0ab099d18b104dd673b01325053ba", "c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", 50)
   ("f45a88bb90d7cc1e492d26f7a34c3041", "c0e5b8b0d3d11ee5f5e017d7d1e7ef0e", 50)

That would indicate that 50 of the resistors are in Box 1A
and 50 are in Box 1B.

Now what I want to do is enforce the invariant that:

  for item_id in items:
sum (item_location_count) where item_location_item_id = item_id
  == item_count

In other words, for a given item ID, the sum of the items in
the storage locations should sum to the value given in the
items table. We don't want to get into a situation where the
items table says we have 100 resistors, but the items_locations
table says that there are 100 of them in Box 1A and 100 of
them in Box 1B!

The problem I'm having is that expressing this goes beyond
what can be expressed in a check constraint, which almost
certainly means expressing it as one or more triggers. The further
problem I'm then having is that it feels like there needs
to be a trigger on both the items and item_locations
tables, and neither statement or row triggers really seem
to apply; by definition I'm going to have to update multiple
rows in multiple tables each time to maintain the invariant.

Is there some better way I could be doing this? Maybe even
some other way I could be defining the tables...

Re: Self-study tutorial and permissions

[Rick Hillegas]

You can also hack around this problem by starting the server without a 
security manager (see 
https://db.apache.org/derby/docs/10.15/security/tsecnetservopen.html):


  java -jar $DERBY_HOME/lib/derbyrun.jar server start -noSecurityManager

I doubt that the Open JDK team will give this issue much attention, 
given their plan to deprecate the security manager: 
https://openjdk.java.net/jeps/411



On 8/6/21 7:52 AM, Rick Hillegas wrote:
I agree with Bryan that this looks like a platform-specific Open JDK 
bug. In your bug report, please include the version numbers for the 
Derby, Open JDK, and Ubuntu distros which you use to trigger this 
exception. It would be interesting to know if the bug turns up when 
you use the latest Open JDK 17 distro.


Thanks,
-Rick

On 8/6/21 7:22 AM, Bryan Pendleton wrote:

That's very interesting! I don't know what it means, but the stack
trace is very explicit and clear and shows us exactly where we are
getting the security exception (which, unfortunately, is deep within
the JDK runtime classes; all we are doing is calling
properties.store() here)

Could you please report this in the Derby JIRA issue tracker, so we
can collect information about the fix?
https://issues.apache.org/jira/browse/DERBY

The exact line of code in context is:
https://svn.apache.org/viewvc/db/derby/code/branches/10.15/java/org.apache.derby.engine/org/apache/derby/impl/services/monitor/StorageFactoryService.java?revision=1850575=markup#l369 



Others have had this exact same problem with JDK 11.

I suspect it may be JDK11 specific; is there any chance you can try a
different JDK version?

>From this very helpful web page at Chalmers Institute of Technology
(https://chalmers.instructure.com/courses/8915/pages/f-dot-a-q-common-issues), 


here is a simple workaround:

The solution is to edit `~/.java.policy` or
`[java.home]/lib/security/java.policy` and add the following:

grant {
 permission java.lang.RuntimePermission 
"getenv.SOURCE_DATE_EPOCH", "read";

};

Thanks again for helping to make Derby better!

bryan

On Thu, Aug 5, 2021 at 5:25 PM Art O Cathain  wrote:

Hi

Here goes...

Fri Aug 06 10:14:45 AEST 2021 : Apache Derby Network Server -
10.15.2.0 - (1873585) started and ready to accept connections on port
1527

Fri Aug 06 10:14:53 AEST 2021:
Booting Derby version The Apache Software Foundation - Apache Derby -
10.15.2.0 - (1873585): instance a816c00e-017b-18cf-ef5d-b7bad466
on database directory /home/art/src/DERBYTUTOR/seconddb with class
loader jdk.internal.loader.ClassLoaders$AppClassLoader@277050dc
Loaded from file:/home/art/src/db-derby-10.15.2.0-bin/lib/derby.jar
java.vendor=Ubuntu
user.dir=/home/art/src/DERBYTUTOR
os.name=Linux
os.arch=amd64
os.version=5.11.0-25-lowlatency
derby.system.home=/home/art/src/DERBYTUTOR
Database Class Loader started - derby.database.classpath=''

Fri Aug 06 10:14:54 AEST 2021:
Shutting down instance a816c00e-017b-18cf-ef5d-b7bad466 on
database directory /home/art/src/DERBYTUTOR/seconddb with class loader
jdk.internal.loader.ClassLoaders$AppClassLoader@277050dc
Fri Aug 06 10:14:54 AEST 2021 Thread[DRDAConnThread_2,5,main] Cleanup
action starting
ERROR XBM01: Startup failed due to an exception. See next exception 
for details.
 at 
org.apache.derby.shared.common.error.StandardException.newException(StandardException.java:300)
 at 
org.apache.derby.iapi.services.monitor.Monitor.exceptionStartingModule(Monitor.java:743)
 at 
org.apache.derby.impl.services.monitor.BaseMonitor.bootService(BaseMonitor.java:1857)
 at 
org.apache.derby.impl.services.monitor.BaseMonitor.createPersistentService(BaseMonitor.java:1022)
 at 
org.apache.derby.impl.services.monitor.FileMonitor.createPersistentService(FileMonitor.java:48)
 at 
org.apache.derby.iapi.services.monitor.Monitor.createPersistentService(Monitor.java:650)
 at 
org.apache.derby.impl.jdbc.EmbedConnection$5.run(EmbedConnection.java:4067)
 at java.base/java.security.AccessController.doPrivileged(Native 
Method)
 at 
org.apache.derby.impl.jdbc.EmbedConnection.createPersistentService(EmbedConnection.java:4061)
 at 
org.apache.derby.impl.jdbc.EmbedConnection.createDatabase(EmbedConnection.java:2654)
 at 
org.apache.derby.impl.jdbc.EmbedConnection.(EmbedConnection.java:435)
 at 
org.apache.derby.iapi.jdbc.InternalDriver$1.run(InternalDriver.java:660) 

 at 
org.apache.derby.iapi.jdbc.InternalDriver$1.run(InternalDriver.java:656) 

 at java.base/java.security.AccessController.doPrivileged(Native 
Method)
 at 
org.apache.derby.iapi.jdbc.InternalDriver.getNewEmbedConnection(InternalDriver.java:654)
 at 
org.apache.derby.iapi.jdbc.InternalDriver.connect(InternalDriver.java:300)
 at 
org.apache.derby.iapi.jdbc.InternalDriver.connect(InternalDriver.java:967)
 at 
org.apache.derby.jdbc

Re: Self-study tutorial and permissions

[Rick Hillegas]

ache.derby.impl.jdbc.EmbedConnection.createDatabase(EmbedConnection.java:2654)
 ... 15 more
= end nested exception, level (3) ===
= begin nested exception, level (4) ===
java.security.AccessControlException: access denied
("java.lang.RuntimePermission" "getenv.SOURCE_DATE_EPOCH")
 at 
java.base/java.security.AccessControlContext.checkPermission(AccessControlContext.java:472)
 at 
java.base/java.security.AccessController.checkPermission(AccessController.java:897)
 at 
java.base/java.lang.SecurityManager.checkPermission(SecurityManager.java:322)
 at java.base/java.lang.System.getenv(System.java:999)
 at 
java.base/java.util.Properties.getFormattedTimestamp(Properties.java:1590)
 at java.base/java.util.Properties.store0(Properties.java:932)
 at java.base/java.util.Properties.store(Properties.java:921)
 at 
org.apache.derby.impl.services.monitor.StorageFactoryService$5.run(StorageFactoryService.java:369)
 at java.base/java.security.AccessController.doPrivileged(Native Method)
 at 
org.apache.derby.impl.services.monitor.StorageFactoryService.saveServiceProperties(StorageFactoryService.java:347)
 at 
org.apache.derby.impl.services.monitor.BaseMonitor.bootService(BaseMonitor.java:1840)
 at 
org.apache.derby.impl.services.monitor.BaseMonitor.createPersistentService(BaseMonitor.java:1022)
 at 
org.apache.derby.impl.services.monitor.FileMonitor.createPersistentService(FileMonitor.java:48)
 at 
org.apache.derby.iapi.services.monitor.Monitor.createPersistentService(Monitor.java:650)
 at 
org.apache.derby.impl.jdbc.EmbedConnection$5.run(EmbedConnection.java:4067)
 at java.base/java.security.AccessController.doPrivileged(Native Method)
 at 
org.apache.derby.impl.jdbc.EmbedConnection.createPersistentService(EmbedConnection.java:4061)
 at 
org.apache.derby.impl.jdbc.EmbedConnection.createDatabase(EmbedConnection.java:2654)
 at 
org.apache.derby.impl.jdbc.EmbedConnection.(EmbedConnection.java:435)
 at org.apache.derby.iapi.jdbc.InternalDriver$1.run(InternalDriver.java:660)
 at org.apache.derby.iapi.jdbc.InternalDriver$1.run(InternalDriver.java:656)
 at java.base/java.security.AccessController.doPrivileged(Native Method)
 at 
org.apache.derby.iapi.jdbc.InternalDriver.getNewEmbedConnection(InternalDriver.java:654)
 at 
org.apache.derby.iapi.jdbc.InternalDriver.connect(InternalDriver.java:300)
 at 
org.apache.derby.iapi.jdbc.InternalDriver.connect(InternalDriver.java:967)
 at org.apache.derby.jdbc.EmbeddedDriver.connect(EmbeddedDriver.java:125)
 at org.apache.derby.impl.drda.Database.makeConnection(Database.java:253)
 at 
org.apache.derby.impl.drda.DRDAConnThread.getConnFromDatabaseName(DRDAConnThread.java:1476)
 at 
org.apache.derby.impl.drda.DRDAConnThread.verifyUserIdPassword(DRDAConnThread.java:1426)
 at 
org.apache.derby.impl.drda.DRDAConnThread.parseSECCHK(DRDAConnThread.java:3380)
 at 
org.apache.derby.impl.drda.DRDAConnThread.parseDRDAConnection(DRDAConnThread.java:1210)
 at 
org.apache.derby.impl.drda.DRDAConnThread.processCommands(DRDAConnThread.java:1010)
 at org.apache.derby.impl.drda.DRDAConnThread.run(DRDAConnThread.java:300)
= end nested exception, level (4) ===
Cleanup action completed
Fri Aug 06 10:14:54 AEST 2021 Thread[DRDAConnThread_2,5,main]
(DATABASE = seconddb), (DRDAID = {1}), Failed to create database
'seconddb', see the next exception for details.
Fri Aug 06 10:14:54 AEST 2021 Thread[DRDAConnThread_2,5,main]
(DATABASE = seconddb), (DRDAID = {1}), Startup failed due to an
exception. See next exception for details.
Fri Aug 06 10:14:54 AEST 2021 Thread[DRDAConnThread_2,5,main]
(DATABASE = seconddb), (DRDAID = {1}), Java exception: 'access denied
("java.lang.RuntimePermission" "getenv.SOURCE_DATE_EPOCH"):
java.security.AccessControlException'.

On Fri, 6 Aug 2021 at 02:55, Rick Hillegas  wrote:

That does look like the problem discussed in the email thread you cited:
http://apache-database.10148.n7.nabble.com/Derby-newbie-error-seen-creating-seconddb-thru-ij-for-network-server-Derby-td150684.html

Can you post the stack trace?

Thanks,
-Rick

On 8/5/21 7:51 AM, Art O Cathain wrote:

Hi

There is a very long stack trace, and the last few lines of the log file are

Cleanup action completed
Fri Aug 06 00:47:55 AEST 2021 Thread[DRDAConnThread_2,5,main]
(DATABASE = seconddb), (DRDAID = {1}), Failed to create database
'seconddb', see the next exception for details.
Fri Aug 06 00:47:55 AEST 2021 Thread[DRDAConnThread_2,5,main]
(DATABASE = seconddb), (DRDAID = {1}), Startup failed due to an
exception. See next exception for details.
Fri Aug 06 00:47:55 AEST 2021 Thread[DRDAConnThread_2,5,main]
(DATABASE = seconddb), (DRDAID = {1}), Java exception: 'access denied
("java.lang.RuntimePermission" "getenv.SOURCE_DATE_EPOCH"):
java.security.AccessContr

Re: Self-study tutorial and permissions

[Rick Hillegas]

That does look like the problem discussed in the email thread you cited: 
http://apache-database.10148.n7.nabble.com/Derby-newbie-error-seen-creating-seconddb-thru-ij-for-network-server-Derby-td150684.html


Can you post the stack trace?

Thanks,
-Rick

On 8/5/21 7:51 AM, Art O Cathain wrote:

Hi

There is a very long stack trace, and the last few lines of the log file are

Cleanup action completed
Fri Aug 06 00:47:55 AEST 2021 Thread[DRDAConnThread_2,5,main]
(DATABASE = seconddb), (DRDAID = {1}), Failed to create database
'seconddb', see the next exception for details.
Fri Aug 06 00:47:55 AEST 2021 Thread[DRDAConnThread_2,5,main]
(DATABASE = seconddb), (DRDAID = {1}), Startup failed due to an
exception. See next exception for details.
Fri Aug 06 00:47:55 AEST 2021 Thread[DRDAConnThread_2,5,main]
(DATABASE = seconddb), (DRDAID = {1}), Java exception: 'access denied
("java.lang.RuntimePermission" "getenv.SOURCE_DATE_EPOCH"):
java.security.AccessControlException'.

I'll post the stack trace too if you need it.

Thanks

Art

On Fri, 6 Aug 2021 at 00:36, Rick Hillegas  wrote:

Using Derby 10.15.2.0 and Open JDK 11 (build 11+28) on Mac OSX 11.2.3, I
get good, expected results from activity 2 at
https://db.apache.org/derby/docs/10.15/getstart/twwdactivity2.html

Can you tell us what you see in derby.log when you run activity 2 and
start up the server with the following additional system properties:

-Dderby.language.logStatementText=true
-Dderby.stream.error.logSeverityLevel=0

Thanks,
-Rick


On 8/4/21 7:49 PM, Art O Cathain wrote:

I've followed the self-study tutorial in the documentation. I'm
impressed by the emphasis on getting your environment set up
correctly, clearly something that beginners would struggle with.
In Activity 2, you get the following error:

Failed to create database 'seconddb', see the next exception for details

This is with Derby 10.15 and Java 11 on Ubuntu. I also tried 10.14
with the same results.

The error can be fixed if you start the server with a permissive
security policy, for example adding the following to the startup
command

-Djava.security.manager -Djava.security.policy=<(echo 'grant {
permission java.security.AllPermission; };')

Interestingly, once the database has been created, you can restart
Derby without that, and it appears to work. So it might be related to
filesystem creation permissions.

Should the documentation be updated to add the extra argument, or is
there a bug here?

Art

PS Someone else had this problem too and some solutions were
suggested: 
http://apache-database.10148.n7.nabble.com/Derby-newbie-error-seen-creating-seconddb-thru-ij-for-network-server-Derby-td150684.html
- but I think someone following a getting started guide shouldn't have
to delve into the details of security permissions.

Re: Self-referential foreign key

[Rick Hillegas]

You're welcome to log an issue. Thanks.

On 6/28/21 6:30 AM, John English wrote:

On 23/03/2021 17:06, Rick Hillegas wrote:
This may help: 
https://stackoverflow.com/questions/29297043/on-delete-set-null-on-self-referencing-relationship 



I solved this with triggers, as suggested. Problem is that the column 
can no longer be a foreign key.


But the other day I was playing around and found that this seems to 
work without any problems in other DBMSs -- even the appalling MySQL!


Any change that someone will look at implementing this in Derby for 
some forthcoming version?



On 3/23/21 6:09 AM, John English wrote:
I have a table in which I want to include a self-referential foreign 
key to the same table:


  CREATE TABLE x (
    id  INTEGER  GENERATED ALWAYS AS IDENTITY,
    idY INTEGER,
    idX INTEGER DEFAULT NULL,
    CONSTRAINT x_pk   PRIMARY KEY (id),
    CONSTRAINT x_1    FOREIGN KEY (idY)
  REFERENCES y(id)
  ON DELETE CASCADE,
    CONSTRAINT x_2    FOREIGN KEY (idX)
  REFERENCES x(id)
  ON DELETE SET NULL
  );

When I try this I get the following error:

Error: Foreign  Key 'X_2' is invalid because 'The delete rule of 
foreign key must be CASCADE. (The referential constraint is 
self-referencing and the table is dependent in a relationship with a 
delete rule of CASCADE.)'.

SQLState:  42915
ErrorCode: 3

My intention is that when rows from table Y are deleted, 
corresponding rows from X are also deleted, but when rows from X are 
deleted, the references in any referencing rows in X are just set to 
NULL.


I don't understand exactly what I'm doing wrong here. How can I 
rewrite the table definition to do what I want?


Thanks,

Re: Derby Encryption Verification

[Rick Hillegas]

Look in the service.properties file in the top level of the database 
directory tree. You should see several encryption properties with names 
like the following:


  encryptionKeyLength
  encryptionAlgorithm
  derby.encryptionBlockSize
  encryptedBootPassword
  data_encrypt_algorithm_version
  dataEncryption


On 6/17/21 3:09 PM, Ed wrote:

Hi,

Is there any way to verify data encryption in the seg0 directory.

I have cat'ed a couple of the seg names and there appears to be plain text
being shown.

I just want to make sure the data is encrypted.

Thank you very much,

Best

Ed

Re: DBSeeder Version 2.9.0

[Rick Hillegas]

On 6/5/21 2:18 AM, Walter Weinmann wrote:

We are happy to announce that version 2.9.0 of DBSeeder (
https://github.com/KonnexionsGmbH/db_seeder) is now available for general
use. DBSeeder also supports the latest version of Apache Derby (10.15.2.0).


Thanks, Walter. This looks like a very useful tool.

Re: Index Corruption

[Rick Hillegas]

Please see my responses on DERBY-7113 and continue the discussion there 
if necessary. Thanks.


On 4/16/21 1:22 AM, Geraldine McCormack wrote:


Hi Folks - I have opened this new issue and am wondering if others have
seen it, and if there are any mitigations to prevent this from happening :
https://issues.apache.org/jira/browse/DERBY-7113

>From time to time an Index keeps a value, which has been deleted from the
underlying table. This results in an error is the row with the value is
selected.

SELECT ID from Some.TABLE WHERE IndexedColumn= 'Remembered Value';
ID

1680880176
ERROR 0A000: The DRDA command parseSQLDIAGSTT is not currently implemented.
The connection has been terminated.

  If only the Indexed Column is selected, then the Query returns 0 rows and
produces no error - because just the index is searched.

The Query Plan shows the index being used.

Dropping and Recreating the index fixes the issue. However, I can reproduce
this by reloading a database backup.

Any advice appreciated,
Kind Regards,
Geraldine

---


Geraldine McCormack
L3 Team Lead - TBSM & Netcool/Impact
IBM Ireland

Visit the IBM Support Portal to open a case, download fixes, access product
documentation, education and training materials.

Visit our devcenters (applications and operations) for blogs, docs and
resources brought to you by the technical people who create our offerings.

--

Re: Self-referential foreign key

[Rick Hillegas]

This may help: 
https://stackoverflow.com/questions/29297043/on-delete-set-null-on-self-referencing-relationship


On 3/23/21 6:09 AM, John English wrote:
I have a table in which I want to include a self-referential foreign 
key to the same table:


  CREATE TABLE x (
    id  INTEGER  GENERATED ALWAYS AS IDENTITY,
    idY INTEGER,
    idX INTEGER DEFAULT NULL,
    CONSTRAINT x_pk   PRIMARY KEY (id),
    CONSTRAINT x_1    FOREIGN KEY (idY)
  REFERENCES y(id)
  ON DELETE CASCADE,
    CONSTRAINT x_2    FOREIGN KEY (idX)
  REFERENCES x(id)
  ON DELETE SET NULL
  );

When I try this I get the following error:

Error: Foreign  Key 'X_2' is invalid because 'The delete rule of 
foreign key must be CASCADE. (The referential constraint is 
self-referencing and the table is dependent in a relationship with a 
delete rule of CASCADE.)'.

SQLState:  42915
ErrorCode: 3

My intention is that when rows from table Y are deleted, corresponding 
rows from X are also deleted, but when rows from X are deleted, the 
references in any referencing rows in X are just set to NULL.


I don't understand exactly what I'm doing wrong here. How can I 
rewrite the table definition to do what I want?


Thanks,

Re: Embedded database, authentication, and derby.system.home

[Rick Hillegas]

The ij CONNECT command issues a DriverManager.getConnection(jdbcURL) 
command. What does your connection logic look like?


Thanks,
-Rick

On 3/23/21 4:59 AM, Michael Remijan wrote:

I'll see if I can reproduce it with ij, but I'm not sure. The error is with the 
Java client connecting to and starting an embedded database. There's no SQL at 
play since it's the connecting and starting that's failing.  If I can reproduce 
it with ij I'll let you know.

-Original Message-
From: Rick Hillegas 
Sent: Monday, March 22, 2021 1:09 PM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home

Sorry. I need SQL scripts. I can't make sense of a compiled application
with no source code.

On 3/22/21 7:05 AM, Michael Remijan wrote:

Hi Rick,

Here is a working Java example for you. It's fully self-contained with its own JRE so all 
you need do is download it and run it. This bin distribution is for Windows, so sorry if 
you're on another platform.  It's too big for email, so I have it on my OneDrive 
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2F1drv.ms%2Fu%2Fs!Ao0GHBDYRFEMgbMss-5a_R3q1GOvBQ%3Fe%3DnpfMBddata=04%7C01%7C%7Cc6fb22f26340407b616708d8ed5d8d2e%7C84df9e7fe9f640afb435%7C1%7C0%7C63752038352293%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000sdata=jBxtsRB9m9TvwKtQyrIrTFjajbrz%2FUTDqXbECfFuhD4%3Dreserved=0

First, download, unzip, then run /bin/start-resiste.bat. You'll see the 
following:
[cid:image001.png@01D71EF8.C4AFB090]


Next, m move the /data directory to a different location and then update 
/conf/db.properties.
[cid:image002.png@01D71EF9.233513D0]


Next, run /bin/start-resiste.bat again. You'll see the following error:
[cid:image003.png@01D71EF9.233513D0]


Next, update /bin/start-resiste.bat to make sure derby.system.home is in sync 
with the new location of the database:
[cid:image004.png@01D71EF9.3A7B5450]

Next, run /bin/start-resiste.bat again. You'll see it's now successful with 
/conf/db.properties and /bin/start-resiste.bat in sync
[cid:image001.png@01D71EF8.C4AFB090]


Now here is a big Caveat. I discovered this while getting this demo together. I 
have Derby 10.14.2.0 installed on my computer. For my first attempt at this 
demo, I created the database from scratch using 10.14.2.0. When I did this, I 
DID NOT experience the connection error as described above. However, when I use 
my original database, I DO experience the error as demonstrated above. 
Unfortunately, I do not remember which version of Derby I used to create the 
original database, but obviously it was an older version. So perhaps this has 
something to do with an older database version?

Mike





From: Rick Hillegas 
Sent: Friday, March 19, 2021 4:04 PM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home

Hi Michael,

I must be missing something in your description of the problem, because I 
cannot reproduce it. I have created the following scripts in order to reproduce 
the behavior of both changing the value of derby.system.home and moving the 
database to another directory:

First SQL script nativeTest.sql:

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';



CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );



-- shutdown in order to enable NATIVE authentication

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';



CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';



VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');



CREATE TABLE t(a INT);

INSERT INTO t VALUES (1), (2);


Second SQL script nativeTest2.sql:

CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';



SELECT * FROM t;


Then a shell script (z1) to run nativeTest.sql, move the database to another 
directory, then run nativeTest2.sql:

#! /bin/bash

#

# Run a derby test



rm -rf z/db1

rm -rf zz/db1

rm zzz/*

rm /*



. setupClasspath



java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz 
-Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 
org.apache.derby.tools.ij  $MAC_HOME/sql/nativeTest.sql



mv z/db1 zz/



java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/ 
-Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 
org.apache.derby.tools.ij  $MAC_HOME/sql/nativeTest2.sql


When I run z1, I get the following output:

ij version 10.16

ij> CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';

ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );

0 rows inserted/updated/deleted

ij> -- shutdown in order to enable NATIVE authentication

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';

ERROR 08006: Database '/Users/rhille

Re: Embedded database, authentication, and derby.system.home

[Rick Hillegas]

Sorry. I need SQL scripts. I can't make sense of a compiled application 
with no source code.


On 3/22/21 7:05 AM, Michael Remijan wrote:

Hi Rick,

Here is a working Java example for you. It’s fully self-contained with its own 
JRE so all you need do is download it and run it. This bin distribution is for 
Windows, so sorry if you’re on another platform.  It’s too big for email, so I 
have it on my OneDrive 
https://1drv.ms/u/s!Ao0GHBDYRFEMgbMss-5a_R3q1GOvBQ?e=npfMBd

First, download, unzip, then run /bin/start-resiste.bat. You’ll see the 
following:
[cid:image001.png@01D71EF8.C4AFB090]


Next, m move the /data directory to a different location and then update 
/conf/db.properties.
[cid:image002.png@01D71EF9.233513D0]


Next, run /bin/start-resiste.bat again. You’ll see the following error:
[cid:image003.png@01D71EF9.233513D0]


Next, update /bin/start-resiste.bat to make sure derby.system.home is in sync 
with the new location of the database:
[cid:image004.png@01D71EF9.3A7B5450]

Next, run /bin/start-resiste.bat again. You’ll see it’s now successful with 
/conf/db.properties and /bin/start-resiste.bat in sync
[cid:image001.png@01D71EF8.C4AFB090]


Now here is a big Caveat. I discovered this while getting this demo together. I 
have Derby 10.14.2.0 installed on my computer. For my first attempt at this 
demo, I created the database from scratch using 10.14.2.0. When I did this, I 
DID NOT experience the connection error as described above. However, when I use 
my original database, I DO experience the error as demonstrated above. 
Unfortunately, I do not remember which version of Derby I used to create the 
original database, but obviously it was an older version. So perhaps this has 
something to do with an older database version?

Mike





From: Rick Hillegas 
Sent: Friday, March 19, 2021 4:04 PM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home

Hi Michael,

I must be missing something in your description of the problem, because I 
cannot reproduce it. I have created the following scripts in order to reproduce 
the behavior of both changing the value of derby.system.home and moving the 
database to another directory:

First SQL script nativeTest.sql:

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';



CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );



-- shutdown in order to enable NATIVE authentication

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';



CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';



VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');



CREATE TABLE t(a INT);

INSERT INTO t VALUES (1), (2);


Second SQL script nativeTest2.sql:

CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';



SELECT * FROM t;


Then a shell script (z1) to run nativeTest.sql, move the database to another 
directory, then run nativeTest2.sql:

#! /bin/bash

#

# Run a derby test



rm -rf z/db1

rm -rf zz/db1

rm zzz/*

rm /*



. setupClasspath



java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz 
-Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 
org.apache.derby.tools.ij  $MAC_HOME/sql/nativeTest.sql



mv z/db1 zz/



java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/ 
-Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 
org.apache.derby.tools.ij  $MAC_HOME/sql/nativeTest2.sql


When I run z1, I get the following output:

ij version 10.16

ij> CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';

ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );

0 rows inserted/updated/deleted

ij> -- shutdown in order to enable NATIVE authentication

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';

ERROR 08006: Database '/Users/rhillegas/derby/mainline/z/db1' shutdown.

ij> CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';

ij(CONNECTION1)> VALUES 
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');

1



NATIVE::LOCAL



1 row selected

ij(CONNECTION1)> CREATE TABLE t(a INT);

0 rows inserted/updated/deleted

ij(CONNECTION1)> INSERT INTO t VALUES (1), (2);

2 rows inserted/updated/deleted

ij(CONNECTION1)> ij version 10.16

ij> CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';

ij> SELECT * FROM t;

A

---

1

2



2 rows selected


Can you post a similar set of scripts which demonstrate the problem you are 
seeing?

Thanks,
-Rick

On 3/19/21 11:14 AM, Michael Remijan wrote:

NATIVE.



After creating the database I, I set the 's

Re: Embedded database, authentication, and derby.system.home

[Rick Hillegas]

Hi Michael,

I must be missing something in your description of the problem, because 
I cannot reproduce it. I have created the following scripts in order to 
reproduce the behavior of both changing the value of derby.system.home 
and moving the database to another directory:


First SQL script nativeTest.sql:

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';

CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );

-- shutdown in order to enable NATIVE authentication

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';

CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';

VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');

CREATE TABLE t(a INT);

INSERT INTO t VALUES (1), (2);



Second SQL script nativeTest2.sql:

CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';

SELECT * FROM t;



Then a shell script (z1) to run nativeTest.sql, move the database to 
another directory, then run nativeTest2.sql:


#! /bin/bash

#

# Run a derby test

rm -rf z/db1

rm -rf zz/db1

rm zzz/*

rm /*

. setupClasspath

java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz 
-Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 
org.apache.derby.tools.ij  $MAC_HOME/sql/nativeTest.sql

mv z/db1 zz/

java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/ 
-Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 
org.apache.derby.tools.ij  $MAC_HOME/sql/nativeTest2.sql



When I run z1, I get the following output:

ij version 10.16

ij> CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';

ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );

0 rows inserted/updated/deleted

ij> -- shutdown in order to enable NATIVE authentication

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';

ERROR 08006: Database '/Users/rhillegas/derby/mainline/z/db1' shutdown.

ij> CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';

ij(CONNECTION1)> VALUES 
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');

1



NATIVE::LOCAL

1 row selected

ij(CONNECTION1)> CREATE TABLE t(a INT);

0 rows inserted/updated/deleted

ij(CONNECTION1)> INSERT INTO t VALUES (1), (2);

2 rows inserted/updated/deleted

ij(CONNECTION1)> ij version 10.16

ij> CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';

ij> SELECT * FROM t;

A

---

1

2

2 rows selected



Can you post a similar set of scripts which demonstrate the problem you 
are seeing?


Thanks,
-Rick


On 3/19/21 11:14 AM, Michael Remijan wrote:

NATIVE.

After creating the database I, I set the 'sa' user like this:

ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa', 'xxx');

Then I create the application user like this:

CALL SYSCS_UTIL.SYSCS_CREATE_USER('resiste_standalone', 'xxx');

And I have the following in derby.properties

# Passwords don't expire for 20 years
derby.authentication.native.passwordLifetimeMillis=63113852
# Use the best hash algorithm you can
derby.authentication.builtin.algorithm=SHA-512
# Use a larger salt length for better security
derby.authentication.builtin.saltLength=128
# Re-hash this number of times for better security
derby.authentication.builtin.iterations=1564

BTW, I've updated code to dynamically 
System.setProperty("derby.system.home","/path/to/dir"); at runtime based off of 
user configuration. That been successful. So if I move the database to somewhere other than my 
application's default location, I set derby.system.home at runtime based on that configuration.

I also found that this is only a factor with embedded databases where the path 
to the database is a fully qualified file system path. If the configuration 
uses a remote database, the value for derby.system.home doesn't seem to matter.

-Original Message-
From: Rick Hillegas 
Sent: Friday, March 19, 2021 11:22 AM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home

Hi Mike,

What kind of authentication are you using: LDAP, NATIVE, or custom?

On 3/19/21 7:05 AM, Michael Remijan wrote:

Greetings,

I have an interesting issue I just ran into and it took a little while to debug 
and figure out exactly what is happening.

I have a project that uses an embedded Derby database.  My project folder 
organization is a typical for an open source project:

/projectname
 /bin
 start.sh   // starts the application
/data
   /projectdb  // this is the derby database.

My projectdb database also has a specific username, p

Re: Embedded database, authentication, and derby.system.home

[Rick Hillegas]

Hi Mike,

What kind of authentication are you using: LDAP, NATIVE, or custom?

On 3/19/21 7:05 AM, Michael Remijan wrote:

Greetings,

I have an interesting issue I just ran into and it took a little while to debug 
and figure out exactly what is happening.

I have a project that uses an embedded Derby database.  My project folder 
organization is a typical for an open source project:

/projectname
/bin
start.sh   // starts the application
   /data
  /projectdb  // this is the derby database.

My projectdb database also has a specific username, password, and 
permissions/roles on that user.

When I start the application, the "start.sh" script sets the -Dderby.system.home to the 
"/data" directory and my JDBC connection URL figures out the fully qualified location of the 
"/projectdb" directory so Derby can easily access it. This all works perfectly fine.

Then I wanted to run a test where I moved the /projectdb directory to a 
different location. I though  I would be able to do this with no problems. But 
I was wrong :( When I do this I get the following authentication exception

Caused by: java.sql.SQLNonTransientConnectionException: Connection 
authentication failure occurred.  Reason: Invalid authentication..
 at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
 at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
 at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown 
Source)
 at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown 
Source)
 at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown 
Source)
 at 
org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)
 at org.apache.derby.impl.jdbc.EmbedConnection.(Unknown Source)
 at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
 at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)
 at java.security.AccessController.doPrivileged(Native Method)
 at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown 
Source)
 at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
 at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
 at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
 at java.sql.DriverManager.getConnection(DriverManager.java:664)
 at java.sql.DriverManager.getConnection(DriverManager.java:247)
 at 
org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)
 ... 67 more
Caused by: ERROR 08004: Connection authentication failure occurred.  Reason: 
Invalid authentication..
 at org.apache.derby.iapi.error.StandardException.newException(Unknown 
Source)
 at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown
 Source)
 ... 84 more

Which I thought was kind of odd. What I eventually discovered is the 
-Dderby.system.home value and the location of the database must be in sync. If 
they are not, I get the authentication exception. If do not set the 
-Dderby.system.home value, I get this authentication exception.

Originally, I remember setting the -Dderby.system.home value because I wanted 
the derby.log files created in that directory. I didn't expect this value would 
also be critical for connecting to the database with a username/password.  I 
haven't tried it unauthenticated, but it seems to me I should be able to move 
the location of the database around and as long as I'm connecting to the 
database, authentication should work OK regardless of -Dderby.system.home and 
the location of the database being in sync.  This make sense?

Thanks!
Mike

Re: How to disable detailed logging/buffer dumps?

[Rick Hillegas]

These look like network trace messages. Has someone set the following 
system property:


  derby.drda.traceAll=true

See the section on drda properties in the Admin Guide: 
https://db.apache.org/derby/docs/10.15/adminguide/tadminconfigsettingnetwrokserverproperties.html


On 3/9/21 10:50 PM, Vamsavardhana Reddy wrote:

Hi,

Off late we started seeing detailed logging/buffer dumps like the below in
the derby logs.  Any idea how to disable this level of logging or how this
logging could have got enabled?

-
[derby][Time:1614982562801][Thread:pool-10-thread-1][Connection@100f5a06]
createStatement () called
[derby][Time:1614982562801][Thread:pool-10-thread-1][Connection@100f5a06]
createStatement () returned Statement@f4937dd3
[derby][Time:1614982562801][Thread:pool-10-thread-1][Statement@f4937dd3]
executeQuery (select 1 from sysibm.sysdummy1) called
[derby][Time:1614982562801][Thread:pool-10-thread-1][Connection@100f5a06]
isClosed () returned false
[derby]
[net][time:1614982562801][thread:pool-10-thread-1][tracepoint:1][Request.flush]
[derby]SEND BUFFER: PRPSQLSTT  (ASCII)
(EBCDIC)
[derby]0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF
  0123456789ABCDEF
[derby]    0079D05100010073  200D00652113002D  .y.Q...s ..e!..-
  .`}.
[derby] 0010   2F7573722F69726F  6E686964652F7661  /usr/ironhide/va
  ...?>../
[derby] 0020   722F64622F6F7263  6D6F6E2F64657262  r/db/orcmon/derb
  .?.._?>.
[derby] 0030   793B637265617465  3D7472756500124E  y;create=true..N
  `/.+
[derby] 0040   554C4C4944202020  2020202020202020  ULLID
.<<.
[derby] 0050   2000125359534C48  3030302020202020   ..SYSLH000
..<.
[derby] 0060   2020202020535953  4C564C3031000B00   SYSLVL01...
  <.<.
[derby] 0070   052116F100052146  04.!!F.
...1.
[derby]
[derby]SEND BUFFER:(ASCII)
(EBCDIC)
[derby]    001AD05300010014  24500A57  ...S$P.W
  ..}..&..
[derby] 0010   49544820484F4C44  20FF002ED0430001  ITH HOLD C..
  .|<.}...
[derby] 0020   00282414  1E73656C65637420  .($..select
...%
[derby] 0030   312066726F6D2073  797369626D2E7379  1 from sysibm.sy
  ?_..`..._..`
[derby] 0040   7364756D6D7931FF  007CD00100020076  sdummy1..|.v
  ...__`...@}.
[derby] 0050   200C00652113002D  2F7573722F69726F   ..e!..-/usr/iro
  ...?
[derby] 0060   6E686964652F7661  722F64622F6F7263  nhide/var/db/orc
  >../.?..
[derby] 0070   6D6F6E2F64657262  793B637265617465  mon/derby;create
  _?>.`/..
[derby] 0080   3D7472756500124E  554C4C4944202020  =true..NULLID
...+.<<.
[derby] 0090   2020202020202020  2000125359534C48   ..SYSLH
  ..<.
[derby] 00A0   3030302020202020  2020202020535953  000  SYS
  
[derby] 00B0   4C564C3031000B00  0821147FFF00  LVL01!..
  <.<.."..
[derby] 00C0   05215D01.!].  ..).

[derby]
[derby]
[net][time:1614982562801][thread:pool-10-thread-1][tracepoint:2][Reply.fill]
[derby]RECEIVE BUFFER: SQLDARD (ASCII)
(EBCDIC)
[derby]0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF
  0123456789ABCDEF
[derby]    0099D04300010093  24110020  ...C$..
.r}l
[derby] 0010   2020202043535331  30303831  CSS10081
  
[derby] 0020       
  
[derby] 0030   00202020  2020202020202020  .

[derby] 0040   FF00  0001  
  
[derby] 0050     0001000A  
  
[derby] 0060   0004  01F0  
  .0..
[derby] 0070   0131  FF00  .1..
  
[derby] 0080       
  
[derby] 0090   00013100  00..1..
.
[derby]
[derby]RECEIVE BUFFER: OPNQRYRM(ASCII)
(EBCDIC)
[derby]    002CD05200020026  220500061149  .,.R...&"I..
  ..}.
[derby] 0010   0006210224170005  211FF1000C215B00  ..!.$...!![.
  ..1...$.
[derby] 0020   E600  05215001  .!P.
  ..W...&.
[derby]
[derby]RECEIVE BUFFER: QRYDSC  (ASCII)
(EBCDIC)
[derby]    001FD05300020019  241A0676D0020004  ...S$..v
  ..}.}...
[derby] 0010   0971E0540001D000  010671F0E0.q.T..q
..\...}0\..
[derby]
[derby]RECEIVE BUFFER: QRYDTA  (ASCII)
(EBCDIC)
[derby]    004ED00300020048  241BFF01  .N.H$...
  .+}.
[derby] 0010   64303230  3030435353313030  d02000CSS100
  
[derby] 0020   

Re: dropping index dead lock

[Rick Hillegas]

Derby DDL is a heavy-weight operation. CREATE/DROP statements invalidate 
the in-memory metadata cache, effectively seizing a database-wide lock 
on the cache. Applications which perform a lot of DDL at steady-state 
will perform badly.


I don't have any better advice than to recommend that you avoid DDL at 
steady-state.


On 2/15/21 10:14 AM, Alex O'Ree wrote:

I've got a process that creates a bunch of temporary indexes, then does
some work, then drops the indexes. All of these indexes are on different
tables but in the same database. During the drop the indexes phase, i
frequently get dead lock warning messages, each drop index statement seems
to be blocking the others. Strangely enough, i'm not running the drop index
statement in parallel. It's all on the same thread linearly.

I'm not super sure what's going on but i'm guessing drop index is creating
a database level lock. Is that the case? Regardless, is there anything else
i can do to mitigate against this?

Re: Out-of-memory errors

[Rick Hillegas]

It looks like the subquery isn't doing any good. I would have hoped that 
the optimizer would be smart enough to join the subquery result to the 
primary key on id and then use the resulting row positions to probe into 
the heap. But the plan is just joining the subquery result to a full 
table scan. I don't understand why the subquery plan is so convoluted. 
However, it looks like you eliminated the memory-exhausting sort.


On 2/15/21 8:49 AM, John English wrote:

On 09/02/2021 18:05, Rick Hillegas wrote:
As Bryan points out, please consult the Tuning Guide for information 
on how to view your query plan. In any event, your descending index 
is not a covering index. That is, it does not contain all of the 
columns in your query. That may be what forces the optimizer to 
choose a full table scan.


From "Tuning Derby" 
(https://db.apache.org/derby/docs/10.8/tuning/ctunoptimz56859.html):
Usually, sorting requires an extra step to put the data into the right 
order. This extra step can be avoided for data that are already in the 
right order. For example, if a single-table query has an ORDER BY on a 
single column, and there is an index on that column, sorting can be 
avoided if Derby uses the index as the access path.


This seems to be my situation: I am selecting from (a view into) a 
single table with an ORDER BY on a single column, and there is an 
index on that column.


The timing I reported earlier (using your suggested nested query) 
prompted me to investigate further. I executed it with runtime 
statistics enabled, and below is what was reported. I cannot make head 
nor tail of what is happening here, but it looks incredibly complicated.


Statement Name:
null
Statement Text:
SELECT time as t_time,
 facility,event,details,name,username,sector,item
  FROM system_log
  WHERE id IN
  (
    SELECT id
    FROM system_log
    ORDER BY time DESC
    NULLS LAST
    FETCH NEXT 20 ROWS ONLY
  )
  ORDER BY t_time
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Scroll Insensitive ResultSet:
Number of opens = 1
Rows seen = 20
Number of reads from hash table = 20
Number of writes to hash table = 20
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 261251.00
optimizer estimated cost: 268953.12
Source result set:
Sort ResultSet:
Number of opens = 1
Rows input = 20
Rows returned = 20
Eliminate duplicates = false
In sorted order = false
Sort information:
    Number of merge runs=1
    Number of rows input=20
    Number of rows output=20
    Size of merge runs=[14]
    Sort type=external
    constructor time (milliseconds) = 0
    open time (milliseconds) = 0
    next time (milliseconds) = 0
    close time (milliseconds) = 0
    optimizer estimated row count: 261251.00
    optimizer estimated cost: 268953.12
Source result set:
    Attached subqueries:
    Begin Subquery Number 0
    Any ResultSet  (Attached to 2):
    Number of opens = 261296
    Rows seen = 261296
    constructor time (milliseconds) = 0
    open time (milliseconds) = 0
    next time (milliseconds) = 0
    close time (milliseconds) = 0
    optimizer estimated row count: 68252085001.00
    optimizer estimated cost: 70264271553.12
    Source result set:
    Project-Restrict ResultSet (7):
    Number of opens = 261296
    Rows seen = 5225730
    Rows filtered = 5225710
    restriction = true
    projection = true
    constructor time (milliseconds) = 0
    open time (milliseconds) = 0
    next time (milliseconds) = 0
    close time (milliseconds) = 0
    restriction time (milliseconds) = 0
    projection time (milliseconds) = 0
    optimizer estimated row count: 68252085001.00
    optimizer estimated cost: 70264271553.12
    Source result set:
    Union ResultSet:
    Number of opens = 261296
    Rows seen from the left = 4964453
    Rows seen from the right = 261277
    Rows returned = 5225730
    constructor time (milliseconds) = 0
    open time (milliseconds) = 0
    next time (milliseconds) = 0
    close time (milliseconds) = 0
    optimizer estimated row count: 20.00
    optimizer estimated 

Re: Slow mount times

[Rick Hillegas]

The Derby engine has some trivial mxbeans which return simple, static 
facts--but nothing which would give you visibility into the progress of 
boot-time recovery.


On 2/15/21 10:15 AM, Alex O'Ree wrote:

Upon further investigation, I've seen if there are more than 4 files in the
log directory, mounting takes significantly longer but i've also seen 20+
files mount instantly. I had the idea of monitoring that directory to see
if the file count changes during the recovery process. It does, but
sometimes the count goes up, other times it goes down or does not change
until shutdown.. I was hoping to get a notice to the user of something like
30% complete or a progress bar kind of thing. So no luck there.

Is this information exposed as a mxbean by chance?  Even with cranking up
the logging for derby, there doesn't appear to be any indication of what's
going on.



On Sun, Feb 7, 2021 at 2:12 PM Alex O'Ree  wrote:


That's perfect, although from my testing, it seems like more than 4 files
indicate an unclean shutdown. I just want to alert the user that it may
take some time to fire up the database connection

On Sun, Feb 7, 2021 at 10:24 AM Peter Ondruška 
wrote:


There is very easy way to check if you are going to run database recovery
at boot time by looking into logs subfolder. If you gracefully shut down,
there are only two log files. If there are more your database will perform
roll forward recovery, in worst case applying all the log files.



*From:* Rick Hillegas 
*Sent:* Sunday, February 7, 2021 4:11 PM
*To:* Derby Discussion ; Alex O'Ree <
alexo...@apache.org>
*Subject:* Re: Slow mount times



I don't know of any special trace flags for this. Maybe something will
turn up in derby.log if you set the diagnostic logging level to its most
verbose level by running the application with the following system property:



   -Dderby.stream.error.logSeverityLevel=0



Hope this helps,

-Rick



On 2/6/21 6:53 PM, Alex O'Ree wrote:

Thanks i'll give it a shot.

Is there any logging in derby that i can enable into regarding this?



On Sat, Feb 6, 2021 at 7:08 PM Rick Hillegas  


wrote:



The usual cause for this behavior is that the application was brought

down ungracefully, say via a control-c or by killing the window where it

was running. The engine then needs to reconstruct the state of the

database by replaying many recovery logs. To gracefully exit Derby, you

need to explicitly shutdown Derby as described here:

https://db.apache.org/derby/docs/10.15/devguide/tdevdvlp20349.html 
<https://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdb.apache.org%2Fderby%2Fdocs%2F10.15%2Fdevguide%2Ftdevdvlp20349.html=04%7C01%7Cpeter.ondruska%40kaibo.eu%7C0ab75b41f6904f15603308d8cb7aa472%7C971fa002c3a649c18191cb7e49d9cb77%7C0%7C0%7C637483074906136655%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000=HPDIFmPdYNgi7O9ALYzgmUdyyxRa7i99KSdPPWkL6pc%3D=0>



On 2/6/21 3:39 PM, Alex O'Ree wrote:

Sometimes when my app starts, it can take several minutes to initialize

the

database. Is there a way to find out whats going on? There isn't much log

output. I have overridden derby.stream.error.method but other than the

the

startup message, I don't have much to go on.



Is there perhaps a startup database file check or something?

Re: Out-of-memory errors

[Rick Hillegas]

The index key (time desc, username, name, facility, event, sector, item, 
details) could potentially be 32867 bytes long. However, an index key 
must be less than 1/2 the page size, according to the "Page size and key 
size" topic at 
https://db.apache.org/derby/docs/10.15/ref/rrefsqlj20937.html That is, 
your new index key is too big.


With the subquery-based approach, your covering index key would be only 
20 bytes long:


CREATE INDEX log_index ON system_log (time DESC, id);

Re: Out-of-memory errors

[Rick Hillegas]

On 2/9/21 8:21 AM, John English wrote:

On 09/02/2021 18:05, Rick Hillegas wrote:
As Bryan points out, please consult the Tuning Guide for information 
on how to view your query plan. In any event, your descending index 
is not a covering index. That is, it does not contain all of the 
columns in your query. That may be what forces the optimizer to 
choose a full table scan.


OK. The concept of a "covering index" is a new one on me, so I need to 
read up on that. Would it be possible to create a covering index for 
this table just by specifying "time DESC" followed by the other 
columns (i.e. everything except id)?

Yes.

Would it be a reasonable thing to do in terms of resource usage?
It would probably double the on-disk footprint of your table since the 
index would have to contain most of the columns (including the big 
DETAILS column). You have a space vs. time tradeoff here if the 
subquery-based solution won't work for your app.


You might be able to trick the optimizer into generating a more 
efficient plan by re-writing your query as follows:


SELECT time as t_time,
  facility,event,details,name,username,sector,item
   FROM system_log
   WHERE id IN
   (
 SELECT id
 FROM system_log
 ORDER BY time DESC
 NULLS LAST
 FETCH NEXT 20 ROWS ONLY
   )
   ORDER BY t_time
;


That's not so easy, since all the tables in the webapp are generated 
by a showTable() method, which displays results from a view as a table 
which allows for sorting, filtering, selecting the numbers of rows per 
page and so on, using a ridiculously long list of parameters to 
specify all the different possibilities. But I'll think about it.

Re: Out-of-memory errors

[Rick Hillegas]

The MergeInserter is doing a merge sort because there is no usable 
descending index on system_log.time. The storage layer's page cache (the 
ConcurrentCache) is filling up because you have to fault-in the entire 
contents of system_log. The logic in MergeInserter.insert() does not 
seem to be smart enough to realize that it is close to exhausting memory 
and needs to spill a merge run to disk.


I would recommend throwing more memory at your JVM or adding a 
descending index to system_log.time.



On 2/8/21 1:41 AM, Peter Ondruška wrote:

OutOfMemoryError: GC overhead limit exceeded happens when JVM spends way much 
garbage collecting. Could it be your heap is too small? One way to check if 
DateTimeFormat may be leaking memory is to run query without this function.

-Original Message-
From: John English 
Sent: Monday, February 8, 2021 10:28 AM
To: Derby Discussion 
Subject: Out-of-memory errors

In the last few days I've suddenly had a bunch of OOM exceptions. I'm using 
Derby 10.9.1.0, Oracle Java 1.8.0 on Ubuntu 64-bit, and haven't upgraded for a 
while (probably years, looking at those numbers).

The place where they happen is in a call to executeQuery() in a method which 
displays a view as a table. Analysing the heap dump for the latest one with the 
Eclipse memory analyser shows this:

One instance of "org.apache.derby.impl.store.access.sort.MergeInserter"
loaded by "org.eclipse.jetty.webapp.WebAppClassLoader @ 0xf04231b0"
occupies 134,841,800 (64.65%) bytes. The memory is accumulated in one instance of 
"org.apache.derby.impl.store.access.sort.SortBuffer", loaded by 
"org.eclipse.jetty.webapp.WebAppClassLoader @ 0xf04231b0", which occupies 134,841,496 
(64.65%) bytes.

One instance of "org.apache.derby.impl.services.cache.ConcurrentCache"
loaded by "org.eclipse.jetty.webapp.WebAppClassLoader @ 0xf04231b0"
occupies 43,766,832 (20.98%) bytes. The memory is accumulated in one instance of 
"org.apache.derby.impl.services.cache.ConcurrentCache",
loaded by "org.eclipse.jetty.webapp.WebAppClassLoader @ 0xf04231b0", which 
occupies 43,766,832 (20.98%) bytes.

The query itself was:

SELECT DateTimeFormat(t_time,null) AS t_time,
   facility,event,details,name,username,sector,item
FROM system_log_view
ORDER BY time DESC
NULLS LAST
FETCH NEXT 20 ROWS ONLY

The view is nothing special except that t_time is a duplicate of the time 
column (the timestamp of the log entry) used to create a separate formatted 
copy for display purposes:

CREATE VIEW system_log_view AS
  SELECT  time AS t_time,
  facility,
  event,
  details,
  name,
  username,
  sector,
  item,
  time
  FROMsystem_log;

The stack trace shows the error is occurring inside the call to DateTimeFormat, 
which is again nothing special:

public static final String formatDateTime (Timestamp date, String
locale) {
  if (date == null) {
return null;
  }
  else {
String fmt = translate("d-MMM- 'at' HH:mm",locale);
return translatePhrases(fmt.format(date),locale);
  }
}

Here's the start of the stack trace:

java.sql.SQLException: The exception 'java.lang.OutOfMemoryError: GC overhead 
limit exceeded' was thrown while evaluating an expression.
at java.text.DigitList.clone()Ljava/lang/Object; (DigitList.java:736)
at java.text.DecimalFormat.clone()Ljava/lang/Object;
(DecimalFormat.java:2711)
at java.text.SimpleDateFormat.initialize(Ljava/util/Locale;)V
(SimpleDateFormat.java:645)
at
java.text.SimpleDateFormat.(Ljava/lang/String;Ljava/util/Locale;)V
(SimpleDateFormat.java:605)
at java.text.SimpleDateFormat.(Ljava/lang/String;)V
(SimpleDateFormat.java:580)
at
database.Functions.formatDateTime(Ljava/sql/Timestamp;Ljava/lang/String;)Ljava/lang/String;
(Functions.java:51)

Does anyone have any idea what might be happening, or what I can do to find out 
more?

TIA,
--
John English

Re: Slow mount times

[Rick Hillegas]

I don't know of any special trace flags for this. Maybe something will 
turn up in derby.log if you set the diagnostic logging level to its most 
verbose level by running the application with the following system property:


  -Dderby.stream.error.logSeverityLevel=0


Hope this helps,
-Rick

On 2/6/21 6:53 PM, Alex O'Ree wrote:

Thanks i'll give it a shot.
Is there any logging in derby that i can enable into regarding this?

On Sat, Feb 6, 2021 at 7:08 PM Rick Hillegas 
wrote:


The usual cause for this behavior is that the application was brought
down ungracefully, say via a control-c or by killing the window where it
was running. The engine then needs to reconstruct the state of the
database by replaying many recovery logs. To gracefully exit Derby, you
need to explicitly shutdown Derby as described here:
https://db.apache.org/derby/docs/10.15/devguide/tdevdvlp20349.html

On 2/6/21 3:39 PM, Alex O'Ree wrote:

Sometimes when my app starts, it can take several minutes to initialize

the

database. Is there a way to find out whats going on? There isn't much log
output. I have overridden derby.stream.error.method but other than the

the

startup message, I don't have much to go on.

Is there perhaps a startup database file check or something?

Re: Slow mount times

[Rick Hillegas]

The usual cause for this behavior is that the application was brought 
down ungracefully, say via a control-c or by killing the window where it 
was running. The engine then needs to reconstruct the state of the 
database by replaying many recovery logs. To gracefully exit Derby, you 
need to explicitly shutdown Derby as described here: 
https://db.apache.org/derby/docs/10.15/devguide/tdevdvlp20349.html


On 2/6/21 3:39 PM, Alex O'Ree wrote:

Sometimes when my app starts, it can take several minutes to initialize the
database. Is there a way to find out whats going on? There isn't much log
output. I have overridden derby.stream.error.method but other than the the
startup message, I don't have much to go on.

Is there perhaps a startup database file check or something?

Re: AW: AW: Paging performance problem

[Rick Hillegas]

Hi Gerrit,

I'm glad that you found a satisfactory answer to your performance 
question. For the record, I can find my away around query plans printed 
in German.


Cheers,
-Rick

On 1/5/21 12:31 AM, Hohl, Gerrit wrote:

Hi Rick,


unfortunately I had to realize that the output of the query plans are printed 
by Derby in German.
And I'm not quiet sure how to change that.

But we maybe found the reason: The slower version uses the "HEAP" (based on the runtime 
statistics) to get the result while the faster version uses only the index ("projection").

The reason behind it seems that the slower version loads records from the table 
(I guess that is what HEAP means - loading records into memory).
It gets the create_timestamp from the index, but any other column - even if it is only 
the "id" - from the table.
It even doesn't matter much - at least in the described case - if we only query for the 
"id" or also for the other columns.

The faster version uses a "projection" and works only on the index, no access 
on the table needed.
If we put the faster version into JOIN like I described in my mail earlier we 
then load only the needed records from the table.

I hope I could explain the magic behind it a little bit, so helps others who 
may come across similar issues.


Best regards,
Gerrit

-----Ursprüngliche Nachricht-
Von: Rick Hillegas 
Gesendet: Montag, 4. Januar 2021 16:43
An: Derby Discussion ; Hohl, Gerrit 
Betreff: Re: AW: Paging performance problem

Hi Gerrit,

It's hard to say without seeing the query plans for these scenarios.
What query plans do you see when you follow the instructions in the "Working with 
RunTimeStatistics" section of the Derby Tuning Guide:
http://db.apache.org/derby/docs/10.15/tuning/ctundepth13055.html

-Rick

On 1/4/21 7:08 AM, Hohl, Gerrit wrote:

Hello everyone,

I guess I found at least one solution:

CREATE INDEX data_create_timestamp_id_key ON data (create_timestamp
ASC, id ASC)

SELECT d.* FROM (SELECT id FROM data ORDER BY create_timestamp OFFSET
440 ROWS FETCH NEXT 10 ROWS ONLY) as tmp, data d WHERE (tmp.id =
d.id)

Takes less than 4 seconds with my test table.
But I'm wondering if that is really the best solution, especially as I'm still 
not sure about the reason.

Isn't that new index I created also an unbalanced binary tree? Shouldn't it 
take the same amount of time?
But if I drop it and have only the primary key and data_create_timestamp_key 
index, the query takes even longer than my originally one (100s ).

In addition: I passed a simply SQL query. Wondering if I also can recreate that 
with JPA/JPQL...

Regards,
Gerrit

Von: Hohl, Gerrit
Gesendet: Montag, 4. Januar 2021 15:43
An: Derby Discussion 
Betreff: Paging performance problem

Hello everyone,

we're trying to use OFFSET and LIMIT for paging. But as our tables our growing, 
we're running in some performance problems we didn't expect.

Let's say we have the following structure (the table can also have more 
columns, but for the sake of simplicity...):

CREATE TABLE data (id INTEGER NOT NULL, create_timestamp TIMESTAMP NOT
NULL, PRIMARY(id)); CREATE INDEX data_create_timestamp_key ON data
(create_timestamp ASC);

We have around 5m records in it. And now we do the two following queries:

SELECT * FROM data ORDER BY create_timestamp OFFSET 0 ROWS FETCH NEXT
10 ROWS ONLY; SELECT * FROM data ORDER BY create_timestamp OFFSET
440 ROWS FETCH NEXT 10 ROWS ONLY;

While the first query returns right after it was started, the 2nd query takes 
almost a minute to complete.
First we thought that Derby might not have used the index for some reasons.
But the runtime statistics showed that it is using it.

We assume that it is due to the fact that the index might be some kind of 
unbalanced binary tree.
Is that true? And is there any work-around?

Gruß
Gerrit

P.S.: We still use Apache Derby 10.14.2.0 as we're still tight to Java 8.

Re: AW: Paging performance problem

[Rick Hillegas]

Hi Gerrit,

It's hard to say without seeing the query plans for these scenarios. 
What query plans do you see when you follow the instructions in the 
"Working with RunTimeStatistics" section of the Derby Tuning Guide: 
http://db.apache.org/derby/docs/10.15/tuning/ctundepth13055.html


-Rick

On 1/4/21 7:08 AM, Hohl, Gerrit wrote:

Hello everyone,

I guess I found at least one solution:

CREATE INDEX data_create_timestamp_id_key ON data (create_timestamp ASC, id ASC)

SELECT d.* FROM (SELECT id FROM data ORDER BY create_timestamp OFFSET 440 
ROWS FETCH NEXT 10 ROWS ONLY) as tmp, data d
WHERE (tmp.id = d.id)

Takes less than 4 seconds with my test table.
But I'm wondering if that is really the best solution, especially as I'm still 
not sure about the reason.

Isn't that new index I created also an unbalanced binary tree? Shouldn't it 
take the same amount of time?
But if I drop it and have only the primary key and data_create_timestamp_key 
index, the query takes even longer than my originally one (100s ).

In addition: I passed a simply SQL query. Wondering if I also can recreate that 
with JPA/JPQL...

Regards,
Gerrit

Von: Hohl, Gerrit
Gesendet: Montag, 4. Januar 2021 15:43
An: Derby Discussion 
Betreff: Paging performance problem

Hello everyone,

we're trying to use OFFSET and LIMIT for paging. But as our tables our growing, 
we're running in some performance problems we didn't expect.

Let's say we have the following structure (the table can also have more 
columns, but for the sake of simplicity...):

CREATE TABLE data (id INTEGER NOT NULL, create_timestamp TIMESTAMP NOT NULL, 
PRIMARY(id));
CREATE INDEX data_create_timestamp_key ON data (create_timestamp ASC);

We have around 5m records in it. And now we do the two following queries:

SELECT * FROM data ORDER BY create_timestamp OFFSET 0 ROWS FETCH NEXT 10 ROWS 
ONLY;
SELECT * FROM data ORDER BY create_timestamp OFFSET 440 ROWS FETCH NEXT 10 
ROWS ONLY;

While the first query returns right after it was started, the 2nd query takes 
almost a minute to complete.
First we thought that Derby might not have used the index for some reasons.
But the runtime statistics showed that it is using it.

We assume that it is due to the fact that the index might be some kind of 
unbalanced binary tree.
Is that true? And is there any work-around?

Gruß
Gerrit

P.S.: We still use Apache Derby 10.14.2.0 as we're still tight to Java 8.

Re: AW: Indexes grow over time insanly big and can't be shrunk

[Rick Hillegas]

Thanks for the extra information, Gerrit. I don't have any theories 
about why the primary key and EVENT_LOG_ENTRY_TIME_CREATED_INDEX are 
failing to compress. I don't have any better suggestion than the following:


1) Run SYSCS_DIAG.SPACE_TABLE() before and after 
SYSCS_UTIL.SYSCS_COMPRESS_TABLE().


2) If you notice that Derby is failing to compress those two indexes 
again, look in derby.log for errors or other diagnostics which may 
provide some clues.


Thanks,
-Rick

On 9/19/20 1:51 AM, Hohl, Gerrit wrote:

Hello everyone,

I'm sorry, I noticed that only replied to Rick. 


Regards,
Gerrit


-Ursprüngliche Nachricht-
Von: Hohl, Gerrit
Gesendet: Mittwoch, 16. September 2020 09:21
An: Rick Hillegas 
Betreff: AW: Indexes grow over time insanly big and can't be shrunk

Hello Rick,


thanks for your reply.


A1) SELECT * FROM TABLE(SYSCS_DIAG.SPACE_TABLE()) AS x WHERE (x.TABLEID = 
'1b30f0f5-0168-60ad-cb8f-366e0651');

CONGLOMERATENAME   |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES
|NUMUNFILLEDPAGES|PAGESIZE   |ESTIMSPACESAVING|TABLEID

EVENT_LOG_ENTRY|0 |1186|115 
|1   |32768  |3768320 
|1b30f0f5-0168-60ad-cb8f-366e0651
SQL190118123711680 |1 |1220394 |0   
|1190424 |4096   |0   
|1b30f0f5-0168-60ad-cb8f-366e0651
EVENT_LOG_ENTRY_LOG_NAME_INDEX |1 |245 |151 
|115 |4096   |618496  
|1b30f0f5-0168-60ad-cb8f-366e0651
EVENT_LOG_ENTRY_SOURCE_INDEX   |1 |437 |177 
|241 |4096   |724992  
|1b30f0f5-0168-60ad-cb8f-366e0651
EVENT_LOG_ENTRY_TIME_CREATED_INDEX |1 |2032376 |0   
|1965938 |4096   |0   
|1b30f0f5-0168-60ad-cb8f-366e0651
EVENT_LOG_ENTRY_LOG_LEVEL_INDEX|1 |181 |134 
|76  |4096   |548864  
|1b30f0f5-0168-60ad-cb8f-366e0651
EVENT_LOG_ENTRY_USER_NAME_INDEX|1 |258 |208 
|113 |4096   |851968  
|1b30f0f5-0168-60ad-cb8f-366e0651


A2) CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('app', 'event_log_entry', 1);

That command is only issued if the cumulated ESTIMSPACESAVING for the table and 
its indexes is greater than 0.
But I'm not sure if Derby really reorganised the both problem indexes - namely 
SQL190118123711680 (primary key) and EVENT_LOG_ENTRY_TIME_CREATED_INDEX - 
because there was nothing to free.

At the point I got that information the compress command also may have failed 
because there wasn't enough space left.
But I think it doesn't matter because before there was enough disk space and at 
that time the index was already growing.
Means none of the previous executions was able to reorganize those 2 indexes 
also there was enough space back then.


A3) Not sure what you mean with "pattern of activity", but we simply have a lot of 
"INSERT INTO event_log_entry (...) VALUES (...)" statements.
At the start of the application there is also a "SELECT COUNT(*) FROM 
event_log_entry" to get the current number of entries.
 From the on the application keeps track of it internally.

If 10.000 entries are reached, for each entry which exceeds that number, 
another entry is deleted (means the oldest). Those actions can also overlap.


Here the DDL of the table and its indexes:

CREATE TABLE event_log_entry (
   id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, 
INCREMENT BY 1),
   flags INT,
   changecount INT,
   checksum BIGINT,
   log_name VARCHAR(256),
   source VARCHAR(256),
   time_created VARCHAR(17),
   log_level VARCHAR(3),
   user_name VARCHAR(256),
   data BLOB(65536)
);
CREATE INDEX event_log_entry_log_name_index ON event_log_entry (log_name); 
CREATE INDEX event_log_entry_source_index ON event_log_entry (source); CREATE 
INDEX event_log_entry_time_created_index ON event_log_entry (time_created); 
CREATE INDEX event_log_entry_log_level_index ON event_log_entry (log_level); 
CREATE INDEX event_log_entry_user_name_index ON event_log_entry (user_name);


I already repaired it by dropping the indexes and recreating them (as I wrote 
in my mail).
Now everything is working perfectly again.
But that doesn't mean that it won't happen again, of course. 


Regards
Gerrit

-Ursprüngliche Nachricht-
Von: Rick Hillegas 
Gesendet: Dienstag, 15. September 2020 17:27
An: Derby Discussion ; Hohl, Gerrit 
Betreff: Re: Indexes grow over time insanly big and can't be shrunk

Hi Gerrit,

I don't have a the

Re: Indexes grow over time insanly big and can't be shrunk

[Rick Hillegas]

Hi Gerrit,

I don't have a theory about what caused this problem. Maybe 
COMPRESS_TABLE() has a serious bug. A couple questions:


Q1) Do you have the results of SPACE_TABLE() for this situation?

Q2) What value did you specify for the SEQUENTIAL argument of 
COMPRESS_TABLE()?


Q3) Other than COMPRESS_TABLE(), what is the pattern of activity on this 
table?


If this happens again, take a look at the ALTER TABLE DROP PRIMARY KEY 
and ALTER TABLE ADD CONSTRAINT commands. They may make it simpler to 
repair the damage.


Thanks,
-Rick

On 9/15/20 5:14 AM, Hohl, Gerrit wrote:

Hello everyone,

we use Apache Derby v10.14.2.0 in our software and now have a problem with an 
installation.

Every night we perform a compact on all tables - at least the ones which can be 
shrunk (SYSCS_DIAG.SPACE_TABLE() and SYSCS_UTIL.SYSCS_COMPRESS_TABLE(String, 
String, Smallint)).
Normally that constellation works fine.
But now faced an installation which ran out of free disk space without any 
reason.

After some analysing we narrowed it down to one table which is meant for 
logging.
Records are inserted and deleted often (the size of it is limited at 10.000 
entries through the software).
The maximum - based on the structure - should be around 40 MB. Not really much.
So we were very surprised seeing that this thing took around 14 GB.

I realized that I accumulated the table and its indexes to get that value.
After splitting it up, I saw that the table itself really only took 40 MB.
But two of the 6 indexes - one was the primary key index - took more than 13 GB 
of space.
Apache Derby also said no NUMFREEPAGES and no ESTIMSPACESAVING.

My solution then was shutting down the service (in-process database), connect 
to the database, dropping the indexes, create a new column for temporarily 
storing the primary key value, copying the values, also dropping that column, 
recreating the primary key column, copying everything back, dropping the 
temporary primary key column, creating all the indexes again.
Now everything is back to normal and the table *including* its indexes is 40 MB.

Any idea on this? Why did the index grow that big? What can I do preventing it?
Seems reorganising isn't an option as Apache Derby itself doesn't realize that 
it can free space.

Regards,
Gerrit

Re: database in a jar : conglomerate does not exists

[Rick Hillegas]

Thanks for letting us know how you resolved the issue.

On 9/14/20 11:08 PM, RAPPAZ Francois wrote:

Thank you all

At the end
- taking care for  the version of derby to be the same when I build the 
database or when I search
- deleting the old database (corrupted) and starting with a new one, then 
creating the table and populating it
Solved the problem.

Archiving the database in a jar was not the reason.

Cheers

François


-Original Message-
From: Rick Hillegas 
Sent: 12 September 2020 18:13
To: RAPPAZ Francois 
Cc: derby-user@db.apache.org
Subject: Re: database in a jar : conglomerate does not exists

This error indicates that you are trying to boot a database with a lower 
version of Derby than the one used to create the database. I believe you said 
that the database was created by Derby 10.8.2.2. It looks like you are trying 
to boot it with some version of Derby in the 10.4 family.

-Rick

On 9/11/20 12:33 AM, RAPPAZ Francois wrote:

If I start from a new database (named docentries), I packed it in a jar, I can 
connect with ij and run a select command.

If I run the code from my java classe, I get the error

- SQLException -
SQL State:  XJ040
Error Code: 4
Message:Failed to start database 'classpath:docentries', see the next 
exception for details.

- SQLException -
SQL State:  XCL20
Error Code: 2
Message:Catalogs at version level 'null' cannot be upgraded to version 
level '10.4'.

I connect with db = new
DBConnector("jdbc:derby:classpath:docentries");

François

-Original Message-----
From: Rick Hillegas 
Sent: 11 September 2020 00:49
To: Derby Discussion ; RAPPAZ Francois

Subject: Re: database in a jar : conglomerate does not exists

Also, look inside the jar file for a directory called docentries/seg0.
Does it contain a file called c560.dat?

On 9/10/20 8:53 AM, Rick Hillegas wrote:

Sorry. Make that query:

SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 1376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


On 9/10/20 8:22 AM, Rick Hillegas wrote:

Hi François,

Do you have any information or theories about how your database
became corrupted? I have never encountered this situation before. A
database in a jar file should be read-only, so the only theory I
have is that the jar file itself was corrupted by some process outside Derby.

Please run the following query in order to find out what table/index
is corrupted:

SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


Thanks,
-Rick

On 9/10/20 3:09 AM, RAPPAZ Francois wrote:

Hi
I have a one table database embedded in a jar file. I tried to
access it from ij with java -jar %DERBY_HOME%/lib/derbyrun.jar ij
-p ij.properties

ij.properties is
derby.ui.codeset=utf8
ij.connection.doc=jdbc:derby:jar:(U:/docs/OA/articles/zlib/autconv/
a
utconv.jar)docentries


I can see that my table (authors) is in the the database with SHOW
TABLES; I can see the columns
ij> DESCRIBE authors;
COLUMN_NAME
|TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
---
-
--

NAME1   |VARCHAR  |NULL|NULL|20    |NULL |40    |NO
NAME2   |VARCHAR  |NULL|NULL|20    |NULL |40    |NO
DATA    |VARCHAR  |NULL|NULL|50    |NULL |100
|YES AUTHOR_ID   |INTEGER  |0   |10  |10 |AUTOINCRE&|NULL
|NO

4 rows selected
But I can't run a select statement:
ij> select * from authors;
ERROR XSAI2: Le conglomerat (1,376) demande n'existe pas.
ij> exit;

I have derby 10.8.2.2
Thanks for any help.

François


.

Re: database in a jar : conglomerate does not exists

[Rick Hillegas]

This error indicates that you are trying to boot a database with a lower 
version of Derby than the one used to create the database. I believe you 
said that the database was created by Derby 10.8.2.2. It looks like you 
are trying to boot it with some version of Derby in the 10.4 family.


-Rick

On 9/11/20 12:33 AM, RAPPAZ Francois wrote:

If I start from a new database (named docentries), I packed it in a jar, I can 
connect with ij and run a select command.

If I run the code from my java classe, I get the error

- SQLException -
   SQL State:  XJ040
   Error Code: 4
   Message:Failed to start database 'classpath:docentries', see the next 
exception for details.

- SQLException -
   SQL State:  XCL20
   Error Code: 2
   Message:Catalogs at version level 'null' cannot be upgraded to version 
level '10.4'.

I connect with db = new DBConnector("jdbc:derby:classpath:docentries");

François

-Original Message-----
From: Rick Hillegas 
Sent: 11 September 2020 00:49
To: Derby Discussion ; RAPPAZ Francois 

Subject: Re: database in a jar : conglomerate does not exists

Also, look inside the jar file for a directory called docentries/seg0.
Does it contain a file called c560.dat?

On 9/10/20 8:53 AM, Rick Hillegas wrote:

Sorry. Make that query:

SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 1376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


On 9/10/20 8:22 AM, Rick Hillegas wrote:

Hi François,

Do you have any information or theories about how your database
became corrupted? I have never encountered this situation before. A
database in a jar file should be read-only, so the only theory I have
is that the jar file itself was corrupted by some process outside Derby.

Please run the following query in order to find out what table/index
is corrupted:

SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


Thanks,
-Rick

On 9/10/20 3:09 AM, RAPPAZ Francois wrote:

Hi
I have a one table database embedded in a jar file. I tried to
access it from ij with java -jar %DERBY_HOME%/lib/derbyrun.jar ij -p
ij.properties

ij.properties is
derby.ui.codeset=utf8
ij.connection.doc=jdbc:derby:jar:(U:/docs/OA/articles/zlib/autconv/a
utconv.jar)docentries


I can see that my table (authors) is in the the database with SHOW
TABLES; I can see the columns
ij> DESCRIBE authors;
COLUMN_NAME
|TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&

--

NAME1   |VARCHAR  |NULL|NULL|20    |NULL |40    |NO
NAME2   |VARCHAR  |NULL|NULL|20    |NULL |40    |NO
DATA    |VARCHAR  |NULL|NULL|50    |NULL |100   |YES
AUTHOR_ID   |INTEGER  |0   |10  |10 |AUTOINCRE&|NULL
|NO

4 rows selected
But I can't run a select statement:
ij> select * from authors;
ERROR XSAI2: Le conglomerat (1,376) demande n'existe pas.
ij> exit;

I have derby 10.8.2.2
Thanks for any help.

François






.

Re: database in a jar : conglomerate does not exists

[Rick Hillegas]

ave been made years ago and it went well for a long 
time without any change from my side.

I did rebuild the table in the database on my disc once in a year, either by 
deleting + adding new rows, or by dropping the table and creating a new then 
adding the row.
The database in the docentries folder is ok, I can run a select again the table.
Then I build the jar (with an ant task that didn't change for years.)

François

-Original Message-----
From: Rick Hillegas 
Sent: 11 September 2020 00:49
To: Derby Discussion ; RAPPAZ Francois 

Subject: Re: database in a jar : conglomerate does not exists

Also, look inside the jar file for a directory called docentries/seg0.
Does it contain a file called c560.dat?

On 9/10/20 8:53 AM, Rick Hillegas wrote:

Sorry. Make that query:

SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 1376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


On 9/10/20 8:22 AM, Rick Hillegas wrote:

Hi François,

Do you have any information or theories about how your database
became corrupted? I have never encountered this situation before. A
database in a jar file should be read-only, so the only theory I have
is that the jar file itself was corrupted by some process outside Derby.

Please run the following query in order to find out what table/index
is corrupted:

SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


Thanks,
-Rick

On 9/10/20 3:09 AM, RAPPAZ Francois wrote:

Hi
I have a one table database embedded in a jar file. I tried to
access it from ij with java -jar %DERBY_HOME%/lib/derbyrun.jar ij -p
ij.properties

ij.properties is
derby.ui.codeset=utf8
ij.connection.doc=jdbc:derby:jar:(U:/docs/OA/articles/zlib/autconv/a
utconv.jar)docentries


I can see that my table (authors) is in the the database with SHOW
TABLES; I can see the columns
ij> DESCRIBE authors;
COLUMN_NAME
|TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&

--

NAME1   |VARCHAR  |NULL|NULL|20    |NULL |40    |NO
NAME2   |VARCHAR  |NULL|NULL|20    |NULL |40    |NO
DATA    |VARCHAR  |NULL|NULL|50    |NULL |100   |YES
AUTHOR_ID   |INTEGER  |0   |10  |10 |AUTOINCRE&|NULL
|NO

4 rows selected
But I can't run a select statement:
ij> select * from authors;
ERROR XSAI2: Le conglomerat (1,376) demande n'existe pas.
ij> exit;

I have derby 10.8.2.2
Thanks for any help.

François

Re: database in a jar : conglomerate does not exists

[Rick Hillegas]

Also, look inside the jar file for a directory called docentries/seg0. 
Does it contain a file called c560.dat?


On 9/10/20 8:53 AM, Rick Hillegas wrote:

Sorry. Make that query:

SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 1376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


On 9/10/20 8:22 AM, Rick Hillegas wrote:

Hi François,

Do you have any information or theories about how your database 
became corrupted? I have never encountered this situation before. A 
database in a jar file should be read-only, so the only theory I have 
is that the jar file itself was corrupted by some process outside Derby.


Please run the following query in order to find out what table/index 
is corrupted:


SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


Thanks,
-Rick

On 9/10/20 3:09 AM, RAPPAZ Francois wrote:

Hi
I have a one table database embedded in a jar file. I tried to 
access it from ij with

java -jar %DERBY_HOME%/lib/derbyrun.jar ij -p ij.properties

ij.properties is
derby.ui.codeset=utf8
ij.connection.doc=jdbc:derby:jar:(U:/docs/OA/articles/zlib/autconv/autconv.jar)docentries 



I can see that my table (authors) is in the the database with SHOW 
TABLES;

I can see the columns
ij> DESCRIBE authors;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
-- 


NAME1   |VARCHAR  |NULL|NULL|20    |NULL |40    |NO
NAME2   |VARCHAR  |NULL|NULL|20    |NULL |40    |NO
DATA    |VARCHAR  |NULL|NULL|50    |NULL |100   |YES
AUTHOR_ID   |INTEGER  |0   |10  |10 |AUTOINCRE&|NULL  |NO

4 rows selected
But I can't run a select statement:
ij> select * from authors;
ERROR XSAI2: Le conglomerat (1,376) demande n'existe pas.
ij> exit;

I have derby 10.8.2.2
Thanks for any help.

François

Re: database in a jar : conglomerate does not exists

[Rick Hillegas]

Sorry. Make that query:

SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 1376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


On 9/10/20 8:22 AM, Rick Hillegas wrote:

Hi François,

Do you have any information or theories about how your database became 
corrupted? I have never encountered this situation before. A database 
in a jar file should be read-only, so the only theory I have is that 
the jar file itself was corrupted by some process outside Derby.


Please run the following query in order to find out what table/index 
is corrupted:


SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


Thanks,
-Rick

On 9/10/20 3:09 AM, RAPPAZ Francois wrote:

Hi
I have a one table database embedded in a jar file. I tried to access 
it from ij with

java -jar %DERBY_HOME%/lib/derbyrun.jar ij -p ij.properties

ij.properties is
derby.ui.codeset=utf8
ij.connection.doc=jdbc:derby:jar:(U:/docs/OA/articles/zlib/autconv/autconv.jar)docentries 



I can see that my table (authors) is in the the database with SHOW 
TABLES;

I can see the columns
ij> DESCRIBE authors;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
-- 


NAME1   |VARCHAR  |NULL|NULL|20    |NULL |40    |NO
NAME2   |VARCHAR  |NULL|NULL|20    |NULL |40    |NO
DATA    |VARCHAR  |NULL|NULL|50    |NULL |100   |YES
AUTHOR_ID   |INTEGER  |0   |10  |10 |AUTOINCRE&|NULL  |NO

4 rows selected
But I can't run a select statement:
ij> select * from authors;
ERROR XSAI2: Le conglomerat (1,376) demande n'existe pas.
ij> exit;

I have derby 10.8.2.2
Thanks for any help.

François

Re: database in a jar : conglomerate does not exists

[Rick Hillegas]

Hi François,

Do you have any information or theories about how your database became 
corrupted? I have never encountered this situation before. A database in 
a jar file should be read-only, so the only theory I have is that the 
jar file itself was corrupted by some process outside Derby.


Please run the following query in order to find out what table/index is 
corrupted:


SELECT s.schemaName, t.tableName, c.conglomerateName

FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t

WHERE c.conglomerateNumber = 376

AND c.tableID = t.tableID

AND t.schemaID = s.schemaID

;


Thanks,
-Rick

On 9/10/20 3:09 AM, RAPPAZ Francois wrote:

Hi
I have a one table database embedded in a jar file. I tried to access it from 
ij with
java -jar %DERBY_HOME%/lib/derbyrun.jar ij -p ij.properties

ij.properties is
derby.ui.codeset=utf8
ij.connection.doc=jdbc:derby:jar:(U:/docs/OA/articles/zlib/autconv/autconv.jar)docentries

I can see that my table (authors) is in the the database with SHOW TABLES;
I can see the columns
ij> DESCRIBE authors;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
--
NAME1   |VARCHAR  |NULL|NULL|20|NULL  |40|NO
NAME2   |VARCHAR  |NULL|NULL|20|NULL  |40|NO
DATA|VARCHAR  |NULL|NULL|50|NULL  |100   |YES
AUTHOR_ID   |INTEGER  |0   |10  |10|AUTOINCRE&|NULL  |NO

4 rows selected
But I can't run a select statement:
ij> select * from authors;
ERROR XSAI2: Le conglomerat (1,376) demande n'existe pas.
ij> exit;

I have derby 10.8.2.2
Thanks for any help.

François

Re: Users suddenly denied database access - Connection authentication error.

[Rick Hillegas]

Hi Kerry,

Thanks for that detailed explanation of your issue. The most likely 
problem is that your user passwords have expired. Check the value of the 
system property derby.authentication.native.passwordLifetimeMillis. The 
following command should get you that value:



  VALUES 
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.native.passwordLifetimeMillis')


If you set this property to 0, then your passwords will not expire, 
according to 
http://db.apache.org/derby/docs/10.15/ref/rrefproperpasswordmillis.html


The default password lifetime is one month, according to 
http://db.apache.org/derby/docs/10.15/security/csecnativeotherprops.html. 
The DBA's password does not expire. That would explain why the DBA can 
connect but no-one else can. As you get down to the wire on your 
password's lifetime, you will receive SQLWarnings when you connect. It 
is likely that your application does not check for these warnings, so 
you never know that passwords are about to age out.


Let us know if that is not the problem. Hope this helps...

-Rick

On 8/22/20 7:50 AM, Kerry wrote:

Hi,

I appear to be having a very odd issue with Derby. I have found on more than 
one occasion (it seems) that all users previously granted access to a database 
are suddenly denied connection and access and there has been no deliberate 
changes applied by myself in respect of user access or any other kind of change 
- I am the only one with access/uses the DB on my home network. And I am sure 
this has now occurred more than once, previously I had assumed I had messed 
something up.

The database can be considered 'production' so there are no deliberate changes 
made to it on my part. Once every week, I have an autonomous service that 
gathers some data and pushes it into the Derby instance. That is all it does 
and it has been happily doing this for the past several weeks. However when I 
checked the logs of the autonomous service today I found it was giving:

java.sql.SQLNonTransientConnectionException: Connection authentication failure 
occurred.  Reason: Userid or password invalid.

I have manually tried connecting to the database with several other user IDs 
including the 'owner' but I get the same error. The only user that can connect 
is the one I used to set up the credentials database - I am using native 
authentication with a dedicated database, or at least that is how I believe I 
have set this up. However this particular user cannot select anything from the 
tables etc because it was never granted permission and because it isn't the 
owner of the database I cannot change the permissions.

I think last time this happened I tried copying the database folder to another 
derby instance which had no authentication enabled in an attempt to recover the 
data but I seem to remember the user permissions were copied with the directory 
and I wasn't able to recover the data.I cannot rule out some third-party 
interference but it would seem unlikely as the database is on my home network 
which isn't open to the world.

Because I cannot connect to the database and select data, does this mean I have 
lost access completely or is there something I can modify in the database 
directory to allow me to recover the data?

Thanks for any help or suggestions

Kerry

Re: best practice for jpa/integration tests

[Rick Hillegas]

Can't you to tell the object/relational mapping layer or the test 
framework to swallow certain errors? Note that an in-memory database can 
be dropped and recreated for each test case if you really need a blank 
slate.


On 8/6/20 3:39 AM, Marco Ferretti wrote:

Hi all,

I have been struggling to find a decent solution for my (low priority) issue.
I have a situation where I want to do an integration test for an application based on 
CDI/EJB. I have my persistence.xml configured with "jdbc:derby :memory" and I 
have the database creation/drop scripts that work when running from SQL console.
My unit tests are set-up so that each time a single is run the database gets to 
dropped and re-created. The problem is that each time a test unit (file) is 
run, it adds to the error logs as it tries to drop the database before any 
object is actually created.
The issue seems to be that the standard JPA way of doing such things (from a 
script) is to run the drop(s) before the create(s) and, for as much as I have 
searched, I haven't found a way to change this behaviour by acting on the 
configuration.
As far as I know, Derby does not support "DROP  IF EXISTS" thus my 
drop-schema script fails with a bunch of errors the first time a test unit is run.
Since we HAVE to be agnostic towards the actual JPA implementation, I was 
wondering if you have any suggestion as to how I shall circumvent such an 
annoying issue.

Thanks is advance for any hints you can provide.

p.s.: I know I can create the database in the @BeforeClass and drop it in the 
@AfterClass, I was looking to work "outside" the application's code-base.

Marco F.

Re: So, Derby works...

[Rick Hillegas]

If we were implementing all of this now, we would not separate 
AutoloadedDriver from EmbeddedDriver. But Derby co-evolved with the JDK 
from the start. By the time that Java introduced driver autoloading, we 
already had legacy customers whose applications we did not want to 
disturb. We did not completely achieve that goal, but we tried.


Class.forName("org.apache.derby.jdbc.EmbeddedDriver" ) boots the Derby 
engine, loading its classes into memory. Some legacy applications came 
to expect this side-effect. However, we did not want to boot the Derby 
engine and incur all of that classloading as a side-effect of an 
application trying to connect to another vendor's database driver. That 
is what would have happened had derby.jar designated EmbeddedDriver as 
the autoloaded driver. So we created a new, largely vacuous placeholder 
driver, the AutoloadedDriver, which does NOT boot the Derby engine as a 
side-effect of simply being faulted into a classloader. That is the 
driver which derby.jar designates as Derby's autoloaded driver.


This is a great deal of complexity for something which ought to be very 
straightforward, but it is the cost we paid to keep abreast of an 
evolving JDBC interface while maintaining backward compatibility.


Hope this helps,
-Rick

On 7/8/20 4:50 PM, Russell Bateman wrote:
The mock driver supports a sort of bogus URL ("jdbc:mock") that it 
recognizes for its purpose which is only to respond to a single query 
with canned data (whereas I'll be able to populate Derby with as much 
and whatever data I ever want). Of course, its driver doesn't ever do 
anything "real," though it supports a few metadata methods (and the 
MockConnectionimplements Connectionand supports a whole pile of 
methods that stub out most actions and unimaginatively return the 
canned data).


Yes, your suggestion #3 works well and solves that particular problem 
of an empty driver list. (The list is empty instead of containing the 
mock driver because I separated the packages. DriverManagerdoes some 
reflection desperately looking for classes that might have drivers.) 
The list now has the Derby driver in it:


Down inside DriverManager's private getConnection(), walking the 
driver list using the IntelliJ IDEA debugger, I see:


   *aDriver* = {DriverInfo@2097}
"driver[className=org.apache.derby.jdbc.*AutoloadedDriver*@6bb4dd34]"
   *con* = {EmbeddedConnection@2585}
   "org.apache.derby.impl.jdbc.*EmbedConnection*@1414013111 (XID =
   164), (SESSIONID = 1), (DATABASE = memory:sys), (DRDAID = null) "

I have to admit that I'm not to the point yet where I grok why I'm 
seeing AutoloadedDriverwhen I forced Java to verify EmbeddedDriver. I 
got no errors doing that; I did it just before making the call to 
getConnection():


   Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" );
   DriverManager.getConnection(
   "jdbc:derby:memory:sampledb;created=true", "sa", "sa" );

I have learned a great deal more about JDBC drivers than I had planned 
thanks to deciding to use Derby, replacing this old test mock and with 
your help which I hugely appreciate!


Russ


On 7/8/20 5:15 PM, Rick Hillegas wrote:
It's hard to say what's going on, but the instability in your 
experiments is hard to reconcile against the deterministic code paths 
involved in JDBC autoloading.


I can only speculate about what is causing this instability:

1) What JDBC URLs does your MockDriver claim to support?

2) Is something tricky being done to the classpath, creating a 
situation in which the Derby jars are not visible when you invoke 
DriverManager.getConnection()?


3) What happens when you do a 
Class.forName("org.apache.derby.jdbc.EmbeddedDriver")? What are the 
chain of errors raised? For the record, this is the recommended JDBC 
way to fault in a driver which was missed at autoloading time. 
Autoloading occurs on the very first call to 
DriverManager.getConnection() during the lifetime of the JVM.


Hope this helps,
-Rick

On 7/8/20 10:37 AM, Russell Bateman wrote:
...again, don't know what I changed.* I'm only depending on 
/derby-10.15.2.0.jar/.


What I think is going on is an artifact of being side-by-side with 
another JDBC driver implementation.


I was hoping to keep the original mocked test driver working 
alongside the Derby implementation at least until I'm able to 
abolish it in every case. Just as used to be the case, it's 
registered thus:


    DriverManager.registerDriver( new MockDriver() );

However, this is not how it works for Derby. It just happens on the 
basis of having the JAR linked (in /pom.xml/). The problem comes 
that, because the MockDriveris explicitly registered, when we go to 
create a Derby connection:


    java.sql.Connection connection = DriverManager.getConnection( 
"jdbc:derby:memory:sampledb;create=true" );


...DriverManagertakes as license to create an instance of the old

Re: So, Derby works...

[Rick Hillegas]

It's hard to say what's going on, but the instability in your 
experiments is hard to reconcile against the deterministic code paths 
involved in JDBC autoloading.


I can only speculate about what is causing this instability:

1) What JDBC URLs does your MockDriver claim to support?

2) Is something tricky being done to the classpath, creating a situation 
in which the Derby jars are not visible when you invoke 
DriverManager.getConnection()?


3) What happens when you do a 
Class.forName("org.apache.derby.jdbc.EmbeddedDriver")? What are the 
chain of errors raised? For the record, this is the recommended JDBC way 
to fault in a driver which was missed at autoloading time. Autoloading 
occurs on the very first call to DriverManager.getConnection() during 
the lifetime of the JVM.


Hope this helps,
-Rick

On 7/8/20 10:37 AM, Russell Bateman wrote:
...again, don't know what I changed.* I'm only depending on 
/derby-10.15.2.0.jar/.


What I think is going on is an artifact of being side-by-side with 
another JDBC driver implementation.


I was hoping to keep the original mocked test driver working alongside 
the Derby implementation at least until I'm able to abolish it in 
every case. Just as used to be the case, it's registered thus:


    DriverManager.registerDriver( new MockDriver() );

However, this is not how it works for Derby. It just happens on the 
basis of having the JAR linked (in /pom.xml/). The problem comes that, 
because the MockDriveris explicitly registered, when we go to create a 
Derby connection:


    java.sql.Connection connection = DriverManager.getConnection( 
"jdbc:derby:memory:sampledb;create=true" );


...DriverManagertakes as license to create an instance of the old test 
mock because that is the (only) driver in its registeredDriverslist. 
*I moved the Derby-based code out of the same Java package to solve this?


Thanks again. I'm sorry for having made this annoying.

Russ

Re: Driver registration, Derby drivers and Derby versions

[Rick Hillegas]

Hi Russell,

This is a little complicated because it involves a bit of history. Derby 
and Java grew up together. The core of Derby is the Cloudscape database 
engine, which appeared around 1996, close to the the appearance of Java 
itself. Two significant events in the evolution of Java have affected 
what happened to the Derby drivers. Where the code ended up is a little 
weird, but the aim all along has been to minimize disruption to legacy 
applications. The two events are:


1) The introduction of JDBC driver autoloading in Java 5 and the further 
refinement of driver autoloading in Java 6.


2) The introduction of the JPMS module architecture in Java 9.

If you are getting connections via 
java.sql.DriverManager.getConnection(), then you don't need to register 
drivers at all. That is because the JVM will load all of the JDBC 
drivers which are visible on the application classpath--that is, all of 
the drivers which have metadata recorded in the manifests of their 
respective jar files.


In Derby 10.15, the drivers around, in order to achieve the clean 
package partitioning required by JPMS. The original embedded driver now 
lives in the tools jar. The autoloading driver moved into another 
package in the engine jar. It is now called 
org.apache.derby.iapi.jdbc.AutoloadedDriver.


Hope this helps,
-Rick


On 7/7/20 6:00 PM, Russell Bateman wrote:

How do the myriad Derby drivers work?

I am using Apache Derby in-memory with no daemon or outside 
dependencies in support of being called from JUnit tests. This is 
working pretty well, but what it's going to be doing underneath will 
eventually cause me to walk the following road, I think.


I am using 10.15.2.0. I find myself needing to register a driver such 
that a call to DriverManager.getConnection()will find it. (Or, I think 
I do; I'm looking at some existing homespun mocking code that does 
this and I'm trying to imitate then replace it with Derby.)


    DriverManager.registerDriver( new 
org.apache.derby.jdbc.AutoLoadedDriver() );


Here and there, in examples on-line, I see references to drivers like:

   org.apache.derby.jdbc.AutoloadedDriver
   org.apache.derby.jdbc.EmbeddedDriver

However, unless I'm mistaken, these do not exist in derby 10.15.2.0. I 
am using derby-10.15.2.0.jar. I have also tried adding the same 
version's shared and tools JARs.


Any thoughts on this would help. I only discovered this phenomenon of 
registering drivers today, so I'm stumbling around a bit.


Thanks.

Re: AW: AW: Questions about query execution and optimization

[Rick Hillegas]

Hi Gerrit,

One thing I notice about your big WHERE clause is that it contains an 
OR. In general, ORs are not optimizable. You will end of with full table 
scans. Please see 
http://db.apache.org/derby/docs/10.15/tuning/ctunoptimz39106.html


Hope this helps,
-Rick

On 7/6/20 6:21 AM, Hohl, Gerrit wrote:

Hi Rick,


thanks for your message.


Thanks also for the link, also I'm not sure I understand its content correctly.
My understand of indexes so far was that they are used to filter tables when 
performing a query.
Let's say we have a table PERSON containing the name and age of persons. We 
have an index on the age column.
In our query we want all persons which are 30 years old: SELECT * FROM person 
WHERE age = 30;
I would expect that the DBMS - after it analysed the query - will look for all 
"30" entries in the age index.
Those contain pointers on the original records / tuples in the table file.
Let's say we have 10 matches in the age index, the DBMS will get those 10 
matches from the table file.
Of course, if all columns we select are already in the index - in the best case we have 
"SELECT age FROM person WHERE age = 30;" - the query would be extremely fast.
And it would be slower if additional columns which are not backed by an index 
can also be found in the WHERE clause.

But even set that aside: In my 1st mail I selected the same columns in the big 
query as well as in the two smaller queries.
There was no difference between them in this aspect.
The difference was in the WHERE clause - which was more complicated in the big 
query and a simpler in the two smaller queries.
But in all versions the same columns were used in the WHERE clause, all of them 
backed by indexes.
So it won't also explain the behaviour.


About the XPLAIN style things: I guess you're talking about this here.
https://db.apache.org/derby/docs/10.14/tuning/ctun_xplain_style.html
Seems like a neat little project on its own...


Regards,
Gerrit

-Ursprüngliche Nachricht-
Von: Rick Hillegas 
Gesendet: Montag, 6. Juli 2020 14:54
An: Derby Discussion ; Hohl, Gerrit 
Betreff: Re: AW: Questions about query execution and optimization

Hi Gerrit,

I suspect that your query performs poorly because your indexes do not cover the 
query. That means that you are selecting columns which don't appear in the 
indexes. In this case, the optimizer knows that Derby cannot satisfy the query 
by simply reading index pages. Derby also has to read base table pages in order 
to fetch the other columns. Please see 
http://db.apache.org/derby/docs/10.15/tuning/ctunoptimz30768.html

Derby query plans are not easy to read. If you are snapshotting plans with 
XPLAIN style, then the details go into snapshot tables. In theory, you could 
write a tool to read those snapshot results and format them better.

Hope this helps,
-Rick

On 7/6/20 12:46 AM, Hohl, Gerrit wrote:

Hello everyone,


me again.
In the meantime I tried to tune the table using the

SYSCS_UTIL.SYSCS_COMPRESS_TABLE

and

SYSCS_UTIL.SYSCS_UPDATE_STATISTICS

functions. It didn't have any effect on the query runtime.
I also tried the query analyse functions:


CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);

VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0);
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);

The result was long, localized (German in my case - nice)... and absolutely 
cryptic.
It didn't mention any of the tokens of my SQL query.
Instead it was taking about UNIONs and JOINs, also I don't use any. At least 
not explicitly.
I couldn't figure at all which entry of the analysis belonged to which part of 
the SQL query.

I remember when I worked with PostgreSQL there was that neat EXPLAIN command.
That command had a very good structure of its output.
And it seems they still have it in their current version:
https://www.postgresql.org/docs/current/using-explain.html


Regards,
Gerrit

-Ursprüngliche Nachricht-
Von: Hohl, Gerrit 
Gesendet: Freitag, 3. Juli 2020 16:22
An: Derby Discussion 
Betreff: Questions about query execution and optimization

Hello everyone,

this week I came across a behaviour of Apache Derby which I couldn't explain to 
myself.
I'm using version 10.14.2.0 and the structure of the database looks like this:

CREATE TABLE license (
id BIGINT NOT NULL,
[...]
);
CREATE TABLE installation (
id BIGINT NOT NULL,
[...]
license_id BIGINT NOT NULL,
[...]
);
CREATE TABLE log (
id BIGINT NOT NULL,
action VARCHAR(255) NOT NULL,
create_timestamp TIMESTAMP NOT NULL,
entity_cls VARCHAR(255),
entity_id BIGINT,
type INTEGER NOT NULL,
message VARCHAR(32672) NOT NULL,
PRIMARY KEY (ID)
);
CREATE INDEX logcreatetimestampindex ON log (create_timestamp); CREATE
INDEX logentitycls

Re: AW: Questions about query execution and optimization

[Rick Hillegas]

Hi Gerrit,

I suspect that your query performs poorly because your indexes do not 
cover the query. That means that you are selecting columns which don't 
appear in the indexes. In this case, the optimizer knows that Derby 
cannot satisfy the query by simply reading index pages. Derby also has 
to read base table pages in order to fetch the other columns. Please see 
http://db.apache.org/derby/docs/10.15/tuning/ctunoptimz30768.html


Derby query plans are not easy to read. If you are snapshotting plans 
with XPLAIN style, then the details go into snapshot tables. In theory, 
you could write a tool to read those snapshot results and format them 
better.


Hope this helps,
-Rick

On 7/6/20 12:46 AM, Hohl, Gerrit wrote:

Hello everyone,


me again.
In the meantime I tried to tune the table using the

SYSCS_UTIL.SYSCS_COMPRESS_TABLE

and

SYSCS_UTIL.SYSCS_UPDATE_STATISTICS

functions. It didn't have any effect on the query runtime.
I also tried the query analyse functions:


CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);

VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0);
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);

The result was long, localized (German in my case - nice)... and absolutely 
cryptic.
It didn't mention any of the tokens of my SQL query.
Instead it was taking about UNIONs and JOINs, also I don't use any. At least 
not explicitly.
I couldn't figure at all which entry of the analysis belonged to which part of 
the SQL query.

I remember when I worked with PostgreSQL there was that neat EXPLAIN command.
That command had a very good structure of its output.
And it seems they still have it in their current version:
https://www.postgresql.org/docs/current/using-explain.html


Regards,
Gerrit

-Ursprüngliche Nachricht-
Von: Hohl, Gerrit 
Gesendet: Freitag, 3. Juli 2020 16:22
An: Derby Discussion 
Betreff: Questions about query execution and optimization

Hello everyone,

this week I came across a behaviour of Apache Derby which I couldn't explain to 
myself.
I'm using version 10.14.2.0 and the structure of the database looks like this:

CREATE TABLE license (
id BIGINT NOT NULL,
[...]
);
CREATE TABLE installation (
id BIGINT NOT NULL,
[...]
license_id BIGINT NOT NULL,
[...]
);
CREATE TABLE log (
id BIGINT NOT NULL,
action VARCHAR(255) NOT NULL,
create_timestamp TIMESTAMP NOT NULL,
entity_cls VARCHAR(255),
entity_id BIGINT,
type INTEGER NOT NULL,
message VARCHAR(32672) NOT NULL,
PRIMARY KEY (ID)
);
CREATE INDEX logcreatetimestampindex ON log (create_timestamp);
CREATE INDEX logentityclsentityidindex ON log (entity_cls, entity_id);
ALTER TABLE installation ADD CONSTRAINT fkinstallationlicense FOREIGN KEY 
(license_id) REFERENCES license (id);

The log table contains log messages about actions on records in other tables.
Because it should be useable with all tables, there can't be any foreign keys.
Especially as also logs should be kept when the corresponding record is deleted.

Now I wanted to create a query which returns all logs of a certain license and 
its installations:

SELECT l.*
FROM log l
WHERE ((l.entity_cls = 'License') AND (l.entity_id = ?))
OR ((l.entity_cls = 'Installation) AND (l.entity_id IN (
SELECT i.id FROM installation i WHERE (i.license_id = ?)
)))
ORDER BY l.create_timestamp DESC, l.id DESC;

But that thing took forever (~ 12.5s).
I thought the reason would maybe my index, so I introduced two more:

CREATE INDEX logentityidindex ON log (entity_cls);
CREATE INDEX logentityclsindex ON log (entity_id);

Unfortunately that didn't change much. The query took almost the exact same 
amount of time.
Next thing was removing the sub-select and directly giving a list of IDs for 
the installation records - just for testing.

SELECT l.*
FROM log l
WHERE ((l.entity_cls = 'License') AND (l.entity_id = 123))
OR ((l.entity_cls = 'Installation) AND (l.entity_id IN (234, 345)))
ORDER BY l.create_timestamp DESC, l.id DESC;

Much to my amazement that also didn't change anything.
In black despair I split the query into two while keeping the sub-select:

SELECT l.*
FROM log l
WHERE ((l.entity_cls = 'License') AND (l.entity_id = ?))
ORDER BY l.create_timestamp DESC, l.id DESC;

SELECT l.*
FROM log l
WHERE ((l.entity_cls = 'Installation) AND (l.entity_id IN (
SELECT i.id FROM installation.id WHERE (l.license_id = ?)
)))
ORDER BY l.create_timestamp DESC, l.id DESC;

Unbelievable: The 1st query took ~0.15s while the 2nd query took ~0.2s.
As the sub-select seems not to have any effect how fast or slow the query is, 
that can be ignored.
In all cases I only have columns in my WHERE clause which are in the indexes.
But somehow Apache Derby seems not to notice it and not using them, if the 
WHERE clause gets too complicated.

Is 

Re: How to migrate a Derby database?

[Rick Hillegas]

I don't have enough information about the original database corruption 
to speculate about the aptness of your solution. Maybe the corruption 
could have been repaired in place without the need to create a new 
database. For my money, 2ii is a faster solution than 2i, but your 
mileage may vary. Glad to hear that you fixed your problem.


Cheers,
-Rick

On 6/30/20 8:37 AM, David Gowdy wrote:
There were a couple of factors related to this initial post. First, 
was that I'd encountered a somewhat minor problem with an application 
that I've been using for a bit more than 15 years without any prior 
problem.  Second, I do some work with relational databases and thought 
I should know how to do such things with Derby.


The minor problem was a failure to allow insertion of a specific new 
row into a table.  Based on knowledge of what was being shown to be in 
the database this should have worked fine.  Other insertions were also 
working as expected.  Therefore, I deduced that a plausible 
explanation would be some kind of corruption that may have crept into 
the underlying files used to store the data. This could have happened 
anytime but based on the specific elements involved I thought it 
likely to be something that happened long ago.


Therefore, my idea was to want to recover as much of the data as 
possible and then create a new database using that data.  I determined 
that when it came to your suggestions 2i was the only one that fit 
this criteria.  In that, this produces new database files that are 
completely independent from the original ones. Fortunately, my 
archives did have a text file that contained the SQL for creating the 
tables.  I haven't used the resulting database much yet but I was able 
to insert the row that previously failed and have no reason to think 
there is any problem.


When compared to the MySQL/phpMyAdmin export/import technique this one 
is NOT quite as simple.  On the other hand ending up with the data in 
.csv format could be considered more desirable for generalized 
compatibility reasons than the SQL format used for MySQL.


Would be grateful to learn about any flaws in my assessment of this 
situation and many thanks for the help.


ajax ...

On 6/29/2020 6:40 PM, Rick Hillegas wrote:

Hi Ajax,

I don't know why you are not receiving email which I posted to the 
derby-user list. In any event, you can try posting your messages both 
to me and to derby-user. I will respond to all so that you and 
derby-user should be copied on the whole conversation.


Thanks,
-Rick

On 6/29/20 7:39 AM, recei...@gowdygroup.net wrote:


Hi Ajax,

Here are a couple points to consider:

...

Quoted from:
http://apache-database.10148.n7.nabble.com/How-to-migrate-a-Derby-database-tp151268p151269.html 



I'm NOT completely sure what I'm doing right now.  I think this 
message should end up being an email addressed to Rick Hillegas.  
I'm able to do this because of his reply to my original post which 
was done by sending an email.  It seems that I'm not able to respond 
to that reply via the website being used to send this message.  I 
sort of thought your reply might have shown up in my inbox as a 
result of having subscribed to the mailing list.  However, even 
after checking SPAM boxes I can find nothing.


While I do have a question related to your excellent reply to my 
original post, the question for now is "How am I supposed to submit 
such questions?  In that, how to respond to your reply?".



_
Sent from http://apache-database.10148.n7.nabble.com

Re: How to migrate a Derby database?

[Rick Hillegas]

Hi Ajax,

I don't know why you are not receiving email which I posted to the 
derby-user list. In any event, you can try posting your messages both to 
me and to derby-user. I will respond to all so that you and derby-user 
should be copied on the whole conversation.


Thanks,
-Rick

On 6/29/20 7:39 AM, recei...@gowdygroup.net wrote:


Hi Ajax,

Here are a couple points to consider:

...

Quoted from:
http://apache-database.10148.n7.nabble.com/How-to-migrate-a-Derby-database-tp151268p151269.html

I'm NOT completely sure what I'm doing right now.  I think this message should 
end up being an email addressed to Rick Hillegas.  I'm able to do this because 
of his reply to my original post which was done by sending an email.  It seems 
that I'm not able to respond to that reply via the website being used to send 
this message.  I sort of thought your reply might have shown up in my inbox as 
a result of having subscribed to the mailing list.  However, even after 
checking SPAM boxes I can find nothing.

While I do have a question related to your excellent reply to my original post, the 
question for now is "How am I supposed to submit such questions?  In that, how to 
respond to your reply?".


_
Sent from http://apache-database.10148.n7.nabble.com

Re: How to migrate a Derby database?

[Rick Hillegas]

Hi Ajax,

Here are a couple points to consider:

0) Derby supports two kinds of upgrade: soft and hard. Soft-upgraded 
databases can be downgraded to previous releases of Derby (but not to a 
release earlier than the original version of the database). 
Hard-upgraded databases can not be downgraded. Please see the section on 
upgrades in the Derby Developer's Guide: 
http://db.apache.org/derby/docs/10.15/devguide/cdevupgrades.html


1) Consider using operating system commands to simply copy your old 
database to a new location and then hard-upgrade the new copy in place. 
You will need to quiesce the source database first, that is, gracefully 
shut it down. The Derby database format has not changed since the code 
was open-sourced in the early noughties. It is a platform-agnostic 
format, so you can simply tar up the whole directory tree holding the 
old database and then un-tar it in your target installation--even if the 
target machine has a different architecture. Hard-upgrade is something 
which we test whenever we vet a new Derby release. The test involves 
creating a database from each of the releases which we have produced 
over the past fifteen years and then hard-upgrading those databases to 
the latest version, that is, to the release which we are vetting. Very 
few bugs have been logged against hard-upgrade.


2) If you want to make this as difficult as MySQL, you can, of course, 
dump the schema-creating DDL from the old database and then replay it in 
a fresh database. Use the dblook tool for this purpose. It is somewhat 
analogous to the MySQL Export tool mentioned below. Please see its 
documentation in the Tools Guide: 
http://db.apache.org/derby/docs/10.15/tools/ctoolsdblook.html Once you 
have a schema shell, you will need to populate the tables with data from 
the original database. There are two ways to do this:


i) Use the SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure to dump data 
from the original database into flat files and then use the 
SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure to load those files into 
the new target database. Please see the sections on these procedures in 
the Reference Manual: 
http://db.apache.org/derby/docs/10.15/ref/rrefexportproc.html and 
http://db.apache.org/derby/docs/10.15/ref/rrefimportproc.html


ii) You can avoid indirecting through flat files and, instead, transfer 
your data quickly and directly by using the foreignViews tool documented 
in the Tools Guide. Please see 
http://db.apache.org/derby/docs/10.15/tools/rtoolsoptforeignviews.html 
In case, you're interested, this tool can also be used to quickly 
migrate data out of non-Derby databases.


I would rank these approaches in ascending complexity and time needed, 
as follows:


  1
  2ii
  2i

I would avoid over-thinking this problem. Derby does not suffer from the 
kinds of version-specific format incompatibilities which plague other 
databases. Option 1 is your best choice.


Please feel free to ask more questions.

Hope this helps,
-Rick


On 6/28/20 10:17 AM, recei...@gowdygroup.net wrote:
It would be nice to have a way to convert a database to a form that is 
independent of the software used to support the database. For example, 
when using phpMyAdmin on a MySQL database there is an option referred 
to as "Export".  This option causes the creation of a text file that 
contains all of the SQL statements required to recreate the database 
at least in MySQL or Maria DB.  In theory, such a file should also be 
useful for migrating the same database to other kinds of relational 
databases that support SQL.


At least in the case of MySQL/MariaDB this capability provides an easy 
way to transfer databases from one server to another which may be 
desirable when making transition to a new release.  If nothing else it 
preserves the ability to restore to current release.  Given how many 
versions of Derby presently exist it seems like this should be 
especially important.


There is a present need to do such a migration simply to upgrade to a 
new version of Derby.  In the case where this migration is from a very 
old version of Derby the idea of a version independent file format 
would seem to be very desirable.  Is that possible with Derby?  If so, 
some reference to appropriate technique would be appreciated.  If NOT, 
is there a prescribed method for doing such that is safe and effective?


Ajax ...

Re: java.sql.SQLException: No suitable driver found for jdbc:derby:memory:sampledb;create=true

[Rick Hillegas]

1) Do you have a stack trace showing what class can't be resolved?

2) What happens if you do a Class.forName() on the following classes, 
which live, respectively, in derby.jar, derbyshared.jar, and derbytools.jar:


org.apache.derby.impl.jdbc.EmbedConnection
org.apache.derby.shared.common.util.ArrayUtil
org.apache.derby.jdbc.BasicEmbeddedDataSource40

Thanks,
-Rick

On 6/25/20 4:20 PM, Russell Bateman wrote:
Oops. I spoke too soon. I still had my JUnit tests @Ignore'd. It 
doesn't work yet. I still get "no suitable driver." I added derbytools 
too, but that made no difference. Further thoughts?



On 6/25/20 5:01 PM, Russell Bateman wrote:
Thank you; that's very kind. It now works. (I'm not using DataSources 
for now.) I greatly appreciate your help.


Best regards,
Russ

On 6/25/20 4:48 PM, Rick Hillegas wrote:
The 10.15 family of releases introduced a JPMS modularization of 
Derby. That re-factored the code a bit. You will need to add 
derbyshared.jar to the classpath and build dependencies. If you are 
using DataSources, then you will also need to add derbytools.jar. 
Please see the detailed release note for DERBY-6945 on the 10.15.1.3 
download page: 
http://db.apache.org/derby/releases/release-10.15.1.3.html


Hope this helps,
-Rick

On 6/25/20 1:28 PM, Russell Bateman wrote:

I have very recent code that works in a JUnit test case.

   @Test
   public void test()
   {
      final String DATABASE = 
"jdbc:derby:memory:sampledb;create=true";

      final String USERNAME = "sa";
      final String PASSWORD = "sa";

      final String CREATE_TABLE = "CREATE TABLE names ( oid INT
   GENERATED ALWAYS AS IDENTITY, name VARCHAR( 20 ) )";
      final String INSERT_NAME1 = "INSERT INTO names ( name ) VALUES (
   'Jack' )";
      final String QUERY    = "SELECT oid, name FROM names";

      Connection connection = null;

      try
      {
        connection = DriverManager.getConnection( DATABASE, USERNAME,
   PASSWORD );

        Statement statement = connection.createStatement();
        ...

In /pom.xml/, I have the following:

   
      org.apache.derby
      *derby*
      10.15.2.0
      test
   

I only want to use Derby _in-memory_ backing some unit test cases 
that need a database (not requiring a running server or dæmon, 
etc.). It all works perfectly inside IntelliJ IDEA.


However, when I build from the command line (mvn clean package), I 
see this and can find no solution:


   java.sql.SQLException: No suitable driver found for
   jdbc:derby:memory:sampledb;create=true
        at 
java.sql.DriverManager.getConnection(DriverManager.java:689)
        at 
java.sql.DriverManager.getConnection(DriverManager.java:247)

        at
com.imatsolutions.database.ApacheDerbyTest.testDirectlyToDerby(ApacheDerbyTest.java:78) 



In /pom.xml/, I have tried adding the following, and I have tried 
many other solutions, some of which are supposed to be obsolete ( 
Class.for(...), DriverManager.registerDriver( ... ), etc. ), but 
cannot find a happy solution.


   
      org.apache.derby
      *derbyclient*
      10.15.2.0
      test
   

Any comment would be welcome.

Thanks.

Re: java.sql.SQLException: No suitable driver found for jdbc:derby:memory:sampledb;create=true

[Rick Hillegas]

The 10.15 family of releases introduced a JPMS modularization of Derby. 
That re-factored the code a bit. You will need to add derbyshared.jar to 
the classpath and build dependencies. If you are using DataSources, then 
you will also need to add derbytools.jar. Please see the detailed 
release note for DERBY-6945 on the 10.15.1.3 download page: 
http://db.apache.org/derby/releases/release-10.15.1.3.html


Hope this helps,
-Rick

On 6/25/20 1:28 PM, Russell Bateman wrote:

I have very recent code that works in a JUnit test case.

   @Test
   public void test()
   {
      final String DATABASE = "jdbc:derby:memory:sampledb;create=true";
      final String USERNAME = "sa";
      final String PASSWORD = "sa";

      final String CREATE_TABLE = "CREATE TABLE names ( oid INT
   GENERATED ALWAYS AS IDENTITY, name VARCHAR( 20 ) )";
      final String INSERT_NAME1 = "INSERT INTO names ( name ) VALUES (
   'Jack' )";
      final String QUERY    = "SELECT oid, name FROM names";

      Connection connection = null;

      try
      {
        connection = DriverManager.getConnection( DATABASE, USERNAME,
   PASSWORD );

        Statement statement = connection.createStatement();
        ...

In /pom.xml/, I have the following:

   
      org.apache.derby
      *derby*
      10.15.2.0
      test
   

I only want to use Derby _in-memory_ backing some unit test cases that 
need a database (not requiring a running server or dæmon, etc.). It 
all works perfectly inside IntelliJ IDEA.


However, when I build from the command line (mvn clean package), I see 
this and can find no solution:


   java.sql.SQLException: No suitable driver found for
   jdbc:derby:memory:sampledb;create=true
        at java.sql.DriverManager.getConnection(DriverManager.java:689)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at
com.imatsolutions.database.ApacheDerbyTest.testDirectlyToDerby(ApacheDerbyTest.java:78)

In /pom.xml/, I have tried adding the following, and I have tried many 
other solutions, some of which are supposed to be obsolete ( 
Class.for(...), DriverManager.registerDriver( ... ), etc. ), but 
cannot find a happy solution.


   
      org.apache.derby
      *derbyclient*
      10.15.2.0
      test
   

Any comment would be welcome.

Thanks.

Re: Is create=true thread-safe?

[Rick Hillegas]

The first thread which gets through will create the database and the 
others will block until the first thread finishes. I am not aware of any 
thead safety issues here. Do you have a test case which suggests otherwise?


Thanks,
-Rick

On 3/19/20 10:24 PM, Behrang Saeedzadeh wrote:

Hi,

Is the create=true attribute

thread-safe?

In particular, what would happen if 10 threads at the same time try to
obtain this connection:

 DriverManager.getConnection("jdbc:derby:mydb;create=true);

(when Derby is used as an embedded database)? Is this safe?

The docs say:


If the database already exists, the attribute creates a connection to the

existing database, and an *SQLWarning* is issued.

But it is not clear if that implies `create=true` is thread safe.

Re: I don't find the JDBC driver class in derbyclient.jar

[Rick Hillegas]

Thanks for spotting this, Richard. I have logged 
https://issues.apache.org/jira/browse/DERBY-7071 to track the 
documentation issue.


On 3/5/20 1:25 AM, Richard Grin wrote:

Thanks a lot Rick. I put these 3 files in the lib directory of Payara and 
everything is working now.

In the meanwhile I found the page 
http://db.apache.org/derby/docs/10.15/getstart/twwdactivity4.html but it does 
not mention the file derbytools.jar; is it a mistake?

I am not used to Derby and I had a problem to solve with the default name and password to 
give (APP) in order to connect my Web application to Derby. I solved it by guessing the 
"APP" values to put. Where can I find this information in the documentation?


Derby's security mechanisms are documented here: 
http://db.apache.org/derby/docs/10.15/security/index.html


However, by default, Derby starts up out-of-the-box with authentication 
turned off. The default schema (and username) is APP (without a 
password). I am surprised that you were not able to connect without a 
username and password. Can you share the details of how you are connecting?


Thanks,

-Rick



Richard

Le 05/03/2020 à 00:53, Rick Hillegas a écrit :
Hey Richard,

The drivers moved into derbytools.jar as part of the JPMS modularization work 
introduced by the previous feature release (10.15.1.3). In addition to 
derbyclient.jar, you will need to put derbyshared.jar and derbytools.jar on 
your client-side classpath or modulepath. Please see the corresponding release 
notes at http://db.apache.org/derby/releases/release-10.15.1.3.html

Hope this helps,
-Rick

On 3/4/20 11:10 AM, Richard Grin wrote:
Hi,

I have just started using Derby (version 10.15.2.0) today.

I would like to use it with Payara server so I have to put the JDBC
driver in Payara. Payara can't find the class
org.apache.derby.jdbc.ClientDriver. I looked for this class in the
derbyclient.jar file but I couldn't find it.

What's my mistake? Has the driver class changed? Is the class in another
file?

I have read articles about JDBC driver for old versions of Derby Network
server but I can't find the information for the 10.15.2.0 version.

Richard

Re: I don't find the JDBC driver class in derbyclient.jar

[Rick Hillegas]

Hey Richard,

The drivers moved into derbytools.jar as part of the JPMS modularization 
work introduced by the previous feature release (10.15.1.3). In addition 
to derbyclient.jar, you will need to put derbyshared.jar and 
derbytools.jar on your client-side classpath or modulepath. Please see 
the corresponding release notes at 
http://db.apache.org/derby/releases/release-10.15.1.3.html


Hope this helps,
-Rick

On 3/4/20 11:10 AM, Richard Grin wrote:

Hi,

I have just started using Derby (version 10.15.2.0) today.

I would like to use it with Payara server so I have to put the JDBC
driver in Payara. Payara can't find the class
org.apache.derby.jdbc.ClientDriver. I looked for this class in the
derbyclient.jar file but I couldn't find it.

What's my mistake? Has the driver class changed? Is the class in another
file?

I have read articles about JDBC driver for old versions of Derby Network
server but I can't find the information for the 10.15.2.0 version.

Richard

[ANNOUNCE] Apache Derby 10.15.2.0 released

[Rick Hillegas]

The Apache Derby project is pleased to announce maintenance release 
10.15.2.0.


Apache Derby is a sub-project of the Apache DB project. Derby is a pure 
Java relational database engine which conforms to the ISO/ANSI SQL and 
JDBC standards. Derby aims to be easy for developers and end-users to 
work with.


Derby 10.15.2.0 contains bug and documentation fixes and it can be 
obtained from the Derby download site:


   http://db.apache.org/derby/derby_downloads.html.

Please try out this new release.

Re: Derby newbie - error seen creating 'seconddb' thru ij for network-server Derby

[Rick Hillegas]

A basic server policy (which you should customize) can be found in the 
Derby Security Guide at 
http://db.apache.org/derby/docs/10.15/security/rsecbasicserver.html It 
is also located in the bin distribution at 
demo/templates/serverTemplate.policy.


Hope this helps,
-Rick

On 11/8/19 2:26 AM, Zero wrote:

On 11/8/19 11:10 AM, Kerry wrote:
1. If you don't have $DERBY_HOME set then I do believe Derby will 
take it's bike from whichever directory it has been started from.
2. Your Derby log confirms it's a permissions error. A *CRUDE* fix 
for this is:


Locate the file 'java.policy'. it's probably located in 
/etc/java-11-openjdk/security.

Add the following immediately above other permissions within that file:

permission java.security.AllPermission;

This however should not be a long term fix and you should investigate 
a more secure solution. Maybe others on this mailing list might have 
a more informed solution.


Kerry



Better specify separately the security policy for the database.

Include on the command line

-Djava.security.manager -Djava.security.policy=$DERBY_HOME/server.policy"

Maybe there is in the derby demo directory an example.

Harm-Jan



Sent from BlueMail 
On 8 Nov 2019, at 09:40, Banibrata Dutta > wrote:


    Thanks Kerry. Finally found 'derby.log' which was in my $HOME (not
    in $DERBY_HOME), perhaps because that's where I started derby
    network from.
    The log file has following 3 entries towards the end, and indeed
    it seems to be permission problem:

    Fri Nov 08 12:34:46 IST 2019 Thread[DRDAConnThread_4,5,main]
    (DATABASE = seconddb), (DRDAID = {5}), Failed to create database
    'seconddb', see the next exception for details.
    Fri Nov 08 12:34:46 IST 2019 Thread[DRDAConnThread_4,5,main]
    (DATABASE = seconddb), (DRDAID = {5}), Startup failed due to an
    exception. See next exception for details.
    Fri Nov 08 12:34:46 IST 2019 Thread[DRDAConnThread_4,5,main]
    (DATABASE = seconddb), (DRDAID = {5}), Java exception: 'access
    denied ("java.lang.RuntimePermission" "getenv.SOURCE_DATE_EPOCH"):
    java.security.AccessControlException'.

    What should be done to provide permissions ? Is the permission
    going to be Derby specific ?

    On Fri, Nov 8, 2019 at 2:44 PM Kerry <
    karaf-u...@avionicengineers.com
    > wrote:

    Hi,

    This sounds like it might be a Java permissions error as you
    are using Open JDK 11. If you look in the derby.log file
    located in $DERBY_HOME it will give an indication. Post the
    content of it here.

    Kerry

    Sent from BlueMail 
    On 8 Nov 2019, at 07:29, Banibrata Dutta <
    banibrata.du...@gmail.com >
    wrote:

    Hi Folk,

    Trying to use Derby for the first time. I am not an expert
    Java developer but dabbling for learning me some. I
    followed the official Derby instructions for installing
    the latest Derby on my freshly minted Lubuntu (lighter
    Ubuntu using LXDE) 64-bit 18.04.3 setup, with OpenJDK11
    (default).

    Have started Derby in network server mode like this:
    $ java -jar $DERBY_HOME/lib/derbyrun.jar server start

    And then trying to using ij to create the database like 
this:

    $ ij
    ij version 10.15
    ij> CONNECT
    'jdbc:derby://localhost:1527/seconddb;create=true';
    ERROR XJ041: DERBY SQL error: ERRORCODE: 4, SQLSTATE:
    XJ041, SQLERRMC: Failed to create database 'seconddb', see
    the next exception for details.::SQLSTATE:
    XBM01::SQLSTATE: XJ001
    ij>

    Trying to read around, there are suggestions to look into
    derby.log file, but I found none so far. What gives ? How
    to proceed ? Didn't find any general troubleshooting
    instructions in the docs. Tried the FAQ, but no obvious
    clues.

    cheers,
    B

Re: Database maintenance routines

[Rick Hillegas]

On 11/5/19 4:03 AM, Alex O'Ree wrote:

I have a use case where by I add a bunch of rows, export then in an
archive, then delete all the rows and repeat for weeks or months on end.
Are there any maintenance procedures I should be running after each purge?

Postgres and mssql has some functions to tell it to recover disk space and
compact itself, or to notify that there was a large change in row volume.
Is there anything like this in derby?

I would recommend using TRUNCATE TABLE to delete all the rows of the 
table. This should be fast and it will re-initialize the table with an 
empty backing file.


In general, you can use the SYSCS_UTIL.SYSCS_COMPRESS_TABLE system 
procedure to squeeze unused space out of a table. You can use 
SYSCS_UTIL.SYSCS_UPDATE_STATISTICS to regenerate the histograms which 
the optimizer uses in order to calculate optimal query plans.


See the Derby Reference Manual for information on these commands.

Hope this helps,
-Rick

Re: Derby driver class name v10.15.1.3

[Rick Hillegas]

Thanks for this discussion, Kerry and Jerry. Please file a docs bug and 
suggest how this can be described better.


You are right, it is the modularization of Derby which broke this 
behavior for you. Before modularization, the two drivers (embedded and 
network) lived in the same package but separate jar files (derby.jar and 
derbyclient.jar). That had to be untangled in order to achieve 
jigsaw-ready partitioning of packages across jars.


If you rely on driver autoloading and simply connect via 
DriverManager.getConnection() using a JDBC URL, then the tools jar is 
NOT needed. But if you connect via a DataSource, then you need 
derbytools.jar, which contains the old drivers.


You also need derbytools.jar if you boot the drivers the very 
old-fashioned way via Class.forName(). I believe that the original 
Spring framework pre-dates the driver autoloading introduced by Java 6. 
It seems that you still have to tell Spring the driver class name. Our 
documentation should say more about this old usage pattern. Thanks in 
advance for the doc issue and for your recommendations.


-Rick

On 10/22/19 2:48 PM, Kerry wrote:

Thanks Jerry,

The one artefact I didn't check, but that doesn't seem right somehow now .. 
seeing as there is still derbyclient? Should documentation be updated too to 
reflect that?:

https://db.apache.org/derby/docs/10.15/getstart/rgslib46043.html

I can see if I can raise a ticket if that;s the case

Kerry

On 22/10/2019 22:24, Jerry Lampi wrote:

Kerry,
Looks like it moved to derbytools.jar.
lib\derbytools.jar->class=org/apache/derby/jdbc/ClientDriver.class
Jerry

-Original Message-
From: Kerry 
Sent: Tuesday, October 22, 2019 3:56 PM
To: derby-user@db.apache.org
Subject: Derby driver class name v10.15.1.3

Hi,

I've been trying to configure a Spring boot application to work with version 
10.15.1.3 of Derby client and I need to specify the driver name in the 
applications.properties file (spring.datasource.driver-class-name= *). 
Prior to this version I would have used org.apache.derby.jdbc.ClientDriver in 
the derbyclient.jar .. but it not longer exists in 10.15.1.3 of Derby? I've 
looked through all the other Derby artifacts for this version and cannot find 
the class.

Should I be doing something different with this version of Derby? (because of 
the modularisation introduced in Java 9?) I couldn't see anything in the 
documentation for v10.15.1.3.

Thanks

Kerry

Re: OSGi manifest headers

[Rick Hillegas]

On 10/17/19 3:04 AM, Mark Raynsford wrote:

Hello!

Derby is now fully modularized in the JPMS sense, but I notice that
there's a rather glaring lack of OSGi manifest headers. This means that
Derby can't be used in an OSGi environment.

Is there any chance of getting Bnd or the maven-bundle-plugin
introduced into the part of the build that produces Maven artifacts?

Thanks for raising this topic. I have created 
https://issues.apache.org/jira/browse/DERBY-7056 to track this issue. I 
recommend moving this discussion there.


None of the active Derby contributors is an OSGi expert. We will need 
your advice on what's required.


Thanks,

-Rick

Re: Infinite loop - stack overflow

[Rick Hillegas]

Hi Geraldine,

I have logged https://issues.apache.org/jira/browse/DERBY-7055 to track 
this issue. If you discover a repro, please update that issue.


Thanks,
-Rick

On 10/4/19 7:12 AM, Geraldine McCormack wrote:

I can certainly log the issue but I do not know how to recreate it
unfortunately...

Re: [derby] searching within a blob

[Rick Hillegas]

If the string really has been serialized as a byte array into a BLOB 
column, then I'm not aware of an off-the-shelf solution. I would tackle 
this problem by writing a function which does the following:


o Retrieves the java.sql.Blob via PreparedStatement.getBlob()

o Retrieves an InputStream from the BLOB via Blob.getBinaryStream()

o Decodes the InputStream's bytes into characters using a 
java.nio.charset.CharsetDecoder obtained from a java.nio.charset.Charset


o Examines the decoded character stream for the pattern you want.

Hope this helps,
-Rick


On 9/30/19 6:18 PM, Alex O'Ree wrote:

yup, definitely a blob. it looks like the like operator doesnt work for
blobs, or maybe i need a cast or some function to the conversion

On Mon, Sep 30, 2019 at 6:50 PM Rick Hillegas 
wrote:


On 9/30/19 9:18 AM, Alex O'Ree wrote:

I have a use case where i have string data stored in a blob and i want to
perform a query similar to

select * from table where column1 like '%hello world%'

It doesn't look like this is possible with derby out of the box. Is there a
way to create a function that calls a java function or something that can
be used to make this work?


Just to clarify, the column has type BLOB rather than CLOB? The following
script works for me on CLOB types:

connect 'jdbc:derby:memory:db;create=true';
CREATE TABLE t (a CLOB);
INSERT INTO t VALUES ('this will not match'), ('but shello worldly will');
SELECT * FROM t WHERE a LIKE '%hello world%';

Thanks,

-Rick

Re: [derby] searching within a blob

[Rick Hillegas]

On 9/30/19 9:18 AM, Alex O'Ree wrote:

I have a use case where i have string data stored in a blob and i want to
perform a query similar to

select * from table where column1 like '%hello world%'

It doesn't look like this is possible with derby out of the box. Is there a
way to create a function that calls a java function or something that can
be used to make this work?

Just to clarify, the column has type BLOB rather than CLOB? The 
following script works for me on CLOB types:


   connect 'jdbc:derby:memory:db;create=true';
   CREATE TABLE t (a CLOB);
   INSERT INTO t VALUES ('this will not match'), ('but shello worldly will');
   SELECT * FROM t WHERE a LIKE '%hello world%';

Thanks,

-Rick

Re: Constants for CHAR() FOR BIT DATA?

[Rick Hillegas]

On 9/28/19 2:11 AM, Mark Raynsford wrote:

create table core.users (
   user_idchar (16) for bit data not null,
   user_password_hash_algovarchar (64) not null,
   user_password_hash varchar (64) not null,
   user_email varchar (128) not null,
   user_display_name  varchar (128) not null,
   user_lockedboolean not null,

   constraint user_id_key primary key (user_id),
   constraint user_id_nonzero check (user_id !=
   cast('\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' as char(16) for bit data)),
   constraint user_display_name_unique unique (user_display_name))


Hi Mark,

The SQL Standard syntax for BINARY literals is a little odd. It is

  X''


For instance:

  X'FEED'


For more information, please see the description of the CHAR datatype in 
the Derby Reference Manual: 
http://db.apache.org/derby/docs/10.15/ref/rrefsqlj57924.html


The following table definition works for me:

connect 'jdbc:derby:memory:db;create=true';

create table core.users
(
  user_id    char (16) for bit data not null,
  user_password_hash_algo    varchar (64) not null,
  user_password_hash varchar (64) not null,
  user_email varchar (128) not null,
  user_display_name  varchar (128) not null,
  user_locked    boolean not null,

  constraint user_id_key primary key (user_id),
  constraint user_id_nonzero check (user_id != 
X''),

  constraint user_display_name_unique unique (user_display_name)
);

Hope this helps,
-Rick

Re: Do triggers block?

[Rick Hillegas]

The statement.execute() method will not return until all triggered 
actions complete. If the trigger invokes a user-written java method, 
then the trigger will not finish executing until the method returns. All 
of the transactional work done by the statement operates within a 
savepoint. That includes the transactional work done by the statement's 
triggers and all of the transactional work done by user-written methods 
invoked by those triggers. If a statement-level error occurs, then all 
of the work done in the savepoint is rolled back--but the work done by 
previous statements in the transaction is not rolled back. Waiting for 
the user-written method to complete is all part of this savepoint 
management.


Hope this helps,
-Rick

On 9/15/19 1:14 PM, fkalim wrote:

Hi,

I'm working with triggers in Derby and their order of execution is described
in the document as given below. However, I wanted to clarify whether
triggers are blocking e.g. if an AFTER trigger fires a function, does it
block until the function has finished running?

Order of execution
When a database event occurs that fires a trigger, Derby performs actions in
this order:
It fires No Cascade Before triggers.
It performs constraint checking (primary key, unique key, foreign key,
check).
It performs the insert, update, or delete.
It fires After triggers.
When multiple triggers are defined for the same database event for the same
table for the same trigger time (before or after), triggers are fired in the
order in which they were created.





--
Sent from: 
http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Calling a java function from a trigger

[Rick Hillegas]

You don't need the schema name in the EXTERNAL NAME clause. The 
INSTALL_JAR and SYSCS_SET_DATABASE_PROPERTY calls wire together a custom 
classpath for your database. All of the classes in the installed jar 
files will appear to your database session as though they are on your 
classpath. So you just need to refer to classes by their ordinary class 
names.


Try the following (assuming that TestListener lives in the root of the 
package space):


CREATE procedure extract_keyword (test VARCHAR(100))   LANGUAGE JAVA
PARAMETER STYLE JAVA  NO SQL EXTERNAL NAME 'TestListener.capitalize';

Hope this helps,
-Rick

On 9/12/19 5:14 PM, fkalim wrote:

To give more information, I compile my code into a jar, install the jar and
then try to use the Java file in the jar. The name of the java class is
TestListener. When I use the sql code below, I get the error:

ERROR 42X51: The class 'APP.TestListener' does not exist or is inaccessible.
This can happen if the class is not public or the derby.database.classpath
property is missing or incorrectly defined.
ERROR XJ001: Java exception: 'APP.TestListener:
java.lang.ClassNotFoundException'.

SQL:

CALL SQLJ.INSTALL_JAR('/listener.jar', 'APP.TestListener', 0);

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.database.classpath',
'APP.TestListener');

CREATE procedure extract_keyword (test VARCHAR(100))   LANGUAGE JAVA
PARAMETER STYLE JAVA  NO SQL EXTERNAL NAME 'APP.TestListener.capitalize';




--
Sent from: 
http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Calling a java function from a trigger

[Rick Hillegas]

Your sample program needs some tweaking:

1) The CREATE TRIGGER statement needs a REFERENCING clause in order to 
bind a new row transition variable. That is why you are getting the 
error 'Column name 'TEST' appears in a statement without a FROM list'


2) After that, you will run into a further issue: CALL statements invoke 
procedures rather than functions. A procedure is a Java method with no 
return value. A function is a Java method with a return value.


The following example works for me:

First compile this Java method:

public class TestProcs

{

    // sample db procedure

    public static void printOnConsole(String text)

    {

    System.out.println("Printing text on console: '" + text + "'");

    }

}


Now run the following ij script:

connect 'jdbc:derby:memory:db;create=true';

CREATE TABLE myTable(keyCol BIGINT, textCol VARCHAR(20));

CREATE PROCEDURE printOnConsole(textArg VARCHAR(20))

LANGUAGE JAVA

PARAMETER STYLE JAVA

NO SQL

EXTERNAL NAME 'TestProcs.printOnConsole';

CREATE TRIGGER insertAlert

AFTER INSERT ON myTable

REFERENCING NEW AS newRow

FOR EACH ROW

CALL printOnConsole(newRow.textCol);

INSERT INTO myTable VALUES

  (1, 'Hello')

, (2, 'World')

;


Hope this helps,
-Rick


On 9/12/19 2:13 AM, fkalim wrote:

Column name 'TEST' appears in a statement
without a FROM list.