mySQL: How to get the execution status of a query

#1

Does anyone know how to obtain the execution status of command such as insert from the mysql node?
I need to set a trigger upon when the data has been inserted successfully.

0 Likes

#2

Does it not pass on a message when it has completed?

0 Likes

#3

I think it does, but I know were to find it.
I've not come across detailed documentation explaining object models.

0 Likes

#4

I think there must be a typo in there somewhere. I don't understand what you mean by object models.

0 Likes

#5

Take the mysql node. It should have properties, methods, and events.
I'm looking for the property that contains the status of the connection, and/or execution.
I'm finding Node js a little bit difficult to use in the the current environment, It's not a clear as the Microsoft Visual Studio.

It appears the only way I'm going to find things out is but diving into the source code to see what it going on, something I don't really want to-do since it's rather time consuming.

Is there any detailed technical documents on the nodes, one can reference ? (mysql in particular)

0 Likes

#6

Put a debug node after the mysql node set to show the complete message object, you will then see exactly what the node returns.

0 Likes

#7

Which node are you talking about? This is a node-red forum so we tend you assume you are talking about a node such as node-red-node-mysql. Node red nodes do not have properties, methods and events in the way I think you mean. In use you pass the node a message and it passes a message on and in addition may generate a status or errors. That's it.

0 Likes

#8

I'm wondering if it may be under message.

I'm current recording millisecond transition rates in a PLC, the PLC is caching the data and I need to ensure the data has been successfully written to the mysql server before requesting the next record from the PLC.

The connector is polling data at 500ms, if the mysql query takes longer to execute, the linked PLC write back node fire regardless of the mysql node state being busy.

I'm not sure how to handle this, If the sql query response time deteriorates over time as the database grows, it may cause problems such as missing samples.

image

0 Likes

#9

If you want to prevent another poll before the write has completed then I think node-red-contrib-semaphore may be what you are looking for. Acquire the semaphore before polling and release it after the db write completes. Then the next poll will be delayed if necessary until it can acquire the semaphore. In fact by setting the Capacity in the semaphore config node to something like 5 then it will allow up to that many to be buffered up before delaying the next poll, that would smooth out variations in the time required.
If you do eventually find that you cannot write the data fast enough then I think you could speed it up dramatically by buffering a number of writes up and doing one block write.
As a matter of interest why is it critical not to lose any writes?

0 Likes

#10

Just noticed that sentence. You are wondering if what may be under message?

0 Likes

#11

Somwrthing to consider....since you said it takes longer for mySQL tha the polling...

If it takes twice as long to write out to mySQL then the readings take you will end up with a backup. it you stop taking readings after an hour, you will have an hour's worth of mySQL inserts to do.

If you need the exact time a reading was made make sure it is part of the PLC data and that you are not going to use the time you insert the data into the database.

If you ran this for a day, you would have another days worth of mySQL to do.
Something to be aware of.

0 Likes

#12

This is a pilot project I'm busy with. Our company has a mine winder division, and a safety requirement is to record and decode all the bell messages between the winder driver, under ground, bank, and sometime cage.

The bell have pulse width that must be exact, and recording the transition for auditing purposes is essential.
I have managed to put a wait on the mysql connector, but returning null message to the connector when a new record is not detected, and to request a new record when the affected rows of the query is 1.

It appears to be working well, and i'm capturing the records perfectly at the moment.

1 Like