Exception in MySqlCommand.Prepare

[george-polevoy]

trafficstars

Software versions MySqlConnector version: 2.0.0 Server type (MySQL) and version: 5.7.32 .NET version: 5.0

Describe the bug Exception in MySqlCommand.Prepare()

Exception

MySqlConnector.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 6
   at MySqlConnector.Core.ServerSession.PrepareAsync(IMySqlCommand command, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 219
   at MySqlConnector.MySqlCommand.Prepare() in /_/src/MySqlConnector/MySqlCommand.cs:line 112

Code sample

var cmdText = "CREATE FUNCTION echo(\nname VARCHAR(63)\n)\nRETURNS VARCHAR(63)\nBEGIN\nRETURN name;\nEND;";
			using var c = new MySqlConnection(MySqlTestsConfig.ConnectionString);
			c.Open();
			var cmd = c.CreateCommand();
			cmd.CommandText = cmdText;
			cmd.Prepare(); // THIS STATEMENT FAILS
			cmd.ExecuteNonQuery();

[prestonj]

I believe this issue also exists in 1.3.0+ versions of the library. Our team went from using 1.2.1 to a 1.3.x release and started encountering issues due to the change in default for the Ignore Prepare flag.

[bgrainger]

It's likely been a bug for a long time, but only exposed when 1.3.0 made Ignore Prepare = false the default, so that MySqlCommand.Prepare actually does something.

It doesn't really make much sense to Prepare a CREATE FUNCTION or CREATE PROCEDURE statement (as they're likely to be executed only once), so the suggested workaround for now would be to avoid calling .Prepare() on those statements.