Uploaded image for project: 'Apache Roller'
  1. Apache Roller
  2. ROL-1927

Roller 5 MSSQL Issues/Fixes



    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.0
    • Fix Version/s: 5.1.0
    • Component/s: Themes and Macros
    • Labels:
    • Environment:
      Tomcat 6.0.32
      MS SQL Server 2008 R2 Express
      Sun JDK
      Ubutnu 11.04(Linux Kernal 2.6.38-10-generic)
      Google Chrome, stable release.
    • Browser Version:
      Google Chrome, stable release
    • Database:
      MS SQL Server 2008 R2 Express
    • JDK Version:
      Sun JDK
    • O/S Version:
      Ubutnu 11.04(Linux Kernal 2.6.38-10-generic)


      When using MSSQL there are a few things that go wrong in the upgrade and new install. First of all the .vm files that build the .sql are building .sql files that will not run on MS SQL Server. Mostly problems with NOT using the $db. variables, fixed those so that they create datetime columns instead of using timestamp. There were a few problems with the macros.vm file as well as it wasn't taking into account the MSSQL syntax, fixed those as well. Now when I build the project it builds the create and update .sql files correctly and they run without errors.

      The .sql script errors wasn't the only thing that was causing a problem when using the MSSQL, there was an even more insidious problem. I am not 100% sure which is the problem, OpenJPA not supporting in-memory operations when using MSSQL or if the MSSQL doesn't support the in-memory operations. My reasoning behind this is because of the problem i encountered, and a few more have encountered it as well but didn't actually fix the problem. So what happens is this, I create a new DB and roller instance, use the default Container and DB providers, which is Jetty and Derby. Go through the motions of creating a user and weblog, everything goes fine. However, when I load up tomcat with the same instance of Roller and point it at a MSSQL database, it does its thing (creates the db and such, happening correctly now) and i create my user no problem but it errors out when creating the weblog. The error is misleading, as it is saying that there was a Failed Object, WeblogPermissions. This object is now just a decorator for the ObjectPermission class, but that isn't the problem. The problem is that the weblog gets flushed on line 287 in the JPAWeblogManagerImpl class, this persists the object to the db. Then next line of code sets up the needed objects when creating a new weblog, it creates the WeblogPermissions (and other objects) and stores them in memory. However, I found that when this call is made "pingTargetMgr.getCommonPingTargets().iterator();" at the end of the addWeblogContents(Weblog) method, it attempts to use a named query that requires the object tree be persisted to the db. It expects that the WeblogPermissions be accessible from the database, but it isn't in the database its in memory, since it hasn't been flushed yet. The named query filters down to a inInMemory(int) call which is in org.apache.openjpa.kernel.QueryImpl, this method then tries to flush the current transaction. This is when the error happens, not sure why it can't flush, but it tries to find the WeblogPermissions and can't. Either way that is where the error source is from, why it can't flush the transaction then run the named query is odd, but it is what it is.

      Instead of finding out why the call to flush() errors out I decided to move a bit of the code around and make an explicit call to flush() prior to the getCommonPingTargets() call. This persists the objects in memory and so the getCommonPingTargets() returns normally, without errors. I have been able to successfully update and create instances of Roller 5, from Roller 4 only. I didn't go through all the previous .vm files to fix them for MSSQL, just the 400-to-500 one.

      Here are a list of files I have edited:


      I have also sent out the (ICLA) so I can contribute this patch, and any others I need to in the future. All the tests ran fine and I did test using Derby and Jetty to ensure nothing I did was wrong for the different container/db provider. I can't think of any reason why my logic would be wrong, or cause problems for, other containers or db providers. Once I have a login for the Roller ticketing system I will create the ticket for these changes. Would you suggest two issues, one for the SQL files an the other for the code change? Please let me know what you will need, I have the files, of course, but also the distributions that contain the fixes for production use. I would like to commit these changes myself, probably to trunk I would assume, so let me know what needs to be done on my end, or if that is possible. Thanks for your time and a very cool project!

      As a side note, I have built tomcat distributions, .tar.gz and .zip, that work with MSSQL; should work with any other provider as well. I have them available here:


      Please let me know if there are any questions, comments, or concerns.


        1. 400-to-500-migration.vm
          6 kB
          Nick Padilla
        2. createdb.vm
          25 kB
          Nick Padilla
        3. macros.vm
          4 kB
          Nick Padilla
        4. JPAWeblogManagerImpl.java
          27 kB
          Nick Padilla



            • Assignee:
              djohnson David Johnson
              nickpadilla Nick Padilla
            • Votes:
              0 Vote for this issue
              0 Start watching this issue


              • Created: