TIBCO Scribe® Online Connector For Oracle MySQL

TheTIBCO Scribe® Online Connector for Oracle MySQL allows you to easily integrate your MySQL databases with your CRM, ERP, or marketing automation applications. The connector discovers tables, views, and fields and understands relationships between tables.

Connector Specifications

	Supported
Agent Types
Connect on-premise	X
Connect cloud	X
Data Replication Apps
Source
Target	X
On Schedule Apps
Source	X
Target	X
On Event Apps
Source	X
Target	X
Flows
Integration	X
Request-Reply	X
Message

Note: This Connector is available from the TIBCO Cloud™ Integration Marketplace. See Marketplace Connectors for more information.

Setup Considerations

The Oracle MySQL Connector for TIBCO Cloud™ Integration - Connect is supported with the following:

Oracle MySQL Versions 5.5, 5.6, 5.7, and 8.0 all editions
Oracle MySQL hosted on Amazon RDS
Oracle MySQL hosted on Amazon Aurora
Oracle MySQL hosted on Google Cloud
When hosting Oracle MySQL on Google Cloud the default configuration is for IPv6, however it can be changed to IPv4. If you are using a Connect on-premise agent, that agent must be hosted on a network that matches the Google Cloud configuration. If Google Cloud is configured to use IPv6, then the agent must be on an IPv6 network. If Google Cloud is configured for IPv4, then the agent must be on an IPv4 network. Note that agents can be hosted on a server with both IPv6 and IPv4 enabled. If configured incorrectly, the following error is generated:
```
Unable to connect to any of the specified MySQL hosts.
```
Note: Connect cloud agents are not supported for IPv6 configurations. To use a Connect cloud agent with Google Cloud, you must change the Google Cloud configuration to IPv4.

SSL Certificates

Oracle MySQL uses the PEM format for certificates and private keys, but .NET does not support this format natively. To work around the issue, convert certificates to PFX also known as PKCS#12 format using openssl. The complete instructions can be found in the Oracle MySQL official documentation, Tutorial: Configuring SSL with Connector/Net and the Connecting To MySQL Using SSL Knowledge Base article in the TIBCO Community.

After conversion copy the certificate to Connect on-premise agent manually.

Selecting An Agent Type For Oracle MySQL

Refer to TIBCO Cloud™ Integration - Connect Agents for information on available agent types and how to select the best agent for your app.

Connecting To Oracle MySQL

Note: Best practice is to create Connections with credentials that limit permissions in the target system, following the principle of least privilege. Using Administrator level credentials in a Connection provides Administrator level access to the target system for TIBCO Cloud™ Integration - Connect users. Depending on the entities supported, a TIBCO Cloud™ Integration - Connect user could alter user accounts in the target system.

Select Connections from the menu.
From the Connections page select Create to open the Create a connection dialog.
Select the Connector from the list to open the Connection dialog, and then enter the following information for this Connection:
- Name — This can be any meaningful name, up to 25 characters.
- Alias — An alias for this Connection name. The alias is generated from the Connection name, and can be up to 25 characters. The Connection alias can include letters, numbers, and underscores. Spaces and special characters are not accepted. You can change the alias. For more information, see Connection Alias.
- Hostname — Name of the Oracle MySQL host to which you want to connect. This can be either a machine name or an IP address. By default, TIBCO Cloud™ Integration - Connect uses port 3306 to connect to the host. You can specify a different port by appending :<port_number> to the host name.
  For example, to use port 1234 to connect to host, myhost.com, enter:
```
myhost.com:1234
```
- Database Schema — Database to which you want to connect.
- Username — User name for this Oracle MySQL database instance.
- Password — Password for this Username.
- Additional Parameters — Optional field where you can specify one or more parameters. If left blank, default MySQL parameter values are used. See the Connector/Net Connection-String Options Reference section of the Oracle MySQL documentation for a list of parameters that can be used and their default values.
  Syntax for the Additional Parameters field is as follows:
  - Parameters are case insensitive.
  - If misspelled or invalid, they are ignored.
  - Separate each parameter with a semicolon (;).
  - Parameters that are handled by other fields in the Connection dialog are ignored if used in the Additional Parameters field, including:
    - User Id, UserID, Username, Uid, User name, User
    - Password, pwd
    - Host, Server, Data Source, DataSource, Address, Addr,
    - Network Address
    - Initial Catalog, Database
Select Test to ensure that the agent can connect to your database. Be sure to test the Connection against all agents that use this Connection. See Testing Connections.
Select OK/Save to save the Connection.

Note: The Username of the TIBCO Cloud™ Integration - Connect MySQL Connection must have access to the MySQL host and to the computer where the agent is installed.

Metadata Notes

Naming

