JS 1.2.0 war install: error after login

[benu]

Hi! I'm seeking help getting my JS 1.2.0 installation going with the .war file method. I've installed MySQL & Tomcat successfully and they run as services just fine on my dev machine. I've restored a recent copy of the JasperServer DB from our production JI machine to my dev machine. I believe I got all the MySQL references set properly, as I can log in to the JI interface, but immediately after login, I get this error:

 

17:36:32,593 ERROR JSErrorPage_jsp,http-8080-Processor23:146 -

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)

 

<and a lot more>

 

Anyone have some thoughts? I'm guessig I missed something in one of the config files somewhere.

 

Thanks!

 

Ben

[tkavanagh]

Ben,

 

It looks like there is something wrong with the hibernate configuration. You can check this at webapps/jasperserver/WEB-INF/hibernate.properties. Yours should be set for MySQL.

 

Also, take a look at you tomcat/webapps directory. You probably have a "jasperserver" directory (ie exploded war file) and a "jasperserver.war" file. I recommend deleting the jaspserserver.war file.

 

You can make changes to your exploded jasperserver directory but they will be thrown away (overwritten) when tomcat starts up and re-explodes the jasperserver.war file.

 

There is more info on this stuff in the docs/JasperServer-WarFile-Install-Guide.pdf. Also, look at tomcat/conf/Catalina/localhost/jasperserver.xml - the META-INF/context.xml is copied and renamed to this location. It holds the DB settings. You can delete this file and let META-INF/context.xml define the DB settings.

[benu]

Thanks for your suggestions. I've been using the install guide you recommended, and had already deleted the .war file per another posting on the forum related to these issues (/jasperserver folder getting overwritten by .war file expansion). I removed (renamed) the tomcat/conf/Catalina/localhost/jasperserver.xml file and verified the MySQL connectivity info in the META-INF/context.xml file - everything looks good there. I can also log into MySQL and access the jasperserver DB via the mysql.exe command line tool - using the same credentials and TCP/IP port (3306).

 

I still get the same error as originally posted. Would it be helpful for me to post additional log data/error messages?

 

Thanks again. -Ben

[benu]

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

[swood]

Could you show more of the stack?

 

 

Sherman

JasperSoft

[benu]

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)

[swood]

It looks like there is something not right with the database configuration. Is the JNDI config right?

 

 

Sherman

JasperSoft

[benu]

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&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>

[swood]

You need to get these settings right for your environment.

 

The install guide at http://jasperforge.org/sf/docman/do/downloadDocument/projects.jasperintelligence/docman.root/doc1011

is clear about all this. Follow the steps there to set up your database connections.

 

 

Sherman

JasperSoft

[benu]

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

[anandharaj]

Hi,

 

As you mention, you copied the DB from production system to your local machine. Are you using same username and password as per production DB?

 

Also check the ROLE for that user.

 

Can you post the hibernate.properties, context.xml and applicationContext.xml

[benu]

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&characterEncoding=UTF-8" />

 

</Context>

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

[swood]

A Google search indicates that the SQLGrammarException is from "bad" SQL against the database. I suspect that the repository database does not have all the tables or something that it needs. There should be some exception in the stack that is a java.sql.SQLException indicating the cause.

 

 

Sherman

JasperSoft

[benu]

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/stdout_err.txt[/file]

[swood]

It looks like the original database is from an earlier version of JasperServer. There is an upgrade process where you can export out of the existing database, run the upgrade script and import into the new database (if needed). Have a look at the import/export process outlined in the user guide. There are also upgrade database scripts included in the downloads.

 

 

 

Sherman

JasperSoft

[tkavanagh]

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

[benu]

OK, I'm feeling a little embarrassed, now - I was using a JS 1.2.0 DB with a JS 1.2.1 WAR file. I ran the jasperserverPatch-1.2.0-1.2.1.ddl patch script against the DB, and was able to get logged in OK.

 

I can navigate through the various folders and objects OK, however, I'm still getting an error when I click on my reports folder (which is a folder I created just below the root level). I've attached the error - any additional help would be greatly appreciated, and thanks again for the assistance thus far!

 

Ben [file name=err.txt size=15350]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/err.txt[/file]

[lucianc]

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:

UPDATE JIInputControl
SET visible = 1

 

Regards,

Lucian

[benu]

Hi, Lucian,

 

Thanks. While running that update statement did allow me to run reports as as role ROLE_ADMINISTRATOR, ROLE_USER still had trouble. I think I saw a few other posts about these security issues, and trouble moving from JS 1.2.0 to JS 1.2.1. In any case, I opted to revert to JS 1.2.0, and managed to get my production enviornment up and running with the WAR file install method - finally.

 

Thanks to all for your thoughtful & diligent work in helping me resolve these issues.

 

Best regards, Ben