Stored procedures not found with lower_case_table_names

[halonn]

Hi all

We've just started project to support MariaDB database in addition to MsSql. In legacy Ms code we had parameter with ParameterDirection.ReturnValue in every sp in our DAL engine and it does not caused any issues, but with MariaDB this causes weird problem:

• when database name in in UPPERCASE - MariaDB complains about missing 'FUNCTION sp_name'
• when database name in in lowercase - sp is executed without errors and this ReturnValue parameter just seems to be ignored

Did you ever encountered such issue? Error about missing db function is completely reasonable (and we've already removed ReturnValue parameters), but I'm just wondering why this behavior is different.

MariaDB - 10.5 installed on windows machine (with default, case-insensitive setting) MySqlConnector - 1.3.2, .NET 4.8

[bgrainger]

Can you provide a sample stored procedure definition and example C# code to reproduce the issue you describe?

[halonn]

Hi Below is code is quite ugly but it was generated from our DAL generator. It throws FUNCTION lowercase_db_name.spProductVersionSelectByAll does not exist. When we remove i_RETURN_VALUE parameter it works correctly for both UPPERCASE and lowercase db in connection string.

const string DB_CONN_STR = "Server=server;Database=UPPERCASE_DB_NAME;Uid=root;Pwd=*****;Port=3306;";

string spName = "spProductVersionSelectByAll";
MySqlCommand mySqlCommand = new MySqlCommand();
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.CommandText = spName;
mySqlCommand.Connection =  new MySqlConnection(DB_CONN_STR);
mySqlCommand.Parameters.Add(new MySqlParameter("i_Active", MySqlDbType.Bit, 1, ParameterDirection.Input, false, (byte) 1, (byte) 0, "Active", DataRowVersion.Current, (object) null)).Value = (object) (int) 1;
mySqlCommand.Parameters.Add(new MySqlParameter("i_RETURN_VALUE", MySqlDbType.Int32, 4, ParameterDirection.ReturnValue, false, (byte) 0, (byte) 0, string.Empty, DataRowVersion.Current, (object) null));
MySqlDataAdapter adr = new MySqlDataAdapter();
adr.SelectCommand = mySqlCommand;
var ds = new DataSet();
var res = adr.Fill(ds);

DELIMITER //
DROP PROCEDURE IF EXISTS spProductVersionSelectByAll;
CREATE PROCEDURE spProductVersionSelectByAll
(
	i_Active bit
)

BEGIN
	SELECT
		PRODUCT_VERSION.ID AS "ID",
		PRODUCT_VERSION.Version_ AS "Version",
		PRODUCT_VERSION.ReleaseDate AS "ReleaseDate"
	FROM
		PRODUCT_VERSION
	WHERE
		(PRODUCT_VERSION.Active = i_Active)
		;
END //

[bgrainger]

To repro using Docker, start the container with docker run --name mariadb-10.5 -e MYSQL_ROOT_PASSWORD=test -p 3105:3306 mariadb:10.5 --lower_case_table_names=1

[bgrainger]

The issue appears to be that SELECT * from mysql.proc WHERE db = 'UPPERCASE'; doesn't work when the database name is uppercase. MariaDB stores the lowercase version, but uses a case-sensitive collation.

https://github.com/mysql-net/MySqlConnector/blob/b8334e34d43fc2462e0d3a2901d18ec0752f5dc2/src/MySqlConnector/Core/CachedProcedure.cs#L27

It's possible this is a server bug, because select * from information_schema.routines where routine_schema = 'UPPERCASE'; does work.

The outcome is that MySqlConnector fails to find (and cache) the stored procedure. When invoking it, it has to guess if it's a procedure or a function. Since there is a return parameter, it assumes function:

https://github.com/mysql-net/MySqlConnector/blob/b8334e34d43fc2462e0d3a2901d18ec0752f5dc2/src/MySqlConnector/Core/SingleCommandPayloadCreator.cs#L170

I think you've found the two possible workarounds:

1. Use a lowercase database name in the connection string
2. Don't add an unnecessary Return parameter when invoking stored procedures

I strongly recommend (1) because that will enable procedure definition caching, which will make MySqlConnector faster and more accurate at calling stored procedures.

[bgrainger]

With lower_case_table_names = 1 (possibly also 2, but not tested), MySQL 5.7 and MariaDB 10.x store the lower-case version of the schema name in the mysql.proc table, but use a case-sensitive collation. Trying to retrieve the stored procedure metadata fails unless the database name is cased correctly. (This is avoided when using case-sensitive database names, because connecting to the database will fail unless the right case is used.)

Meanwhile, information_schema.routines.routine_schema uses a case-insensitive collation, so on a server with multiple databases that differ only in case, retrieving the stored procedure might get one from a different database.