Connection metadata must have unique entity, relationship, and field names. If your Connection metadata has duplicate names, review the source system to determine if the duplicates can be renamed.

Oracle MySQL only supports lower case table names.

Oracle MySQL Connector As An App Source

Net Change

The first time a Data replication app executes it creates the tables and adds all the records for the selected or recommended entities in the target Oracle MySQL datastore. Subsequent replications attempt to process only new and updated records. To filter for only new and updated records, TIBCO Scribe® does the following:

Requests the name of the modified date column from the source datastore for each entity being replicated.
Checks that field in the target datastore.
Locates the newest date for each replicated table.
Uses that date as a filter to query the source datastore and select only those records that are newer than the date in the target datastore.

Note: In cases where there are entities that do not have an associated date field, all records for those entities are processed each time a Data replication app runs.

Deleted Records In Oracle MySQL Data Replication Apps

Records deleted in the source datastore are not deleted in the corresponding table in the target Oracle MySQL replication database. Instead, each record has an associated value in the SCRIBE_DELETEDON field for the replicated table. The SCRIBE_DELETEDON field contains a datetime stamp that corresponds to the date and time the record was deleted from the source datastore.

Note: Occasionally, some deleted records are reprocessed because the source modified date field controls the target field and date used to filter for new, changed, and deleted records from the source datastore. See Net Change. If the most recent modified date is older than the most recent SCRIBE_DELETEDON date, the deleted records between the two dates are reprocessed.

Entities Without Modification Dates

Typically, entities without modification date fields are not included in the Recommended entities list, but can be chosen using the Entities tab on the Data replication app Details page. If a selected source entity does not have a date for tracking deleted records, when the Data replication app executes, TIBCO Cloud™ Integration - Connect does the following:

Compares the source records to the records in the target.
Determines which records are missing from the source, but still exist in the target.
Updates the SCRIBE_DELETEDON field for the corresponding records in the target database using the datetime of the Data replication app execution.
Deletes the remaining records that have NULL in the SCRIBE_DELETEDON field from the Oracle MySQL table.
Appends all of the source records to the table. This guarantees that existing records, updated records, and new records are included in the replication.

Duplicate Records

Warning: Modifying MySQL database table Index Properties can cause duplicate records during subsequent executions of aData replication app when Oracle MySQL is the target for a Data replication app. See Duplicate Records When Using SQL As Replication Service Target for additional information.

Miscellaneous

For Data replication apps, replicated tables are, by default, created with InnoDB storage engines. For more information, see your MySQL Database Administrator.
If the source data being replicated contains field values that exceed the maximumlength setting of the corresponding target field, the Connector generates truncation errors. To resolve this either modify the data in the source to conform to the field length setting or change the value of the max_allowed_packet variable in the configuration of your Oracle MySQL instance.
Merging data from more than one source datastore into the same MySQL database is not recommended.

Oracle MySQL Connector As An App Target

Batch Processing

Supports Batch Processing for the following operations:
- Create
- Delete
See Batch Processing for additional information.

Miscellaneous

Changing Access And Privileges In Oracle MySQL

As part of troubleshooting a Oracle MySQL Connection, you may need to change access in privileges in Oracle MySQL.

From Oracle MySQL Workbench, open the Users & Privileges Administration page.
Open the Server Access Management tab.
From the Server Access Management tab, associate the Login username with the hostname of the computer where the agent is installed. The hostname can be either the machine name or IP address of the machine where the agent is installed. For example:

Failed Connection Test

If the Username for this Connection does not have appropriate privileges, the Connection Test fails with the following message.

Host <hostname> is not allowed to connect to MySQL Server

Set privileges from the Server Access Management tab of the MySQL Workbench Users and Privileges page. For more information, see your MySQL Database Administrator.

TIBCO Cloud™ Integration - Connect API Considerations

To create Connections with the TIBCO Cloud™ Integration - Connect API, the Oracle MySQL Connector requires the following information:

Connector Name	Oracle MySQL
Connector ID	905F0025-7DB2-4EA3-B22B-C88B099680EB

TIBCO Cloud™ Integration - Connect Connection Properties

In addition, this Connector uses the Connection properties shown in the following table.

Note: Connection property names are case-sensitive.

Name	Data Type	Required	Secured
Server	String	Yes	No
Database	String	Yes	No
UserName	String	Yes	No
Password	String	Yes	Yes
ExtendedConnectionProperties	String	No	No

License Agreement

The TIBCO End User License Agreement for the Oracle MySQL Connector describes TIBCO and your legal obligations and requirements. TIBCO suggests that you read the End User License Agreement.

More Information

For additional information on this Connector, refer to the Knowledge Base and Discussions in the TIBCO Community.