MySQL

Getting Started With MySQL

The Integromat MySQL modules let you execute your custom logic directly in your database server through stored procedures. Integromat loads the interface of input/output parameters and recordsets dynamically, so each parameter/value can be mapped individually.

Prerequisites:

• A MySQL server connection
• A Configured MySQL server

Creating a Connection to the MySQL Server

Configuring Your MySQL Server

Read Access to MySQL Tables

Before you start configuring your scenario, make sure the account you're using to connect to your database has read access to information_schema.tables, information_schema.columns, information_schema.routines, and information_schema.parameters tables.

Integromat IP Range

Integromat will make connections to your database from the following IP range: 82.208.14.110 - 82.208.14.119. Our servers are set up with valid PTR records, so you can easily restrict access by allowing %.integromat.com.

Enabling Access on Your Firewall

Enable incoming TCP connections to port 3306 from source IP addresses 82.208.14.110 - 82.208.14.119.

On Linux, run the following command:

iptables -A INPUT -m iprange --src-range 82.208.14.110-82.208.14.119 -p tcp --dport 3306 -j ACCEPT

Configuring MySQL for Remote Access

Linux

1. Open the configuration file /etc/mysql/my.cnf

2. Comment outline bind-address = 127.0.0.1 (by adding a hash at the beginning of the line):

#bind-address = 127.0.0.1

3. Restart the MySQL daemon

Creating and Granting Remote Access to Your MySQL Account

1. Start the MySQL command line interface:

mysql -u root -p

2. Execute these commands (with <database>, <user> and <password> replaced by actual values):

CREATE USER <user>@'%' IDENTIFIED BY <password>;
GRANT ALL PRIVILEGES ON <database>.* TO <user>@'%' IDENTIFIED BY <password> WITH GRANT OPTION;
FLUSH PRIVILEGES;

Tolerance of Non-interactive Connection

Integromat processes scenarios transactionally. That's why all Integromat modules that support transactions (e.g., MySQL) have open transactions throughout the whole run of a scenario. Therefore, make sure that you have set a long enough time to tolerate non-interactive connections. This MySQL command will list the values:

SHOW VARIABLES LIKE 'wait_timeout';

See more information in the MySQL documentation on Server System Variables.

Connect With MySQL Hosted From DigitalOcean: 

Click Here to follow the documentation. 

Actions

Insert Row Into a Table

Inserts a row with the desired values into a selected table.

Update Row(s) in a Table

Updates rows (can be defined by the filter settings) in the selected table.

Delete Row(s) From a Table

Deletes a row according to a defined filter.

Execute a Query (Advanced)

Execute a Stored Procedure

Example

The following example adds 2 to the provided parameter (e.g., in the Execute a Stored Procedure module), and returns the result, together with the current date (can be further iterated using the Iterate Recordset of a Stored Procedure module).

CREATE PROCEDURE `test_procedure`(IN id integer, OUT outid integer)
BEGIN
set outid = id + 2;
select id as inid, now() as current_date;
END$$

Searches

Select Row(s) From a Table

This action selects a row from a table according to a defined filter.

You may also use the existing basic text operators for comparing dates and datetimes. Just use text formatted date/datetime as the second operand:

• YYYY-MM-DD for date type
• YYYY-MM-DD HH:mm:ss for datetime type

Literal static datetime value example:
Formatted dynamic datetime value example:

Iterators

Iterates Recordset of a Stored Procedure.

Troubleshooting

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client:

MySQL 8 uses an authentication method that is not supported by Integromat yet.

Solution

Use the mysql_native_password plugin to make the account fall back to the "traditional" process during the handshake, keeping compatibility by default for any previously supported server version.

Create the account using the mysql_native_password authentication plugin:

CREATE USER 'new_user'@'%' IDENTIFIED WITH mysql_native_password BY 'user_password';

Or, modify the existing user account, and specify the mysql_native_password authentication plugin:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourRootPassword';

(Reference: https://github.com/mysqljs/mysql/issues/2046#issuecomment-396039909)

Error: ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction:

This error occurs when you modify the same data using multiple modules. It is caused by SQL transactions.

When any SQL module is executed, then it starts the transaction (1), and the transaction is finished after the scenario is fully executed.
If another module tries to access the same data used in another unfinished transaction (1), then it has to wait until the previous transaction (1) is finished. This, however, never happens because the first transaction (1) will be finished when the scenario is finished.

Solution

Turn on Auto-commit. It finishes (commits) every transaction immediately after the module execution is done.

1. Open Scenario settings.

2. Enable the Auto commit checkbox.

3. Confirm the settings dialog by clicking the OK button.