Derby
  1. Derby
  2. DERBY-4172

You can open a read-write connection to a database which was originally opened by another thread using the classpath subprotocol

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: None
    • Component/s: JDBC
    • Urgency:
      Normal

      Description

      The original connection, using the classpath subprotocol, was supposed to open the DATABASE (not the connection) as read-only, according to the Developer's Guide section titled "Database connection examples". However, I am able to write to this database in another connection which opens the database using the default, file-based protocol.

      At a minimum, the documentation is wrong. But the documentation may be trying to impose a consistent, easily described model on the behavior of our subprotocols. It may be that the behavior of our subprotocols cannot be described by a simple set of rules that users can grasp easily.

      To show this problem, I created a database and then moved it into a directory on my classpath (in this case, trunk/classes). Here is a script which shows this behavior:

      connect 'jdbc:derby:classpath:derby10.6' as conn1;

      – fails because a database which is opened on the classpath is supposed to be marked as read-only
      insert into t( a ) values 1;

      connect 'jdbc:derby:trunk/classes/derby10.6' as conn2;

      – this succeeds even though the database is supposed to be read-only
      – according to the Developer's Guide section "Database connection examples"
      insert into t( a ) values 2;

      select * from t;
      delete from t;

        Activity

        Hide
        Dag H. Wanvik added a comment -

        It seems another database instance is opened "on top" of the read-only one, since the read-only database places no lock file (reasonably enough), Derby doesn't figure out that when the file based boot takes place, we are really dealing with the same database, so another instance is booted. In short, to Derby, they are two different databases

        Show
        Dag H. Wanvik added a comment - It seems another database instance is opened "on top" of the read-only one, since the read-only database places no lock file (reasonably enough), Derby doesn't figure out that when the file based boot takes place, we are really dealing with the same database, so another instance is booted. In short, to Derby, they are two different databases
        Hide
        Dag H. Wanvik added a comment - - edited

        If the file name is simple, Derby handles it (current directory is on classpath here):

        ij> connect 'jdbc:derby:classpath:wombat';
        ij> insert into t values 1;
        ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
        ij> connect 'jdbc:derby:wombat';
        ij(CONNECTION1)> insert into t values 1;
        ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
        ij(CONNECTION1)>

        The second connect uses the normal file based URL. If I used an absolute path Derby fails to match them:

        ij(CONNECTION1)> connect 'jdbc:derby:/export/home/dag/java/sb/tests/foo/wombat';
        ij(CONNECTION2)> insert into t values 1;
        1 row inserted/updated/deleted

        although this is the same "wombat":

        ij(CONNECTION2)> exit;
        dag@T61pOS:~/java/sb/tests/foo$ pwd
        /export/home/dag/java/sb/tests/foo

        derby.log shows two booted instances, corresponding to connect 1 and 3 above,

        Show
        Dag H. Wanvik added a comment - - edited If the file name is simple, Derby handles it (current directory is on classpath here): ij> connect 'jdbc:derby:classpath:wombat'; ij> insert into t values 1; ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database. ij> connect 'jdbc:derby:wombat'; ij(CONNECTION1)> insert into t values 1; ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database. ij(CONNECTION1)> The second connect uses the normal file based URL. If I used an absolute path Derby fails to match them: ij(CONNECTION1)> connect 'jdbc:derby:/export/home/dag/java/sb/tests/foo/wombat'; ij(CONNECTION2)> insert into t values 1; 1 row inserted/updated/deleted although this is the same "wombat": ij(CONNECTION2)> exit; dag@T61pOS:~/java/sb/tests/foo$ pwd /export/home/dag/java/sb/tests/foo derby.log shows two booted instances, corresponding to connect 1 and 3 above,
        Hide
        Knut Anders Hatlen added a comment -

        In the first case mentioned by Dag, the second db is probably opened in read-only mode because of DERBY-4171. (That is, when not specifying a subsubprotocol, a connection to a currently booted db with the same name is opened, even if the booted database does not use the default subsubprotocol.)

        Show
        Knut Anders Hatlen added a comment - In the first case mentioned by Dag, the second db is probably opened in read-only mode because of DERBY-4171 . (That is, when not specifying a subsubprotocol, a connection to a currently booted db with the same name is opened, even if the booted database does not use the default subsubprotocol.)
        Hide
        Kristian Waagan added a comment -

        I can confirm what Knut says.
        When running the sequence described by Dag with the patch for DERBY-4171, I'm able to insert data with the second connection (using the default subsubprotocol; 'jdbc:derby:wombat').

        Show
        Kristian Waagan added a comment - I can confirm what Knut says. When running the sequence described by Dag with the patch for DERBY-4171 , I'm able to insert data with the second connection (using the default subsubprotocol; 'jdbc:derby:wombat').
        Hide
        Rick Hillegas added a comment -

        How does the following user-visible behavior sound? I think this would be easy to explain and the backward incompatibilities would be very rare (probably non-existent) and easy to workaround:

        • We could distinguish between logical and physical database names:
          o A logical database name consists of the subprotocol plus the database path. That is, each subprotocol defines a separate namespace of databases.
          o The physical database is an actual database which is opened. The same physical database may have more than one logical name.
        • We would prevent a physical database from being opened under two different logical names simultaneously. Once a database has been opened under a logical name, other connections to the database must use that original logical name. After the database has been closed, it can be re-opened under a different logical name.
        Show
        Rick Hillegas added a comment - How does the following user-visible behavior sound? I think this would be easy to explain and the backward incompatibilities would be very rare (probably non-existent) and easy to workaround: We could distinguish between logical and physical database names: o A logical database name consists of the subprotocol plus the database path. That is, each subprotocol defines a separate namespace of databases. o The physical database is an actual database which is opened. The same physical database may have more than one logical name. We would prevent a physical database from being opened under two different logical names simultaneously. Once a database has been opened under a logical name, other connections to the database must use that original logical name. After the database has been closed, it can be re-opened under a different logical name.
        Hide
        Dag H. Wanvik added a comment -

        Sounds reasonable.
        So, do these have the same logical name (protocol+path defines a logical name):

        jdbc:derby:wombat
        jdbc:derby:/export/home/wombat

        assuming pwd is /export/home? It not, if would be more restrictive that currently. So I think you need to refine
        the definition of logical name if your rule is to work?

        Show
        Dag H. Wanvik added a comment - Sounds reasonable. So, do these have the same logical name (protocol+path defines a logical name): jdbc:derby:wombat jdbc:derby:/export/home/wombat assuming pwd is /export/home? It not, if would be more restrictive that currently. So I think you need to refine the definition of logical name if your rule is to work?
        Hide
        Rick Hillegas added a comment -

        Hi Dag,

        Thanks for bringing up this issue. I agree that those two logical names should be considered the same. We could add another rule for the default directory subprotocol and say that the full logical name involves the canonicalized version of the directory name. I don't think this issue comes up with the other subprotocols, does it?

        Show
        Rick Hillegas added a comment - Hi Dag, Thanks for bringing up this issue. I agree that those two logical names should be considered the same. We could add another rule for the default directory subprotocol and say that the full logical name involves the canonicalized version of the directory name. I don't think this issue comes up with the other subprotocols, does it?
        Hide
        Knut Anders Hatlen added a comment -

        I think you can see a similar issue with the jar protocol:

        jdbc:derby:jar:(mydb.jar)wombat
        jdbc:derby:jar/export/home/mydb.jar)wombat

        To avoid a special rule for the directory subprotocol, perhaps we could say: A logical database name consists of the subprotocol plus a canonical database path determined by the subprotocol's persistent service.

        The described behaviour sounds fine to me. Basically, what you suggest we disallow is the following (assuming /path/to is in the classpath):

        connect 'jdbc:derby:directory:/path/to/wombat'; -> OK
        connect 'jdbc:derby:classpath:wombat'; -> ERROR: The database '/path/to/wombat' is already opened using another protocol

        This would currently work, but the second (read-only) connection could see strange behaviour if the first connection makes any changes to the database, so I think disallowing it is fine.

        Show
        Knut Anders Hatlen added a comment - I think you can see a similar issue with the jar protocol: jdbc:derby:jar:(mydb.jar)wombat jdbc:derby:jar /export/home/mydb.jar)wombat To avoid a special rule for the directory subprotocol, perhaps we could say: A logical database name consists of the subprotocol plus a canonical database path determined by the subprotocol's persistent service. The described behaviour sounds fine to me. Basically, what you suggest we disallow is the following (assuming /path/to is in the classpath): connect 'jdbc:derby:directory:/path/to/wombat'; -> OK connect 'jdbc:derby:classpath:wombat'; -> ERROR: The database '/path/to/wombat' is already opened using another protocol This would currently work, but the second (read-only) connection could see strange behaviour if the first connection makes any changes to the database, so I think disallowing it is fine.
        Hide
        Rick Hillegas added a comment -

        Thanks, Knut. That sounds like a good refinement to me.

        Show
        Rick Hillegas added a comment - Thanks, Knut. That sounds like a good refinement to me.
        Hide
        Rick Hillegas added a comment -

        Triaged July 2, 2009: Assigned normal urgency.

        Show
        Rick Hillegas added a comment - Triaged July 2, 2009: Assigned normal urgency.

          People

          • Assignee:
            Unassigned
            Reporter:
            Rick Hillegas
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development