Can't delete Apache Derby Embedded DB System Directory

Question

Derby is unable to delete the system directory upon exiting using drop=true.

Here's a minimal example of my challenge:

package derbytest;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author aelder
 */
public class DerbyTest {

    private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
    private static final String CONN_URL = "jdbc:derby:EmbeddedDBAudit";

    private static File databaseFile;
    private static final String USER_HOME_DIR = System.getProperty("user.home", ".");

    public static Connection getConnection(boolean createDatabase) throws SQLException {
        return DriverManager.getConnection(CONN_URL + (createDatabase ? ";create=true" : ""));
    }

    public static void shutdownConnectionAndCleanup() {
        try {
            DriverManager.getConnection(CONN_URL + ";drop=true");
        } catch (SQLException ex) {
            if (!ex.getSQLState().equals("08006")) {
                ex.printStackTrace();
            }
        }
    }

    public static void setDerbyHome() {
        setDatabaseFile("");

        int index = 1;
        while (databaseFile.exists()) {
            setDatabaseFile(String.valueOf(index++));
        }

        // Set the db system directory.
        System.setProperty("derby.system.home", databaseFile.getAbsolutePath());
    }

    private static void setDatabaseFile(String auditFolderCount) {
        String databaseFilePATH = USER_HOME_DIR + File.separator + ".EmbeddedDBAudit" + auditFolderCount;

        databaseFile = new File(databaseFilePATH);
        databaseFile.deleteOnExit();
    }

    public static void initDerbyHomeAndDriver() {
        setDerbyHome();

        initDerbyDriverInstance();
    }

    public static void initDerbyDriverInstance() {
        try {
            Class.forName(DRIVER).newInstance();
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException ex) {
            Logger.getLogger(DerbyTest.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static boolean tableAlreadyExists(SQLException e) {
        return e.getSQLState().equals("X0Y32");
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        try {
            initDerbyHomeAndDriver();
            getConnection(true);
            shutdownConnectionAndCleanup();
        } catch (SQLException ex) {
            Logger.getLogger(DerbyTest.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

When I run this I get:

java.sql.SQLException: Directory EmbeddedDBAudit cannot be removed.
    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.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.<init>(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:270)
    at derbytest.DerbyTest.shutdownConnectionAndCleanup(DerbyTest.java:34)
    at derbytest.DerbyTest.main(DerbyTest.java:104)
Caused by: ERROR XBM0I: Directory EmbeddedDBAudit cannot be removed.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.iapi.services.monitor.Monitor.removePersistentService(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection$6.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at org.apache.derby.impl.jdbc.EmbedConnection.removePersistentService(Unknown Source)
    ... 12 more

The code attempts to append a counter to the end of the system directory so that multiple instances of a program using the embedded database can be running at the same time.

Desired behavior: The folder created at user.home/.EmbeddedDBAudit should be deleted when drop=true is called.

Answer 1

The behavior you are seeing is the documented behavior.

drop=true is to be used for in-memory databases only, not persistent databases.

See: http://db.apache.org/derby/docs/10.14/ref/rrefattribdrop.html

If you specify this attribute with a database that is not an in-memory database, Derby generates the SQLException XBM0I.