Bug #48007 Connector/Net throws exceptions when calling stored procedure
Submitted: 12 Oct 2009 18:30 Modified: 11 May 2011 16:34
Reporter: Dave Adams Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.2.2, 6.3.5 OS:Microsoft Windows (XP SP3)
Assigned to: Julio Casal CPU Architecture:Any
Triage: D3 (Medium)

[12 Oct 2009 18:30] Dave Adams
Description:
After upgrading from Connector/Net 6.0.4 to 6.1.2, every time I execute a stored procedure for the first time, the following exceptions are thrown:

A first chance exception of type 'System.Data.SqlTypes.SqlNullValueException' occurred in MySql.Data.dll
A first chance exception of type 'System.InvalidOperationException' occurred in MySql.Data.dll
A first chance exception of type 'System.InvalidOperationException' occurred in MySql.Data.dll

It's always the same pattern:  SqlNullValueException followed by 2x invalidOperationException.  I reverted to 6.0.4 and verified the exceptions are not thrown in that build.

How to repeat:
Use this database schema:

DROP DATABASE IF EXISTS foo;
CREATE DATABASE foo;
USE foo;
DELIMITER $$
CREATE PROCEDURE bar(Param1 varchar(25), Param2 varchar(25))
BEGIN
SELECT Param1, Param2;
END$$
DELIMITER ;

Create a C# Windows Forms application that calls the stored procedure:

        private void Form1_Load(object sender, EventArgs e)
        {
            string strCon = "server=localhost;user id=XXX;password=YYY;persist security info=True;database=foo;use procedure bodies=False";
            MySqlConnection con = new MySqlConnection(strCon);
            MySqlCommand cmd = new MySqlCommand("bar", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Param1", "Hello");
            cmd.Parameters.AddWithValue("@Param2", "World");
            try
            {
                con.Open();
                MySqlDataReader rd = cmd.ExecuteReader();
                rd.Read();
                this.Text = rd["Param1"] + " " + rd["Param2"];
            }
            catch (MySqlException exc)
            {
                MessageBox.Show(this, exc.Message);
            }
            finally
            {
                con.Close();
            }

Suggested fix:
[13 Oct 2009 18:02] Dave Adams
I cannot reproduce this on another machine.  I'll let you know if I can find a reproducible scenario.
[15 Oct 2009 7:37] Tonci Grgin
Hi Dave and thanks for your report.

Truly, this sounds like an error in driver. Did you check MD5? Please try downloading the driver again and retest.

Waiting on reproducible test case.
[15 Oct 2009 7:49] Tonci Grgin
Btw, your test case works perfectly on my box.
[16 Nov 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[8 Jan 2010 6:47] Dave Adams
I have found the problem, and reproduced it in 6.2.2.  It appears that "use procedure bodies=False" is being ignored in the connection string.  If the MySQL user does not have select privileges on mysql.proc, I get the exceptions, and if I grant the select privilege on mysql.proc to the user, the exceptions go away.  Here is a trace showing that the Connector is trying to retrieve stored procedure metadata even when "use procedure bodies=False" is specified in the connection string.  I have surrounded the line of interest with asterisks:

A first chance exception of type 'System.Data.SqlTypes.SqlNullValueException' occurred in MySql.Data.dll

Additional information: Data is Null. This method or property cannot be called on Null values.

A first chance exception of type 'System.InvalidOperationException' occurred in MySql.Data.dll

Additional information: Unable to retrieve stored procedure metadata for routine 'Foo'.  Either grant  SELECT privilege to mysql.proc for this user or use "use procedure bodies=false" with  your connection string.

A first chance exception of type 'System.InvalidOperationException' occurred in MySql.Data.dll

Additional information: Unable to retrieve stored routine parameters.  Either grant access to the routine or add the 'Use Procedure Bodies=false' option to your connection string.

****XXXXXXXX.vshost.exe Information: 0 : Retrieving procedure metadata for `XXXX`.`Foo` from server.****
[8 Jan 2010 6:51] Dave Adams
Note that I got the more detailed trace info by checking break on Common Language Runtime exceptions under Debug | Exceptions in VS2008.

I don't have the ability to change the status of this case from "can't repeat" to "open", so hopefully someone with sufficient privileges will see this and reopen the case.  Thanks!
[11 Jan 2010 20:47] Dave Adams
Attempting to close and reopen this case since I can't change the status from "Can't repeat"
[11 Jan 2010 20:47] Dave Adams
Reopening...
[12 Jan 2010 6:15] Tonci Grgin
Ok Dave, will check.
[14 Jan 2010 8:42] Tonci Grgin
Verified as described with one remark. The procedure used *must* be created with root account privileges, otherwise things will work. This is described in detail in http://dev.mysql.com/doc/refman/5.1/en/show-create-procedure.html.

Explanation:
  o Create basic user with *no* privileges over mysql database
  o Use database and a procedure with parameters created by super-user in database basic user has full rights over
  o Add connection in server explorer with basic user to "his" database specifying "no access to procedure body". SP will show up in server explorer as it's collected via I__S query.
  o Double-click on procedure and the error is thrown due to c/NET issuing "SHOW CREATE PROCEDURE ..." which goes into mysql.proc and returns NULL in "Create Procedure".

So, when an user is not the definer of SP nor it has privileges over mysql.proc table the value displayed for the Create Procedure or Create Function field will be NULL after calling SHOW CREATE PROCEDURE. This is where c/NET throws exception instead, imo, just reporting /* */ as Create Procedure value and notifying user of the problem.

This behavior could be intentional (as error does exist) but, imo, it could be more descriptive.
[22 Jun 2010 22:35] Vladislav Vaintroub
Could not reproduce the problem using latest 6.x from bzr.
If permissions are missing , SHOW CREATE PROCEDURE  would indeed  return NULL, and SqlNullValueException is thrown and rethrown as InvalidOperationException. But exceptionis not propagated to user, it is eaten near the end of ProcedureCache.GetProcData()
[14 Nov 2010 5:41] Dave Adams
Closing and reopening...
[14 Nov 2010 5:42] Dave Adams
This is still an issue in 6.5.3.  Also, I created the stored procedures with a non-root account, and the problem persists.
[14 Nov 2010 5:43] Dave Adams
Sorry, make that 6.3.5
[11 May 2011 16:34] Julio Casal
The exception itself never reached the application developer as it was caught inside Connector/Net. However the exception should not happen if the server is version 5.5.3 or greater as the new parameters table of Information Schema allows for correctly getting parameter metadata without inspecting stored procedure bodies.

I have introduced a patch to fix this issue in versions 6.1.6 and 6.2.5, but keep in mind it will only work if server is 5.5.3 or greater. The patch is also included in versions 6.3.7 and 6.4.1 but in these cases new Connector/Net behavior resolves the issue even if the server version is less than 5.5.3.