Highlighted

Phantom cfquery column

Participant ,
Sep 15, 2020

Copy link to clipboard

Copied

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

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.

Views

89

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Phantom cfquery column

Participant ,
Sep 15, 2020

Copy link to clipboard

Copied

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

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.

Views

90

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 15, 2020 0
Adobe Community Professional ,
Sep 15, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 15, 2020 0
Participant ,
Sep 15, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 15, 2020 0
Advocate ,
Sep 15, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 15, 2020 1
Adobe Community Professional ,
Sep 16, 2020

Copy link to clipboard

Copied

Indeed EddieLotter, a trigger probably misfired.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 16, 2020 0
Adobe Community Professional ,
Sep 16, 2020

Copy link to clipboard

Copied

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. 

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 16, 2020 0
Participant ,
Sep 16, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 16, 2020 1
Adobe Community Professional ,
Sep 16, 2020

Copy link to clipboard

Copied

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.

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 16, 2020 0