ERROR: “Table 'wso2db.API_REQUEST_SUMMARY' doesn't exist” when WSO2 API Manager and Analytics were both deployed and started on the same machine

2
votes

The error in carbon.log on API Manager as below:

TID: [-1234] [] [2017-05-14 18:06:40,604] ERROR {org.wso2.carbon.apimgt.usage.client.impl.APIUsageStatisticsRdbmsClientImpl} -  Error occurred while querying from JDBC database Table 'wso2db.API_REQUEST_SUMMARY' doesn't exist {org.wso2.carbon.apimgt.usage.client.impl.APIUsageStatisticsRdbmsClientImpl}
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'wso2db.API_REQUEST_SUMMARY' doesn't exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1432)
at org.wso2.carbon.apimgt.usage.client.impl.APIUsageStatisticsRdbmsClientImpl.queryFirstAccess(APIUsageStatisticsRdbmsClientImpl.java:2210)
at org.wso2.carbon.apimgt.usage.client.impl.APIUsageStatisticsRdbmsClientImpl.getFirstAccessTime(APIUsageStatisticsRdbmsClientImpl.java:2155)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:126)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:225)
at org.mozilla.javascript.optimizer.OptRuntime.call1(OptRuntime.java:32)
at org.jaggeryjs.rhino.publisher.modules.statistics.c1._c_getFirstAccessTime_13(/publisher/modules/statistics/usage.jag:398)
at org.jaggeryjs.rhino.publisher.modules.statistics.c1.call(/publisher/modules/statistics/usage.jag)
at org.mozilla.javascript.ScriptRuntime.applyOrCall(ScriptRuntime.java:2430)
at org.mozilla.javascript.BaseFunction.execIdCall(BaseFunction.java:269)
at org.mozilla.javascript.IdFunctionObject.call(IdFunctionObject.java:97)
at org.mozilla.javascript.optimizer.OptRuntime.call2(OptRuntime.java:42)
at org.jaggeryjs.rhino.publisher.modules.statistics.c0._c_anonymous_16(/publisher/modules/statistics/module.jag:39)
at org.jaggeryjs.rhino.publisher.modules.statistics.c0.call(/publisher/modules/statistics/module.jag)
at org.mozilla.javascript.optimizer.OptRuntime.call1(OptRuntime.java:32)
at org.jaggeryjs.rhino.publisher.site.blocks.stats.api_last_access_times.ajax.c0._c_anonymous_1(/publisher/site/blocks/stats/api-last-access-times/ajax/stats.jag:67)
at org.jaggeryjs.rhino.publisher.site.blocks.stats.api_last_access_times.ajax.c0.call(/publisher/site/blocks/stats/api-last-access-times/ajax/stats.jag)
at org.mozilla.javascript.optimizer.OptRuntime.call0(OptRuntime.java:23)
at org.jaggeryjs.rhino.publisher.site.blocks.stats.api_last_access_times.ajax.c0._c_script_0(/publisher/site/blocks/stats/api-last-access-times/ajax/stats.jag:4)
at org.jaggeryjs.rhino.publisher.site.blocks.stats.api_last_access_times.ajax.c0.call(/publisher/site/blocks/stats/api-last-access-times/ajax/stats.jag)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:394)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3091)
at org.jaggeryjs.rhino.publisher.site.blocks.stats.api_last_access_times.ajax.c0.call(/publisher/site/blocks/stats/api-last-access-times/ajax/stats.jag)
at org.jaggeryjs.rhino.publisher.site.blocks.stats.api_last_access_times.ajax.c0.exec(/publisher/site/blocks/stats/api-last-access-times/ajax/stats.jag)
at org.jaggeryjs.scriptengine.engine.RhinoEngine.execScript(RhinoEngine.java:567)
at org.jaggeryjs.scriptengine.engine.RhinoEngine.exec(RhinoEngine.java:273)
at org.jaggeryjs.jaggery.core.manager.WebAppManager.exec(WebAppManager.java:588)
at org.jaggeryjs.jaggery.core.manager.WebAppManager.execute(WebAppManager.java:508)
at org.jaggeryjs.jaggery.core.JaggeryServlet.doPost(JaggeryServlet.java:29)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:747)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:485)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:377)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:337)
at org.jaggeryjs.jaggery.core.JaggeryFilter.doFilter(JaggeryFilter.java:21)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.wso2.carbon.ui.filters.cache.ContentTypeBasedCachePreventionFilter.doFilter(ContentTypeBasedCachePreventionFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.filters.HttpHeaderSecurityFilter.doFilter(HttpHeaderSecurityFilter.java:120)
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:218)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.wso2.carbon.tomcat.ext.valves.CompositeValve.continueInvocation(CompositeValve.java:99)
at org.wso2.carbon.tomcat.ext.valves.CarbonTomcatValve$1.invoke(CarbonTomcatValve.java:47)
at org.wso2.carbon.webapp.mgt.TenantLazyLoaderValve.invoke(TenantLazyLoaderValve.java:57)
at org.wso2.carbon.event.receiver.core.internal.tenantmgt.TenantLazyLoaderValve.invoke(TenantLazyLoaderValve.java:48)
at org.wso2.carbon.tomcat.ext.valves.TomcatValveContainer.invokeValves(TomcatValveContainer.java:47)
at org.wso2.carbon.tomcat.ext.valves.CompositeValve.invoke(CompositeValve.java:62)
at org.wso2.carbon.tomcat.ext.valves.CarbonStuckThreadDetectionValve.invoke(CarbonStuckThreadDetectionValve.java:159)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:958)
at org.wso2.carbon.tomcat.ext.valves.CarbonContextCreatorValve.invoke(CarbonContextCreatorValve.java:57)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:452)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1087)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1756)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1715)
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)
TID: [-1234] [] [2017-05-14 18:06:40,609] ERROR {JAGGERY.modules.statistics.usage:jag} -  org.wso2.carbon.apimgt.usage.client.exception.APIMgtUsageQueryServiceClientException: Error occurred while querying from JDBC databaseTable 'wso2db.API_REQUEST_SUMMARY' doesn't exist {JAGGERY.modules.statistics.usage:jag}

