TABLE_OR_VIEW_NOT_FOUND_1 = 42102 (table name is VERSION)

[Arthur]

Hi All,

With h2-1.3.160.jar, I create a DB which has one of table as this:
...
CREATE TABLE VERSION
(
DB_VERSION INTEGER PRIMARY KEY,
IE_VERSION VARCHAR(10) NOT NULL,
DB_PROGRESS INTEGER NOT NULL
);
...
INSERT INTO VERSION (DB_VERSION, IE_VERSION, DB_PROGRESS) VALUES (8,
'6.6.0', 0);
...

However, the application (which is using a threads pool to handle DB)
dump error as below. But running same query from H2console has no
issue.

Any idea why the application can't see the table VERSION?

Thanks,

Arthur

>> Console output:

SELECT MAX(DB_VERSION)
FROM VERSION
WHERE DB_PROGRESS = 0;
MAX(DB_VERSION)
8
(1 row, 0 ms)

>> Application error log:

19/09/11 11:15:48 AM WARNING
com.xxxx.dms.server.LocalDownloadManagerServiceImpl - Exception
encountered in the download manager service
com.xxxx.dms.businessobjects.DmsDataAccessException:
org.h2.jdbc.JdbcSQLException: Table "VERSION" not found; SQL
statement:
SELECT MAX(DB_VERSION)
FROM VERSION
WHERE DB_PROGRESS = 0 [42102-160]
at
com.xxxx.dms.businessobjects.DmsExceptionFactory.getDataAccessException(DmsExceptionFactory.java:
39)
at
com.xxxx.dms.businessobjects.DmsExceptionFactory.getDataAccessException(DmsExceptionFactory.java:
34)
at com.xxxx.dms.server.BaseDownloadManagerServiceImpl
$Impl.getDatabaseVersion(BaseDownloadManagerServiceImpl.java:241)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.xxxx.gws.server.ServiceGuard.execute(ServiceGuard.java:250)
at com.xxxx.gws.server.LoginManager
$ServicePrivilegedAction.run(LoginManager.java:512)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAsPrivileged(Subject.java:517)
at com.xxxx.gws.server.LoginManager.doAs(LoginManager.java:370)
at
com.xxxx.gws.server.LocalGatewayServiceImpl.doAs(LocalGatewayServiceImpl.java:
150)
at com.xxxx.gws.server.ServiceGuard.invoke(ServiceGuard.java:195)
at $Proxy21.getDatabaseVersion(Unknown Source)
at
com.xxxx.dms.server.LocalDownloadManagerServiceImpl.getDatabaseVersion(LocalDownloadManagerServiceImpl.java:
457)
at
com.xxxx.gems.download.server.model.DataCheckServerInitialisingListener.checkDatabaseIsUpToDate(DataCheckServerInitialisingListener.java:
89)
at
com.xxxx.gems.download.server.model.DataCheckServerInitialisingListener.serverAfterInitialise(DataCheckServerInitialisingListener.java:
60)
at com.xxxx.service.server.Server.initialise(Server.java:171)
at com.xxxx.service.server.Server.main(Server.java:1126)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
com.xxxx.xxxxxx.starter.xxxxxxClassLoader.main(xxxxxxClassLoader.java:
185)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.zerog.lax.LAX.launch(DashoA8113)
at com.zerog.lax.LAX.main(DashoA8113)
Caused by: com.xxxx.datamodel.api.DataModelException:
org.h2.jdbc.JdbcSQLException: Table "VERSION" not found; SQL
statement:
SELECT MAX(DB_VERSION)
FROM VERSION
WHERE DB_PROGRESS = 0 [42102-160]
at
com.xxxx.datamodel.api.DataModelExceptionFactory.getDataModelException(DataModelExceptionFactory.java:
12)
at
com.xxxx.datamodel.defaultmodel.dms.DataModelJDBC.getDatabaseVersion(DataModelJDBC.java:
2848)
at com.xxxx.dms.server.BaseDownloadManagerServiceImpl
$Impl.getDatabaseVersion(BaseDownloadManagerServiceImpl.java:237)
... 28 more
Caused by: org.h2.jdbc.JdbcSQLException: Table "VERSION" not found;
SQL statement:
SELECT MAX(DB_VERSION)
FROM VERSION
WHERE DB_PROGRESS = 0 [42102-160]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
329)
at org.h2.message.DbException.get(DbException.java:169)
at org.h2.message.DbException.get(DbException.java:146)
at org.h2.command.Parser.readTableOrView(Parser.java:4747)
at org.h2.command.Parser.readTableFilter(Parser.java:1075)
at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1679)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1786)
at org.h2.command.Parser.parseSelectSub(Parser.java:1673)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1518)
at org.h2.command.Parser.parseSelect(Parser.java:1506)
at org.h2.command.Parser.parsePrepared(Parser.java:405)
at org.h2.command.Parser.parse(Parser.java:279)
at org.h2.command.Parser.parse(Parser.java:251)
at org.h2.command.Parser.prepareCommand(Parser.java:217)
at org.h2.engine.Session.prepareLocal(Session.java:415)
at org.h2.engine.Session.prepareCommand(Session.java:364)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1119)
at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:70)
at
org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:
208)
at
com.xxxx.datamodel.defaultmodel.dms.DataModelJDBC.getDatabaseVersion(DataModelJDBC.java:
2839)
... 29 more

http://www.h2database.com/javadoc/org/h2/constant/ErrorCode.html?highlight=error&search=error#c42102

[Rami Ojares]

First thing that comes to my mind is that your application might be in
another schema.

- rami

[Arthur]

I am not quite sure how schema works with H2DB but if I change the DB
password in the application property file, then I got another error
which mentioned that it failed on connection:
...ConnectionPoolDBCP - Unable to retrieve connection from connection
pool
org.h2.jdbc.JdbcSQLException: Wrong user name or password
[28000-160]...

So I think the application connected to the same DB file which I used
from console.

The ConnectionPool-default.properties file has:

cp.jdbcDriver=org.h2.Driver
cp.jdbcUrl=jdbc:h2:h2db/xxxxx.h2db
cp.jdbcUser=xxx
cp.jdbcPassword=xxxxx

What can I do to check the schema?

Thanks,

Arthur

[Thomas Mueller]

Hi,

Most likely the problem is the database URL (you are pointing to a different database file). What URL do you use currently? 

Regards,

Thomas

[Thomas Mueller]

Hi,

See http://h2database.com/html/faq.html#database_files

> jdbc:h2:h2db/xxxxx.h2db

This is relative to the current working directory.

Try jdbc:h2:~/h2db/xxxxx

Or see http://h2database.com/html/features.html#database_url

Regards,

Thomas

[Arthur]

Thanks Thomas. Yes the problem is the wrong URL. The application
property file used to have the URL ends with dbfilename.h2db. Removing
'.h2db' resolved the issue.

Thanks for your help!!

Arthur

One thing I don't understand is that with the old version of the app
(and h2-1.1.118.jar), I have
Arthur

On Sep 19, 1:06 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:
> Hi,
>
> Seehttp://h2database.com/html/faq.html#database_files