How to configure WSO2 API Manager Analytics

Question

I'm new to WSO2, and I'm trying to configure a single node WSO2 API Manager with Analytics using mysql as the RDBMS on my local machine (ubuntu).

I've successfully configured the API Manager to use mysql as the RDBMS using the official documentation page here, however I can't configure the Analytics to use mysql as the RDBMS.

I've followed the official documentation related to Configuring APIM Analytics, downloaded the Connector/J 8.0.23 from MySQL official website and copied it to the lib directory, and here's my db-related configuration on <APIM_ANALYTICS_HOME>/conf/worker/deployment.yaml file:

  name: APIM_ANALYTICS_DB
  description: "The datasource used for APIM statistics aggregated data."
  jndiConfig:
    name: jdbc/APIM_ANALYTICS_DB
  definition:
    type: RDBMS
    configuration:
      jdbcUrl: 'jdbc:mysql://localhost:3306/analytics_db?useSSL=false'
      username: behzad
      password: myStronPassword!
      driverClassName: com.mysql.cj.jdbc.Driver
      maxPoolSize: 50
      idleTimeout: 60000
      connectionTestQuery: SELECT 1
      validationTimeout: 30000
      isAutoCommit: false

I get this error:

Cannot connect to JDBC URL jdbc:mysql://localhost:3306/analytics_db?useSSL=false. Failed due to Failed to initialize pool: Unknown database 'analytics_db'

Here's the full log:

JAVA_HOME environment variable is set to /usr/lib/jvm/java-1.11.0-openjdk-amd64
CARBON_HOME environment variable is set to /home/behzad/0/projects/wso2/wso2am-analytics-3.2.0
RUNTIME_HOME environment variable is set to /home/behzad/0/projects/wso2/wso2am-analytics-3.2.0/wso2/worker
[2021-02-21 16:05:04,879]  INFO {org.wso2.carbon.launcher.extensions.OSGiLibBundleDeployerUtils updateOSGiLib} - Successfully updated the OSGi bundle information of Carbon Runtime: worker  
osgi> [2021-02-21 16:05:07,208]  INFO {org.wso2.msf4j.internal.websocket.WebSocketServerSC} - All required capabilities are available of WebSocket service component is available.
[2021-02-21 16:05:07,216]  INFO {org.wso2.carbon.metrics.core.config.model.JmxReporterConfig} - Creating JMX reporter for Metrics with domain 'org.wso2.carbon.metrics'
[2021-02-21 16:05:07,235]  INFO {org.wso2.msf4j.analytics.metrics.MetricsComponent} - Metrics Component is activated
[2021-02-21 16:05:07,241]  INFO {org.wso2.carbon.databridge.agent.internal.DataAgentDS} - Successfully deployed Agent Server 
[2021-02-21 16:05:07,902] ERROR {com.zaxxer.hikari.pool.HikariPool} - HikariPool-4 - Exception during pool initialization. java.sql.SQLSyntaxErrorException: Unknown database 'analytics_db'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:833)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:453)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:353)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:473)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:562)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
    at org.wso2.carbon.datasource.rdbms.hikari.HikariRDBMSDataSource.getDataSource(HikariRDBMSDataSource.java:61)
    at org.wso2.carbon.datasource.rdbms.hikari.HikariDataSourceReader.createDataSource(HikariDataSourceReader.java:74)
    at org.wso2.carbon.datasource.core.DataSourceBuilder.buildDataSourceObject(DataSourceBuilder.java:79)
    at org.wso2.carbon.datasource.core.DataSourceBuilder.buildDataSourceObject(DataSourceBuilder.java:60)
    at org.wso2.carbon.datasource.core.DataSourceBuilder.buildCarbonDataSource(DataSourceBuilder.java:44)
    at org.wso2.carbon.datasource.core.DataSourceManager.initDataSources(DataSourceManager.java:163)
    at org.wso2.carbon.datasource.core.internal.DataSourceListenerComponent.onAllRequiredCapabilitiesAvailable(DataSourceListenerComponent.java:125)
    at org.wso2.carbon.kernel.internal.startupresolver.StartupComponentManager.lambda$notifySatisfiableComponents$7(StartupComponentManager.java:266)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
    at org.wso2.carbon.kernel.internal.startupresolver.StartupComponentManager.notifySatisfiableComponents(StartupComponentManager.java:252)
    at org.wso2.carbon.kernel.internal.startupresolver.StartupOrderResolver$1.run(StartupOrderResolver.java:204)
    at java.base/java.util.TimerThread.mainLoop(Timer.java:556)
    at java.base/java.util.TimerThread.run(Timer.java:506)

[2021-02-21 16:05:07,903] ERROR {org.wso2.carbon.datasource.rdbms.hikari.HikariRDBMSDataSource} - Cannot connect to JDBC URL jdbc:mysql://localhost:3306/analytics_db?useSSL=false. Failed due to Failed to initialize pool: Unknown database 'analytics_db'retrying in 5 seconds com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Unknown database 'analytics_db'
    at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:597)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:576)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
    at org.wso2.carbon.datasource.rdbms.hikari.HikariRDBMSDataSource.getDataSource(HikariRDBMSDataSource.java:61)
    at org.wso2.carbon.datasource.rdbms.hikari.HikariDataSourceReader.createDataSource(HikariDataSourceReader.java:74)
    at org.wso2.carbon.datasource.core.DataSourceBuilder.buildDataSourceObject(DataSourceBuilder.java:79)
    at org.wso2.carbon.datasource.core.DataSourceBuilder.buildDataSourceObject(DataSourceBuilder.java:60)
    at org.wso2.carbon.datasource.core.DataSourceBuilder.buildCarbonDataSource(DataSourceBuilder.java:44)
    at org.wso2.carbon.datasource.core.DataSourceManager.initDataSources(DataSourceManager.java:163)
    at org.wso2.carbon.datasource.core.internal.DataSourceListenerComponent.onAllRequiredCapabilitiesAvailable(DataSourceListenerComponent.java:125)
    at org.wso2.carbon.kernel.internal.startupresolver.StartupComponentManager.lambda$notifySatisfiableComponents$7(StartupComponentManager.java:266)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
    at org.wso2.carbon.kernel.internal.startupresolver.StartupComponentManager.notifySatisfiableComponents(StartupComponentManager.java:252)
    at org.wso2.carbon.kernel.internal.startupresolver.StartupOrderResolver$1.run(StartupOrderResolver.java:204)
    at java.base/java.util.TimerThread.mainLoop(Timer.java:556)
    at java.base/java.util.TimerThread.run(Timer.java:506)
Caused by: java.sql.SQLSyntaxErrorException: Unknown database 'analytics_db'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:833)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:453)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:353)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:473)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:562)
    ... 15 more

Answer 1

First of all, manually You need to create a database called analytics_db and point to it. Then automatically DB tables will be created.