SET SESSION sql_mode causes exception (.NET connector)

0

I am trying to execute the command 'SET SESSION sql_mode = 'PAD_CHAR_TO_FULL_LENGTH' using .NET connector (MySql.Data version 6.8.3.0). Everything works fine, except in the case that I call a stored procedure. In this case it causes the exception (0x80004005): Incorrect database name 'sakila

To restore the problem, here is the C# code and a short script that builds a test DB. Note that commenting the lines:

cmd.CommandText = "SET SESSION sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'";
cmd.ExecuteNonQuery();

eliminates the error.

It turns out that executing "SET GLOBAL sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'" in MySQL workbench causes the problem even if these lines are commented but it is not fired in MySQL workbench.

Here is the C# code:

using MySql.Data.MySqlClient;
using System.Data;

namespace PadCharDBTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new MySqlConnection("Server=localhost;Database=testquestion;Uid=root;Pwd=*****;"))
            {
                conn.Open();
                using(var cmd = new MySqlCommand(null, conn))
                {
                    //1.sets the sql mode
                    cmd.CommandText = "SET SESSION sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'";
                    cmd.ExecuteNonQuery();

                    // 2. simple select
                    cmd.CommandText = "select * from mytable";
                    using (var r = cmd.ExecuteReader()) { }                         

                    // 3. stored procedure                  
                    cmd.CommandText = "getTopNames";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new MySqlParameter("top", MySqlDbType.Int32, 3));
                    using (var reader = cmd.ExecuteReader()) { }
                }
            }
        }
    }
}

And here is an SQL script to build the test DB:

CREATE DATABASE IF NOT EXISTS testQuestion;
USE testQuestion;

DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(45) NOT NULL,
  PRIMARY KEY (`Id`)
);

INSERT INTO mytable (Name) 
VALUES
    ('AAA'),
    ('BBB'),
    ('CCC'),
    ('DDD'),
    ('EEE');


DROP PROCEDURE IF EXISTS getTopNames;

DELIMITER $$
CREATE PROCEDURE getTopNames (IN top INT)
BEGIN
    SELECT * FROM mytable LIMIT top;
END$$

DELIMITER ;

Thanks

c#
mysql
.net
asked on Stack Overflow Jan 15, 2020 by Ilan

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0