MySQL Caching

Question

Last night I added a parameter to a stored procedure in a mySQL database. I accessed it, messed up the parameter, and decided to remove the parameter again, for testing. Still minutes after re-creating the procedure without the parameter, my command object was still complaining about a missing parameter. Is this mySQL, MySQL/Connector, ADO, or Enterprise library's fault, and what can I do about it?

Answer 1

By default MySQL caches queries in your stored procedures. See if Query Cache is enabled:

SHOW VARIABLES LIKE 'query_cache%'

Stored procedure calls are not cached by MySQL, but if query_cache_type is ON this will affect caching of queries issued from within the procedure. Possibly causing MySQL to return the same results for a couple of minutes. Try flushing the cache, or better yet, reset the query cache to remove all queries if your updated procedure keeps returning the previous result set:

RESET QUERY CACHE

That should eliminate any caching by MySQL server.

Next time this happens you could execute the procedure by hand through another tool that does not use MySQL/Connector or Enterprise Library. That will tell you if the old result set is cached by either MySQL or the drivers and application blocks in your application.