The WSO2 API Manager and the Analytics(not DAS) were deployed on Linux with both version 2.1.0. The database is MySQL 5.6.34, the driver relevent was copied to /repository/components/lib folder. I configed them as Configuring APIM Analytics :

API-M-ANALYTICS_HOME/repository/conf/datasources/analytics-datasources.xml was configed as below:

{
<datasources-configuration>

    <providers>
        <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
        <!--<provider>org.wso2.carbon.datasource.reader.hadoop.HBaseDataSourceReader</provider>-->
        <!--<provider>org.wso2.carbon.datasource.reader.cassandra.CassandraDataSourceReader</provider>-->
    </providers>

    <datasources>
        <datasource>
            <name>WSO2_ANALYTICS_EVENT_STORE_DB</name>
            <description>The datasource used for analytics record store</description>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://192.168.212.212:3306/wso2db</url>
                    <username>dbuser</username>
                    <password>passwd#$</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <validationQuery>SELECT 1</validationQuery>
                    <defaultAutoCommit>false</defaultAutoCommit>
                    <initialSize>0</initialSize>
                    <testWhileIdle>true</testWhileIdle>
                    <minEvictableIdleTimeMillis>4000</minEvictableIdleTimeMillis>
                    <defaultTransactionIsolation>READ_COMMITTED</defaultTransactionIsolation>
                </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>WSO2_ANALYTICS_PROCESSED_DATA_STORE_DB</name>
            <description>The datasource used for analytics record store</description>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://192.168.212.212:3306/wso2db</url>
                    <username>dbuser</username>
                    <password>passwd#$</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <validationQuery>SELECT 1</validationQuery>
                    <defaultAutoCommit>false</defaultAutoCommit>
                    <initialSize>0</initialSize>
                    <testWhileIdle>true</testWhileIdle>
                    <minEvictableIdleTimeMillis>4000</minEvictableIdleTimeMillis>
                    <defaultTransactionIsolation>READ_COMMITTED</defaultTransactionIsolation>
                </configuration>
            </definition>
        </datasource>
    </datasources>
</datasources-configuration>

}

