SqlJmsProvider - Table or view does not exist

[Raja Ramachandran]

Hi,

 

This weird error started showing up in our dev all of a sudden. atg.dynamo.messaging.SqlJmsProvider is unable to startup due to table not found error. I checked all the dms_* tables in the db and they look fine to me( matched it again the DDL scripts).  

 

I'm on 2007.1 with Jboss 4.0.5 and Oracle 10G.

 

This error is not happening in int/qa etc so I'm sure this an environment specific configuration issue. I check the connection of JTDatasource and they are fine.

 

Any ideas?

 

Thanks,

Raja

[Raj]

This is the error.....

2008-03-08 09:59:03,989 ERROR
[nucleusNamespace.atg.dynamo.messaging.SqlJmsProvider]
java.sql.SQLException: ORA-00942: table or view does not exist
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
145)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:
216)
at
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:
1120)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:
1279)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:
3415)
at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:
3498)
at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:
251)
at atg.dms.sql.SqlTopicManager.flagWithStatement(SqlTopicManager.java:
991)
at atg.dms.sql.SqlTopicManager.flagNewEntries(SqlTopicManager.java:
872)
at atg.dms.sql.SqlTopicManager.pollForNewEntries(SqlTopicManager.java:
816)
at atg.dms.sql.SqlJmsManager.poll(SqlJmsManager.java:1584)
at atg.dms.sql.SqlJmsManager.performScheduledTask(SqlJmsManager.java:
895)
at atg.service.scheduler.ScheduledJob.runJobs(ScheduledJob.java:441)
at atg.service.scheduler.Scheduler$2handler.run(Scheduler.java:760)
2008-03-08 09:59:23,969 INFO [STDOUT] 9:03 AM
oracle.jdbc.driver.PhysicalConnection needLine
FINE: PhysicalConnection.needLine()--no return
Mar 8, 2008 9:59:03 AM oracle.jdbc.driver.T4CConnection open

[Belmont, Adam]

I believe this is the SQL attempting to be executed there:
"UPDATE dms_topic_entry
sb.append("SET read_state = ? ");
sb.append("WHERE delivery_date < ? ");
sb.append(" AND read_state = 0 ");
sb.append(" AND subscriber_id IN (SELECT ts.subscriber_id ");
sb.append(" FROM dms_topic_sub ts ");
sb.append(" WHERE ts.client_id = ? ");
sb.append(" AND ts.active = 1) ");

So make sure you have

dms_topic_entry
and
dms_topic_sub_ts

Confirm you are using the correct data sources as well. Maybe a jndi name
got crossed or something.

-- adam

This is the error.....

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"ATG_Tech" group.
To post to this group, send email to atg_...@googlegroups.com
To unsubscribe from this group, send email to
atg_tech-u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/atg_tech?hl=en
-~----------~----~----~----~------~----~------~--~---

[Raja Ramachandran]

Thanks for the response.

 

You are right. That is the SQL.  Unfortunately the datasource connections are correct and I can see these two tables in the DB.

 

Here is what I did just,

 

Invoked  doStopService() on SqlJmsProvider, it unregistered a bunch of subscribers and then did a doStartService().

 

"unregistering subscriber 14090002 with topic sqldms/DASTopic/GSAInvalidator" ...there were about 10 more like this.

 

That seems to have fixed the issue. I'm going to be testing it for a while to see it doesnt resurfaces. 

 

Can you tell me what happened behind the scenes?

 

Thanks,

Raja 

[George Oprica]

This is a bit off the wall, but I saw an issue recently where someone
was having similar problems. They would execute the SQL from a SQL
editor and it would work fine. Select statements would always work
either from a SQL editor or from within the ATG server. Only updates
on this table by the ATG server via the datasource would ever error
out with the table not found error.

We finally reproduced the problem with a simple standalone JDBC
program (no ATG code at all), so the problem is either a driver issue,
or a permissions problem. Check your DB privileges and synonym
configuration and hopefully you find something there. Note that if you
connect using the credentials of the owner of the tables (not someone
with access to them via synonyms) then it works fine.

Like I said, off the wall, but keep this in mind if everything else checks out.
- George

[Belmont, Adam]

I’m not sure.

It’s a pretty simple loop.

It gets a datasource from MessagingManager, calls “getConnection()” on that datasource then executes sql.

Doing a “restart” might cause messaging manager to get a new instance of a datasource.

 

 -- Adam

 

---

[Raja Ramachandran]

False call. It just suspended the issue for that session. And after that none of my JMS messages were getting fired. Once I resart the box, the issue comes back to haunt.

 

To George's suggestion,

 

the schema_name is atg_core( essentially the owner of the objects) and I connect as that in toad and in the app. Let me try the simple java program that and see if that flies.

 

 

On Mon, Mar 10, 2008 at 3:48 PM, Belmont, Adam <ad...@atg.com> wrote:

I'm not sure.

[Raja Ramachandran]

Ok. Since it was dev, I just truncated all the dms_* tables and this issue mysteriously disappeared. I have been able to place orders, the JMS message flow is unhindered. So my conclusion is that there was some spurious data that resulted in this error. However, its interesting to know that a data issue  can manifest  as a "Table or View not exist" error. Really weird.

Thanks for your help.

[Raja Ramachandran]

Darn! It comes back from the dead to haunt me. This started again later in the day. I have tried the pure java jdbc query/update on the table and that works.

Any other idea? This is becoming an big impediment.

[George Oprica]

Sounds like it could be data related? Make sure that the standalone
java app tries to delete the same row that the ATG server is trying
to delete (enable SQL debugging to get that).

- George