Symptoms of Stored Procedure Caching
You update your sproc & code accordingly, but the page gives you an error asking for old, deleted parameters. The error looks like this :
System.ArgumentException: Parameter ‘deleted_parameter_foo_bar’ not found in the collection. at MySql.Data.MySqlClient.MySqlParameterCollection …
What is MySQL Stored Procedure Caching?
Normally, stored procedure caching is a good thing. It saves the MySQL server some time and work by storing the execution plan for future use instead of recreating it all over again every time. However, as in above, it can also be a great pain.The key thing to know about it is:
Every single connection to the MySQL server maintains it’s own stored procedure cache. (The Art of SQL)
What this means for you is that refreshing your browser, re-executing your CREATE PROCEDURE command, etc, all won’t work.
How to Check if Caching is On
Execute this MySQL query:
SHOW VARIABLES LIKE 'query_cache%'
How To Fix It?
Here’s what will and won’t fix the problem:
- Restarting your browser
- Using a different browser
- Restarting your (not-the-server) computer
- HttpResponse.RemoveOutputCacheItem(“./folder/page.aspx”); (in ASP.NET code-behind)
- Delete & re-upload the problematic files
- Further updating, deleting, re-creating the stored procedure on the server
- Deleting & re-uploading the entire ASP.NET application (or web.config or global.asax). It forces it to re-compile on the server, clearing the connection’s cache.
- Executing RESET QUERY CACHE on the MySQL server to clear the cache
- Restarting the MySQL server or MySQL server computer
As you can see, the first option, to simply re-upload web.config, global.asax, or the entire web app, is the simplest, safest, and most viable solution for developers with limited MySQL server permissions.