# Mysql ER\_PARSE\_ERROR until flow is redeployed or restarted

**URL:** <https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631>\
**Category:** General\
**Created:** [3 June 2021 07:03 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631 "2021-06-03T07:03:58Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![crabman](https://avatars.discourse-cdn.com/v4/letter/c/2acd7d/32.png) [@crabman](https://discourse.nodered.org/u/crabman)\
**Post date:** [3 June 2021 07:03 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/1 "2021-06-03T07:03:58Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/e/ae93094821c8280aa48fd42555d5fb3723c99965.png)

```auto
var dataIn = msg.payload;
var price = dataIn.bpi.USD.rate_float;
if (price != null){
    flow.set('price',price);
    var dataForInsertQuery = [];
    var ts = dataIn.time.updatedISO.slice(0, 19).replace('T', ' ');
    dataForInsertQuery.push(ts);
    dataForInsertQuery.push(price.toFixed(1));
    var SQLcmd = "INSERT INTO crypto.btc_tbl (ts,price) VALUES (?,?);";
    var SQLval = [dataForInsertQuery[0],parseFloat(dataForInsertQuery[1])];
    msg.topic = SQLcmd;
    msg.payload = SQLval;
    return msg;
}
return null;

```

\</\>

I receive this error about 15-20 minutes of same code working. Once it happens then on every transaction I get this error. Redploying the node without any changes in config or code starts working with following message output:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/5/35e2b4cd57b837b5529c4a4c90b8fb09d567381f.png)

Node: node-red-node-mysql  
Node Red Version v1.3.4  
Node.js version v10.24  
mysql Ver15.1 Distrib 10.3.27-MariaDB

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [3 June 2021 07:08 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/2 "2021-06-03T07:08:29Z")

</div>

