Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Incorrect number of arguments for PROCEDURE when Database is not set on Connection String #282

Closed
pabloordonez opened this issue Jun 26, 2017 · 6 comments
Labels

Comments

@pabloordonez
Copy link

pabloordonez commented Jun 26, 2017

Steps To Reproduce

  1. Open a connection to a MySql Server without the Database parameter in int.
  2. Create a database.
  3. Create a table.
  4. Create a stored procedure.
  5. Execute the stored procedure.

Observed
When calling stored procedures, if the database is not provided in the connection string, the execution will fail saying Incorrect number of arguments for PROCEDURE <procedure name>; expected N, got M message. After testing a lot, we saw the problem disappears when the database name is provided.

Tested against
MySql Server 5.7.17
MySql Server 5.7.18

MySqlConnector 0.21.0 0.20.2 0.20.1 0.20.0

Notes
Just in case, we ran the test using the oracle connector and both test passed successfully. This is not priority for us, but we found the error writing stored procedure tests.

MSTest Code

using System;
using System.Data;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using MySql.Data.MySqlClient;

namespace MySqlStoredProcedureError
{
    [TestClass]
    public class StoredProcedureTest
    {
        [TestMethod]
        public void ShouldExecuteWithDatabaseOnConnectionString()
        {        
            using (var connection = new MySqlConnection("Server=localhost;Database=test;User=test;Password=test1234;Connection Timeout=3600;Allow User Variables=True;POOLING=true"))
            {
                this.CreateDatabase(connection);
                this.CreateTable(connection);
                this.CreateStoredProcedure(connection);
                this.ExecuteProcedure(connection);
                this.DropDatabase(connection);
            }
        }

        [TestMethod]
        public void ShouldExecuteWithoutDatabaseOnConnectionString()
        {
            using (var connection = new MySqlConnection("Server=localhost;User=test;Password=test1234;Connection Timeout=3600;Allow User Variables=True;POOLING=true"))
            {
                this.CreateDatabase(connection);
                this.CreateTable(connection);
                this.CreateStoredProcedure(connection);
                this.ExecuteProcedure(connection);
                this.DropDatabase(connection);
            }
        }

        private void CreateDatabase(MySqlConnection connection)
        {
            using (var command = this.GetCommand(connection, this.GetDatabaseCreation()))
            {
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
        }

        private void CreateTable(MySqlConnection connection)
        {
            using (var command = this.GetCommand(connection, this.GetTableCreation()))
            {
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
        }

        private void CreateStoredProcedure(MySqlConnection connection)
        {
            using (var command = this.GetCommand(connection, this.GetStoredProcedureCreation()))
            {
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
        }

        private void ExecuteProcedure(MySqlConnection connection)
        {
            using (var command = this.GetCommand(connection, "SearchTestTable"))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Name", "John");

                command.ExecuteNonQuery();
            }
        }

        private void DropDatabase(MySqlConnection connection)
        {
            using (var command = this.GetCommand(connection, this.GetDropDatabase()))
            {
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
        }

        private MySqlCommand GetCommand(MySqlConnection connection, string commandText)
        {
            var command = connection.CreateCommand() as MySqlCommand;

            if (connection.State != ConnectionState.Open)
                connection.Open();

            command.CommandText = commandText;
            return command;
        }

        private string GetDatabaseCreation()
        {
            return @"
                CREATE DATABASE IF NOT EXISTS `test`;
                USE `test`;
            ";
        }

        private string GetDropDatabase()
        {
            return @"
                DROP DATABASE IF EXISTS `test`;
            ";
        }

        private string GetTableCreation()
        {
            return @"
                CREATE TABLE IF NOT EXISTS `test`.`testtable`
                (
                    `Id`            INT             NOT NULL AUTO_INCREMENT,
                    `Name`          NVARCHAR(200)   NOT NULL,
                    `IsActive`      BOOL            NOT NULL,
                    `Amount`        DECIMAL(20,9)   NOT NULL,
                    `CreatedDate`   DATETIME        NOT NULL,

                    CONSTRAINT `PK_TestTableTable` PRIMARY KEY (`Id` ASC),
	                CONSTRAINT `UX_TestTableTable_Name` UNIQUE (`Name`)

                )ENGINE=INNODB;
            ";
        }

        private string GetStoredProcedureCreation()
        {
            return @"
                DROP PROCEDURE IF EXISTS `SearchTestTable`;
                CREATE PROCEDURE `SearchTestTable`
                (
                    `Name`        VARCHAR(200)
                )
                BEGIN
                    SELECT *
                    FROM `test`.`testtable` t
                    WHERE t.`Name` = `Name`; 
                END
            ";
        }
    }
}
@pabloordonez pabloordonez changed the title Stored Procedure parameter missing when Database is not set on Connection String Incorrect number of arguments for PROCEDURE when Database is not set on Connection String Jun 26, 2017
@bgrainger
Copy link
Member

bgrainger commented Jun 26, 2017

Thanks for the clear and thorough bug report!

I'm guessing this probably happens because MySqlConnection.Database just reflects the connector's local state and isn't updated for any USE test; statement that has been executed, and GetCachedProcedure depends on this.

We probably need to add tests for whether this behaviour of .Database is different than Connector/NET (and fix it if so); and also need to make stored procedures work correctly even if the current database isn't being tracked properly.

@bgrainger bgrainger added the bug label Jun 26, 2017
@pabloordonez
Copy link
Author

pabloordonez commented Jun 26, 2017

Excellent! Let me know if we can help with something!
Changing the database produce adverse results on different connectors (SQL Server, PostgreSQL, etc).

In real case scenarios you must certainly will have the database parameter in your connection string. So this shouldn't be a important behavior.

For testing purposes, the CREATE and DROP works either way with the database being specified.

Thanks for the fast response as always!

@bgrainger
Copy link
Member

bgrainger commented Jan 2, 2018

Implemented two fixes for this:

  1. The CachedProcedure code will now ignore failure to find the parameters in information_schema.parameters, which avoids the exception in this bug report.
  2. The enhancement in Implement CLIENT_SESSION_TRACK #323 will track USE db; statements (in MySQL Server 5.7 or later) which allows the parameters to be found (and cached).

@pabloordonez
Copy link
Author

pabloordonez commented Jan 2, 2018

Excellent! I'll be updating the nuget reference to our ORM soon!

@bgrainger
Copy link
Member

bgrainger commented Jan 2, 2018

Fixed in 0.34.0.

(And I added a link to Paradigm ORM to https://mysql-net.github.io/MySqlConnector/overview/use-with-orms/)

@pabloordonez
Copy link
Author

pabloordonez commented Jan 2, 2018

Amazing! And thank you for including the link! We also added the connector link in our documentation. In our unit and performance tests this connector ended on top on every aspect, it was a no-brainer.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Development

No branches or pull requests

2 participants