Error in MySqlBatch leaves connection in bad state with MariaDB

[Dargazo]

trafficstars

MySQLCOnnection.Open() throws a MySqlConnector.MySqlProtocolException: 'Packet received out-of-order. Expected x; got y.' after an error has occurred in a batch command.

I managed to consistently reproduce the issue. My Config: .NET 5.0 Database: MariaDB 10.5.8, currently the latest version (Issue is also present using -not latest- 10.3.15) MySqlConnector: NuGet v1.2.1 (current latest)

The following code reproduces the issue consistently:

        private static string GetConnStr()
        {
            MySqlConnectionStringBuilder l_Builder = new MySqlConnectionStringBuilder();
            l_Builder.Database = "db_test";
            l_Builder.Server = "localhost";
            l_Builder.Port = 3306;
            l_Builder.UserID = "root";
            l_Builder.Password = "MyPassword";
            l_Builder.AllowLoadLocalInfile = true;
            l_Builder.AllowUserVariables = true;
            return l_Builder.ToString();
        }
        static async Task Main(string[] args)
        {

            // Setup
            using (MySqlConnection l_Conn = new MySqlConnection(GetConnStr()))
            {
                string l_DropQ = "DROP TABLE IF EXISTS `tbl`";
                string l_CreateQ = @"CREATE TABLE `tbl` (
	                                `A` INT(11) NOT NULL,
	                                PRIMARY KEY (`A`) USING BTREE
                                )
                                COLLATE='utf8_general_ci'
                                ENGINE=InnoDB";
                l_Conn.Open();

                MySqlCommand l_DropCmd = new MySqlCommand(l_DropQ, l_Conn);
                await l_DropCmd.ExecuteNonQueryAsync();

                MySqlCommand l_CreateCmd = new MySqlCommand(l_CreateQ, l_Conn);
                await l_CreateCmd.ExecuteNonQueryAsync();
            }

            string[] l_Q = new string[]
            {
                "INSERT INTO `db_test`.`tbl` (`A`) VALUES (1)", // Insert first value
                "INSERT INTO `db_test`.`tbl` (`A`) VALUES (1)", // Generates duplicate key error, as it should
                "INSERT INTO `db_test`.`tbl` (`A`) VALUES(2)" // Valid query
            };


            using (MySqlConnection l_Conn = new MySqlConnection(GetConnStr()))
            {
                MySqlBatch l_Batch = l_Conn.CreateBatch();
                foreach (string l_Query in l_Q)
                {
                    MySqlBatchCommand l_Temp = new MySqlBatchCommand( l_Query);
                    l_Batch.BatchCommands.Add(l_Temp);
                }
                try
                {
                    l_Conn.Open();
                    await l_Batch.ExecuteNonQueryAsync();
                }
                catch (Exception l_Ex)
                {
                    // Throws expected duplicate key exception                    
                }
            }

            MySqlConnection l_Conn2 = new MySqlConnection(GetConnStr());

            // throws 'Packet received out-of-order Exception'
            l_Conn2.Open();
        }

I am using .NET 5.0. I notice the issue goes away if the second insert statement is executed last in the batch (i.e. has index 2 in the l_Q array)

However, as these queries are generated in my production case, I can not predetermine whether queries are going to fail.

[bgrainger]

Thanks for the example code; I can repro this with MariaDB 10.5.8 (but not MySQL 8.0.23).

This problem will probably "go away" when the MariaDB team removes batch command execution in 10.6.0: https://jira.mariadb.org/browse/MDEV-21612. At that point, MySqlConnector will fall back to the same logic it uses with MySQL, which doesn't exhibit a problem.

As a workaround, you could switch from MySqlBatchCommand to MySqlCommand.

[Dargazo]

No problem, glad you can reproduce it. For me, using MySqlCommand is not really an option because in my use case:

• I have a table containing 106 columns.... batching multiple insert/update queries into a single MySQLCommand would soon hit the maximum parameter limit.
• Currently I am pushing many MySqlBatches, in bursts (every time data comes in periodically from an external system). each having +- 500 parameterized MySqlBatchCommands. This performs great! Switching to single MySqlCommands increases the execution time over 10 times. For my use case this is less than optimal.

Finally, there is no MariaDB 10.6 release out yet (at time of writing) and we'll have to wait a while before it gets the GA status... So upgrading currently is not an option.

I did however, find an other workaround, which might hint towards a fix direction. You can wrap the l_Conn2.Open() in a try/catch, catch the packet out-of-order Exception, and close/open the connection again. you can even do the following:

try
{
    l_Conn.Open();
    await l_Batch.ExecuteNonQueryAsync(); // Generates duplicate key exception
}
catch (Exception l_Ex) // Catch duplicate key exception
{
    try 
    {
        l_Conn.Close();
        l_Conn.Open(); // Generates packet out-of-order exception
    } 
    catch (Exception l_Ex2) // Catch packet out-of-order exception
    {
        l_Conn.Close();
        l_Conn.Open(); // No more out-of-order exception, all is well again.
    }
}
// Here l_Conn is open, and can be used again.

[bgrainger]

batching multiple insert/update queries into a single MySQLCommand would soon hit the maximum parameter limit

I'm not aware of a maximum parameter limit.

Switching to single MySqlCommands increases the execution time over 10 times. ... there is no MariaDB 10.6 release out yet ... upgrading currently is not an option.

My point wasn't that you should upgrade to fix this bug; rather, it's that MariaDB is taking away the feature that MySqlBatchCommand depends on. So this API may not be a long-term viable option for your code. If and when you update to MariaDB 10.6, MySqlConnector will be forced to go back to the "slow" way of doing things. (Although it will still be faster than executing MySqlCommand objects one-at-a-time because MySqlConnector will combine them into one command.)

[bgrainger]

I can't repro with the latest code on master (against mariadb:10.5.8). This may have been fixed along the way? Additionally, the code that caused the problem will be removed in #946.