Re: Connector 8.0 - Stored Procedure Error in MySqlDataReader

[Zachary Graham]

This ended up being related to the following setting for the connection string (from the documentation):

Use Procedure Bodies , UseProcedureBodies , procedure bodies
Default: true

When set to true, the default value, MySQL Connector/Net expects the body of the procedure to be viewable. This enables it to determine the parameter types and order. Set the option to false when the user connecting to the database does not have the SELECT privileges for the mysql.proc (stored procedures) table or cannot view INFORMATION_SCHEMA.ROUTINES, and then explicitly set the types of all the parameters before the call and add the parameters to the command in the same order as they appear in the procedure definition.

This option was deprecated in MySQL Connector/Net 6.3.7 and removed in MySQL Connector/Net 6.10.4; use the Check Parameters option instead.


So you can either set CheckParameters=false in your connection string, or give the account (account used in your connection string) global SELECT permissions, since the Connector/NET relies on the extension "SHOW CREATE PROCEDURE mydb.myprocedure" to do its parameter discovery. From the documentation for SHOW CREATE PROCEDURE:

~~This statement is a MySQL extension. It returns the exact string that can be used to re-create the named stored procedure. A similar statement, SHOW CREATE FUNCTION, displays information about stored functions (see Section 13.7.6.8, “SHOW CREATE FUNCTION Syntax”).

To use either statement, you must have the global SELECT privilege.~~

I think the exception could be handled better in the Connector/NET when this case occurs, since CheckParameters=true is the default connection string option. Would be nice if it told you that the account you are using in your connection string has insufficient permissions to do parameter discovery.