2

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. – Bart Koopman Jan 8 '14 at 12:51
  • Maybe security issues? I've had issues with SQL Server logins not working after restoring, and always needed some googling to figure out how to re-enable them – Nuno Linhares Jan 8 '14 at 13:22
  • Updated question with some config details. I tested with a UDL file that the web server could connect to the server using the credentials in cd_storage and it could. – Neil Jan 8 '14 at 13:33
5

Turned out to be my database user - it was missing db_datareader & db_datawriter roles from its Database role membership so was being denied the SELECT permission.

I discovered this by setting logging to DEBUG in logback.xml and the following errors appeared in my cd_core log:

2014-01-08 13:42:07,143 DEBUG JDBCExceptionReporter - could not execute update query [delete from DYNAMIC_LINKS where SRC_PUB_ID=? and SRC_ITEM_ID=? and SRC_ITEM_TYPE=?]
com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT permission was denied on the object 'DYNAMIC_LINKS', database 'Tridion_Broker', schema 'dbo'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.