I've got some Node-Red flows serving as a REST interface to a web app I'm working on. However, I've run into a wall with an insert transaction on MySQL.
The query being fed into the mysql node is:
INSERT INTO `light_schedule` (`name`, `light_pwm`,
`accent_pwm_red`, `accent_pwm_green`, `accent_pwm_blue`, `strobe`,
`start_date`, `start_time`, `end_date`, `end_time`, `repeat_type`,
`repeat_every`, `repeat_sunday`, `repeat_monday`, `repeat_tuesday`,
`repeat_wednesday`, `repeat_thursday`, `repeat_friday`, `repeat_saturday`,
`repeat_by`, `end_after_num`, `end_on`, `is_template`)
VALUES ('example', NULL, NULL, NULL, NULL, 1, '2016-4-1', NULL, NULL, NULL, 2, 1, 1, 1, 1,
1, 1, 1, 1, NULL, NULL, NULL, 0);
SET @schedule = LAST_INSERT_ID();
INSERT INTO `strobe_colors` (`schedule_id`, `order_index`,
`duration`, `accent_pwm_red`, `accent_pwm_green`, `accent_pwm_blue`,
VALUES (@schedule, 0, 4600, NULL, NULL, NULL, 1),
(@schedule, 1, 4100, 150, 200, 163, 0);
INSERT INTO `lights_schedules` (`light_id`, `schedule_id`)
VALUES (5, @schedule),
Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO
`light_schedule` (`name`, `light_pwm`, `accent_pwm_red`, `accent_pw' at line 2
It appears that this is a limitation of the mysql node for node-red. I tried breaking my query down to the simplest parts I could and building up from there to see where things break. After some testing and a ton of trash getting inserted into my DB, it appears the node type is not built to handle multiple queries, and that includes not being able to handle transactions.
My next solution was to attempt to create a stored procedure to run the transaction, but testing with a trivial
SELECT procedure seems to indicate this node has issues with procedures, as well. No matter what permissions I set up on the DB or what user created the procedure, the mysql node would claim it didn't have execute permissions.
The solution I have settled on was creating a chain of mysql calls, passing relevant data forward to function nodes via
flow.set. This does mean I can't take advantage of transactions, unfortunately, but for this project they're "nice to have" more than a critical database feature.