MySQL caching
I added a parameter to a stored procedure in mySQL database last night. I reached out to him, confused the setting and decided to remove that setting again for testing. A few more minutes after re-creating the procedure without a parameter, my command object was still complaining about a missing parameter. Is this a mySQL, MySQL / Connector, ADO or Enterprise bug and what can I do about it?
source to share
By default, MySQL caches queries in stored procedures. See if the request cache is enabled:
SHOW VARIABLES LIKE 'query_cache%'
Stored procedure calls are not cached by MySQL, but if query_cache_type
enabled it will affect caching of queries issued from the procedure. Perhaps forcing MySQL to return the same results in a couple of minutes. Try clearing the cache, or better yet, resetting the query cache to remove all queries if your updated procedure keeps returning the previous result set:
RESET QUERY CACHE
This should eliminate any caching by the MySQL server.
The next time this happens, you can perform the procedure manually using another tool that does not use MySQL / Connector or Enterprise Library. This will tell you if the old result set will be cached either by MySQL or by drivers and application blocks in your application.
Edit: please read the comments below, by desertwebdesign. After recreating the sproc, remove the connection. The connection pool can keep the connection open, so it is best to kill the MySQL connection. Most query browsers have a tab to kill connections in MySQL if you are logged in with process / super privileges.
SHOW FULL PROCESSLIST
KILL CONNECTION thread_id
source to share