AM_ANALYTICS_HOME/repository/conf/datasources/stats-datasources.xml was configed as below, and i was puzzled about the diffences between 3.b and 4 in Configuring databases TITLE([Configuring APIM Analytics][1]):
{
<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">

    <providers>
        <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
    </providers>

    <datasources>
        <datasource>
          <name>WSO2AM_STATS_DB</name>
          <description>The datasource used for setting statistics to API Manager</description>
          <jndiConfig>
            <name>jdbc/WSO2AM_STATS_DB</name>
            </jndiConfig>
          <definition type="RDBMS">
            <configuration>
              <url>jdbc:mysql://192.168.212.212:3306/wso2db</url>
              <username>dbuser</username>
              <password>passwd#$</password>
              <driverClassName>com.mysql.jdbc.Driver</driverClassName>
              <maxActive>50</maxActive>
              <maxWait>60000</maxWait>
              <testOnBorrow>true</testOnBorrow>
              <validationQuery>SELECT 1</validationQuery>
              <validationInterval>30000</validationInterval>
              <defaultAutoCommit>false</defaultAutoCommit>
              </configuration>
            </definition>
        </datasource>

     </datasources>

</datasources-configuration>

}


API-M_HOME/repository/conf/datasources/master-datasources.xml was configed as below:
{
<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">

    <providers>
        <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
    </providers>

    <datasources>
        <datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://192.168.212.212:3306/wso2db</url>
                    <username>dbuser</username>
                    <password>passwd#$</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>WSO2AM_DB</name>
            <description>The datasource used for API Manager database</description>
            <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://192.168.212.212:3306/wso2db</url>
                    <username>dbuser</username>
                    <password>passwd#$</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>WSO2AM_STATS_DB</name>
            <description>The datasource used for getting statistics to API Manager</description>
            <jndiConfig>
                <name>jdbc/WSO2AM_STATS_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://192.168.212.212:3306/wso2db</url>
                    <username>dbuser</username>
                    <password>passwd#$</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
         </datasource>

         <datasource>
            <name>WSO2_MB_STORE_DB</name>
            <description>The datasource used for message broker database</description>
            <jndiConfig>
                <name>WSO2MBStoreDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://192.168.212.212:3306/wso2db</url>
                    <username>dbuser</username>
                    <password>passwd#$</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
            </definition>
        </datasource>
    </datasources>
</datasources-configuration>
}

The API Manager works well, and Analytics does not work. The ERROR was triggered when some of the Options such as Top API User or API last access times under API Publisher/Analytics portal were clicked. Sorry for pictures could not uploaded here for some reasons.

1
wso2wso2esbwso2-amwso2carbonwso2dss
And some ERRORs were reported when Analytics was started by command. sh AM_ANALYTICS_HOME/bin/wso2server.sh start – Shakalaka
TID: [-1234] [] [2017-05-13 00:56:05,938] ERROR {org.wso2.carbon.analytics.dataservice.core.AnalyticsDataServiceComponent} - Error in activating analytics data service: Error in creating table: Error in executing SQL queries: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs Table 'wso2db.ANX___8GO8JC2Q_' doesn't exist – Shakalaka

1 Answers

0
votes

While configuring APIM-Analytics for standard setup as mentioned in https://docs.wso2.com/display/AM210/Configuring+APIM+Analytics, in step 2 of 'Configuring databases', while making schemas, specify 'latin1' as collation instead of 'utf8'. Latin collation takes up less space per VARCHAR compared to UTF8, thus it complies with row size restriction of MySQL.