close_transactions() silently overwrites audit_trail.stamp

2026-05-12T15:22:50Z

After closing an old fiscal year, I noticed `0_audit_trail.stamp` had been rewritten to "now" for every row whose `gl_seq` was updated by the close routine. Rows the close didn't touch kept their original stamps. This destroys the historical creation timestamp for every GL transaction in the closing year, and breaks any report that relies on `audit_trail.stamp`.

Two innocent pieces interacting:

1. `0_audit_trail.stamp` is defined as `TIMESTAMP ... ON UPDATE CURRENT_TIMESTAMP` (FA default, used to track edit time).
2. close_transactions() in `includes/db/audit_trail_db.inc` runs `UPDATE 0_audit_trail SET gl_seq=$seq WHERE id=...

The UPDATE only sets `gl_seq`, but MySQL auto-refreshes `stamp` as a side effect of the `ON UPDATE` clause. The `if ($row['gl_seq'] != $seq)` guard is why only some rows get hit — exactly the ones being re-sequenced.

I tried fixing it with the following change

$sql2 = "UPDATE ".TB_PREF."audit_trail SET"
. " gl_seq=$seq"
. " WHERE id=".$row['id'];

to:

$sql2 = "UPDATE ".TB_PREF."audit_trail SET"
. " gl_seq=$seq,"
. " stamp=stamp" // preserve original timestamp
. " WHERE id=".$row['id'];

Explicitly listing `stamp` in the SET clause suppresses the `ON UPDATE CURRENT_TIMESTAMP` auto-refresh in MySQL. No schema change needed, and normal `add_audit_trail()` edits are unaffected since they already set `stamp` explicitly.

Has anyone else hit this? Kindly share your experience.

Thanks

FrontAccounting forum — close_transactions() silently overwrites audit_trail.stamp

close_transactions() silently overwrites audit_trail.stamp