My WebMethods startup problem with mysql database driver

Hello,

we wanted to change our database from SQL to MySQL community edition. Therfore, we did the following steps:

  1. Installed MySQL server and created database
  2. Stopped Integration Server and My webmethods Server
  3. Downloaded MySQL jdbc connector and put the jar file into folders \common\lib\ext and \IntegrationServer\lib\jars\custom as well as \MWS\lib
  4. Started Integration Server, created new jdbc pool alias with url jdbc:mysql://localhost:3306/ and changed functional alias definitions to this pool Alias
  5. Restarted Integration Server
    6.Create a driver-name .bnd text file in the Software AG_directory \MWS\lib directory,
    where driver-name is the name of the .jar file, for example, mysql-connectorjava-
    version .bnd.
  6. Provided instructions for the OSGi bundle conversion in the .bnd text file, as
    described in the following example:

[i]Replace the values in italics . For Bundle-Version , you can use the version number
of the .jar file, but any unique number is valid. You can also use the value (5.1.41 )
provided in the following example.

# attach as fragment to the caf.server bundle
Fragment-Host: com.webmethods.caf.server
Bundle-SymbolicName: mysql-connector-java-
Bundle-Version: 5.1.41
Include-Resource: mysql-connector-java-5.1.41 .jar
-exportcontents: *
Bundle-ClassPath: mysql-connector-java-5.1.41 .jar
Import-Package: *;resolution:=optional
[/i]

  1. At a command line prompt, typed the following command to move to the server’s bin
    directory (under Path MWS\bin)
    mws.bat -s serverName update
  2. Changed mws.db.xml in path \MWS\server\default\config in order to use com.mysql.jdbc.Driver (see attached file)
  3. Restarted My WebMethods Server

After this. everything works fine in Integration Server. Test of connections is successful (see attached file). However, the My webmethods Server fails to start. The error in log file is as follows:

<log4j:event logger=“com.webmethods.portal.service.sql.core.provider.pool.PooledConnectionProvider” timestamp=“1531386091234” level=“ERROR” thread=“Start Level: Equinox Container: e02fe91b-b285-0018-1328-aa56e1a696d0”>
log4j:message</log4j:message>
log4j:NDC</log4j:NDC>
log4j:throwable</log4j:throwable>
</log4j:event>

Why cant My webMebMethods find the driver?
Any idea why this is happening?
Thanks a lot in advance :slight_smile:


Hi Natalie,

which version of wM are you using?

Were the tables created on MySQL database by Database Configurator?

When MySQL is officially supported by your wM version, there should be an appropriate driver in the bundled DataDirect Driver jar.

Regards,
Holger

Dear Holger,

thank you for your quick reply.
We use wM 10.1 that officially supports MySQL 5.7.
However I solved the problem mentioned above. The solution was to uninstall MywebMethods which was initially installed using SQL database. Afterwards we newly installed My webmethods based on embedded Database and did the mentioned configurations (steps 3-11).

The problem now is as follows:
My webMethods server can be started but in the web ui we get the error:

[ISS.0088.9134] Exception occurred while processing the body of the message

The reason might be that the database tables are not completely created. Running the database component configurator we get the following error message:

  • Executing script : C:\SoftwareAG\common\db/scripts/mysql/crossreference/11/create/mys_xrf_c_ddl.sql
    

Error: InnoDB: A general tablespace named WEBMDATA cannot be found.

Do you have any suggestions?
Regards

Hi Natalie,

do you have the possibility to create the database components with dba rights?
This will automatically create the tablespaces.

Otherwise you will have to run the tablespace creation scripts manually.
These scripts are located in the storage directory of the mysql branch.

Regards,
Holger

Hi Holger,

trying to create the database components with dba rights was not successfull -> same error. However we solved the problem by manually create the tablespace running command:
CREATE TABLESPACE WEBMDATA ADD DATAFILE ‘webmdata.ibd’ Engine=InnoDB;

Thanks a lot.

Hi Natalie,

did you also create the tablespace WEBMINDX?

Usually the database scripts are referring to the WEBMDATA tablespace for the most database objects except for indices which are located in the WEBMINDX tablespace.

Regards,
Holger

Hi Holger,

precautionally we also created tablespace WEBMINDX. However, everything could be started without this step.
Nevertheless, we keep stumbling from one problem to the next…
Now, the My webMethods Server can be started without errors, but does not found/show items from database which were written by the Integration Server. Connection is successfull and entries are written in table wmservice.
Any idea?
Regards,

Natalie

Hi Natalie,

are there any messages in the IntegrationServer server.log and/or error log or the MWS full.log?

Is the WmMonitor package configured to point to the MWS?
Is the MWS SAML Resolver URL configured to the SAML service of the MWS?
Is the MWS configured to know about the IntegrationServer?

MWS does not have direct access to the Audit database, but uses the WS API of the WmMonitor package instead, which in turn uses the internal user management of MWS to check for the permissions to execute the services in the WmMonitor package.

Regards,
Holger

Hi Holger,

I checked every config again

  • WmMonitor package is configured to point to the MWS
  • MWS SAML Resolver URL is configured to the SAML service of the MWS
  • MWS is configured to know about the IntegrationServer

See also attached screenshots.
I also checked error.log and MWS_full_.log and couldnt find any errors.
FailedAudit_ is also empty.

