0

I've inherited a rather old ASP.NET application that runs on our company intranet. The server it is on is extremely old and I am trying to get it working on a slightly newer server but I am having an issue with some MySQL Stored Procedures.

The error I am catching is "FUNCTION mann_production.spGetDraftID does not exist"

Here is the way I am creating the command object and calling ExecuteScalar()

Using connection As New MySqlConnection(ConfigurationManager.ConnectionStrings("MYSQL5_Products").ConnectionString)
    Using command As New MySqlCommand("spGetDraftID", connection)
        command.CommandType = Data.CommandType.StoredProcedure
        command.Parameters.Add(New MySqlParameter("?parModuleID", ModuleID))
        command.Parameters.Add(New MySqlParameter("?retNewModuleID", "0"))
        command.Parameters("?retNewModuleID").Direction = Data.ParameterDirection.ReturnValue
        connection.Open()
        Try
            command.ExecuteScalar()
        Catch e As Exception
            Return Nothing
        End Try
        Return command.Parameters("?retNewModuleID").Value
    End Using
End Using

I have the ConnectionString defined in Web.config.

<connectionStrings>
  <add name="MySQL5_Products" connectionString="Server=localhost; User ID=mannsql; Pwd=XXXXXX; pooling=true; Max Pool Size=300; Persist Security Info=true; Database=mann_production" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

The Stored Procedure in my MySQL database definitely exists. This is it.

DELIMITER $$

USE `mann_production`$$

DROP PROCEDURE IF EXISTS `spGetDraftID`$$

CREATE DEFINER=`mannsql`@`` PROCEDURE `spGetDraftID`(
IN parModuleID INT(11),
OUT retNewModuleID INT(11))
    COMMENT 'Either makes a draft of the given Module ID or returns the ID of'
BEGIN
/* Find draft if exists */
SET retNewModuleID = (SELECT Module.ModuleID
     FROM Module INNER JOIN Module AS Module_1 ON Module.ModuleName = Module_1.ModuleName
     WHERE ((Module.Rev = 'Draft') AND (Module_1.ModuleID = parModuleID))
     LIMIT 1);
IF (IFNULL(retNewModuleID, 0) = 0) THEN 
    /*Create a new Module Draft*/
    INSERT INTO Module ( Module.ModuleName, Module.Rev, Module.MinorRevision, Module.Current, Module.Description, Module.Notes) SELECT Module.ModuleName, 'Draft' AS Rev, 1 AS MinorRevision, 0 AS CURRENT, Module.Description, Module.Notes  FROM(Module) WHERE (((Module.ModuleID)=parModuleID)) LIMIT 1;
    /* Get the ID*/
    SET retNewModuleID = (SELECT LAST_INSERT_ID());
    /*Copy Operations*/
    INSERT INTO ModuleOperations ( ModuleOperations.OP, ModuleOperations.Description, ModuleOperations.ModuleID ) SELECT ModuleOperations.OP, ModuleOperations.Description, retNewModuleID AS Expr1 FROM(ModuleOperations) WHERE (((ModuleOperations.ModuleID)= parModuleID));
    /* Copy Parts */
    INSERT INTO ModuleBOM ( ModuleBOM.OP, ModuleBOM.Seq, ModuleBOM.ItemID, ModuleBOM.Qty, ModuleBOM.Um, ModuleBOM.Ref, ModuleBOM.Notes, ModuleBOM.ModuleID ) SELECT ModuleBOM.OP, ModuleBOM.Seq, ModuleBOM.ItemID, ModuleBOM.Qty, ModuleBOM.Um, ModuleBOM.Ref, ModuleBOM.Notes, retNewModuleID AS Expr1  FROM(ModuleBOM) WHERE (((ModuleBOM.ModuleID)=parModuleID));
    /* Add New Revision Record */
INSERT INTO ModuleReleaseNotes (ModuleReleaseNotes.OldModuleID, ModuleReleaseNotes.NewModuleID, ModuleReleaseNotes.Revision) VALUES (parModuleID, retNewModuleID, 'Draft');
END IF;
END$$

DELIMITER ;

Any ideas would be greatly appreciated.

Cheers.

MILO
  • 195
  • 1
  • 2
  • 11
  • Does the user "mannsql" have execute permission on that stored procedure? [MySQL Stored Procedure Permissions](https://stackoverflow.com/q/10089308/1115360). – Andrew Morton Jun 22 '20 at 09:08
  • @AndrewMorton I gave the user all global permissions, but when I go into the stored procedure using SQLyog the three permissions are unchecked. I ran the query GRANT EXECUTE ON PROCEDURE mann_production.spGetDraftID TO 'mannsql'@'%' and it runs successfully, however when I go back and look at the users permissions for that stored procedure the checkboxes are unchecked still. – MILO Jun 22 '20 at 10:38
  • Is there a chance there is more than one of the function? If you specify it like `New MySqlCommand("mann_production.spGetDraftID", connection)` does it make any difference? – Andrew Morton Jun 22 '20 at 12:02
  • @AndrewMorton No there is only one stored procedure with this name, and no it still throws the same error when prefixing it with the database name. After I run the command to grant execute permission to the user do I have to do anything else? Should I be restarting MySQL or saving the session or anything? Why is execute permission not ticked when viewing the users permissions in the SQLyog GUI? – MILO Jun 23 '20 at 00:46
  • Is this the only SP which has this problem? If you haven't tried any others, please make a really simple one which just does something like SELECT 123 and see if that works. Also, check which database SQLyog is connected to, just in case. – Andrew Morton Jun 23 '20 at 08:17
  • @AndrewMorton I created a simple test procedure with no parameters that just returned some data from the database and it worked fine. With the procedure in question I noticed that when I comment out the lines that deal with parameters I get error messages telling me I am missing parameters for the procedure so it is finding it however when I add the parameters it throws the error that it can't find the "function". – MILO Jun 24 '20 at 01:18
  • In the VB, it shows a string in `New MySqlParameter("?retNewModuleID", "0")`, but the parameter in the SQL is declared as `INT(11)`. Perhaps it is unhappy about that. – Andrew Morton Jun 24 '20 at 08:23
  • @AndrewMorton I wondered about that too but no I've passed it in as an Integer as well. I did figure out something though, I was curious about why it was saying "FUNCTION does not exist" when it was a stored procedure and not a function. I changed the parameter type from ReturnValue to Output and now it says the PROCEDURE does not exist. I created a new console application and only had this in it. Calling the new test procedure I created in MySQL works fine until I try to add parameters to it. I am so baffled by this. – MILO Jun 24 '20 at 09:34
  • I suggest taking out the output parameter, adjusting the SP to use a SELECT, and using the value returned from ExecuteScalar. We can find other straws to clutch at if that doesn't work. – Andrew Morton Jun 24 '20 at 16:19

0 Answers0