B2B Integration

 View Only
  • 1.  Problem with MySQL custom pool

    Posted Wed December 02, 2020 02:25 PM
      |   view attached
    Hello,

    I'm creating a custom pool to interact with a MySQL database on my B2Bi 6.1 installation. Here you can find how I configured it on customer_overrides. I configured it based on this web site: http://myibm-b2bi.blogspot.com/2018/09/

    jdbcService.mysqlPool.driver=com.mysql.cj.jdbc.Driver
    jdbcService.mysqlPool.url=jdbc:mysql://X.X.X.X:YYYY/edi
    jdbcService.mysqlPool.user=XXXX
    jdbcService.mysqlPool.password=XXXX
    jdbcService.mysqlPool.varDataClassName=com.sterlingcommerce.woodstock.util.frame.jdbc.MySQLVarData
    jdbcService.mysqlPool.catalog=edi
    jdbcService.mysqlPool.type=remote
    jdbcService.mysqlPool.transaction=false
    jdbcService.mysqlPool.testOnReserve=true
    jdbcService.mysqlPool.testOnReserveQuery=select * from users
    jdbcService.mysqlPool.testOnReserveInterval=60000
    jdbcService.mysqlPool.maxRetries=100
    jdbcService.mysqlPool.blobPageSize=1024000
    jdbcService.mysqlPool.compressBlob=true
    jdbcService.mysqlPool.dbvendor=mysql
    jdbcService.mysqlPool.buffersize=500
    jdbcService.mysqlPool.maxsize=30
    jdbcService.mysqlPool.initsize=0
    jdbcService.mysqlPool.factory=com.sterlingcommerce.woodstock.util.frame.jdbc.ConnectionFactory
    jdbcService.mysqlPool.behaviour=2
    jdbcService.mysqlPool.lifespan=0
    jdbcService.mysqlPool.idletimeout=86400000
    jdbcService.mysqlPool.housekeepinginterval=3600000
    jdbcService.mysqlPool.waittime=1000
    jdbcService.mysqlPool.errorMissingTable=1146
    jdbcService.mysqlPool.systemPool=true

    I have previously installed mysql-connector-java-8.0.21.jar driver and I can assure you that using that driver and the same database information I can connect remotely to the database. Here is the error I obtain on system.log when doing a SELCT with a Lightweight JDBC Adapter over the new pool.

    [2020-12-02 11:45:27.034] ERRORDTL [1606931127033]com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

    I can tell you also that we are correctly reaching the database since after some tries MySQL is blocking my connections and the error changes to the following:
    [2020-12-02 13:18:36.404] ERROR [1606936716404] null, message from server: "Host 'X.X.X.X' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

    On the server side I just can see that the number of abort connections increase on each try but no more information. I think that the problem resides on Sterling since same driver and configuration are able to connect to the database.

    Do you have any idea to solve it?

    Regards,

    ------------------------------
    Victor Bonilla Palencia
    ------------------------------

    #SupplyChain
    #B2BIntegration

    Attachment(s)

    txt
    error MySQL.txt   35 KB 1 version


  • 2.  RE: Problem with MySQL custom pool

    Posted Fri December 04, 2020 08:08 PM
    Maybe u have a FW or a LB between your B2B and ur DB.


    ------------------------------
    Alejandro Rodriguez
    ------------------------------



  • 3.  RE: Problem with MySQL custom pool

    Posted Mon December 07, 2020 03:01 AM
    I concurre,

    The issue seems to be on "network" layer (or on "database" layer). Your first error in your error.txt is : 
    Communications link failure

    you need to check the "network" layer :

    1) try to access to the database
    with a client software (as Oracle SQL Developer) to check the availability of the database in JDBC from your PC.

    2) check Ping between server
    try to ping the IP adress of the DB server from the B2B server.
    You will know if they are able to exchange.

    3) check Port issue
    Your blog's example being on Windows, you need to open the port of the database (inbound and outbound) to be able to access it. 


    B2B is just implementing JDBC to acces to the database, with this type of error, it is mostly 2 :
    - wrong input (the jdbc client test is helping to solve it)
    - network issue ( host not accessible, Port not open)

    Best regard.

    ------------------------------
    Vincent Lahousse
    Satisco
    ------------------------------



  • 4.  RE: Problem with MySQL custom pool

    Posted Mon December 07, 2020 11:05 AM
    Sometimes (for example if u have a DMZ) the comms depends on the configuration of your FW. Maybe the FW is dropping some messages.

    ------------------------------
    Alejandro Rodriguez
    ------------------------------



  • 5.  RE: Problem with MySQL custom pool

    Posted Tue December 08, 2020 10:57 AM

    this seems to SSL handshake related issues:

    Caused by: javax.net.ssl.SSLHandshakeException: No appropriate protocol, may be no appropriate cipher suite specified or protocols are deactivated
    


    ------------------------------
    Guido Bahlmann
    Principal Support Engineer - Team Lead EMEA L2
    Precisely
    ------------------------------



  • 6.  RE: Problem with MySQL custom pool

    Posted Tue December 08, 2020 03:23 PM
    Hello all,

    I have been validating the connectivity between both servers and there is no problem on that. I have installed a mysql client on the same server where B2Bi is installed and I can connect without any problem to the database.

    Right now I'm more oriented to the SSL handshake error that Guido found. I think that the driver is trying to connect by default with SSL and I show on Java implementations of the driver that some properties are set to disable SSL. How could I specify specific values to the implementation of the driver on Sterling to configure SSL or to disable it? Anyone has any idea?

    Regards

    ------------------------------
    Victor Bonilla Palencia
    ------------------------------



  • 7.  RE: Problem with MySQL custom pool

    Posted Tue December 08, 2020 06:13 PM
    Hello everybody,

    Finally we were able to make it work using an older version of the JDBC driver. Despite connecting to a MySQL 8.0.21 it worked with the driver 8.0.17 instead of 8.0.21

    Thanks for your replies.

    Regards,

    ------------------------------
    Victor Bonilla Palencia
    ------------------------------