IBM DB2 + Activiti Explorer = weird error

[lossril]

When I start Activiti Explorer 5.19 webapp which is configured to connect to IBM DB2 database and go to Manage>Database in webapp, it throws an error:

Exception

com.vaadin.event.ListenerMethod$MethodException: Invocation of method buttonClick in org.activiti.explorer.ui.mainlayout.MainMenuBar$ShowManagementClickListener failed.

   at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:530)

   at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:164)

   at com.vaadin.ui.AbstractComponent.fireEvent(AbstractComponent.java:1219)

   at com.vaadin.ui.Button.fireClick(Button.java:567)

   at com.vaadin.ui.Button.changeVariables(Button.java:223)

   at com.vaadin.terminal.gwt.server.AbstractCommunicationManager.changeVariables(AbstractCommunicationManager.java:1460)

   at com.vaadin.terminal.gwt.server.AbstractCommunicationManager.handleVariableBurst(AbstractCommunicationManager.java:1404)

   at com.vaadin.terminal.gwt.server.AbstractCommunicationManager.handleVariables(AbstractCommunicationManager.java:1329)

   at com.vaadin.terminal.gwt.server.AbstractCommunicationManager.doHandleUidlRequest(AbstractCommunicationManager.java:761)

   at com.vaadin.terminal.gwt.server.CommunicationManager.handleUidlRequest(CommunicationManager.java:325)

   at com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:501)

   at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)

   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

   at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

   at org.activiti.explorer.filter.ExplorerFilter.doFilter(ExplorerFilter.java:42)

   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)

   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)

   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)

   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)

   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

   at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)

   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)

   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)

   at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)

   at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)

   at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)

   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

   at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

   at java.lang.Thread.run(Thread.java:745)

Caused by: org.activiti.engine.ActivitiException: couldn't get table counts

   at org.activiti.engine.impl.persistence.entity.TableDataManager.getTableCount(TableDataManager.java:146)

   at org.activiti.engine.impl.cmd.GetTableCountCmd.execute(GetTableCountCmd.java:30)

   at org.activiti.engine.impl.cmd.GetTableCountCmd.execute(GetTableCountCmd.java:25)

   at org.activiti.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:24)

   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:57)

   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:47)

   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)

   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45)

   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)

   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)

   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)

   at org.activiti.engine.impl.ManagementServiceImpl.getTableCount(ManagementServiceImpl.java:53)

   at org.activiti.explorer.ui.management.db.DatabasePage.populateTableList(DatabasePage.java:93)

   at org.activiti.explorer.ui.management.db.DatabasePage.initUi(DatabasePage.java:57)

   at org.activiti.explorer.ui.AbstractPage.attach(AbstractPage.java:43)

   at com.vaadin.ui.AbstractComponent.setParent(AbstractComponent.java:560)

   at com.vaadin.ui.AbstractComponentContainer.addComponent(AbstractComponentContainer.java:211)

   at com.vaadin.ui.CssLayout.addComponent(CssLayout.java:95)

   at org.activiti.explorer.ui.mainlayout.MainLayout.setMainContent(MainLayout.java:58)

   at org.activiti.explorer.ui.MainWindow.switchView(MainWindow.java:79)

   at org.activiti.explorer.DefaultViewManager.switchView(DefaultViewManager.java:347)

   at org.activiti.explorer.DefaultViewManager.showDatabasePage(DefaultViewManager.java:261)

   at org.activiti.explorer.ui.mainlayout.MainMenuBar$ShowManagementClickListener.buttonClick(MainMenuBar.java:213)

   at sun.reflect.GeneratedMethodAccessor230.invoke(Unknown Source)

   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

   at java.lang.reflect.Method.invoke(Method.java:497)

   at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:510)

   … 34 more

Caused by: org.apache.ibatis.exceptions.PersistenceException: 

### Error querying database.  Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=ACTIVITI.ACTIONS, DRIVER=3.69.24

### The error may exist in org/activiti/db/mapping/entity/TableData.xml
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Here is my db.properties file:

db=db2
jdbc.driver=com.ibm.db2.jcc.DB2Driver
jdbc.url=jdbc:db2://*****:60000/PL5DB2:currentSchema=ACTIVITI;
jdbc.username=*****
jdbc.password=*****
‍‍‍‍‍‍‍

The point is that error exists only here, in Activiti Explorer. No signs of database failures are to be seen if I create ProcessEngine programmatically. I'm concerned it may reappear on production, so can anyone here tell me if this bug is caused ONLY by Explorer behaviour?

[lossril]

Btw, we found the error:
<java>
public List<String> getTablesPresentInDatabase() {
    List<String> tableNames = new ArrayList<String>();
    Connection connection = null;
    try {
      connection = getDbSqlSession().getSqlSession().getConnection();
      DatabaseMetaData databaseMetaData = connection.getMetaData();
      ResultSet tables = null;
      try {
        log.debug("retrieving activiti tables from jdbc metadata");
        String databaseTablePrefix = getDbSqlSession().getDbSqlSessionFactory().getDatabaseTablePrefix();
        String tableNameFilter = databaseTablePrefix+"ACT_%";
        if ("postgres".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
          tableNameFilter = databaseTablePrefix+"act_%";
        }
        if ("oracle".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
          tableNameFilter = databaseTablePrefix+"ACT" + databaseMetaData.getSearchStringEscape() + "_%";
        }
        tables = databaseMetaData.getTables(null, null, tableNameFilter, getDbSqlSession().JDBC_METADATA_TABLE_TYPES);
        while (tables.next()) {
          String tableName = tables.getString("TABLE_NAME");
          tableName = tableName.toUpperCase();
          tableNames.add(tableName);
          log.debug("  retrieved activiti table name {}", tableName);
        }
      } finally {
        tables.close();
      }
    } catch (Exception e) {
      throw new ActivitiException("couldn't get activiti table names using metadata: "+e.getMessage(), e);
    }
    return tableNames;
  }
</java>

In this method you did some character escaping for postgres and oracle databases, but not for db2. "_%" means "one symbol" so it goes into different data schema and finds the ACTIONS table. That's clearly a bug, please fix it in next release.

[jbarrez]

Thanks for reporting this. I wonder why we've never seen this on our QA environment.
We'll look into it and see if we can amend that code, thanks for pointing us already in the right direction!

[lossril]

Probably because you didn't have a table named 'ACTsomething' in your QA environment and/or database user which is used to access Activiti tables didn't have rights to read them. To reproduct the case, create the table - in our case it was even in the other schema and grant the user rights to read it. Unfortunately, we cannot restrict system user from reading some tables due to environment specifics.