db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kristian Waagan (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4428) Add proper delete mechanism for in-memory databases
Date Mon, 02 Nov 2009 11:10:59 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4428?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12772492#action_12772492
] 

Kristian Waagan commented on DERBY-4428:
----------------------------------------

Thanks for the comments, Dag and Rick.
See my replies below, which address comments from both of you.

(Dag)> SQL uses the keyword "DROP", is there a reason you prefer "delete" over drop in
the URL?
No, I wouldn't mind changing it to "drop" if that is more in line with the SQL spec. Is there
something like "drop database" in the SQL spec?
Note that I don't have any plans at this time to implement a way to delete databases using
SQL.

(Dag)> ... [authentication and authorization] ... But it would be slightly asymmetrical.
What do you think?
(Rick)> I wonder if we need to check whether authentication is on?
I think we need two different paths here. Assuming SQL authorization will be used to control
who can delete a database in the future, what about this?
  if (authenticationOn && sqlAuthorizationOn) {
    checkPrivileges(); // May also want to see if the user is the DBO here, depending on implementation
details.
  } else {
    checkIsDBOwner();
  }

Since we don't have the required functionality to use system privileges through GRANT/REVOKE
yet, we can just check for DBO unconditionally as Rick suggests.
Note that this will allow only the DBO to delete the database. Another option is to allow
everyone to delete a database if no authentication/authorization is enabled, but this is of
course very liberal.

(Rick)> ... [authentication] ... In such a situation, I think it's enough to require that
there be no open connections to the database. 
This doesn't happen on shutdown, does it? Why should it happen on drop/delete, in which case
the data goes away anyway?
The only reason I can think of, is to allow the existing connections to finish their queries.
As an experiment, I implemented a simple mechanism to block database access. The mechanism
was activated by the delete request, but could also be made available through the JDBC connection
URL (would make it possible for the DBO to block access for maintenance work). My test, which
ran 3 - 20 concurrent threads accessing the database to be deleted and then executing a simple
query, gave much better results when the block mechanism was used. There are levels of consideration:
 - wait for ongoing connection attempts to finish (with the block mechanism, new connections
won't be allowed)
 - wait for a given time to allow [some] queries to complete (issue with long running queries)
 - wait for all connected clients to disconnect (with timeout?)

Especially ongoing connection attempts result in lots of different error messages. I've seen
the shutdown exception (which is fine, I think), NPEs, conglomerate not found exceptions,
and others. It is also a question of when one should signal / perform shutdown, as this will
cause the connections to be dropped at some point.

(Dag)> I think enabling dropping of on-disk databases is interesting. What would the cons
be?
I'm thinking mostly about the security aspect. Are there other issues?
A possible add-on feature here would be a property disabling the JDBC delete mechanism for
production systems, but this could be problematic if deleting some databases in the system
should be allowed. Maybe SQL authorization could be used instead, if the drop/delete privilege
can't be granted again if it has been revoked?

(Rick)> I think the feature should be designed so that it can be extended to the on-disk
back end. However, I would treat on-disk back ends as a separate JIRA - ...
I agree. This will require some extra code right away, because the functionality is already
used internally (to re-create the service root). I know about several ways deletion can be
disabled for a specific storage factory, but they all have drawbacks... Maybe there is a better
way I haven't found yet, but I'll get back to the details later.

(Rick)> ... [system privileges] ... That privilege includes the specification of where
you are allowed to create databases.
Even though "where" isn't quite as well-defined in the in-memory back end as in the on-disk/directory
back end, I think such a privilege may be enough. However, the Java security manager, or a
custom one, would have to be faulted in since the in-memory back end doesn't access the virtual
files through java.io.File. 
It is not clear to me if such a privilege would allow us to differentiate between back ends,
or rather subsubprotocols.
Would the system privileges also allow us to say who are allowed to create databases?


I now have the following known tasks on my list:
 - add in-memory specific code required for drop
 - add generic code required for drop (includes URL handling)
     - add database access block mechanism
 - disable drop/delete for on-disk back end (the rest of the back ends are read only)
 - add drop tests (for in-memory back end primarily)
 - add mechanism to control / limit database creation (we can already  use the Java security
manager for on-disk databases, but not for in-memory databases)
(- improve handling of unsuccessful boots in the in-memory storage factory (resource management
issue))

Further comments are welcome.

> Add proper delete mechanism for in-memory databases
> ---------------------------------------------------
>
>                 Key: DERBY-4428
>                 URL: https://issues.apache.org/jira/browse/DERBY-4428
>             Project: Derby
>          Issue Type: Improvement
>          Components: JDBC, Services, Store
>    Affects Versions: 10.6.0.0
>            Reporter: Kristian Waagan
>            Assignee: Kristian Waagan
>         Attachments: derby-4428-0a-preview_patch.diff
>
>
> The current mechanism for deleting in-memory databases isn't good enough, and a proper
one must be added.
> It is also important to be able to delete in-memory databases, since they occupy valuable
main memory that should be discarded when the database is no longer needed.
> I intend to implement the mechanism by using the JDBC connection URL:
> "jdbc:derby:memory:myDatabase;delete=true[;user=X;password=Y]
> The connection attempt will throw an exception in any case, either because the request
failed or because it succeeded.
> Reasons for a failure can be invalid user and/or password, lacking encryption attributes,
or conflicting attributes.
> For the time being, only the database owner will be allowed to delete databases (*note*:
do we have a way to control/limit in-memory database creation?)

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message