MySQL Database Account
In this article
Overview
You can use this account type to connect MySQL Snaps with data sources that use MySQL Database accounts.
Prerequisite
Important
The account must have the JDBC JAR file. The default JAR file does not support connectivity to MYSQL8. To connect to MYSQL8, you must upload JAR mysql-connector-java
(v8). Learn more about version compatibility in the MySQL article.
To ensure compatibility, we recommend that you use the same versions of MySQL driver and MySQL server for configuring your MySQL account. For example, when using server version 8.0.22, ensure that the driver version is also 8.0.22. If you do not add any JDBC JARs, a default JDBC JAR file is loaded.
Limitations
None.
Account Settings
Parameter Name | Data Type | Description | Default Value | Example |
---|---|---|---|---|
Label | String | Required. The user-provided name for the account. Preferably, update the account name if your project includes more than one account of the same type. | N/A | MySQL_DatabaseAccount |
JDBC Driver Class | String | The class name of the JBDC driver. | N/A | com.mysql.jdbc.Driver |
JDBC JARs | List of JDBC JAR files to be loaded. Click 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 | |||
JDBC Driver | String | Upload the JDBC driver and other JAR files that you want to use into SLDB. Click ee Downloading the JDBC Driver. to add a new row. Add each JDBC JAR file in a separate row. For more information about JDBC drivers and downloading the appropriate driver for your account, s When you use the relative path for the JDBC JAR file in accounts across different projects, the pipelines fail with the | N/A | Project Spaces/my_diagnostic_space/my_diagnostic /sqljdbc4-2.0.jar |
Account properties* | Use this field set to specify the account properties required to connect to the database. | |||
Hostname* | String | Specify the server address to which the application must connect. | N/A | mysql.awerwe.us-east-1.rds.amazonaws.com |
Port Number* | Integer | Specify the database server's port number to which the application must connect. | 3306 | 3306 |
Database name* | String | Specify the name of the database to which the application must connect. | N/A | information_schema |
Username | String | Specify 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 | EricJones |
Password | String | Specify 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 | N/A |
Configure SSH Tunnel | String | Select this checkbox 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 | String | Select the mode for authenticating the user on the SSH tunnel. The associated properties are displayed. The valid options are:
This value is considered only if the Configure SSH Tunnel check box is selected. | Password | Password |
SSH Hostname | String | Specify 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 | mysql-tunnel-qa.snaplogic.com |
SSH Username | String | Specify the SSH username for connecting to the tunnel. This value is considered only if the Configure SSH Tunnel check box is selected. | N/A | guestuser |
SSH Password | String | Specify the password associated with the SSH username. This field is required if SSH Auth Mode is Password. This value is considered only if the Configure SSH Tunnel check box is selected. | N/A | N/A |
KeyStore | String | Specify 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 This value is considered only if the Configure SSH Tunnel check box is selected. | N/A | x590_certificate.jks |
KeyStore Password | String | Specify the password to access the keystore file. 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 | N/A |
Key alias | String | Specify the identifier or label of the private key to be used from the keystore. 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 | 1 |
Private Key Passphrase | String | Specify 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 | Use this field set to set advanced properties required for connecting to the database. | |||
Auto commit | N/A | Select this checkbox to auto-commit batches immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back. Deselect this checkbox to commit 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 | Specify the batch size to indicate the number of SQL statements to execute 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.
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 | Specify the 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 | Specify the maximum number of concurrent connections a pool maintains at a time. The number of max pool size depends on the number of 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 life time* | Integer | Specify 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* | Integer | Specify the maximum time (in seconds) a connection is allowed to remain idle in the pool. A value of 0 indicates that idle connections are never removed from the pool. | 5 | 5 |
Checkout timeout* | Integer | Specify the number of milliseconds to wait for a connection to be available when the pool is exhausted. 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 | Use this field set to define the URL properties. You can define the time zone values in this field set. The server time zone is important to convert date and time values stored in the database to the time zone set in the application server. You must configure either the database server or JDBC driver (by passing the serverTimeZone in the Url property name field) to use a specific time zone value for time zone support.
| N/A | N/A |
URL property name | String | Specify the name of the URL property name. | N/A | zeroDateTimeBehavior |
URL property value | String | Specify the value for the URL property defined above. | N/A | convertToNull |
Troubleshooting
None.
Have feedback? Email documentation@snaplogic.com | Ask a question in the SnapLogic Community
© 2017-2024 SnapLogic, Inc.