SXI Forum

SeanR

Administrator

Registered: 20-11-2018

Posts: 148

Warning about SSL when connecting to MYSQL

With the following configuration in a XLayer workflow Connector configuration:

    <Connector id="MySQL_DB_Connector" type="MYSQL">
      <Connection xsi:type="sxi:MYSQL">
        <sxi:Server>localhost</sxi:Server>
        <sxi:User>sxiadmin</sxi:User>
        <sxi:Password>T@gi2b2</sxi:Password>
        <sxi:RetryDelay>30</sxi:RetryDelay>
        <sxi:DBName>xlayerrestapi</sxi:DBName>
        <sxi:Port>3306</sxi:Port>
      </Connection>
      <sxi:ReturnCodes>
        <sxi:Success />
        <sxi:RecoverableError>
          <sxi:Code>I/O Error: Read timed out</sxi:Code>
          <sxi:Code>Invalid state, the Connection object is closed.</sxi:Code>
          <sxi:Code>I/O Error: Connection reset by peer: socket write error</sxi:Code>
        </sxi:RecoverableError>
      </sxi:ReturnCodes>
    </Connector>

if you try to connect to a MYSQL v8 database you will receive a message in the X-ServiceBroker/bin/error.log log file which looks as follows:

 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

You will not be able to connect to the database and will get the following errors in the workflow's logfile:

21:10:45.110 - ERROR - Connection failed with error 'getConnection failed, using driver org.gjt.mm.mysql.Driver. Reason:Communications link failure

The last packet successfully received from the server was 13 milliseconds ago.  The last packet sent successfully to the server was 13 milliseconds ago.' after attempt 2, waiting 30s before connecting again
za.co.sxi.exceptions.DatabaseConnectionException: getConnection failed, using driver org.gjt.mm.mysql.Driver. Reason:Communications link failure

The last packet successfully received from the server was 13 milliseconds ago.  The last packet sent successfully to the server was 13 milliseconds ago.

This is due to a new requirement for SSL when connecting to the database. This can easily be turned off by changing the Connector configuration as follows:

<Connector id="MySQL_DB_Connector" type="MYSQL">
      <Connection xsi:type="sxi:MYSQL">
        <sxi:Server>localhost</sxi:Server>
        <sxi:User>sxiadmin</sxi:User>
        <sxi:Password>T@gi2b2</sxi:Password>
        <sxi:RetryDelay>30</sxi:RetryDelay>
        <sxi:DBName>xlayerrestapi</sxi:DBName>
        <sxi:Properties>
          <sxi:Property name="useSSL" value="false"/>
        </sxi:Properties>
        <sxi:Port>3306</sxi:Port>
      </Connection>
      <sxi:ReturnCodes>
        <sxi:Success />
        <sxi:RecoverableError>
          <sxi:Code>I/O Error: Read timed out</sxi:Code>
          <sxi:Code>Invalid state, the Connection object is closed.</sxi:Code>
          <sxi:Code>I/O Error: Connection reset by peer: socket write error</sxi:Code>
        </sxi:RecoverableError>
      </sxi:ReturnCodes>
    </Connector>

The important piece that was added was the

<sxi:Properties>
  <sxi:Property name="useSSL" value="false"/>
</sxi:Properties>

However, while this will work it may not be the best and most secure solution.  An excellent explanation can be found here

This may need some additional configuration options to be added to XLayer.  These will be discussed in a future version of XLayer.  The above is a working solution in the meantime.