SQL Request

[Mapantz]

Not sure if its possible with the custom SQL uploads or not, but can a Retention String option be added to the Custom upload - seconds interval? Like the Realtime option?

[mcrossley]

I can take a look, but the other way to tackle this is to create a post insert trigger on your table that deletes all rows older than the latest row - your retention time.

[cliftonweather]

Not all MySQL hosts allow the use of triggers, unless you pay for a dedicated server, mine doesn't (which is annoying). But there are other methods, if you terminate your SQL in the Custom Upload box with a semicolon you can then run another SQL command, something like:-
DELETE FROM YourTableName WHERE LogDateTime < DATE_SUB(NOW(), INTERVAL 7 DAY); to keep a week
DAY can be replaced with SECOND, MINUTRE, HOUR etc.

Or you can create a procedure with the DELETE SQL in it and use:-
CALL YourProcedureName(); after your SQL.

Hope this helps

[mcrossley]

Good idea about using the custom update.

Re-Triggers, if you do have them, then I actually put the trigger on the full log table - that way the "housekeeping" only gets done every 5-10 minutes (depending on you log interval) rather than every few seconds.

[Mapantz]

if you terminate your SQL in the Custom Upload box with a semicolon you can then run another SQL command, something like:-
DELETE FROM YourTableName WHERE LogDateTime < DATE_SUB(NOW(), INTERVAL 7 DAY); to keep a week
DAY can be replaced with SECOND, MINUTRE, HOUR etc.


Hope this helps

Sweet! I didn't know you could add multiple commands, not in CMX anyway. That's very helpful and it's working. You learn something new every day!