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.

Advertisements

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.

MySQL Stored Procedures

MySQL stored procedures are available, but not frequently used. It’s more common to see SQL in the code, especially with PHP.

MySQL has an odd stored procedure format and it looks like this :

DELIMITER $$

DROP PROCEDURE IF EXISTS `myProcedure` $$
CREATE DEFINER=`myDatabase`@`%` PROCEDURE `myProcedure`
(
/* optional parameters here */
IN variable1 TYPE,
OUT variable2 TYPE
)
BEGIN

/* body here */

  DECLARE myVariable TEXT;

  IF (boolean expression) THEN
    /* stuff */
  ELSE
    /* stuff */
  END IF;

  SELECT MyTextColumn INTO myVariable FROM myTable WHERE ... ;

END $$

DELIMITER ;

MySQL Gotcha’s

It’s important to note that all DECLARE’s inside of the procedure body must be located above/before any code. Unlike MSSQL, strict ‘;’ semi-colon end-line delimiters are enforced. You cannot specify input/output parameter default values like you can in MSSQL.

The MySQL query editor will only execute the first line of any query, so if you wish to do anything multiline, you will need to use a script or procedure/function. Highlighting text and pressing TAB will not tabulate the line but replace with tab spacing instead. In the query table editor, you may not move columns around (but you can remake the table and/or drop/recreate columns).

Select statement assignment is possible, and do-able in a quick and easy way without cursors (see code).

There are now many differences between MS SQL and MySQL datatypes, so be sure to do your reading. For example, a MySQL VARCHAR column can hold up to 255 characters, and anything more needs to be stored in a TEXT datatype. Integer(#) does not specify the number of digits (something else). There is no BOOLEAN/BIT in MySQL. There are two time/date types, DATESTAMP, and TIMESTAMP.

In a table, you may not specify a default time/date for DATESTAMP like Now(), but you may specify up to a single TIMESTAMP column to have a default of CURRENT_TIMESTAMP.

Perhaps not quite as much of a Gotcha as the others, your Syntax Errors are now akin to LaTeX (Badness on line … ), “You have an error in your MySQL syntax, check the manual that corresponds to your MySQL version for the right syntax to use near’ <100 characters of problematic line>'”