MySQL

MySQL is an open-source relational database management system. It is widely used for storing, managing, and retrieving structured data, making it a popular choice for web applications, content management systems, and various other software solutions.

Prerequisites

Authorize Connection to MySQL

In MySQL

Please keep in mind that this guide provides general instructions, and your individual setup might need a different approach or some extra steps. Always take your organization's security guidelines into account when you're making these changes. Additionally, it's a good idea to seek assistance from a database administrator or an IT professional to make sure everything is set up correctly.

Create a MySQL User and Set Permissions

1. Log into MySQL Shell.

2. Run a command to create a new user. The command below presumes using root user for user creation, however, you can replace it with any user that has role for creating the users and granting permissions.

   mysql -u root -p
   

   CREATE USER 'dataddo'@'%' IDENTIFIED BY '***';
   

3. Grant CREATE, SELECT, UPDATE, INSERT, DELETE permissions for your database (please replace database_name with the correct value):

   GRANT CREATE ON database_name.* TO 'dataddo'@'%';
   GRANT INSERT, UPDATE, DELETE ON database_name.* TO 'dataddo'@'%';
   FLUSH PRIVILEGES;
   

Allow Network Connection

For this destination, whitelisting IP addresses is not available in the database system. Instead, you can configure the network access at the firewall level. Here are the general steps:

1. Identify the firewall that's protecting your service. This could be a Windows or Linux firewall running on the server itself, or it could be a network firewall.
2. Open the firewall's configuration settings. This process will vary depending on the type of your firewall.
3. Look for the setting that allows you to define inbound rules or policies.
4. Create a new rule or policy that allows inbound traffic on the port that the service is using (the default is 3306) from the Dataddo IP addresses.
5. Save your changes and test the connection from Dataddo to make sure everything is working as intended.

In Dataddo

1. In the Authorizers tab, click on Authorize New Service and select Universal MySQL.
2. In the Universal MySQL configuration page, click on Add new account.
3. You will be asked to fill the following fields
   1. Server IP or Hostname: Public IP or hostname of your MySQL Server.
   2. Database: Name of the database you will use for writing or reading the data.
   3. Username: Username of your MySQL user.
   4. Password: Password to your MySQL user.
   5. Port: Port to connect to MySQL. The default value is 3306.
   6. TLS/SSL Settings: Keep the value on PREFERRED, this will ensure using the SSL connection when available. If you are using a self-signed certificate, please use SKIP VERIFY.
4. [Optional] Configure the connection via SSH tunnel. To do so, please follow these steps.
5. Click on Save.

Create a New MySQL Destination

Create a Flow to MySQL

Table Naming Convention

When naming your table, please make sure the table name contains only:

• Letters
• Numbers
• Underscores
• Dollar signs

Troubleshooting

Incorrect String Value Error

Error 1366 (22007): Incorrect string value: '***' for column *** at row ***

This issue may be caused by incorrect encoding in the target table or column. To solve the issue, please follow these steps:

1. Ensure your table and column use the utf8mb4 encoding to handle a wider range of characters.
2. Run the following query in your MySQL instance to fix the issue. Remember to replace your_table_name with the actual name of your table.

   ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   

Failed to verify certificate

pinging database server: tls: failed to verify certificate: x509: cannot validate certificate for 0.0.0.0 because it doesn't contain any IP SANs

For your own security we are by default enforcing the TLS connection to your MySQL Instance. The error indicates that the Transport Layer Security (TLS) protocol failed to verify the server's SSL/TLS certificate, specifically due to an issue validating the certificate with the x509 standard. This typically arises from a problem with the certificate's trust chain, expiration, or format.

1. In Dataddo MySQL Authorizer Set TLS/SSL Settings to SKIP_VERIFY

Data Too Long Error

Error 1406 (22001): Data too long for column '***' at row ***

This issue may is caused by attempting to insert data that's too lengthy for the specified column's defined data type or length. To solve the issue, please follow these steps:

1. Ensure the data you're inserting matches the column's allowed length or data type.
2. Run the following query in your MySQL instance to fix the issue. Replace your_table_name with your table's name, your_column_name with the column in question, DATA_TYPE with the desired type (e.g., VARCHAR), and SIZE with the new size limit.

ALTER TABLE your_table_name MODIFY your_column_name DATA_TYPE(SIZE);

Cannot Change Write Mode to Upsert in Existing Flow

ERROR MESSAGE

"Action failed: stream transfer: write data from stream: do batch write: writeBufferedData: Failed to prepare table: checking unique constraints: missing UNIQUE INDEX for column: id"

This issue specifically occurs when you attempt to change the write mode to upsert for an existing flow to MySQL. Dataddo creates indexes to ensure smooth system operations, and to maintain the integrity of these operations, changes to the write mode in existing flows to MySQL are restricted. However, the following workarounds are available:

1. If you can either lose the data in the existing table without consequences or recover it through backfilling, we recommend deleting the table and the existing flow. Then, re-create the flow using upsert to ensure that the table and indexes are correctly established.
2. If preserving the existing data is crucial, consider these steps:
   1. Delete the existing flow.
   2. Edit the destination table to add a unique index on the columns you wish to include in the composite key. For example, you could use the SQL query like in the example below.
   3. Re-create the flow, selecting upsert write mode and a composite key that incorporates the column on which you created the unique index.

ALTER TABLE table_name
ADD UNIQUE INDEX unique_index_name (column1, column2);