After restoring the CM and Broker databases from our production environment any publish action fails with the following:
Phase: Deployment Prepare Commit Phase failed, Unable to prepare transaction: tcm:0-125412-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query, Unable to prepare transaction: tcm:0-125412-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query
Stack trace from cd_deployer log:
2014-01-08 11:53:08,112 ERROR DeployPipelineExecutor - Final attempt in Phase: Deployment Prepare Commit Phase failed for transaction: tcm:0-125412-66560 2014-01-08 11:53:08,112 ERROR DeployPipelineExecutor - Original stacktrace for transaction: tcm:0-125412-66560 com.tridion.deployer.ProcessingException: Unable to prepare transaction: tcm:0-125412-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query at com.tridion.deployer.phases.PreCommitPhase.handleFailure(PreCommitPhase.java:120) ~[cd_deployer.jar:na] at com.tridion.deployer.phases.PreCommitPhase.execute(PreCommitPhase.java:101) ~[cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.runMainExecutePhase(DeployPipelineExecutor.java:186) [cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.doExecute(DeployPipelineExecutor.java:97) [cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.execute(DeployPipelineExecutor.java:61) [cd_deployer.jar:na] at com.tridion.deployer.TransactionManager.handleDeployPackage(TransactionManager.java:80) [cd_deployer.jar:na] at com.tridion.deployer.queue.QueueLocationHandler$1.run(QueueLocationHandler.java:176) [cd_deployer.jar:na] at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [na:1.6.0_37] at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) [na:1.6.0_37] at java.util.concurrent.FutureTask.run(Unknown Source) [na:1.6.0_37] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) [na:1.6.0_37] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.6.0_37] at java.lang.Thread.run(Unknown Source) [na:1.6.0_37] 2014-01-08 11:53:08,143 ERROR DeployPipelineExecutor - Unable to start processing deployment package with transactionId: tcm:0-125412-66560
Stack trace from cd_core log:
2014-01-08 11:53:08,112 ERROR DeployPipelineExecutor - Final attempt in Phase: Deployment Prepare Commit Phase failed for transaction: tcm:0-125412-66560 2014-01-08 11:53:08,112 ERROR DeployPipelineExecutor - Original stacktrace for transaction: tcm:0-125412-66560 com.tridion.deployer.ProcessingException: Unable to prepare transaction: tcm:0-125412-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query at com.tridion.deployer.phases.PreCommitPhase.handleFailure(PreCommitPhase.java:120) ~[cd_deployer.jar:na] at com.tridion.deployer.phases.PreCommitPhase.execute(PreCommitPhase.java:101) ~[cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.runMainExecutePhase(DeployPipelineExecutor.java:186) [cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.doExecute(DeployPipelineExecutor.java:97) [cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.execute(DeployPipelineExecutor.java:61) [cd_deployer.jar:na] at com.tridion.deployer.TransactionManager.handleDeployPackage(TransactionManager.java:80) [cd_deployer.jar:na] at com.tridion.deployer.queue.QueueLocationHandler$1.run(QueueLocationHandler.java:176) [cd_deployer.jar:na] at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [na:1.6.0_37] at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) [na:1.6.0_37] at java.util.concurrent.FutureTask.run(Unknown Source) [na:1.6.0_37] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) [na:1.6.0_37] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.6.0_37] at java.lang.Thread.run(Unknown Source) [na:1.6.0_37] 2014-01-08 11:53:08,143 ERROR DeployPipelineExecutor - Unable to start processing deployment package with transactionId: tcm:0-125412-66560
Nothing has changed in the config files (storage, deployer etc) and I've checked that the broker database username/password are correct and the db can be accessed from the deployer server.
The version of Tridion is the same in both cases - anyone seen this before?
UPDATE
Some details on the database servers:
- Both are SQL Server 2008 R2 (10.50.2500.0).
- Both have collation set to Latin1_General_CI_AS.
- Both have matching character sets: ntext:UNICODE, nvarchar:UNICODE, varchar:iso_1
I restored my backup copy of the broker db to a new db, changed cd_storage and republished and got Success! When I switched cd_storage back to my restored copy, I get Failed (with the SQLGrammarException).
org.hibernate.exception.SQLGrammarException
makes me wonder about your database configuration, can you share some more details like type (Oracle or MS SQL Server) and some details of its configuration like character set and collation? Most of the times for a restore to work correctly, these should be identical to the ones where the backup was made.