Phantom cfquery column

[ghanna1]

Here's a unique one.  My only SQL statement on this page is:

<cfquery datasource="#session.mix#" >
Update Status SET
AuthToken = '#Results.Auth_Token#',
AuthTokenExpire = #AuthExpire#
Where FID = '#key.FID#'
</cfquery>

When I execute it I get:

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'NorthStatus'. 

The variable NorthStatus doesn't appear anywhere in the code for this page. I've RDP to the server and checked the code there too.  It doesn't appear in the SQL table. It's a column that was long ago deleted from the table. If I update the SQL table and add a column NorthStatus the page runs fine and nothing ever changes in the column NorthStatus.  Yes, it's a workaround but it makes zero sense. I've rebooted both the SQL server and CF server just for kicks.

Anybody got a clue?

Gary

[BKBK]

You say, "It's a column that was long ago deleted from the table.". So, perhaps, something might have been cached somewhere.

For further kicks, revert to the original situation. Go to the caching page of the ColdFusion Administrator, press the button Clear Template Cache Now and then the button Clear Query Cache Now. Next, delete all files from wwwroot\WEB-INF\cfclasses.

[ghanna1 · Accepted solution]

That's great information but it didn't solve the problem. As a test, I moved the page to our disaster recovery server and it had the same issue. It's a different SQL server also BUT the tables are synced. As a last resort I deleted the tables in both the primary and DR SQL servers and recreated it. Poof...Like magic the code works. So something was corrupt in the SQL table and CF reported it back as a column missing.

[EddieLotter]

It might have been a trigger causing the error. Dropping the table will have dropped all triggers as well. Just a thought.

[BKBK]

Indeed EddieLotter, a trigger probably misfired.

[Charlie Arehart]

I realize you've solved this with a delete of the table, but in case somehow someone else ever has this and finds this, it would have been interesting if things would have "worked" merely by modifying the update statement in any way, like switching the order of the cols listed to be updated.

If that worked, it would confirm that there was indeed a cached statement somewhere, either in the dB or perhaps in the dB driver. (Changing the sql statement causes any cached variant to be ignored and a new one to be created. And such a cache would not be cleared by any of the buttons in the CF Admin, as it seems whatever it was did survive restarts of cf and the dB server. But if doing this solved things, maybe it wouldn't matter to dig further.) 

Again, a moot point now, but just sharing it for future consideration. 

[ghanna1]

Charlie, The original page had 3 potential update statements. 1 of the 3 would trigger based on conditions. When I moved it to DR I stripped it down to just one SQL Update as a test. The original code also had a column named auth token which I changed to AuthToken (removed the space). So yes it's possible that changing the column name was hung up somewhere. I am also using symmetricDS to sync the two SQL servers. It creates a bunch of triggers behind the scenes so that's a possibility also.

The table also only had 1 row. It was storing tokens for f5 cloud load balancing so deleting it was no big deal. It would have been great to keep troubleshooting to find the real cause but banging my head against the desk for 3 hours was enough 🙂 

Gary

[Charlie Arehart]

Totally understandable, and glad you were able to prevent further head-banging. 🙂 Again, I just shared the info in case it may help others (or perhaps you), if somehow something like this ever happens again.