Transaction groups delete records older than

Ignition
, , , ,
47

Well, I use postgreSQL, so the syntax is a bit different. :wink:

48

I’m thinking you really need to contact support. This may be related to your original issue.

49

thanks for the help!

50

sorry for the distraction!!!

51

2021-01-28 at 8.55 AM
2021-01-28 at 8.55 AM1626×752 186 KB

A few thoughts:

  1. Make sure that you’re using system.db.runUpdateQuery() if you’re just performing a DELETE, runQuery() will not allow data manipulation statements.

  2. The syntax below seems to work fine in my MySQL 8.0 test bench for removing records older than a minute:

    DELETE FROM statustest WHERE t_stamp <= DATE_SUB(NOW(), INTERVAL 1 MINUTE)

    One of the things I typically do when I’m developing statements like this is to use SELECT * in place of DELETE until I’m darn sure of what I’m including in my statement scope. Far too easy to accidentally delete your entire table otherwise. :slight_smile:

  3. Make sure that your scheduled rate for a gateway event script is set correctly. I was worried about the screenshot you posted earlier:
    2021-01-28 at 8.43 AM

Hope this helps!

5 Likes
52

very good solution! thanks, it is working!!!

53

Any thoughts on the original issue, Kevin?

54

I’ve not reviewed the backing code yet, but from my own user-context view, it appears that the scheduling for the cleanup is based on the frequency selection. When I picked 1-minute threshold, I didn’t see the deletion get “scheduled” any faster than every minute. So it is not checking every trigger-period (e.g. 1 second) to cleanse old records. This is definitely regarded as more of a clean-up measure and not intended to “guarantee” that only new-ish records exist. That is my first pass based on previous experience as a user and my observations in the test setup I spooled up this morning (hooray Docker :slight_smile: ). If there is interest in a more formal definition, I can review the backing code at some point…

1 Like
55

I used this code because standart function isn`t work!

56

Hopefully the workaround will get you going in the meantime. As @JordanCClark mentioned earlier, reaching out to support regarding the original issue might be a good idea… My initial test with a Transaction Group similar to what I’ve seen here seems to work as expected.

1 Like
57

For clarification, the linked post from @c.bertulli is correct; the schedule for the delete task depends on the rate of the group(s) you have pointed at a particular DB table:

    protected long getPeriodForAge(long maxAge) {
        if (maxAge < 3600000) {
            //Less than 1 hour
            return 60000L; //Run every minute
        } else if (maxAge >= 3600000 && maxAge < 43200000) {
            //between 1 and 12 hours
            return 900000L; //Run every 15min
        } else {
            return 3600000L; //Run every hour
        }
    }
1 Like