Uploaded image for project: 'ModeShape'
  1. ModeShape
  2. MODE-2664

Microsoft SQL Server persistence uses incorrect CONTENT type and SQL Server create/drop Exceptions caught


    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 5.3.0.Final
    • Fix Version/s: 5.4.0.Final
    • Component/s: None
    • Labels:


      See community post relating to the error ModeShape 5.3 / 5.4 and SQL Server|https://developer.jboss.org/message/968016#968016]

      Two problems were observed.

      1. problem 1 The schema DDL script needs to be updated in sqlserver_database.properties,
        1. Create_table, CONTENT's type should be changed from VARBINARY to VARBINARY(MAX). Otherwise, the content table is getting created in SQL Server 2014 as VARBINARY(1) which is too small to store almost anything. See revised file.
      2. problem 2 When ModeShape starts up against a SQL Server database, it tries to create the necessary tables. If it fails because the object already exists, SQL Server's exception is not caught and the whole application exits. We need similar changes to what were make for Oracle, to gracefully catch and ignore the exceptions when trying to create an object that already exists or trying to a delete a non-existent object / or permissions error.
        1. Creation of a new class SQLServerStatements.java which handles the two exceptions in SQL Server. This is modeled after OracleStatements.java
        2. ## RelationalDb, to call SQLServerStatements if the database type is SQL Server.

      When I applied these changes to the 5.3.0.Final modeshape-persistence-relational code, I was able to start up ModeShape against a SQL Server database, without exceptions stopping the application, on initial database creation, or subsequent startups. The modeshape-persistence-relational 5.3.0.Final is identical to the 5.4 master branch, as of Feb 3, 2017, so this revised version could be applied to 5.4 w/o problem.

      I have not performed testing on the flow where it tries to delete a non-existent object or where the database user does not have permissions to do so.

      ModeShape w/ SQL Server should ignore (or treat errors as warnings) for these exceptions
      2714/* object already exists*/,
      3701/* object doesn't exist or cannot drop due to permissions*/

      Error before bug fix

      Current errors
      09:12:10,024 DEBUG Looking for JBoss Standalone JTA...
      09:12:10,027 DEBUG Looking for Atomikos JTA...
      09:12:10,032 DEBUG No javax.transaction.TransactionManager was found; falling back to LocalTransactionManager. Enable DEBUG logging for more information.
      09:12:10,087 DEBUG Creating table MODESHAPE_REPOSITORY...
      09:12:10,105 DEBUG Shutting down locking service...
      09:12:10,119 ERROR Cannot load the names of the available repositories.
      javax.jcr.RepositoryException: org.modeshape.persistence.relational.RelationalProviderException: com.microsoft.sqlserver.jdbc.SQLServerException: There is already an object named 'MODESHAPE_REPOSITORY' in the database.
      at org.modeshape.jcr.JcrRepositoriesContainer$UrlRepositoryLookup.repository(JcrRepositoriesContainer.java:228)

        Gliffy Diagrams




              • Assignee:
                hchiorean Horia Chiorean
                dharrison David Harrison
              • Votes:
                1 Vote for this issue
                3 Start watching this issue


                • Created: