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:
Node: node-red-node-mysql
Node Red Version v1.3.4
Node.js version v10.24
mysql Ver15.1 Distrib 10.3.27-MariaDB
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)
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.
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.
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?
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:
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;
