Details on MySQL Connection

This dialog is used to configure a connection with the MySQL connector.  You can choose whether to analyze data in-database or to import it into your analysis.

Prerequisites

To be able to use the MySQL connector, you must install a driver on your computer.

For details on what drivers you can use, see the system requirements for Spotfire Connectors at http://spotfi.re/sr/connectors.

For instructions on where you can find the supported drivers, see Drivers and Data Sources in Spotfire.

Note: In versions of Spotfire earlier than 10.3 LTS, the Connector for MySQL required a different driver. If you are upgrading from one of these versions, you must install a new driver on all computers running Spotfire clients, to be able to continue using the MySQL connector and any analyses with existing connections to MySQL.

To add a new MySQL connection to the library:

  1. On the menu bar, select Data > Manage Data Connections.

  2. Click Add New > Data Connection and select MySQL.

To add a new MySQL connection to an analysis:

  1. Click Files and data .

  2. In the Files and data flyout, click Connect to.

  3. In the list of connectors, click MySQL.

  4. In the MySQL flyout, click New connection.

General

Option

Description

Server

The name of the server where your data is located. To include a port number, add it directly after the name preceded by colon.

Example with port number:
MyDatabaseServer:1234

Database system

Select the type of database system of the server you want to connect to. Select from:

  • MySQL

  • MariaDB

  • MongoDB Connector for BI

  • SingleStore

Your selection also decides which ODBC driver the connector will use, in case there are multiple supported drivers installed on the computer that is running Spotfire.

Tip: For details about which drivers the different options correspond to, and where you can find them, see Drivers and Data Sources in Spotfire.

If the driver for the selected database system is not installed, but another supported driver is, then the connector will attempt to use that driver for the connection instead. If multiple other supported drivers are installed, Spotfire selects which driver to use in this order:

  1. MySQL Connector/ODBC

  2. MariaDB Connector/ODBC

  3. MongoDB ODBC Driver for BI Connector

Note: If you select SingleStore, Spotfire requires that you have installed a specific version of the MySQL Connector/ODBC driver. For details about the supported driver version, see the system requirements.

Use SSL

Determines if SSL is enabled for connections to the data source.

Important: Use SSL and the options for configuring SSL are not supported in connections to MemSQL and MongoDB Connector for BI.

   Embed certificate file

[Only available when you select Use SSL.]

The certificate file to use for verifying the database server, when you select SSL Mode VERIFY_CA or VERIFY_IDENTITY.

To embed a certificate, click Browse… and select a certificate file on your computer. The certificate file is embedded and stored in the Spotfire data source.  

Tip: Because the certificate file is embedded, other users do not have to have the certificate file on their computers.

What certificate file should I use?

Note: As a TIBCO Cloud™ Spotfire® user, you can only use certificate files with public keys.

The certificate file must match the CA certificate on the database server.

It is recommended to only use certificate files with public keys.

It can be a security risk to use certificate files with private keys, because the certificate file is embedded in the Spotfire data source, and potentially also in analysis files.

To be able to embed certificate files with private keys, you must be part of a user group with the preference setting AllowEmbeddingCertificatesWithPrivateKeys enabled.

Tip: For efficient reuse and maintainability, it is recommended that you shared connection data sources in the library.

      Browse...

Open a dialog where you can browse and select a certificate file on your computer.

      View

Open the Certificate dialog in Windows, where you can view information about the embedded certificate file.

      Remove

Remove the embedded certificate file.

   SSL mode

  

[Only available when you select Use SSL.]

Determines the SSL mode to use in connections to the data source. The following options are available:

PREFERRED

Use encryption in the connection if possible.

REQUIRED

The connection must be encrypted.

VERIFY_CA

[To use this option, you must embed a certificate file.]

The connection must be encrypted. The client verifies the database server’s CA certificate.

VERIFY_IDENTITY

[To use this option, you must embed a certificate file.]

The connection must be encrypted. The client verifies the database server’s CA certificate, including that the host name matches the certificate.

One-way TLS

[Only available for connections to MariaDB.]

When you select MariaDB in the Database system drop-down menu, One-way TLS is the only available SSL mode.

Authentication method

The authentication method to use when logging into the database. Choose from Windows authentication and Database authentication.

Windows authentication

[Only applicable for connections to MySQL databases.]

With Windows authentication, Spotfire uses the currently logged in Windows user (DOMAIN\Username) for authentication. This requires that you have a corresponding user in the format DOMAIN\Username set up in the MySQL database.

Database authentication

With database authentication the authentication is done using a database user. Database credentials can be stored, unencrypted, as part of the analysis file, using a setting in the Data Source Settings dialog. If credentials are found in the analysis file they will be used to automatically authenticate against the database.

If no credentials or credentials profiles are found in the analysis file all who open the file will be prompted for database credentials.

Note that there will be no prompting for credentials if the credentials embedded in the analysis file fail.

Username

The username you wish to use when logging into the MySQL database.

Password

The password for the specified username.

Connect

Connects you to the specified server and populates the list of available databases below.

Database

Select the database of interest from the drop-down list.

Advanced

Note: For information about the settings available on the Advanced tab, please refer to the official documentation provided by the driver vendor.

Option

Description

Convert zero DateTime

Determine how DateTime values that are not valid (for example ‘0000-00-00 00:00:00’) should be handled in the connection.

When Convert zero DateTime is selected, invalid DateTime values are returned as ‘null’.

When Convert zero DateTime is cleared, invalid DateTime values are not accepted.

By default, Convert zero DateTime is cleared.

Character set

Select the character set that should be used to encode queries from the driver to the database.

Connection timeout (s)

Specify the maximum time, in seconds, allowed for a connection to the database to be established.

The default value is 120.

Note: If you set the connection timeout to zero, it will be interpreted as no timeout. This means that there will be no upper limit for trying to connect. This is generally not recommended.

Command timeout (s)

Specify the maximum time, in seconds, allowed for a command to be executed.

The default value is 1800.

Note: If you set the command timeout to zero, it will be interpreted as no timeout. This means that there will be no upper limit for trying to execute the command. This is generally not recommended.

 

 

See also:

MySQL Data Types

Supported Functions