The only think I found is this entry in Server.log:
2018-07-18 08:15:14 MESZ [ISS.0025.0057I] Reading system level information
2018-07-18 08:15:14 MESZ [MED.0010.0319W] Registered consumer names file [C:\SoftwareAG\IntegrationServer\instances\default\packages\WmMediator\config\resources\consumers\registeredNames.xml] could not be found! Could not load RegisteredNameType information into Mediator.
2018-07-18 08:15:14 MESZ [MED.0010.0803W] Alias-Services association file [C:\SoftwareAG\IntegrationServer\instances\default\packages\WmMediator\config\resources\aliases\service-alias-association.xml] could not be found! Could not load AliasServiceAssociation information into Mediator.
2018-07-18 08:15:26 MESZ [MED.0204.0013W] No value defined in CCC cache for this key: currentInterval
2018-07-18 08:16:46 MESZ [ISS.0053.0002C] Access denied for user Administrator on port 5555 -> ‘wm-message’ from 127.0.0.1.
2018-07-18 08:18:05 MESZ [ISS.0053.0002C] Access denied for user Administrator on port 5555 -> ‘soap/rpc’ from .
2018-07-18 09:19:10 MESZ [BPM.0101.0002E] Exception: java.sql.SQLException
2018-07-18 09:19:10 MESZ [BPM.0101.0512E] Storage exception: Can’'t call commit when autocommit=true

Regards,


WmMonitor.PNG
MWSConfig.PNG

Hi Natalie,

the SQL-Exception “[BPM.0101.0002E] Exception: java.sql.SQLException” should be analysed further.

Beside that I am running out of ideas now.

Regards,
Holger

Dear Holger,

I just want to let you know that, after a lot of analysis and Support by Software AG colleagues, we finally solved the issue.

Problem:
The My webMethods Server uses the following service to display the results: pub.monitor.service:getList
By setting the server logging for Integration Server (Settings->Logging->Server Logger) for positions 119 and 120 to TRACE, in file \IntegrationServer\instances\default\logs\server.log we found the following SQL query:
SELECT DISTINCT t13.ROOTCONTEXTID , t13.PARENTCONTEXTID , t13.CONTEXTID , t13.SERVICENAME , t13.SERVERID , t13.USERID , t14.FIRSTSTATUS , t14.FIRSTSTATUS FIRSTSTATUSDECODE, t14.FIRSTTIME , t14.FIRSTTIME FIRSTTIMESTRING, t14.LASTSTATUS , t14.LASTSTATUS LASTSTATUSDECODE, t14.LASTTIME , t14.LASTTIME LASTTIMESTRING, t14.DURATION FROM WMSERVICE t13, WMSERVICE_MIN_MAX t14 WHERE t14.CONTEXTID = t13.CONTEXTID AND t13.AUDITTIMESTAMP = t14.LASTTIME ORDER BY t14.LASTTIME DESC

However, the format of AUDITTIMESTAMP (timestamp(6)) und LASTTIME (datetime) differs -> No results will be selected (except of those which where startet f.e. at 2018-08-23 16:30:03.000).

Solution:
The error can be fixed by setting the format of columns FIRSTTIME und LASTTIME of table WMSERVICE_MIN_MAX to timestamp(6).

ALTER TABLE .wmservice_min_max MODIFY FIRSTTIME TIMESTAMP(6) NULL;
ALTER TABLE .wmservice_min_max MODIFY LASTTIME TIMESTAMP(6) NULL;

Thank you very much for your support and kind regards,

Natalie

Hi Natalie.
I’m sorry, can you help me ?
i’m in troble with installation MyWebmethodServer cause MWS Cannot load database driver: com.mysql.jdbc.Driver.
i haven’t documentation about Installing webMethods and Intelligent
Business Operations Products
.
can you give detail installation of MWS ?
tq every helping. Im sorry for the bad english.

Hi,

for every Version of wM which supports MySQL database there is a built-in driver for MySQL as well.
It can be found under common/lib/ext/dd-cjdbc.jar
This driver is known by the MWS bootstrap routine by default.

URL should then start with “jdbc:wm:mysql://”.

When selecting “External Database” and “MySQL” the Installer should apply the correct data to the mws.db.xml.
Before starting MWS for the first time remember to create the MWS DB schema by running the Database Configurator.

Regards,
Holger

To configure “MyWebmethods” Server with MySql CE
Place downloaded mysql-java-connector jar in the following directory:
\MWS\lib
Create a < mysql-connector-name>.bnd text file in
\MWS\lib directory,
Example:
mysql-connector-java-version.bnd
Provide instructions for the OSGi bundle conversion in the .bnd text file

attach as fragment to the caf.server bundle

Fragment-Host: com.webmethods.caf.server
Bundle-SymbolicName: mysql-connector-java-
Bundle-Version: < mysql-connector-version >
Include-Resource: < mysql-connector-jar >
-exportcontents: *
Bundle-ClassPath: < mysql-connector-jar >
Import-Package: *;resolution:=optional

Example:

attach as fragment to the caf.server bundle

Fragment-Host: com.webmethods.caf.server
Bundle-SymbolicName: mysql-connector-java-
Bundle-Version: 5.1.41
Include-Resource: mysql-connector-java-5.1.41.jar
-exportcontents: *
Bundle-ClassPath: mysql-connector-java-5.1.41.jar
Import-Package: *;resolution:=optional
Run following command at
\MWS\bin
mws.bat -s < serverInstanceName > update
Example:
mws.bat –s default update