MySQL Bugs: #100306: Command.Prepare() send wrong statement to server

Bug #100306	Command.Prepare() send wrong statement to server
Submitted:	23 Jul 2020 14:04	Modified:	9 Oct 2020 8:39
Reporter:	Francesco Serafini	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:

Preparing command stored procedure, send statement (?,?,?, ....) without the name of database.storedprocedure

How to repeat:

Create connection.
Create command.
Set CommandText to database.storedprocedure name
Set CommandType to StoredProcedure
Add parameters to command
Call command.Prepare()

Suggested fix:

Connector NET 8.0.20 doesn't have this bug.

Thank you for the bug report. Please provide repeatable test case, SQL script and the code file for test. Thanks.

DELIMITER $$

CREATE DATABASE test_prepare$$

USE test_prepare$$

CREATE PROCEDURE sp_prepare()
BEGIN
  SELECT 'Ok';
END$$

DELIMITER ;

MySqlConnection connection = new MySqlConnection("server=localhost;User Id=****;password=****;CheckParameters=false;SSL Mode=None;Ignore Prepare=false");

connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "test_prepare.sp_prepare";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Prepare(); // or command.ExecuteNonQuery();
// throw
// You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1

Connector NET useless.

How can version 8.0.21 be marked stable?

Thank you for providing requested details.
Observed this with C/NET 8.0.21.

regards,
Umesh

using MySql.Data.MySqlClient;
using System;

namespace Bug100306
{
    class Program
    {
        static void Main(string[] args)
        {
            using var connection = new MySqlConnection("server=localhost;port=3306;user id=root;password=mysql123!;CheckParameters=false;SSL Mode=None;IgnorePrepare=false");
            connection.Open();

            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "test_prepare.sp_prepare";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Prepare(); // or command.ExecuteNonQuery();
            command.ExecuteNonQuery(); // this would throw a FormatException if cmd.Prepare() isn't called

        }
    }
}

- VS 2019, Connector/NET 8.0.21 on Win10

Unhandled exception. MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.PrepareStatement(String sql, MySqlField[]& parameters)
   at MySql.Data.MySqlClient.Driver.PrepareStatement(String sql, MySqlField[]& parameters)
   at MySql.Data.MySqlClient.PreparableStatement.Prepare()
   at MySql.Data.MySqlClient.MySqlCommand.Prepare(Int32 cursorPageSize)
   at MySql.Data.MySqlClient.MySqlCommand.Prepare()
   at Bug100306.Program.Main(String[] args) in C:\Work\MySQLNet\source\Bug100306\Bug100306\Program.cs:line 16

C:\Work\MySQLNet\source\Bug100306\Bug100306\bin\Debug\netcoreapp3.1\Bug100306.exe (process 19472) exited with code -532462766.
Press any key to close this window . . .

- VS 2019, Connector/NET 8.0.20 on Win10

C:\Work\MySQLNet\source\Bug100306\Bug100306\bin\Debug\netcoreapp3.1\Bug100306.exe (process 17868) exited with code 0.
Press any key to close this window . . .

Posted by developer:
 
Hi Francesco, 

Thanks for the bug report, we will address it ASAP. Meanwhile you can put inverted quotes around the procedure name as a workaround, e.g. command.CommandText = "`test_prepare`.`sp_prepare`";

Regards,
Daniel

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.22 release, and here's the proposed changelog entry from the documentation team:

Procedure names were malformed before being sent to the server when the
database name was not specified in the connection string.

Thank you for the bug report.

Bug #100391 marked as duplicate of this one

Suggested fix:

Thanks for the bug report, we will address it ASAP. Meanwhile you can put inverted quotes around the procedure name as a workaround, e.g. command.CommandText = "`test_prepare`.`sp_prepare`";

DOESN'T WORK.