Uploaded image for project: 'XWiki Platform'
  1. XWiki Platform
  2. XWIKI-16722

SQL error when accessing Notifications Settings leads to failure of loading notifications after instance upgrade on Oracle

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 11.6, 11.7
    • Fix Version/s: 11.8-rc-1, 11.3.5
    • Component/s: None
    • Labels:
    • Environment:
      Windows 10 Pro 64 bit, Firefox 69, Chrome 77, using a local instance of XWiki 11.6 and 11.7 on Oracle 12c
    • Difficulty:
      Unknown
    • Documentation:
      N/A
    • Documentation in Release Notes:
      N/A
    • Similar issues:

      Description

      STEPS TO REPRODUCE

      1. Create an older instance of XWiki (e.g. 8.4.6 or 10.11.9)
      2. Close the instance
      3. Go to a newer instance (e.g. 11.6 or 11.7) and copy/cut the
        [...]webapps\xwiki 

        folder and replace it (first delete the old one) in the same location on the older version

      1. Delete 'solr' folder from configured permanent directory
      2. Start XWiki and follow the Distribution Wizard through the upgrade process
      3. On the upgraded instance, click on Notifications Bell > Settings

      EXPECTED RESULTS

      The Notifications Preferences page is opened without any errors.

      ACTUAL RESULTS

      When accessing Notifications Settings, there are some SQL errors encountered in console which lead to other errors related to notifications. Stacktrace:

      2019-09-17 14:45:48,497 [Notification event executor: list : 5false/16xwiki:XWiki.ILIE///0/0/0/16xwiki:XWiki.ILIE/10/4true/5false/5false/4true/0/5xwiki/false : 3] WARN  o.h.e.j.s.SqlExceptionHelper   - SQL Error: 17004, SQLState: 99999 
      2019-09-17 14:45:48,548 [Notification event executor: list : 5false/16xwiki:XWiki.ILIE///0/0/0/16xwiki:XWiki.ILIE/10/4true/5false/5false/4true/0/5xwiki/false : 3] ERROR o.h.e.j.s.SqlExceptionHelper   - Invalid column type: getCLOB not implemented for class oracle.jdbc.driver.T4CVarcharAccessor 
      2019-09-17 14:45:48,576 [Notification event executor: list : 5false/16xwiki:XWiki.ILIE///0/0/0/16xwiki:XWiki.ILIE/10/4true/5false/5false/4true/0/5xwiki/false : 3] ERROR .r.i.NotificationEventExecutor - Failed to retrieve notifications for cache key [5false/16xwiki:XWiki.ILIE///0/0/0/16xwiki:XWiki.ILIE/10/4true/5false/5false/4true/0/5xwiki/false] 
      org.xwiki.notifications.NotificationException: Fail to get the list of notifications.
      	at org.xwiki.notifications.sources.internal.DefaultParametrizedNotificationManager.getEvents(DefaultParametrizedNotificationManager.java:144)
      	at org.xwiki.notifications.sources.internal.DefaultParametrizedNotificationManager.getEvents(DefaultParametrizedNotificationManager.java:103)
      	at org.xwiki.notifications.rest.internal.DefaultNotificationsResource.getCompositeEvents(DefaultNotificationsResource.java:395)
      	at org.xwiki.notifications.rest.internal.DefaultNotificationsResource.getCompositeEvents(DefaultNotificationsResource.java:350)
      	at org.xwiki.notifications.rest.internal.DefaultNotificationsResource.lambda$getCompositeEvents$0(DefaultNotificationsResource.java:195)
      	at org.xwiki.notifications.rest.internal.NotificationEventExecutor$CallableEntry.execute(NotificationEventExecutor.java:187)
      	at org.xwiki.notifications.rest.internal.NotificationEventExecutor$CallableEntry.call(NotificationEventExecutor.java:152)
      	at java.util.concurrent.FutureTask.run(Unknown Source)
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      	at java.lang.Thread.run(Unknown Source)
      Caused by: org.xwiki.query.QueryException: Exception while executing query. Query statement = [select event from LegacyEvent event where ((((((event.type = :value_6197595503f01ee2a34e403fe08d2e1d9d0c14cf1cdfc2b74739895dc9a15a04) AND (event.date >= :date_f348062179a20c4c4b1fc1e0ae91b0a33c9a17e726916a41b62bf863c49e0ff2)) OR ((event.type = :value_2937013f2181810606b2a799b05bda2849f3e369a20982a4138f0e0a55984ce4) AND (event.date >= :date_f348062179a20c4c4b1fc1e0ae91b0a33c9a17e726916a41b62bf863c49e0ff2))) OR ((event.type = :value_fa8847b0c33183273f5945508b31c3208a9e4ece58ca47233a05628d8dba3799) AND (event.date >= :date_f348062179a20c4c4b1fc1e0ae91b0a33c9a17e726916a41b62bf863c49e0ff2))) OR ((event.type = :value_22a0dd4eeff7a342d41f1e47997d2005653be9238d8699d720aa21528501bc29) AND (event.date >= :date_f348062179a20c4c4b1fc1e0ae91b0a33c9a17e726916a41b62bf863c49e0ff2))) AND (((event.user <> :value_cc42f2c5837886c484267cc4182efb42ce6818ce257792dad5fdb74dbe7af1a5) AND ( NOT ((event.type = :value_2937013f2181810606b2a799b05bda2849f3e369a20982a4138f0e0a55984ce4) AND ( NOT (event.version LIKE concat('%', :value_f7f4791af8c2fe5825591201df09b72c2463d705c04f2f8e5ccf54f16ebaa424) ESCAPE '!'))))) AND (event.user IN (:value_b31ddd76d39a233d9fcd6622737623e5144f81b3c5c8d691f4f27ef9df0d2a63)))) AND (event.hidden <> true) ORDER BY event.date DESC]
      	at com.xpn.xwiki.store.hibernate.query.HqlQueryExecutor.execute(HqlQueryExecutor.java:182)
      	at org.xwiki.query.internal.DefaultQueryExecutorManager.execute(DefaultQueryExecutorManager.java:72)
      	at org.xwiki.query.internal.DefaultQuery.execute(DefaultQuery.java:306)
      	at org.xwiki.eventstream.store.internal.LegacyEventLoader.searchEvents(LegacyEventLoader.java:110)
      	at org.xwiki.eventstream.store.internal.LegacyEventStream.searchEvents(LegacyEventStream.java:75)
      	at org.xwiki.notifications.sources.internal.DefaultParametrizedNotificationManager.getEvents(DefaultParametrizedNotificationManager.java:125)
      	... 10 common frames omitted
      Caused by: com.xpn.xwiki.XWikiException: Error number 0 in 3: Exception while hibernate execute
      	at com.xpn.xwiki.store.XWikiHibernateBaseStore.execute(XWikiHibernateBaseStore.java:826)
      	at com.xpn.xwiki.store.XWikiHibernateBaseStore.executeRead(XWikiHibernateBaseStore.java:889)
      	at com.xpn.xwiki.store.hibernate.query.HqlQueryExecutor.execute(HqlQueryExecutor.java:170)
      	... 15 common frames omitted
      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
      	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
      	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1534)
      	at com.xpn.xwiki.store.hibernate.query.HqlQueryExecutor.lambda$execute$0(HqlQueryExecutor.java:173)
      	at com.xpn.xwiki.store.XWikiHibernateBaseStore.execute(XWikiHibernateBaseStore.java:820)
      	... 17 common frames omitted
      Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
      	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
      	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
      	at org.hibernate.loader.Loader.doList(Loader.java:2813)
      	at org.hibernate.loader.Loader.doList(Loader.java:2792)
      	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2624)
      	at org.hibernate.loader.Loader.list(Loader.java:2619)
      	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
      	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:396)
      	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
      	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1547)
      	at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1557)
      	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1525)
      	... 19 common frames omitted
      Caused by: java.sql.SQLException: Invalid column type: getCLOB not implemented for class oracle.jdbc.driver.T4CVarcharAccessor
      	at oracle.jdbc.driver.GeneratedAccessor.getCLOB(GeneratedAccessor.java:1037)
      	at oracle.jdbc.driver.GeneratedStatement.getCLOB(GeneratedStatement.java:386)
      	at oracle.jdbc.driver.GeneratedScrollableResultSet.getCLOB(GeneratedScrollableResultSet.java:529)
      	at oracle.jdbc.driver.GeneratedScrollableResultSet.getClob(GeneratedScrollableResultSet.java:170)
      	at oracle.jdbc.driver.GeneratedResultSet.getClob(GeneratedResultSet.java:534)
      	at org.apache.commons.dbcp2.DelegatingResultSet.getClob(DelegatingResultSet.java:448)
      	at org.apache.commons.dbcp2.DelegatingResultSet.getClob(DelegatingResultSet.java:448)
      	at org.apache.commons.dbcp2.DelegatingResultSet.getClob(DelegatingResultSet.java:448)
      	at org.hibernate.type.descriptor.sql.ClobTypeDescriptor$1.doExtract(ClobTypeDescriptor.java:44)
      	at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
      	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257)
      	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
      	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243)
      	at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329)
      	at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:3047)
      	at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1861)
      	at org.hibernate.loader.Loader.hydrateEntityState(Loader.java:1789)
      	at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1762)
      	at org.hibernate.loader.Loader.getRow(Loader.java:1615)
      	at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:745)
      	at org.hibernate.loader.Loader.processResultSet(Loader.java:1008)
      	at org.hibernate.loader.Loader.doQuery(Loader.java:964)
      	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
      	at org.hibernate.loader.Loader.doList(Loader.java:2810)
      	... 28 common frames omitted 

      If Pages Notifications are set to ON (e.g. for currently logged-in Admin, the wiki or a page being watched) and some Notifications events are received, the Notifications list is not loaded (endless spinning wheel is displayed instead).

      Please find attached the log from upgrading XWiki 10.11.9 to 11.7.

      I've tested on XWiki 8.4.6 and 10.11.9 by upgrading them to both XWiki 11.6 and respectively 11.7.

      However, the issue could not be reproduced by upgrading to XWiki 11.5.

        Attachments

          Activity

            People

            • Assignee:
              tmortagne Thomas Mortagne
              Reporter:
              iandriuta Ilie Andriuta
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: