So lately, at our host, I’ve begun receiving a new error after running updates on the Pencilcase‘s Schema. I’ve had a fairly standard system of how I update the schema since we created version 3: I run an update file that has SQL code to add/aleter/drop tables, columns, indexes, etc.
I’ve successfully run these scripts hundreds of times across the dozens of domains that use the Pencilcase. However, 2 domains have started giving me the following error:
[Macromedia][SQLServer JDBC Driver][SQLServer]Could not complete cursor operation because the table schema changed after the cursor was declared.
The issue, as far as I can figure it out is that the ColdFusion JDBC server is somehow caching the old schema, and then after loading that, notices that the actual schema is no longer the same, and throws and error. I can’t replicate this error at all locally, or on our staging server to test with.
So far, the only solution has been to restart the entire Cold Fusion server, which forces a restart not just on the site in questino, but on all sites on the server. This, clearly, is not a tenable solution, as we can’t be restarting other people’s sites everytime I want to add a column to the DB.
So, oh wise and wonderful Internet, have any of you run into this problem? Have any other workarounds to a) prevent it happening from the first place (complicated, I know, as I’m not showing the code, but be assured, it’s pretty straightforward, basic SQL being run through Cold Fusion’s JDBC connector), or b) workaround the error in a way that doesn’t require restarting the entire Cold Fusion server?