Brian S Brian S - 1 year ago 250
SQL Question

ER_PARSE_ERROR during insert transaction with Node-Red

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),
(6, @schedule);


This query works perfectly if I copy and paste it into phpMyAdmin's query window. However, when I try to fire it through my flow, I get an error:

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

I've been unable to make very much progress on this error. The closest thing to being helpful that I've found was advice to make sure I had backticks on all of the columns and table names, which I've done to no avail.

Answer Source

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.