New to Kestra?
Use blueprints to kickstart your first workflows.
Detect a forgotten MySQL general query log, approve runtime OFF and prove instrumented file writes stop while slow-log settings stay intact.
SET GLOBAL general_log = ON is the quickest way to see every statement a MySQL server receives. It is usually turned on for a debugging session and then forgotten. From then on every query, including the text of statements with personal data, is written to disk on every request. The log grows without bound and costs I/O. Turning it off is a one-line change, but the wrong server, an intentional audit log or a change made by another administrator a second ago must be left alone. So the flow refuses by default and every guard below is load bearing. Each one was proven by its own run.
| Guard | What it stops |
|---|---|
expected_general_log: OFF policy, INTENTIONAL_ON to opt out |
turning off a log someone keeps on purpose |
protected_setters |
undoing a change made by a protected account such as observability |
Grace period on variables_info.SET_TIME |
ending a debugging session that is still going on |
Persisted ON, startup ON, TABLE or FILE,TABLE output spared |
fighting configuration that was set deliberately |
| Missing file instrumentation makes the server ineligible | a change the flow could not verify afterwards |
auto_remediate false, dry_run true, Pause |
three separate gates before one write |
| Recheck of server UUID, change time, setter and slow-log settings after approval | acting on a plan that went stale while the approval waited |
| Conditional write that sends an invalid value when the precondition fails | rewriting the setter and time metadata when nothing should change |
| Read-back plus a controlled query burst against the file counter | a SET that returned success being reported as a fix |
detect (io.kestra.plugin.jdbc.mysql.Query) reads general_log, log_output, the persisted and startup values, the setter and SET_TIME from performance_schema.variables_info, the slow-log settings and the byte counter of the wait/io/file/sql/query_log instrument. It classifies the server as eligible or names why not.report logs the setter, age, output destination and persisted state. It never reads statement contents. incident (If) continues only when the server is eligible and posts the finding to Slack (finding).change_enabled (If) requires auto_remediate: true and dry_run: false. Otherwise report_only logs and the run ends.approval (Pause) waits for a person. approved_change reads onResume.approved ?? false, so a resume with no answer is a refusal.recheck reads the server again. unchanged_plan (If) continues only if the server UUID, change time, setter, output destination and slow-log settings all match the plan. Otherwise stale_approval fails the run before any write.restore_runtime_off sets general_log with one conditional statement: OFF if the same change is still in place, otherwise the invalid value -1. MySQL rejects -1 with error 1231 and leaves the variable, its setter and its time unchanged.after_write reads the value back. controlled_query_burst (Loop) runs five harmless queries. verify reads the file counter again. outcome fails the run (unresolved) unless logging is OFF and the counter did not move. alert_failure posts failures to Slack.The event trigger forgotten_log (io.kestra.plugin.jdbc.mysql.Trigger) ships disabled: true, with dry_run: true and auto_remediate: false.
Database operators and SREs who turn on MySQL's general query log for debugging and want it reliably turned off afterwards.
A forgotten log has no alert of its own: the server keeps working while the file grows. Kestra makes it an event-driven check, holds the change at a Pause for a person, rechecks the server after the approval and proves the effect with a controlled workload, all in one execution. MySQL's own variable metadata and Performance Schema instrument provide the evidence.
Kestra 2.0.5, JDBC MySQL plugin 2.1.0, MySQL 8.0.46. Validate was clean: no constraints, warnings or deprecations. Every state was read with an independent mysql client, not from the flow's output. All seven required cases ran on the final revision 3:
| Case | Execution | Result |
|---|---|---|
| Healthy | 7gxsy51Fl9wSzIqGqAbVln |
SUCCESS. general_log OFF, nothing written |
| Dry run | 1iGhiT2g7fP8yVppD3aDxm |
SUCCESS. Eligible, still ON, file kept growing from 277,992 to 285,124 bytes, no write |
| Declined approval | hApUtoVkQwzACJPUBqgzs |
SUCCESS. Still ON, setter and set time unchanged |
| Remediation | 2R6ypFIzyx7Mqmj4Nj5YkE |
SUCCESS. ON to OFF. The file stayed at 308,814 bytes with the same SHA-256 through five queries and a separate client burst. Slow logging stayed ON and its file kept growing |
| Repeat | QPpLY4R1PqSLcsTMfTdx4 |
SUCCESS. Already OFF, nothing written |
| Refused write | 2oFKIHLWHrM83IlbXqKBWd |
FAILED as intended. A readonly user without SYSTEM_VARIABLES_ADMIN reached the write, MySQL refused it, logging stayed ON and the file grew from 310,976 to 339,133 bytes. Verification failed the run |
| Change during the Pause | 7mABDq1Mdyg8z03hHk3lxz |
FAILED as intended. The protected observability account changed the setting while the approval waited. recheck refused before any write and the new setter was kept |
Earlier revisions also proved the protected cases: intentional ON, protected setter, persisted ON, TABLE and FILE,TABLE output, disabled instrumentation, startup ON, below-grace changes and a two-second grace boundary. Five changes made during the Pause failed before any write. A fresh digest-pinned fixture repeated the seven required cases. An enabled copy of the event trigger produced no execution while healthy and one execution once logging was turned on. Slack was a local HTTP sink.
Revision 3 exists because a review found that the earlier conditional write, when its precondition failed, rewrote general_log to its current value and so changed SET_TIME and SET_USER. The invalid-value form leaves all three untouched, measured on the same server.
wait/io/file/sql/query_log instrument enabled. Tested on 8.0.46 only.SELECT on performance_schema, plus SYSTEM_VARIABLES_ADMIN to turn the log off. No SUPER.MYSQL_JDBC_URL, MYSQL_USERNAME, MYSQL_PASSWORD: the server and account.SLACK_WEBHOOK_URL: Slack incoming webhook for findings and failures.| Input | Default | Purpose |
|---|---|---|
expected_general_log |
OFF |
Policy. INTENTIONAL_ON spares a server that keeps the log on purpose |
protected_setters |
[observability] |
Accounts whose changes are never undone |
grace_seconds |
1800 |
Time the log may stay on before it counts as forgotten |
dry_run |
true |
Report only |
auto_remediate |
false |
Ask for approval to turn the log off |
SELECT on performance_schema and SYSTEM_VARIABLES_ADMIN.protected_setters.dry_run: false and auto_remediate: true, then approve the Pause.forgotten_log trigger to check continuously.Limits: the change is runtime only. It does not edit my.cnf or a persisted value. It does not shrink the existing file either. The conditional SET is not a lock, so another administrator can still change the variable afterwards. MySQL 8.4 was not tested.
This blueprint was created by zkasuran.
