MySQL / ASP.NET Stored Procedure Caching

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:

Won’t Fix:

  • 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

Will Fix:

  • 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.

MySQL/ASP.NET Parameter ‘foo’ not found in the collection.

The Error

Parameter ‘foobar’ not found in the collection. at MySql.Data.MySqlClient.MySqlParameterCollection.GetParameterFlexible

Does not mean what it says. It’s somehow related to a documented bug, and fortunately does have a fix. It can occur when arguments in ASP.NET to a MySQL stored procedure are specified in a differing order.

The Solution

In ASP.NET when working with MySQL stored procedures, you MUST specify the parameters in the exact same order and upper/lower casing they appear in your stored procedure.

If this doesn’t fix it, you may have a problem with MySQL Stored Procedure Caching (how to fix). Symptoms here are that MySQL complains about wanting/missing parameters that you just deleted/added/updated being missing or too many.