MySQL

true false 107false unhandled content type false unhandled content type requirements to use the mysql app in {{product name}} , you must have a mysql server connection and a configured mysql server connect mysql and {{product name}} to get started, you must first create a connection between mysql and {{product name}} , allowing the two services to communicate to create the connection, you need to mysql /#configure your mysql server mysql /#create a connection in configure your mysql server remote access must be allowed, and the create routine privilege must be granted on the mysql server set the read access to mysql tables make sure the account you are using to connect to your database has read access to the information schema tables , information schema columns , information schema routines , and information schema parameters tables allow connections to and from {{product name}} ip addresses make will make connections to your database from the allow connections to and from make ip addresses docid\ d0fwd60kzrvcj9b0ozriy our servers are set up with valid ptr records, so you can easily restrict access by allowing % make com enable access on your firewall enable incoming tcp connections to port 3306 from source ip addresses configure mysql for remote access linux open the configuration file /etc/mysql/my cnf comment outline bind address = 127 0 0 1 (by adding a hash at the beginning of the line) #bind address = 127 0 0 1 restart the mysql daemon create and grant remote access to your mysql account start the mysql command line interface mysql u root p execute these commands (with \<database> , \<user> and \<password> replaced by actual values) create user \&lt;user\&gt;@'%' identified by \&lt;password\&gt; grant all privileges on \&lt;database\&gt; to \&lt;user\&gt;@'%' identified by \&lt;password\&gt; with grant option; flush privileges; set the tolerance of non interactive connection {{product name}} processes {{scenario plural lowercase}} transactionally that's why all {{product name}} modules that support transactions have open transactions throughout the whole run of a {{scenario singular lowercase}} 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 create a connection in {{product name}} once you have configured your mysql server, you're ready to create the connection in {{product name}} to create the connection log in to your {{product name}} account, add a mysql module to your {{scenario singular lowercase}} , and click create a connection optional in the ​​ connection name​​ field, enter a name for the connection complete the following fields true falsefalse left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type click save if prompted, authenticate your account and confirm access you've successfully created the connection and can now use the mysql app in your {{scenario plural lowercase}} if you want to make changes to your connection in the future, follow the steps connect an application docid\ so88fm6pkt0g adkddfzz mysql modules after connecting to the mysql app, you can use the following types of modules to build your {{scenario plural lowercase}} actions delete row(s) from a table deletes a row according to a defined filter true false 136false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type execute a query (advanced) true false 287false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type only one sql statement is allowed (one semicolon ; as a statement terminator per query) execute a stored procedure true false 180false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type 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$$ insert row into a table inserts a row with the desired values into a selected table true falsefalse left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type to pass a null value, use an insert statement using the mysql docid\ lrghwt5k6gxjpe7xv brc module for more information, refer to mysql docid\ lrghwt5k6gxjpe7xv brc insert row update row(s) in a table updates rows (can be defined by the filter settings) in the selected table true falsefalse left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type to pass a null value, use an insert statement using the mysql docid\ lrghwt5k6gxjpe7xv brc module for more information, refer to mysql docid\ lrghwt5k6gxjpe7xv brc pass null values into a table as of now, it is not possible to pass null values in our standard modules, insert/update row(s) into a table even using the null pill to achieve this, you need to use the execute a query (advanced) module where you prepare a full sql statement (either insert or update) as a workaround as shown in the example you need to update an existing row in your database with three columns name, email, and phone number you want to update a contact and set the contact's phone to null based on the contact’s email address to achieve that, use this statement `update yourtablenamehereset name='joe doe',email='joe\@doe com',phone=nullwhere email='joe\@doe com'` in your {{scenario singular lowercase}} , map the email address (and the name possibly too) from preceding modules and ensure the mapped pills are in single quotes searches select row(s) from a table selects a row from a table according to a defined filter true false 284false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type 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 iterate recordset of a stored procedure true falsefalse left unhandled content type false left unhandled content type false left unhandled content type false left unhandled content type troubleshooting mysql 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 {{product name}} 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 mysql module is executed, then it starts the transaction (1), and the transaction is finished after the {{scenario singular lowercase}} 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 singular lowercase}} is finished solution turn on auto commit it finishes (commits) every transaction immediately after the module execution is done open the {{scenario singular lowercase}} settings enable the auto commit checkbox click ok to confirm the settings