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

[pabloordonez]

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
            ";
        }
    }
}

[bgrainger]

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.

[pabloordonez]

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]

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]

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

[bgrainger]

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]

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.