Niggling Cold Fusion issue resolved

I have been experiencing a vexing database issue that I finally resolved this week.

I have a database with a field titled "reviewed_date". It keep periodically resetting itself to "10/01/2006".

At first (and for quite a while), I blamed it on a system problem, but the problem was actually mine!

Earlier I posted on the "DATEDIFF" function. When I started using it, some rows did not have the "reviewed_date" field populated and so the function failed. So I created a utility piece of CF code to populate all NULL fields to "10/01/2006".

I moved this code to production and never removed it.

Meanwhile I weekly took a backup of this particular table.

This past Friday I noticed that the table was intact with no issues. Monday when I came in all the "reviewed_date" fields had been reset to "10/01/2006".

Wednesday I scanned for all occurances of "10/01/2006" in my *.CFM code and noticed that this utilize code was still in production. I had used it once! I purged it from production and pondered the situatution. I hypothesized that something had been running that code in an automated way periodically. My manager scoffed at my hypothesis.

To test my hypothesis, I created a sample table, that mirrored my table that was perioically corrupted. Then I created code - aptly named hypothesis.cfm - that would update that table and reset the "reviewed_date" column to "10/01/2006". I set the "reviewed_date" values to my birthday "08/19/1949". The hypothesis.cfm code also included a CFMAIL section that would send me an email when it executed.

My hypothesis was that a regular indexing of our corporate Intranet was causing that code to execute.

Today at 10:02 am that code executed and sent me an email. The column "reviewed_date" had been set to "10/01/2006".

This seemingly small detail had vexed me for months. And I thank God that I got it resolved!

There's a programming lesson in this story!