I have just recently purchased the licence for Factory SQL and I have seen an intermittent problem occur with an Insert query I am running on the hour to insert a new row in 7 tables storing data about 7 different machines. I am triggering the group holding the 7 insert statements when the current minute is zero. It works most of the time but on some occassions I discover that the new row has been entered but the values form the previous row are populating the new row. The columns are all of integer type bar the t_stamp column.I have set the default values for all the columns to zero and I am jsut inserting the value of the current timestamp using the insert statement. I have tried various setting of the trigger but it is happening maybe once or twice over 24 hours and does not always happen on the same table. I run an update statement on the same 7 tables at 1 minute past the hour and it has never failed. I would appreciate any information on what I could try to fix this issue.
It sounds like you have a race condition for the new values. Is the data to populate these rows OPC items, SQL queries or some other calculation? Are you using an Action Item to run an insert querie, or is the FactorySQL group inserting new rows?
Yeah sounds like a race condition to me. I usually trigger the plc at the 0 minute to store the data to an intermediate set of registers, then at teh 1 minute, trigger the factorysql group and populate the database. This is the safest route, even if you were not seeing these issues. Only way to be reliable
I am using an action item to trigger the group on the zero minute of each hour. Within the group I have 7 action items which are insert SQL queries which insert a new row in 7 different tables. There are no OPC items in this group. The tables that are updated with the insert statement are in 7 seperate groups which are populated using the Hour Meter for Blocked,Starved and Running states of the machine and the event meter for the count of parts produced for the hour. These are OPC items from the PLC. I have selected the Update last row option for these groups so the inserted row will increment with the new values of blocked, starved and parts produced. I got the idea to do this from one of video tutorials. I have designed the system so as not to store any values in the PLC by using the event and hour meter.
Over the past 12 hours it has worked ok except for one instance where it used the previous row values in the new row. I am at a loss as what to do to fix this issue. Any help greatly appreciated. I have attached some screen shots of my FSQL setup for these Groups
I added in a condition so that the groups containing the event and hour meters will not update at the same time that the insert is taking place. Hopefully this will solve the problem for me. many thanks for the feedback
So, you have 7 groups set to update the last row, and 1 group which inserts 7 new rows?
In that case, you have to realize that there is no guarantee things will happen in a particular order. You’re hoping that the insert group will insert one row, an update group will run, another row will be inserted, etc. The problem is that everything is independent. Furthermore, the 7 inserts are in one group, so they’ll get executed quickly in a row. When the updates come along, it is very likely that multiple will update the same row, and that some rows won’t get any data at all.
Probably the best thing you could do would be the following:
Create a new column in the table, for the “machine id”- an integer 1-7 that represents your rows (I’ll use “mach_id” in my example)
Change your insert action items to include this value. So, in your inserts, you’ll be creating rows with ids 1-7.
Change your update groups to use “custom” instead of last row, and make the WHERE condition a variation of this (I’ll pretend your table is called “data_table”- change it to be correct):
mach_id=1 order by data_table_ndx desc limit 1
Each update group will have a different mach_id value.
In this way, each update group will update the latest row for its machine (determined by sorting the automatically created index column and limiting the query to affecting 1 row), and the insert action items will create a new row for each machine. In this way, there is no ambiguity.
Please let me know if you have any questions about this!
Thanks for the Information. I have prevented the updates from running when the Inserts are taking place which has been ok for the past 24 hours. If this does not work I will do what you suggest and see if that will work.