search

lbehnke / h2database

Automatically exported from code.google.com/p/h2database
0 stars 0 forks source link

Table not found #79

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?
I am trying to use H2 database with Sun's Glassfish/OpenESB/BPEL-SE
-platform. From my glassfish JDBC connection pool, I am able to ping to the
server but when BPEL-SE try to update the data, I see following two errors.
 !st one is just warning but 2nd one is more crucial.

1.
[#|2009-04-22T16:07:47.645-0400|WARNING|sun-appserver2.1|javax.enterprise.resour
ce.resourceadapter|_ThreadID=21;_ThreadName=RMI
TCP
Connection(80)-172.24.168.41;_RequestID=0831469f-c4fd-44c8-8f34-11b509647e70;|RA
R7115:
Unable to set ClientInfo for connection
java.sql.SQLClientInfoException
    at org.h2.jdbc.JdbcConnection.setClientInfo(JdbcConnection.java:1454)
at
com.sun.gjc.spi.jdbc40.ConnectionHolder40.setClientInfo(ConnectionHolder40.java:
304)
    at
com.sun.gjc.spi.jdbc40.ConnectionHolder40.close(ConnectionHolder40.java:511)

2. It is not finding the table.
Caused by: org.h2.jdbc.JdbcSQLException: Table ENGINE not found; SQL statement:
UPDATE ENGINE SET lastupdatetime = CURRENT_TIMESTAMP WHERE engineid = ?
[42102-111]
    at org.h2.message.Message.getSQLException(Message.java:107)
    at org.h2.message.Message.getSQLException(Message.java:118)
    at org.h2.message.Message.getSQLException(Message.java:77)
    at org.h2.command.Parser.readTableOrView(Parser.java:4259)
    at org.h2.command.Parser.readTableOrView(Parser.java:4239)
    at org.h2.command.Parser.readSimpleTableFilter(Parser.java:678)
    at org.h2.command.Parser.parseUpdate(Parser.java:636)
    at org.h2.command.Parser.parsePrepared(Parser.java:412)
    at org.h2.command.Parser.parse(Parser.java:288)
    at org.h2.command.Parser.parse(Parser.java:260)
    at org.h2.command.Parser.prepareCommand(Parser.java:232)
    at org.h2.engine.Session.prepareLocal(Session.java:409)
    at org.h2.server.TcpServerThread.process(TcpServerThread.java:221)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:136)
    at java.lang.Thread.run(Thread.java:619)

    at org.h2.engine.SessionRemote.done(SessionRemote.java:525)
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:70)
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:48)
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:420)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1042)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:74)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:227)
    at
com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:475
)
    at
com.sun.jbi.engine.bpel.core.bpel.connection.AbstractDBConnection.constructUpdat
eStmt(AbstractDBConnection.java:417)
    at
com.sun.jbi.engine.bpel.core.bpel.connection.AbstractDBConnection.update(Abstrac
tDBConnection.java:190)
    at
com.sun.jbi.engine.bpel.core.bpel.persist.impl.EngineStateManagerImpl.insertHear
tbeat(EngineStateManagerImpl.java:466)

When I connect using the web console, I do see table exist. Please find
attached web_console_Screen.doc.  I am using Schema SA.

I am using following properties from Glassfish JDBC Connection pool to
connect to H2 server.

* Database Classname: org.h2.jdbcx.JdbcDataSource
* Resource type: javax.sql.DataSource
* Username: SA
* Password: password
* URL: jdbc:h2:tcp://172.24.168.41/bpelseDB;MODE=Derby

I tried without MODE=Derby but didn't see any difference. 

Glassfish JDBC Connection Pool works fine with Derby.

What version of the product are you using? On what operating system, file
system, and virtual machine?
I recompiled using following platform:
OS: Linux 2.6.9-67.ELsmp.
Java version: Java HotSpot(TM) Server VM (11.0-b16) for linux-x86 JRE
(1.6.0_11-b03), built on Nov 10 2008 01:21:35 by "java_re" with gcc
3.2.1-7a (J2SE release)

