Strange behaviour with stored procedures in docker environment

[jsbeanapps]

Software versions
MySqlConnector version: 2.1.8
Server type: AWS Aurora MySql 5.7
.NET version: 6.0

Describe the bug
It's rather elusive and hard to replicate, but we have experienced it consistently for some time to give a description.

We have an application that runs in a dockerized environment with multiple frontend servers hosting. When we upgrade the application, we send a new docker container to AWS, and it loads up new instances of the application that runs side-by-side with the "old" version of the application for a couple of minutes to avoid downtime on the system.

In the application a number of stored procedures are automatically generated, and they're used to update columns in the database. When a new version of the software is uploaded, all stored procedures are regenerated and updated in the database.

Now, when some of the "old" versions of the application calls a stored procedure to update a table in the database data are written in the "wrong" columns. We don't see any exceptions in the system, so the update/create goes through. When we analyse the difference between the "old" and "new" version of the stored procedure, the only difference is the order in which the parameters of the procedure are listed in the "create" statement - the parameters are the same, just in a different order.

We have come up with a temporary fix where we sort the parameters by their name in the create statement, and then we don't experience the issue, but that seems a bit like a hack.

We expect that the issue is that the order of the parameters are cached somewhere in the client, and then when the underlying stored procedure is updated, the cache isn't invalidated.

[bgrainger]

We expect that the issue is that the order of the parameters are cached somewhere in the client, and then when the underlying stored procedure is updated, the cache isn't invalidated.

This is correct: there is a stored procedure cache (per connection pool).

MySQL provides no indication to the client when a stored procedure is changed, so there's no way to automatically invalidate the cache. MySqlConnector also assumes that most (if not almost all) users don't change the definition of an existing stored procedure in the database while an application is running.

One solution would be for MySqlConnector to support an option to disable stored procedure caching. I suspect that this would be a fairly significant performance regression, though.

Another (and what I would strongly recommend) would be for you to preserve stored procedure definitions across "versions" of your database. A given named stored procedure shouldn't change its parameters' names, types, or order, nor the columns in its returned result set. If it does, it should be given a new name.

We have come up with a temporary fix where we sort the parameters by their name in the create statement, and then we don't experience the issue, but that seems a bit like a hack.

That seems like exactly the right solution to me. (Not dropping and recreating them in the first place would be even better.)

[bgrainger]

users don't change the definition of an existing stored procedure in the database while an application is running

Of course it's fine to alter a stored procedure to fix a bug in its "implementation"; to be clear, I was meaning changing the number/type of parameters, or changing the columns in the result set, etc. That should be handled by creating a new "v2" stored procedure.

[bgrainger]

As per #924 (comment), another workaround is to call MySqlConnection.ClearPool (or ClearAllPools) from code running in the old Docker container. This has the side-effect of clearing the procedure cache.

Perhaps you could create a special controller route that clears the pool, then invoke that route on all the old Docker containers after the DB is deployed.

[jsbeanapps]

@bgrainger - thank you very much for your quick reply. We have updated the application, so the stored procedures now include the application version in their name. That way any version of the application will only call procedures created by it. Old procedures are the purged when all "old" Docker containers have been stopped.