State exception when executing procedure with runtime error

[Hjorthen]

Hi. I've been using MySQLConnector for some time now. However, I've run into an exception System.InvalidOperationException : 'Expected state to be Failed but was Connected.' which fires on the last line of the following:

 MySqlCommand command = conn.Get().CreateCommand();
 command.CommandText = "getUserRole";
 command.CommandType = CommandType.StoredProcedure;
 command.Parameters.AddWithValue("@userID", username);
 command.Parameters.AddWithValue("@projectID", -1);
 command.Parameters.AddWithValue("@userRole", MySqlDbType.Int32).Direction = ParameterDirection.Output;
 await command.ExecuteNonQueryAsync();

Stack trace:

at MySqlConnector.Core.ServerSession.VerifyState(State state) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 882
   at MySqlConnector.Core.ServerSession.FinishQuerying() in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 154
   at MySql.Data.MySqlClient.MySqlDataReader.DoClose() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 419
   at MySql.Data.MySqlClient.MySqlDataReader.Dispose(Boolean disposing) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 266
   at MySqlConnector.Core.TextCommandExecutor.<ExecuteNonQueryAsync>d__1.MoveNext() in C:\projects\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 33
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()

Any suggestions for what the cause might be or how to prevent it?

[bgrainger]

Are you able to copy the CREATE PROCEDURE statement here? That may help me reproduce the issue.

Which version of MySQL Server are you using?

[Hjorthen]

It happens whenever there is a runtime error in the procedure(such as duplicate key)
This is the latest procedure I have encountered it with(same error as previously stated):

  CREATE PROCEDURE `createProject`(IN nameparam VARCHAR(64), IN userid VARCHAR(32), IN commentparam VARCHAR(512), OUT id INT)
BEGIN 
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
		ROLLBACK;
        RESIGNAL;
    END;
	
	START TRANSACTION;
	INSERT INTO projectJournal (name)
	VALUES (LOWER(nameparam));
    
    SET @newID = LAST_INSERT_ID();
    
    INSERT INTO projectStaticIds (activeProjectId)
	VALUES (@newID);
	SET id = LAST_INSERT_ID();
    INSERT INTO projectLog 
    VALUES (NULL, id, @newID, userid, UNIX_TIMESTAMP(NOW()), commentparam, 'created'); 
    COMMIT;
END

If the procedure it called twice with the same primary key(nameparam) then the exception accours.

MySQL version: 5.7.20-0ubuntu0.16.04.1
Client is running on .net core 2.0
Package include <PackageReference Include="MySqlConnector" Version="0.31.1" />(Tried 0.34.0, no change)

[bgrainger]

FWIW there is a bug in your first code snippet:

command.Parameters.AddWithValue("@userRole", MySqlDbType.Int32).Direction = ParameterDirection.Output;

The second parameter to AddWithValue is used to initialize the MySqlParameter.Value property, not the MySqlParameter.MySqlDbType property. In this case it doesn't really matter because this is an out parameter and its value will get overwritten anyway. However, it looks like you might be trying to set .MySqlDbType and that will not happen. (AFAICT this is the same in MySqlConnector and Connector/NET.)

[bgrainger]

You probably wanted .Add("@userRole", MySqlDbType.Int32) but that overload doesn't exist yet; created #424 to add it.

[Hjorthen]

Right. What about raising the exception on C# level?

[bgrainger]

Your sample code calls getUserRole but the stored procedure you've given is createProject. When I modify the example to call that (with a duplicate name as a parameter), I get the following exception:

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: Duplicate entry 'test' for key 'name' ---> MySql.Data.MySqlClient.MySqlException: Duplicate entry 'test' for key 'name'
   at MySqlConnector.Core.ServerSession.TryAsyncContinuation(Task`1 task) in C:\MySqlConnector\src\MySqlConnector\Core\ServerSession.cs:line 1024
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at MySqlConnector.Core.ResultSet.<ReadResultSetHeaderAsync>d__1.MoveNext() in C:\MySqlConnector\src\MySqlConnector\Core\ResultSet.cs:line 43
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\MySqlConnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 101
   at MySql.Data.MySqlClient.MySqlDataReader.<ReadFirstResultSetAsync>d__65.MoveNext() in C:\MySqlConnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 296
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable.ConfiguredTaskAwaiter.GetResult()
   at MySql.Data.MySqlClient.MySqlDataReader.<CreateAsync>d__64.MoveNext() in C:\MySqlConnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 287
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at MySqlConnector.Core.TextCommandExecutor.<ExecuteReaderAsync>d__3.MoveNext() in C:\MySqlConnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 73
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at MySqlConnector.Core.StoredProcedureCommandExecutor.<ExecuteReaderAsync>d__1.MoveNext() in C:\MySqlConnector\src\MySqlConnector\Core\StoredProcedureCommandExecutor.cs:line 79
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at MySqlConnector.Core.TextCommandExecutor.<ExecuteNonQueryAsync>d__1.MoveNext() in C:\MySqlConnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 26
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at MySqlTestApp.Program.<StoredProcedureOutParameter>d__1.MoveNext() in C:\MySqlTestApp\MySqlTestApp\Program.cs:line 80
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at MySqlTestApp.Program.<Main>d__0.MoveNext() in C:\MySqlTestApp\MySqlTestApp\Program.cs:line 28
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at MySqlTestApp.Program.<Main>(String[] args)

This looks like it's working as expected to me; can you please update to 0.34.0 and let me know if you can still reproduce the problem with that version? And, if so, post a complete example to reproduce it?

[bgrainger]

Never mind, I just saw that you posted above:

(Tried 0.34.0, no change)

In that case, a self-contained repro would be greatly appreciated.

[Hjorthen]

I don't even know where to start. What could cause the session to become in an invalid state?

[bgrainger]

Once I have a repro, I can figure that out. I'm just not able to write code that reproduces the exception you're reporting.

[bgrainger]

Can you show me the definition of the getUserRole stored procedure?

[Hjorthen]

I changed getUserRole to a function instead of a procedure. The runtime error for it was that it was trying to retrieve a column that didn't exist(typo). It seems to be an issue with all runtime errors.

The code I use for calling create project is here:

        public async Task<int> CreateProject(string name, string userId, string comment)
        {
            using(Connection conn = await connection.Create())
            {
                MySqlCommand command = conn.Get().CreateCommand();
                command.CommandText = "createProject";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@nameparam", name);
                command.Parameters.AddWithValue("@userid", userId);
                command.Parameters.AddWithValue("@commentparam", comment);
                command.Parameters.Add("@id", DbType.Int32).Direction = ParameterDirection.Output;
                await command.ExecuteNonQueryAsync();
                return (int)command.Parameters["@id"].Value;
            }
        }

[bgrainger]

I believe I've identified the cause of this bug.

[Hjorthen]

Something I can do to prevent it?

[bgrainger]

AFAICT, this happens when executing the stored procedure or function causes an exception (which is then masked by the exception you reported in the original bug report). So the only workaround I can think of is: "don't cause that exception in the first place". To find out what it is, you'd need to debug with "Break on all first-chance exceptions", then look at it in the debugger.

A real fix should be shipping soon.

[Hjorthen]

I'll just make the procedures return a "magic" number for now. Thanks for your support!

[bgrainger]

Fixed in 0.34.1.