"Deploy failed: No response from server" when inserting into MySQL

General
21

The same flow I have posted which is giving the deploy failed message:

[{"id":"2867501b.43456","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"560199de.cdcaf8","type":"mysql","z":"2867501b.43456","mydb":"2a44bcb3.ad6994","name":"","x":550,"y":180,"wires":[["6d7e9abd.a213b4"]]},{"id":"6d7e9abd.a213b4","type":"debug","z":"2867501b.43456","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":750,"y":180,"wires":},{"id":"845ca325.9ebb2","type":"inject","z":"2867501b.43456","name":"","topic":"","payload":"{"message":"Foo"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":180,"wires":[["7303633e.cac12c"]]},{"id":"7303633e.cac12c","type":"function","z":"2867501b.43456","name":"","func":"msg.topic = "INSERT INTO aress_test (message) VALUES (?);"\nmsg.payload=[msg.payload.message];\nreturn msg;","outputs":1,"noerr":0,"x":310,"y":180,"wires":[["560199de.cdcaf8"]]},{"id":"2a44bcb3.ad6994","type":"MySQLdatabase","z":"","host":"localhost","port":"3306","db":"AressSensorData","tz":""}]

If we use any other query except the INSERT in the Function node in the given flow, the deployment will be successful.

22

In the screenshot for your template node you seem to be setting the query into msg.payload... it needs to be in msg.topic as per the info sidebar

23

There is a syntax error in the json for that flow (the second one you posted), which may explain why it will not deploy. Try to import it and it shows the error. I will have to leave it to others to comment on how that may have come about and what to do about it.

24

@Sagar Your flow does not import

25

@Sagar well I was able to hack and import your flow (removed the function code and added back after doing the import). I created the DB and the table and ran the flow and it works fine fo me.

This tells me that it is something to do with your environment or permissions although if I use a ser without insert permission, I get an expected

Error: ER_TABLEACCESS_DENIED_ERROR: 
INSERT command denied to user 'paul'@'localhost' 
for table 'aress_test'"

error from the node

26

@zenofmud Thank you for the efforts.

As far as permissions are concerned, I have granted all the privileges for the user that I am using.

27

@Colin The first flow which I have posted is the same as the second one. The syntax error might have occur because I have exported the flow which had "Deployment Failed Error"

28

@Sagar I replicated your issue on my test server, created a mysql user with all the privileges and tried to insert into database using a function node, even I am facing the error 'Deploy Failed. No Response from server'.

When I tried the 'REPLACE INTO' query, instead of 'INSERT INTO' it was successfully deployed and the data was inserted into the database. Can't figure out what is the issue with the 'INSERT' query.

Please check the flow below:

[{"id":"ae2c51af.ede1a","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"772a94ed.a1d10c","type":"mysql","z":"ae2c51af.ede1a","mydb":"9bd6189c.4d77e8","name":"","x":640,"y":240,"wires":[[]]},{"id":"fb2d008f.830f7","type":"inject","z":"ae2c51af.ede1a","name":"","topic":"","payload":"{\"temp\":\"10\",\"humidity\":\"21\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":210,"y":240,"wires":[["abe9cbe0.c760a8"]]},{"id":"abe9cbe0.c760a8","type":"function","z":"ae2c51af.ede1a","name":"Replace","func":"msg.topic = \"REPLACE INTO sensor_data(temp,humidity) VALUES ('\"+msg.payload.temp+\"','\"+msg.payload.humidity+\"')\";\nreturn msg;","outputs":1,"noerr":0,"x":400,"y":240,"wires":[["772a94ed.a1d10c"]]},{"id":"9bd6189c.4d77e8","type":"MySQLdatabase","z":"","host":"localhost","port":"3306","db":"sensor","tz":""}]

29

@Esa Could you get it to the point where the deploy fails, and then export the nodes and post here please. I want to see if it is the same syntax problem that is seen on @sagar's second flow.

30

@Colin , Please find the flow below for which the deployment is failing:

[{"id":"ae2c51af.ede1a","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"772a94ed.a1d10c","type":"mysql","z":"ae2c51af.ede1a","mydb":"9bd6189c.4d77e8","name":"","x":640,"y":240,"wires":[["5a797f0.1deca8"]]},{"id":"fb2d008f.830f7","type":"inject","z":"ae2c51af.ede1a","name":"","topic":"","payload":"{\"temp\":10,\"humidity\":21}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":210,"y":240,"wires":[["abe9cbe0.c760a8"]]},{"id":"abe9cbe0.c760a8","type":"function","z":"ae2c51af.ede1a","name":"Insert","func":"msg.topic = \"INSERT INTO sensor_data(temp,humidity) VALUES ('\"+msg.payload.temp+\"','\"+msg.payload.humidity+\"');\"\nreturn msg;","outputs":1,"noerr":0,"x":390,"y":240,"wires":[["772a94ed.a1d10c"]]},{"id":"5a797f0.1deca8","type":"debug","z":"ae2c51af.ede1a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","x":860,"y":240,"wires":[]},{"id":"9bd6189c.4d77e8","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"sensor","tz":"GMT +5.30"}]

Sagar's second flow has syntax error but his first flow is without syntax error.

31

@Colin:
Apologize for the syntax error in 2nd flow. I copied the first flow only to format it in a right way. There must have been some error while copying. But you can check with my first flow which is the same as second one.

32

@Esa: Exactly.. This is the same issue I am facing. Thanks for posting.

33

@Esa - I imported your flow and the insert works fine for me.

Both of you, what verion of mySQL are you using? (from a terminal enter mysql -v). I'm running
Server version: 5.6.19 MySQL Community Server (GPL)

34

@sagar, no need to apologise, I suspect that this may be key to the problem. Make the deploy fail and then export again and post it here again. It could be that the error in the exported code is exactly the reason it cannot deploy, as it is trying to deploy an invalid flow.

35

Could the mysql version explain a deploy failure?

36

I'm not sure, but something is different about their setup and mine. The deploy might causing a new connection to the DB which might cause a problem(??)

@Sagar @Esa what type of deploy have you got setup? i.e.
Screen Shot 2019-12-07 at 6.25.24 AM

37

@zenofmud, MySQL version is
mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper

Node-Red version:
Node-RED version: v1.0.3
Deployment Type:
Full
I even tried using Modified Flows, but no success.

38

Hi, I have the same problem. After insert an query, the node-red stopped.
I have tried a 32bit window system and raspberry pi. Same problem...
Here is the video, pls take a look

I played with mysql last year and everything was fine. I wonder if there is any update that crash the node-red? Thx

39

This thread is over two years old, please open a new thread and provide some more information
what version of NR and node.js?
what device is running NR?
what device is running mysql?
what version of mysql?
what is showing in the NR log (do a copy and paste)
a copy of your flow.

I'll close this thread now.