In this article

Overview

Use this account type to connect MySQL Snaps with datasources that use MySQL Dynamic accounts. This account deals with the dynamically calculated values. You can specify the account properties as expressions referencing pipeline parameters. The values for dynamic accounts are not stored; hence, use them when requested.

Expression-enabled authentication fields, such as Username, Password, and Client Secret, support Secrets Management, a SnapLogic add-on that allows you to store endpoint credentials in a third-party secrets manager, such as AWS Secrets Manager, Azure Key Vault, or HashiCorp Vault. During validation and execution, pipelines obtain the credentials directly from the secrets manager. Learn more: Configure Accounts to use secrets.

Prerequisite

To ensure compatibility, we recommend that you use matching versions of MySQL driver and MySQL server for configuring your account. For example, when using server version 8.0.22, ensure that the driver version is also 8.0.22. For details, see the official MySQL article on version compatibility.

Account Settings

Parameter Name	Data Type	Description	Default Value	Example
Label*	String	Required. The name for the account. Preferably, update the account name if your project includes more than one account of the same type.	N/A	MySQL_DynamicAccount
JDBC driver class	String	The class name of the JBDC driver.	N/A	com.mysql.jdbc.Driver
JDBC JARs	N/A	List of JDBC JAR files to be loaded. Click the + button on the right of the field to add a row. If you do not add any JDBC JARs, a default JDBC JAR file is loaded. The default JAR file does not support connectivity to MYSQL8. To connect to MYSQL8, you must upload JAR `mysql-connector-java` (v5.1.47 or v8).	N/A	../shared/sqljdbc4-2.0.jar
JDBC driver	String	The software component enabling an application to interact with a database. When you use the relative path for the JDBC JAR file in accounts across different projects, the pipelines fail with the `JAR FILE NOT FOUND` error. Therefore, you must use the absolute path for the JDBC JAR file to run your pipelines successfully.	N/A	Project Spaces/my_diagnostic_space/my_diagnostic /sqljdbc4-2.0.jar
Account properties*	Fieldset	Required. Input required for the application to connect to the database.
Hostname*	String/Expression	Required. The Server address to which the application must connect.	N/A	_hostname
Port number*	Integer/Expression	Required. Database server's port number to which the application must connect.	3306	_port
Database name*	String/Expression	Required. The database name to which the application must connect.	N/A	_database
Username*	String/Expression	Required. The user name that is allowed to connect to the database. It is used as the default user name when retrieving connections. The user name must be valid in order to set up the data source.	N/A	_username
Password*	String/Expression	Required. The password used to connect to the data source. It is used as the default password when retrieving connections. The password must be valid in order to set up the data source.	N/A	_passsword
Configure SSH Tunnel	Checkbox/Expressions	Select this check box if the Snap must create an SSH tunnel dynamically for connecting the JCC node to the MySQL server. Once the operation is completed, the tunnel is closed. If selected, the configuration details of the SSH tunnel must be provided.	Not selected	N/A
SSH Auth Mode	Dropdown List/Expressions	Select the mode for authenticating the user on the SSH tunnel. The associated properties are displayed. The valid options are: Password: If selected, the SSH Hostname, SSH Username and the SSH Password properties must be configured. KeyFile: If selected, the SSH Hostname, SSH Username, KeyStore, KeyStore Password, Key alias, and Private Key Passphrase properties must be configured. This value is considered only if the Configure SSH Tunnel check box is selected.	Password	Password
SSH hostname*	Strings/Expressions	The IP address or the domain name of the SSH server to which you want to connect. This value is considered only if the Configure SSH Tunnel check box is selected.	N/A	_sshhostname
SSH username*	Strings/Expressions	The SSH username for connecting to the tunnel. This value is considered only if the Configure SSH Tunnel check box is selected.	N/A	_sshusername
SSH password*	Strings/Expressions	The password associated with the SSH user name. This field is required if Auth Mode is Password. This value is considered only if the Configure SSH Tunnel check box is selected.	N/A	_sshuserpassword
KeyStore	Strings/Expressions	The location of the keystore file. This field is required if SSH Auth Mode is KeyFile. The file can be in SLDB, on the host machine that is hosting the JCC, or at any other remote location. For a local file, click to select the appropriate file using the file browser. You can also upload the file using any protocol such as `https, ftp, sldb, and sftp.` This value is considered only if the Configure SSH Tunnel check box is selected.	N/A	_keystorepath
KeyStore password	Strings/Expressions	The password required to access the keystore file. This field is required if SSH Auth Mode is KeyFile. This value is considered only if the Configure SSH Tunnel check box is selected.	N/A	_keystorepassword
Key alias	Strings/Expressions	The identifier or label of the private key to be used from the keystore. This field is required if the SSH Auth Mode is KeyFile. This value is considered only if the Configure SSH Tunnel check box is selected.	N/A	_keyalias
Private key passphrase	Strings/Expressions	The password associated with the private key. If no value is provided, the keystore password is used. This value is considered only if the Configure SSH Tunnel check box is selected.	N/A	N/A
Advanced properties	Fieldset	Advanced input required for connecting to the database.
Auto commit	Checkbox/Expressions	Auto-commit property for the database. When selected, each of the batches is committed immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back. When deselected, the Snap execution output is committed only after all the batches are executed. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server, and routes the error documents to the Error view.	Selected	Selected
Batch size*	Integer/Expressions	Required. Batch size defines how many SQL statements can be executed in a single batch. For example, when inserting 100 records with batch size as 40, there will be 100 insert statements, and they are executed in 3 batches, the first and second batches contain 40 insert statements each and the third batch contains 20. It is part of the JDBC standard, there is no maximum limit on the batch size. However, the JDBC drivers do not necessarily follow the standard and could throw exceptions if the batch size is too large for a specific driver. Reduce the batch size if you see an exception saying that the batch size is too large. Select queries are not batched. Using a large batch size could use up the JDBC placeholder limit of 2100. If the batch size is too large, sending a single batch request to the database server can take too long and it would time out. You should test and monitor the performance with different batch sizes and decide the best batch size.	50	50
Fetch size*	Integer/Expressions	Required. Number of rows to fetch at a time when executing a query. Large values could cause the server to run out of memory.	100	100
Max pool size*	Integer/Expressions	Required. Maximum number of concurrent connections a pool will maintain at a time. The number of max pool size depends on how many concurrent connections you need. Each MySQL Snap holds a separate connection when running. For a pipeline that has 4 MySQL Insert, 2 MySQL Execute, and 1 MySQL Update Snaps, the max pool size should be no less than 7 to make sure that the pipeline is executed successfully.	50	50
Max lifetime (minutes)*	Integer/Expressions	Required. The maximum time (in minutes) of a connection in the pool. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed.	30	30
Idle timeout (minutes)*	Integer/Expressions	Required. The maximum time (in seconds) a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool.	5	5
Checkout timeout (milliseconds)*	Integer/Expressions	Required. The number of milliseconds to wait for a connection to be available when the pool is exhausted. A value of 0 waits forever. An exception will be thrown after the wait time has expired. If you provide 0, the Snap waits infinitely until the connection is available. Therefore, we recommend you not to specify 0 for Checkout Timeout.	10000	10000
URL properties	N/A	Input regarding URL properties.	N/A	N/A
URL property name	String/Expression	URL property name	N/A	zeroDateTimeBehavior
URL property value	String/Expression	URL property value	N/A	convertToNull

SnapLogic Documentation

MySQL Dynamic Account

Overview

Prerequisite

Account Settings