8 Replies Latest reply on Aug 8, 2006 2:37 PM by Danny Hon

    SQLGrammarException in JBP 2.4

    Danny Hon Newbie

      I checked out and compiled the latest 2.4 code, and deployed the portal to AS 4.0.4GA, using MySQL 4.x as database. I got a SQLGrammarException during login. Below is the exception.

      17:00:29,009 WARN [JDBCExceptionReporter] SQL Error: 1146, SQLState: 42S02
      17:00:29,009 ERROR [JDBCExceptionReporter] Table 'jbptest.jbp_instance_per_user' doesn't exist
      17:00:29,009 ERROR [ControllerCommand] Rendering portlet window default.default.CatalogPortletWindow produced an interna
      l error
      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:2148)
      at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2061)
      at org.hibernate.loader.Loader.list(Loader.java:2021)
      at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:392)
      at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:333)
      at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
      at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1114)
      at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
      at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:756)
      at org.jboss.portal.core.impl.model.instance.InstanceImpl.invoke(InstanceImpl.java:248)
      at org.jboss.portal.core.command.RenderWindowCommand.execute(RenderWindowCommand.java:108)
      at org.jboss.portal.core.command.ControllerCommand.dispatch(ControllerCommand.java:91)
      at org.jboss.portal.common.invocation.Invocation.invokeNext(Invocation.java:140)
      at org.jboss.portal.core.aspects.controller.EventBroadcasterInterceptor.invoke(EventBroadcasterInterceptor.java:
      171)
      at org.jboss.portal.core.command.CommandInterceptor.invoke(CommandInterceptor.java:37)
      at org.jboss.portal.common.invocation.Invocation.invokeNext(Invocation.java:130)
      at org.jboss.portal.core.aspects.controller.PageNavigationInterceptor.invoke(PageNavigationInterceptor.java:80)
      at org.jboss.portal.core.command.CommandInterceptor.invoke(CommandInterceptor.java:37)
      at org.jboss.portal.common.invocation.Invocation.invokeNext(Invocation.java:130)
      at org.jboss.portal.core.aspects.controller.PolicyEnforcementInterceptor.invoke(PolicyEnforcementInterceptor.jav
      a:79)
      at org.jboss.portal.core.command.CommandInterceptor.invoke(CommandInterceptor.java:37)
      at org.jboss.portal.common.invocation.Invocation.invokeNext(Invocation.java:130)
      at org.jboss.portal.core.aspects.controller.PortalNodeInterceptor.invoke(PortalNodeInterceptor.java:59)
      at org.jboss.portal.core.command.CommandInterceptor.invoke(CommandInterceptor.java:37)
      at org.jboss.portal.common.invocation.Invocation.invokeNext(Invocation.java:130)
      at org.jboss.portal.common.invocation.Invocation.invoke(Invocation.java:171)
      at org.jboss.portal.core.command.CommandContext.execute(CommandContext.java:102)
      at org.jboss.portal.core.command.ExecutionContext.execute(ExecutionContext.java:91)
      at org.jboss.portal.core.command.CommandContext.chain(CommandContext.java:148)
      at org.jboss.portal.core.command.MarkupCommand.renderPortletWindow(MarkupCommand.java:463)
      at org.jboss.portal.core.command.RenderPageCommand.renderFragments(RenderPageCommand.java:76)
      at org.jboss.portal.core.command.MarkupCommand.execute(MarkupCommand.java:340)
      at org.jboss.portal.core.command.ControllerCommand.dispatch(ControllerCommand.java:91)
      at org.jboss.portal.common.invocation.Invocation.invokeNext(Invocation.java:140)
      at org.jboss.portal.core.aspects.controller.EventBroadcasterInterceptor.invoke(EventBroadcasterInterceptor.java:
      171)

        • 1. Re: SQLGrammarException in JBP 2.4
          Chris Laprun Master

          Did you remove your data directory before running the latest version of the code?

          • 2. Re: SQLGrammarException in JBP 2.4
            Danny Hon Newbie

            Yes, it was a brand new server instance (duplicated from default). The problem happens when I tried to login. Looks like it is looking for tables that do not exist.

            08:03:01,515 ERROR [JDBCExceptionReporter] Table 'jbptest.jbp_instance_per_user' doesn't exist

            Is this a new table in RC3?

            • 3. Re: SQLGrammarException in JBP 2.4
              Roy Russo Master

              Yes, this is a new table. There should be several new ones.

              You said you used a new server instance... are you using the same database from a previous version?

              • 4. Re: SQLGrammarException in JBP 2.4
                Danny Hon Newbie

                I dropped and recreated the database when I tried it. I just did it again, recreated the database, deleted the data, tmp, work, and log directories in the domain. When I started the server, it created some of the old tables, but not the new ones. Here are the tables that it created.

                mysql> show tables;
                +-------------------------------+
                | Tables_in_jbptest |
                +-------------------------------+
                | jbp_cms_cmsentry |
                | jbp_cms_repositoryentry |
                | jbp_cms_version_binval |
                | jbp_cms_version_node |
                | jbp_cms_version_prop |
                | jbp_cms_version_refs |
                | jbp_cms_versionentry |
                | jbp_cms_wsp_binval |
                | jbp_cms_wsp_node |
                | jbp_cms_wsp_prop |
                | jbp_cms_wsp_refs |
                | jbp_context |
                | jbp_instance |
                | jbp_instance_security |
                | jbp_instance_security_actions |
                | jbp_object_node |
                | jbp_object_node_sec |
                | jbp_object_node_sec_actions |
                | jbp_page |
                | jbp_portal |
                | jbp_portal_mode |
                | jbp_portal_object |
                | jbp_portal_object_props |
                | jbp_portal_window_state |
                | jbp_portlet_state |
                | jbp_portlet_state_entry |
                | jbp_portlet_state_entry_value |
                | jbp_role_membership |
                | jbp_roles |
                | jbp_user_prop |
                | jbp_users |
                | jbp_window |
                +-------------------------------+
                32 rows in set (0.00 sec)

                • 5. Re: SQLGrammarException in JBP 2.4
                  Roy Russo Master

                  Strange. You should have 33 tables, so youre only missing the jbp_instance_per_user table.

                  I'll check with HEAD and mysql4.

                  • 6. Re: SQLGrammarException in JBP 2.4
                    Danny Hon Newbie

                    I found these in the log file:

                    2006-08-08 11:14:02,518 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create table JBP_INSTANCE_PER_USER (PK bigint not null auto_increment, INSTANCE_PK bigint, SER_STATE longblob, USER_ID varchar(255) not null, PORTLET_REF varchar(255) not null, primary key (PK), unique (USER_ID, PORTLET_REF))

                    2006-08-08 11:14:02,518 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Specified key was too long; max key length is 1024 bytes

                    • 7. Re: SQLGrammarException in JBP 2.4
                      Roy Russo Master

                      Mysql allow 1024 key lengths, but since were in UTF8 db setting, its using bytes per... so 255*3 + 255*3 > 1024.


                      One workaround Im looking at, is to modify the hibernate cfg:

                       <class
                       name="org.jboss.portal.core.impl.model.instance.UserInstance"
                       table="JBP_INSTANCE_PER_USER">
                       <cache usage="@portal.hibernate.cache.usage@"/>
                       <id
                       name="key"
                       column="PK"
                       access="field">
                       <generator class="native">
                       <param name="sequence">instance_seq</param>
                       </generator>
                       </id>
                       <many-to-one
                       name="instance"
                       column="INSTANCE_PK"
                       class="org.jboss.portal.core.impl.model.instance.InstanceImpl"
                       cascade="none"
                       fetch="select"/>
                       <property
                       name="state"
                       column="SER_STATE"
                       not-null="false"
                       unique="false"
                       length="50000000"/>
                       <properties
                       name="user_and_portlet_ref"
                       unique="true">
                       <property
                       name="userId"
                       column="USER_ID"
                       not-null="true"
                       length="170"/>
                       <property
                       name="portletRef"
                       column="PORTLET_REF"
                       not-null="true"
                       length="170"/>
                       </properties>
                       </class>
                      


                      This is a shortterm fix, as it requires testing.

                      • 8. Re: SQLGrammarException in JBP 2.4
                        Danny Hon Newbie

                        I made the changes you recommended. I can now login to the portal. Thank you.