Transaction Isolation Snapshot Exception on 2.1.1 upgrade

[NickPadilla]

I have checked out the 2.1.1 tag and have been attempting to upgrade a 1.9.x MSSQL 2008 R2 database. I am running using the sqljdbc4.jar to support a clustered/mirrored environment. This is the error I am getting:

[03/08/12 11:26:24:523 MDT]  INFO startup.StartupTasksExecutor: Running: Task00800CreateTemplateContainers
[03/08/12 11:26:24:523 MDT]  INFO db.DotConnect: Executing SET TRANSACTION ISOLATION LEVEL READ COMMITTED
[03/08/12 11:26:24:523 MDT]  INFO db.DotConnect: Executing Create table template_containers(id varchar(36) NOT NULL  primary key,template_id varchar(36) NOT NULL,container_id varchar(36) NOT NULL)
[03/08/12 11:26:24:524 MDT]  INFO db.DotConnect: Executing alter table template_containers add constraint FK_template_id foreign key (template_id) references identifier(id)
[03/08/12 11:26:24:571 MDT] ERROR runonce.Task00800CreateTemplateContainers: Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation.
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:213)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:676)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5569)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1629)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:649)
    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at com.dotmarketing.common.db.DotConnect.executeStatement(DotConnect.java:212)
    at com.dotmarketing.startup.runonce.Task00800CreateTemplateContainers.executeUpgrade(Task00800CreateTemplateContainers.java:48)
    at com.dotmarketing.startup.StartupTasksExecutor.executeUpgrades(StartupTasksExecutor.java:260)
    at com.liferay.portal.servlet.MainServlet.init(MainServlet.java:117)
    at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173)
    at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:993)
    at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4187)
    at org.apache.catalina.core.StandardContext.start(StandardContext.java:4496)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:546)
    at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:637)
    at org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.java:563)
    at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:498)
    at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1277)
    at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:321)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
    at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
    at org.apache.catalina.core.StandardHost.start(StandardHost.java:785)
    at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
    at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
    at org.apache.catalina.core.StandardService.start(StandardService.java:519)
    at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
    at org.apache.catalina.startup.Catalina.start(Catalina.java:581)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
    at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)

There must be some confusion on how the DotConnect.executeStatement() works. When using this method it creates a new connection for every statement and sets the Isolation Level to Snapshot, when it finds we are using MSSQL. I have run the needed SQL queries found on the dotcms upgrade 2.0 notes, and set the database to use READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION. I still get this error. If I comment out the setting of the Snapshot isolation level in the getConnection() method everything updates and inserts properly. I know this isn't going to be a solution but I needed to know if that would work. The problem is because of trying to add a constraint on a table within SNAPSHOT isolation, not sure on how other upgrade tasks are dealing with this; since this seems to be the only location where this is happening.

[jtesser]

I would use the proper driver. This works for us on MSSQL.

[NickPadilla]

@jtesser So your saying that dotCMS doesn't support MSSQL Clustering or Mirroring?

[NickPadilla]

@jtesser well mirroring anyway..

[jtesser]

We would recommend Oracle for DB replication etc..

It might work but we don't test/QA with the driver you are using.

[jtesser]

Also it appears from the message that your Isolation settings are not holding. you shouldn't get it. I would remove the clustering aspect of MSSQL for the upgrade. Upgrade get it working. Then put cluster back in place.

[NickPadilla]

@jtesser Well, this is in a development environment - no cluster or mirroring, but still need to use the sqljdbc4.jar. I think the isolation levels are not holding because we set the isolation level in a per connection basis, so each subsequent connection gets the isolation level reset.

[jtesser]

and that I believe is your issue. We do not expect that. Goes back to the settings I was mentioning earlier. Needs to be on the DB and let us manage the conns.

Again should work if you do your work around then revert the code for running after the upgrade. No promises here but it should work.

[NickPadilla]

@jtesser okay, cool - thanks! At least I know where I stand.