Please find attached hs_err_pid11122.log.

Do you know a workaround?
NO

How important/urgent is the problem for you?
It's critical as we need to make decision whether we can use H2 DB for our
new product.  Currently we are using Derby during prototype but performance
is too slow and we can't meet our GOAL. I saw H2 is the fastest java based
database server.

In your view, is this a defect or a feature request?
It's some configuration settings.

Please provide any additional information below.
This is how I started server:
#Start server
nohup java -cp bin/h2-1.1.111.jar org.h2.tools.Server -tcp -tcpAllowOthers
-baseDir /opt/h2/data &

web_console_scree.doc shows I connected using Web console which is able to
find the table.

Let me know if you need more information.

Original issue reported on code.google.com by rohit.c.joshi@gmail.com on 22 Apr 2009 at 8:31

Attachments:

GoogleCodeExporter commented 9 years ago 
Most likely the problem is the database URL:
jdbc:h2:tcp://172.24.168.41/bpelseDB;MODE=Derby

Try using an absolute path such as:
jdbc:h2:tcp://172.24.168.41/file:/data/bpelseDB;MODE=Derby
or
jdbc:h2:tcp://172.24.168.41/~/data/bpelseDB;MODE=Derby

See also
http://www.h2database.com/html/features.html#embedded_databases

Original comment by thomas.t...@gmail.com on 22 Apr 2009 at 8:46

GoogleCodeExporter commented 9 years ago 
Thanks. I had to give file: path.

Original comment by rohit.c.joshi@gmail.com on 22 Apr 2009 at 9:45

GoogleCodeExporter commented 9 years ago 
OpenESB/BPLE-SE is able to connect to H2 database.  When it updates the 
timestamp, I
get following error.  It seems TIMESTAMPDIFF function is not supported.

NOTE:I am connecting using MODE=Derby.

[#|2009-04-22T17:40:12.883-0400|WARNING|sun-appserver2.1|com.sun.jbi.engine.bpel
.BPELSEInOutThread|_ThreadID=15;_ThreadName=sun-bpel-engine-thread-0;_RequestID=
7a02f1d8-35b1-4333-8cc1-d76f207125de;|BPJBI-6004:
caught exception while processing message
java.lang.RuntimeException: org.h2.jdbc.JdbcSQLException: Function 
TIMESTAMPDIFF not
found; SQL statement:
update STATE  set engineid = ?  where (status = 'RUNNING' or status = 
'SUSPENDED')
and engineid IN (select engineid from ENGINE  where     
TIMESTAMPDIFF(SQL_TSI_SECOND,
timestamp(lastupdatetime), CURRENT_TIMESTAMP)  > ? / 1000  and engineid != ?) 
[90022-111]
    at org.h2.message.Message.getSQLException(Message.java:107)
    at org.h2.message.Message.getSQLException(Message.java:118)
    at org.h2.message.Message.getSQLException(Message.java:77)

Any workaround to support TIMESTAMPDIFF would be appreciated.

Original comment by rohit.c.joshi@gmail.com on 22 Apr 2009 at 9:49

GoogleCodeExporter commented 9 years ago 
I have written a TIMESTAMPDIFF function and seem to be working fine.  Now it
complains about timestamp() function.

Original comment by rohit.c.joshi@gmail.com on 22 Apr 2009 at 10:51

GoogleCodeExporter commented 9 years ago 
Giving up on H2 and going to try HSQLDB (:

Original comment by rohit.c.joshi@gmail.com on 23 Apr 2009 at 1:15

GoogleCodeExporter commented 9 years ago 
timestamp() is not supported, use CAST(... AS TIMESTAMP) instead.

Original comment by thomas.t...@gmail.com on 27 Apr 2009 at 6:27