> [@crabman](#):
>
> I receive this error about 15-20 minutes of same code working. Once it happens then on every transaction I get this error. Redploying the node without any changes in config or code starts working

I've seen this some time ago but failed to capture it in an issue (I thought I was doing something wrong). However it was very similar & certainly smells like a bug. I would suggest raising an issue on GitHub

Ps, I believe I worked around it by avoiding prepared statements & reverted to building a string query (not ideal but I needed a quick solution)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [3 June 2021 07:12 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/3 "2021-06-03T07:12:38Z")

</div>

How frequent are the inserts?

---

<div class="post-metadata">

**Author:** ![crabman](https://avatars.discourse-cdn.com/v4/letter/c/2acd7d/32.png) [@crabman](https://discourse.nodered.org/u/crabman)\
**Post date:** [3 June 2021 07:50 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/4 "2021-06-03T07:50:09Z")

</div>

> [@Steve-Mcl](#):
>
> ve I worked around it by avoiding prepared statements & reverted to building a string query (not ideal but I needed a quick solution

Can you explain it a bit more.... I'm a noob at this....

regarding raising issue on GitHub, should it be on node-red-node-mysql ?

---

<div class="post-metadata">

**Author:** ![crabman](https://avatars.discourse-cdn.com/v4/letter/c/2acd7d/32.png) [@crabman](https://discourse.nodered.org/u/crabman)\
**Post date:** [3 June 2021 07:51 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/5 "2021-06-03T07:51:48Z")

</div>

every 2-minute interval

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [3 June 2021 07:58 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/6 "2021-06-03T07:58:56Z")

</div>

ok, I just wanted to check if it was being flooded but every two minutes shouldn't be and issue.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [3 June 2021 08:44 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/7 "2021-06-03T08:44:54Z")

</div>

How are ts and price defined in your database schema ?

If you do raise an issue we are going to need a means of recreating the problem reliably - sorry to say but we can't sit around waiting for random failures for hours on end. Which version of the node-red-node-mysql are you using ? as there was a recent update to improve the pooling of connections.

---

<div class="post-metadata">

**Author:** ![crabman](https://avatars.discourse-cdn.com/v4/letter/c/2acd7d/32.png) [@crabman](https://discourse.nodered.org/u/crabman)\
**Post date:** [3 June 2021 09:19 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/8 "2021-06-03T09:19:49Z")

</div>

CREATE TABLE btc\_tbl(id INT NOT NULL AUTO\_INCREMENT, ts TIMESTAMP NOT NULL, price DECIMAL(10,2) NOT NULL, comment VARCHAR(45) NULL, PRIMARY KEY(id));

node-red-node-mysql v0.1.9

And yes you are right about the testing part. Sometimes the failure is not as mentioned in OP, it is random, can happen in 5-minutes, and sometimes works for hours before going into this problem.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [3 June 2021 10:00 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/9 "2021-06-03T10:00:15Z")

</div>

Is the mariaDB server on the same machine as node-red?

---

<div class="post-metadata">

**Author:** ![crabman](https://avatars.discourse-cdn.com/v4/letter/c/2acd7d/32.png) [@crabman](https://discourse.nodered.org/u/crabman)\
**Post date:** [3 June 2021 11:09 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/10 "2021-06-03T11:09:57Z")

</div>

Yes, node-red and mariadb are both running on a headless raspberry pi

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [3 June 2021 12:28 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/11 "2021-06-03T12:28:40Z")

</div>

If you stop the db server and restart it does it trigger the error?

---

<div class="post-metadata">

**Author:** ![crabman](https://avatars.discourse-cdn.com/v4/letter/c/2acd7d/32.png) [@crabman](https://discourse.nodered.org/u/crabman)\
**Post date:** [3 June 2021 12:54 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/12 "2021-06-03T12:54:16Z")

</div>

used following commands to stop and start  
sudo mysqladmin shutdown  
sudo service mysql start

And without redeploying flows it started working again.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [3 June 2021 13:00 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/13 "2021-06-03T13:00:27Z")

</div>

if you deliberately send a bad INSERT (e.g. send a string of "hello" for the decimal price field) followed by a good INSERT - does that cause the issue?

if you deliberately send a bad INSERT (e.g. send no parameters in payload or payload an empty array) followed by a good INSERT - does that cause the issue?

---

<div class="post-metadata">

**Author:** ![crabman](https://avatars.discourse-cdn.com/v4/letter/c/2acd7d/32.png) [@crabman](https://discourse.nodered.org/u/crabman)\
**Post date:** [3 June 2021 13:18 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/14 "2021-06-03T13:18:30Z")

</div>

1. 

> [@Steve-Mcl](#):
>
> if you deliberately send a bad INSERT (e.g. send a string of "hello" for the decimal price field) followed by a good INSERT - does that cause the issue?

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/6/d6a4dec1b9ea9e3e3d88174eb9a05b20f0433a45.png)

1. 

> [@Steve-Mcl](#):
>
> if you deliberately send a bad INSERT (e.g. send no parameters in payload or payload an empty array) followed by a good INSERT - does that cause the issue?

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/d/9d32b9e2e0aad2bfcede9b18c9e1c63ce0650a12.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [3 June 2021 13:21 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/15 "2021-06-03T13:21:17Z")

</div>

hmmmm.

what if you do 20 (or more) of those bad ones in a row (then try a good insert) - perhaps it will exhaust the pool?

---

<div class="post-metadata">

**Author:** ![crabman](https://avatars.discourse-cdn.com/v4/letter/c/2acd7d/32.png) [@crabman](https://discourse.nodered.org/u/crabman)\
**Post date:** [3 June 2021 13:22 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/16 "2021-06-03T13:22:10Z")

</div>

> [@Steve-Mcl](#):
>
> hose bad ones in a row (then try a good insert) - perhaps it will exhaust the

Will try tomorrow and report.

---

<div class="post-metadata">

**Author:** ![crabman](https://avatars.discourse-cdn.com/v4/letter/c/2acd7d/32.png) [@crabman](https://discourse.nodered.org/u/crabman)\
**Post date:** [5 June 2021 10:54 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/17 "2021-06-05T10:54:07Z")

</div>

> [@Steve-Mcl](#):
>
> f those bad ones in a row (then try a good insert) - perhaps it will exhaust the pool?

I have observed that the insert query isn't the problem. Alone it keeps working, however this error occurs after data is pulled from the DB via another MySQL node (same version). Data comes out fine and is displayed on dashboard, and this nodes stays green displays OK status. But after that all insert queries keep failing until the flow is redeplyed/restarted.  
Following is code of Read function:

```auto

var formData=msg.payload;
var startDate = formData.startDate.slice(0, 19).replace('T', ' ');
var endDate = formData.endDate.slice(0, 19).replace('T', ' ');
var dataForRetrieveQuery = [];
var SQLcmd = "";
var timeMA = formData.timeMA;
if (formData.enableMA == true) {
    if (timeMA<2){
        timeMA=2;
    }
    SQLcmd = "SELECT ts,price, AVG(price) OVER (ORDER BY id ROWS BETWEEN " + timeMA + " PRECEDING AND CURRENT ROW) AS average FROM crypto.btc_tbl WHERE ts between " + "'" + startDate + "'" + " AND " + "'" + endDate + "'" + " LIMIT 1000;";
} else {
    SQLcmd = "SELECT ts, price FROM crypto.btc_tbl WHERE ts between " + "'" + startDate + "'" + " AND " + "'" + endDate + "'" + " LIMIT 1000;";
}
msg.topic=SQLcmd;    

return msg;

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [5 June 2021 11:00 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/18 "2021-06-05T11:00:54Z")

</div>

Good find.

In the interest of getting this resolved, would it be possible for you to build a demo flow that does the following...

1. Inject that does a CREATE TABLE → MYSQL node
2. inject that does INSERT → MYSQL node
3. inject that does a SELECT --\> MYSQL node (a separate one)
4. inject that does DROP TABLE → MYSQL node (for clean up)

If you can, I will also test it & if it fails for me too, then we can raise an issue on the repo with a repeatable flow that demonstrates the issue.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [5 June 2021 13:00 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/19 "2021-06-05T13:00:55Z")

</div>

Are you using the same MySQL database config node in the two mysql nodes?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [4 August 2021 13:01 UTC](https://discourse.nodered.org/t/mysql-er-parse-error-until-flow-is-redeployed-or-restarted/46631/20 "2021-08-04T13:01:18Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.