Content deployment failed when publishing to Oracle broker db

Question

I get an error when I try to deploy content to the oracle broker database (I am running Tridion 2011 sp1). I had a search on the SDL Tridion forum and I can not find anyone who had the same problem.

Error message in cme:

Committing Deployment Failed Phase: Deployment Prepare Commit Phase failed, Unable to prepare transaction: tcm:0-55-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query, Unable to prepare transaction: tcm:0-55-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query 

And in the deployment logs, I get the following:

 2012-02-24 15:07:27,866 WARN DeployPipelineExecutor - Phase: Deployment Prepare Commit Phase failure message: Unable to prepare transaction: tcm:0-55-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query for transaction: tcm:0-55-66560 2012-02-24 15:07:27,866 DEBUG DeployPipelineExecutor - Failure in Phase: Deployment Prepare Commit Phase attempt: 11 for transaction: tcm:0-55-66560 2012-02-24 15:07:27,866 ERROR DeployPipelineExecutor - Final attempt in Phase: Deployment Prepare Commit Phase failed for transaction: tcm:0-55-66560 2012-02-24 15:07:27,875 ERROR DeployPipelineExecutor - Original stacktrace for transaction: tcm:0-55-66560 com.tridion.deployer.ProcessingException: Unable to prepare transaction: tcm:0-55-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(Executors.java:417) [na:1.5.0_11] at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269) [na:1.5.0_11] at java.util.concurrent.FutureTask.run(FutureTask.java:123) [na:1.5.0_11] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650) [na:1.5.0_11] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675) [na:1.5.0_11] at java.lang.Thread.run(Thread.java:595) [na:1.5.0_11] 2012-02-24 15:07:27,875 INFO TransactionPersistence - Removing deployment transaction information: tcm:0-55-66560 2012-02-24 15:07:27,876 ERROR DeployPipelineExecutor - Unable to start processing deployment package with transactionId: tcm:0-55-66560 2012-02-24 15:07:27,880 DEBUG DeployPipelineExecutor - Checking if transaction is completed: tcm:0-55-66560 is true 2012-02-24 15:07:27,880 INFO DeployPipelineExecutor - Transaction is completed: tcm:0-55-66560 2012-02-24 15:07:27,882 INFO DeployPipelineExecutor - Finished executing deployment pipeline for: tcm:0-55-66560 in 2274 ms. 

EDIT:

Here is the node from the cd_storage_conf.xml file:

 <Storages> <Storage Type="persistence" Id="ddeploy" dialect="ORACLESQL" Class="com.tridion.storage.persistence.JPADAOFactory"> <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120" /> <DataSource Class="oracle.jdbc.pool.OracleDataSource"> <Property Name="driverType" Value="thin"/> <Property Name="networkProtocol" Value="tcp"/> <Property Name="serverName" Value="secret"/> <Property Name="portNumber" Value="1526"/> <Property Name="databaseName" Value="secret"/> <Property Name="user" Value="secret"/> <Property Name="password" Value="secret"/> </DataSource> </Storage> <Storage Type="filesystem" Class="com.tridion.storage.filesystem.FSDAOFactory" Id="defaultFile" defaultFilesystem="false"> <Root Path="/htdocs/en_US/" /> </Storage> <Storage Type="filesystem" Class="com.tridion.storage.filesystem.FSDAOFactory" Id="defaultDataFile" defaultFilesystem="true" defaultStorage="true"> <Root Path="/htdocs/en_US/data" /> </Storage> </Storages> 

And item types

 <ItemTypes defaultStorageId="ddeploy" cached="false"> <Item typeMapping="Page" cached="false" storageId="defaultFile"/> <Item typeMapping="Binary" cached="false" storageId="defaultFile"/> </ItemTypes> 

Any help would be greatly appreciated.

Answer 1

Doh! “I can confirm that the problem is that the oracle database user did not have access to the correct schema.”

However, if someone is looking for the same problem here, they will know the right place to search :)

Answer 2

The error message indicates that the SQL query generated by hibernation is not working properly in your database. Can you double check the configuration of your storage (perhaps place the <Storage /> element here. In particular, you can check the correctness of the dialect attribute.