Bug #35938 Problem using MySqlCommand and Procedures
Submitted: 9 Apr 2008 14:38 Modified: 23 Apr 2008 13:23
Reporter: Dayvison Pellegrina Email Updates:
Status: Unsupported Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:5.0.8.1 OS:Microsoft Windows
Assigned to:

[9 Apr 2008 14:38] Dayvison Pellegrina
Description:
Hello everybody! 

I am having a problem when using procedures that access views using MySqlCommand. When I use this process  there's no error messages, but the procedure does not return values to my GridView. 

I believe that problem  can be in version 5.0.8.1,  because the problem does not occur in version 5.2.1 . 

Important note: 
When I don't use the directive “use procedure bodies = false”  the error below  occurs :

 

***************************************************************

 

 

Server Error in '/exemplos' Application.

--------------------------------------------------------------------------------

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

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

Source Error: 

Line 33:                 

Line 34:                 

Line 35:                 using (MySqlDataReader myDR = myCmd.ExecuteReader())

Line 36:                 {

Line 37:                     GridView1.DataSource = myDR;

Source File: c:\Dayvison\projetos\exemplos\procedure_mysql.aspx    Line: 35 

Stack Trace: 

[SqlNullValueException: Data is Null. This method or property cannot be called on Null values.]

   MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean checkNull) +386

   MySql.Data.MySqlClient.MySqlDataReader.GetString(Int32 column) +38

   MySql.Data.MySqlClient.ISSchemaProvider.GetParametersFromShowCreate(DataTable parametersTable, String[] restrictions, DataTable routines) +354

 

[InvalidOperationException: Unable to retrieve stored procedure metadata.  Either grant  SELECTprivilege to mysql.proc for this user or use "use procedure bodies=false" with  your connection string.]

   MySql.Data.MySqlClient.ISSchemaProvider.GetParametersFromShowCreate(DataTable parametersTable, String[] restrictions, DataTable routines) +413

   MySql.Data.MySqlClient.ISSchemaProvider.GetProcedureParameters(String[] restrictions, DataTable routines) +502

   MySql.Data.MySqlClient.ProcedureCache.GetProcData(MySqlConnection connection, String spName) +227

   MySql.Data.MySqlClient.ProcedureCache.AddNew(MySqlConnection connection, String spName) +35

   MySql.Data.MySqlClient.ProcedureCache.GetProcedure(MySqlConnection conn, String spName) +102

   MySql.Data.MySqlClient.StoredProcedure.GetParameters(String procName) +98

   MySql.Data.MySqlClient.StoredProcedure.Resolve() +148

   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +518

   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() +6

   ASP.procedure_mysql_aspx.Button1_Click(Object sender, EventArgs e) in c:\Dayvison\projetos\exemplos\procedure_mysql.aspx:35

   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105

   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107

   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7

   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11

   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33

   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

--------------------------------------------------------------------------------

Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 

 

 

***************************************************************

 

 

What is the purpose of  “use procedure bodies” diretive? 

Thank you for your attention .
 
Best regards,  

How to repeat:
Web: Windows 2003 Server IIS6 2.0/3.5 .Net Framework 
DataBase: Linux RedHat 5 - MySQL 5.0.41

Example Table:

*********************************************

