JS 1.2.0 war install: error after login

I meant to also respond to the point about editing the hibernate.properties file - it (correctly, I believe) reads:

metadata.hibernate.dialect=org.hibernate.dialect.MySQLDialect

metadata.hibernate.dataSource.jndiName=jdbc/jasperserver

(as well as some #-prefixed comment lines above those).

Would love some help getting past this error - can anyone think of anything else?

Thanks, Ben

Hi Sherman,

Yes, here's the whole error stack I get in my browser. I don't see any errors in the catalina.log file, though the stdout log file has similar error info.

Thanks for taking a look at this!

Ben
--------------------------------------
org.springframework.webflow.engine.ActionExecutionException: Exception thrown executing [AnnotatedAction@1450f1f targetAction = com.jaspersoft.jasperserver.war.action.RepositoryAction@1677737, attributes = map[[empty]]] in state 'initAction' of flow 'repositoryFlow' -- action execution attributes were 'map[[empty]]'; nested exception is com.jaspersoft.jasperserver.api.JSExceptionWrapper: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: com.jaspersoft.jasperserver.api.JSExceptionWrapper: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:95)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at org.springframework.orm.hibernate3.HibernateTemplate$35.doInHibernate(HibernateTemplate.java:979)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367)
at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:969)
at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:962)

Hi, Sherman,

I'm not exactly sure where the references to JNDI are, nor what they ought to be. I find some references in the context.xml file, the contents of which I've pasted below.

Re: database configuration, I copied the JasperServer MySQL DB from our production machine (running in console mode rather than as a service, and with the bundled Tomcat and MySQL). The DB server on this DEV machine does not have the Foodmart nor SugarCRM DBs - could that be the problem?

Thanks again,

Ben
*****************************************

<Context path="/jasperserver"
debug="5" reloadable="true" crossContext="true">

<!-- maxActive: Maximum number of dB connections in pool. Make sure you
configure your mysqld max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
-->
<!-- maxIdle: Maximum number of idle dB connections to retain in pool.
Set to -1 for no limit. See also the DBCP documentation on this
and the minEvictableIdleTimeMillis configuration parameter.
-->
<!-- maxWait: Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->

<!--
Look at your jdbc.properties defined in Maven2 settings.xml,
something like:

metadata.hibernate.dialect=org.hibernate.dialect.MySQLDialect

metadata.jdbc.driverClassName=com.mysql.jdbc.Driver
metadata.jdbc.url=jdbc:mysql://localhost:3306/jasperserver?autoReconnect=true
metadata.jdbc.username=root
metadata.jdbc.password=******

metadata.jndi=jdbc/jasperserver

test.jdbc.driverClassName=com.mysql.jdbc.Driver
test.jdbc.url=jdbc:mysql://localhost:3306/sugarcrm?autoReconnect=true
test.jdbc.username=root
test.jdbc.password=******

test.jndi=jdbc/sugarcrm

-->
<Resource name="jdbc/jasperserver" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="root" password="*******" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/jasperserver?useUnicode=true&amp;characterEncoding=UTF-8" />

<Resource name="jdbc/sugarcrm" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="root" password="*******" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/sugarcrm"/>

<Resource name="jdbc/foodmart" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="root" password="********" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/foodmart"/>

</Context>

Hi, Sherman,

I made one change - "localhost:3306" -> "127.0.0.1:3306". This didn't help, and I still get the same error. Note that I can login with a valid user & PW, and get denied when I enter a bogus pairing. Thus, it would seem the access to the MS Metabase on MySQL is at least partially working. The things I find in the doc you linked me to (which is what I've been using all along) that I need to verify are:

Username & PW for MySQL: VALID - can log in manually to the jasperserver metabase DB
url: VALID - left arguments as default but for the DNS resolution as per above

It would seem these are all that may differ from default in a specific implementation, the use of sample DBs notwithstanding (manual clearly states the sugarcrm and foodmart settings may be ignored).

The only reference I find in web.xml to the JNDI/JDBC stuff is in the naming of the resource, which does match the context.xml. Also, the cefault context.xml makes reference to a "maven2 settings.xml" file, which I cannot find. The hibernate.properties file seems right, and matches my production environment.

Other thoughts? Thanks again for your help. To restate my goal: I'd like to get JS running via the war file method so I have better portability and management of the individual primary components (Tomcat & MySQL).

Ben

Thanks for jumping in....

I've tried both the jasperserver and root logins in my config file - both of which work fine for logging into MySQL directly via the command line mysql.exe tool. When you say to check the role, what do you mean exactly? Permissions for those users on objects within the DB?

And, yes, here are the config. files (pasting the hibernate.properties and context.xml inline; attaching the applicationContext.xml).

Thanks! -Ben
*****************************************

(hibernate.properties)
# Properties file with JDBC-related settings.
# Applied by PropertyPlaceholderConfigurer from "applicationContext-*.xml".
# Targeted at system administrators, to avoid touching the context XML files.

# Property that determines the Hibernate dialect
metadata.hibernate.dialect=org.hibernate.dialect.MySQLDialect

metadata.hibernate.dataSource.jndiName=jdbc/jasperserver


(context.xml)
<Context path="/jasperserver"
debug="5" reloadable="true" crossContext="true">

<!-- maxActive: Maximum number of dB connections in pool. Make sure you
configure your mysqld max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
-->
<!-- maxIdle: Maximum number of idle dB connections to retain in pool.
Set to -1 for no limit. See also the DBCP documentation on this
and the minEvictableIdleTimeMillis configuration parameter.
-->
<!-- maxWait: Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->

<!--
Look at your jdbc.properties defined in Maven2 settings.xml,
something like:

metadata.hibernate.dialect=org.hibernate.dialect.MySQLDialect

metadata.jdbc.driverClassName=com.mysql.jdbc.Driver
metadata.jdbc.url=jdbc:mysql://localhost:3306/jasperserver?autoReconnect=true
metadata.jdbc.username=root
metadata.jdbc.password=********

metadata.jndi=jdbc/jasperserver

test.jdbc.driverClassName=com.mysql.jdbc.Driver
test.jdbc.url=jdbc:mysql://localhost:3306/sugarcrm?autoReconnect=true
test.jdbc.username=root
test.jdbc.password=********

test.jndi=jdbc/sugarcrm

-->
<Resource name="jdbc/jasperserver" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="jasperserver" password="*********" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1:3306/jasperserver?useUnicode=true&amp;characterEncoding=UTF-8" />

</Context>
[file name=applicationContext.xml size=29160]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/app...

Hi, Sherman,

Indeed, the "stdout_20070508.log" file contains this snippet:

org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Unknown column 'this_4_.visible' in 'field list'

(I've attached a file with a little more of the stdout file. Is this what you mean by "the stack"?)

I just used mysqldump to transfer a fresh copy of the metabase from production to my dev environment, and I still get this error. It feels like something is wrong with the dev environment setup, as the metabase is unchanged from production to dev. I also just ensured that all privileges were assigned to both root and jasperserver in MySQL, and did a "flush privileges" - no luck.

Thanks again for your assistance with this.

Ben [file name=stdout_err.txt size=2636]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/std...

Yes, this looks like a database difference (ie different than what the code expects to find).

There is upgrade procedure documentation in the Install Guide (found in the most recent Install Guide included with JS 1.2.1).

-Tony

This was a slip-up in the 1.2.0 to 1.2.1 upgrade script.

To fix it, run the following statement on your 1.2.1 database:

Code:

<br /> UPDATE JIInputControl<br /> SET visible = 1<br /> </td></tr></tbody></table><br /> <br /> Regards,<br /> Lucian