Connector for MySQL – Features and settings

Page content

Connector features

The following functionality is available when you access data with the connector for MySQL.


Feature	Supported?
Load methods	Import (in-memory) External (in-database) On-demand
Custom queries	Yes
Stored procedures	Yes (except for SingleStore)
Custom connection properties	No
Single sign-on with identity provider	No
Authoring in web client	No
Included in Spotfire distribution on Linux	Yes

Data source properties

The following are the supported data source properties that you can configure when you create a data connection with the connector for MySQL.


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: 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: MySQL Connector/ODBC MariaDB Connector/ODBC MongoDB ODBC Driver for BI Connector
Use SSL	Determines if Secure Sockets Layer (SSL) is enabled for connections to the database. 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: If you are working against a public Spotfire Server (not on-premises), 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 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 between 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.
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.

Note: For more information about the advanced settings, please refer to the official documentation provided by the driver vendor.

Supported data types

When you are setting up a connection to an external data source, Spotfire needs to map the data types in the data source to data types in Spotfire. The following are the data types that the MySQL connector supports.


Database data type	Spotfire data type
BINARY	Binary
BLOB	Binary
LONGBLOB	Binary
MEDIUMBLOB	Binary
TINYBLOB	Binary
BIT	Boolean
DATE	Date
DATETIME	DateTime
TIMESTAMP	DateTime
INT	Integer
MEDIUMINT	Integer
SMALLINT	Integer
TINYINT	Integer
YEAR	Integer
BIGINT	LongInteger
DOUBLE	Real
FLOAT	SingleReal
CHAR	String
ENUM	String
LONGTEXT	String
MEDIUMTEXT	String
NCHAR	String
NVARCHAR	String
SET	String
TINYTEXT	String
VARBINARY	String
VARCHAR	String
TIME	Time
DECIMAL(p, s)	When p <= 9 and s = 0: Integer When 9 < p <= 18 and s = 0: LongInteger Else: Real

Supported functions

Supported functions are the functions that you can use when you work with in-database data tables, for example for calculated columns and custom expressions.

Note: Some supported functions might not be possible to use with your database. This depends on what functions are available in the database, which often differs between database versions and types.

The following are the functions that the MySQL connector supports.


Function type	Functions supported
Date and Time	`Year, Quarter, Month, Week, WeekDay, WeekOfYear, Day, DayOfMonth, DayOfYear, DayOfWeek, DayName, Hour, Minute, Second, Microsecond`
Conversion	`SN`
Math	`Abs, ASin, ACos, ATan, Ceiling, Cos, Sin, Tan, Cot, Exp, Floor, Log, Log10, Log2, Ln, Power, Sqrt, Degrees, Pi, Truncate`
Operators	`+, -, *, /, %`
Statistical	`Sum, Avg, Min, Max, Count, UniqueCount, Std, Stddev_Samp, Var_Samp, Var_Pop`
Text	`Concat, Instr, Length, Lower, LPad, RPad, RTrim, Trim, Upper, LeftString, RightString, Locate, Space, Replace, Substring, Substring_Index, Soundex, Reverse, Format, Insert`