CREATE TABLE `tbProcedure` (

  `id` int(11) NOT NULL auto_increment,

  `texto1` varchar(100) collate latin1_general_ci NOT NULL,

  `texto2` text collate latin1_general_ci NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

*********************************************

 

Example  View:

*********************************************

CREATE VIEW `vw_tbProcedure` AS select `tbProcedure`.`id` AS `id`,`tbProcedure`.`texto1` AS `texto1`,`tbProcedure`.`texto2` AS `texto2` from `tbProcedure` where (1 = 1);

 

*********************************************

 

Example  Proc:

*********************************************

CREATE PROCEDURE `SP_Login`(IN login varchar(100), IN senha text)

BEGIN

  select * from vw_tbProcedure where texto1 = login and texto2 = senha;

END

*********************************************

 

 

Example  ASPX file :

*********************************************

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="MySql.Data.MySqlClient" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<script runat="server">

 

protected void  Button1_Click(object sender, EventArgs e)

{

        using (MySqlConnection myConn = new MySqlConnection())

        {

            //myConn.ConnectionString = "Data Source=****;User Id=****;Password=****;Database=*****;pooling=false;";

            myConn.ConnectionString = "Data Source=****;User Id=****;Password=****;Database=*****;pooling=false;use procedure bodies=false";

            myConn.Open();

            

            

            using(MySqlCommand myCmd = new MySqlCommand())

            {

                myCmd.Connection = myConn;

                

                myCmd.CommandType = CommandType.StoredProcedure;

                

                myCmd.CommandText = procTextBox.Text;

                

                myCmd.Parameters.AddWithValue(parTextBox1.Text, valTextBox1.Text);

                

                myCmd.Parameters.AddWithValue(parTextBox2.Text, valTextBox2.Text);

                

                

                using (MySqlDataReader myDR = myCmd.ExecuteReader())

                {

                    GridView1.DataSource = myDR;

                    GridView1.DataBind();

                }

            }

        }

 

}

</script>

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <span style="font-size: 10pt; font-family: Verdana"><strong>Insira os dados abaixo:</strong><br />

            <br />

            Procedure: </span>

        <asp:TextBox ID="procTextBox" runat="server">SP_Login</asp:TextBox><br />

        <span style="font-size: 10pt; font-family: Verdana">Parâmetro 01:

            <asp:TextBox ID="parTextBox1" runat="server">@Usuario</asp:TextBox>

            Valor:

            <asp:TextBox ID="valTextBox1" runat="server">teste</asp:TextBox><br />

            Parâmetro 02:

            <asp:TextBox ID="parTextBox2"

            runat="server">@Senha</asp:TextBox>

            Valor:

            <asp:TextBox ID="valTextBox2" runat="server">teste</asp:TextBox><br />

            <br />

            <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Enviar" /><br />

            <br />

            <br />

            <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#DEDFDE"

                BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">

                <FooterStyle BackColor="#CCCC99" />

                <RowStyle BackColor="#F7F7DE" />

                <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />

                <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />

                <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />

                <AlternatingRowStyle BackColor="White" />

            </asp:GridView>

        </span>

    

    </div>

    </form>

</body>

</html>

********************************************* 

thanks again !
[10 Apr 2008 6:38] Tonci Grgin
Hi Dayvison and thanks for your report. Can you make absolutely sure the user name you connect with is allowed to execute that SP? Can you please try with root account too and inform me of result.
[15 Apr 2008 17:32] Dayvison Pellegrina
Thanks for  your  feedback, 

I am sure that the permissions are appropriate.  In some tests  I've defined the following administrator permissions in my user: 

Select_priv             Insert_priv             Update_priv             Delete_priv             Create_priv             Drop_priv             Reload_priv             Shutdown_priv             Process_priv             File_priv             Grant_priv             References_priv             Index_priv             Alter_priv             Show_db_priv             Super_priv             Create_tmp_table_priv             Lock_tables_priv             Execute_priv             Repl_slave_priv             Repl_client_priv             Create_view_priv             Show_view_priv             Create_routine_priv             Alter_routine_priv             Create_user_priv        

 Then I've reloaded the application. With the "procedure bodies = false"  directive the application did not return anything. Without it,  the error bellow ocurred. 

**********************************************************************
Server Error in '/ASPNET' Application.
Parameter '?login' not found in the collection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: Parameter '?login' not found in the collection.
Source Error:
Line 52:                 
Line 53:                 
Line 54:                 using (MySqlDataReader myDR = myCmd.ExecuteReader())
Line 55:                 {
Line 56:                     GridView1.DataSource = myDR;
Source File: e:\home\dayvison\Web\ASPNET\mysql_procedure.aspx    Line: 54
Stack Trace:
[ArgumentException: Parameter '?login' not found in the collection.]
   MySql.Data.MySqlClient.MySqlParameterCollection.GetParameter(String parameterName) +275
   MySql.Data.MySqlClient.MySqlParameterCollection.get_Item(String name) +8
   MySql.Data.MySqlClient.StoredProcedure.Resolve() +530
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +510
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() +6
   ASP.mysql_procedure_aspx.Button1_Click(Object sender, EventArgs e) in e:\home\dayvison\Web\ASPNET\mysql_procedure.aspx:54
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433

**********************************************************************

What is the "procedure bodies = false" directive finality?
Has any further hint that can help me? 

Thanks.
[23 Apr 2008 11:56] Tonci Grgin
Hi Dayvison. I do owe you an answer to "What is the "procedure bodies = false" directive finality?".

The only place where connector can fetch SP info is mysql.proc table. Above option tells c/NET your account has privilege to read from mysql.proc table. You should check this from command line client like this:
mysql -uUser_ID -p -hYour_host mysql

mysql> select * from proc;

This problem of yours sure seems privilege related but I need to test it properly before I'm sure.
[23 Apr 2008 13:23] Tonci Grgin
Dayvison, according to changelog this should be fixed in c/NET 5.1.3 and later. Please test and inform me of result.

This is a duplicate of Bug#29098.
[10 May 2016 6:33] Disha Vaghela
I had also the same issue and i checked for privileges and found that my user had no privileges at all. I assign privileges and worked fine. Thank you so much! I spent whole day to resolve this.