Errors when using DbDataAdapter.Fill with parameterized and prepared command

[Michel20367]

Software versions MySqlConnector version: 2.3.7 Server type (MySQL, MariaDB, Aurora, etc.) and version: MariaDb 10.6 and 10.11 .NET version: 8 (Optional) ORM NuGet packages and versions: no ORM

Describe the bug I run the select queries with the parameterized and prepared commands. It runs several times without problem until at some point (it can happen after several minutes or hours) the error "Value cannot be null. (Parameter 'Data table may not be null.)". If this is the case, the command can no longer be executed and returns the above error message. The command somehow seems to be permanently "broken" Other queries with non-parametrized commands can still be executed. The only thing that helps at this point is reconnect. The problem seems to be related to the switch to MySqlConnector 2.3.x and .NET 8. Previously it worked stable with MySqlConnector 2.2.x and .NET 6 or even earlier with MySqlConnector 1.3.x and .NET Framework 4.7.2

Exception

"Specified argument was out of the range of valid values"

    at MySqlConnector.Protocol.Payloads.ColumnDefinitionPayload.ReadNames() in /_/src/MySqlConnector/Protocol/Payloads/ColumnDefinitionPayload.cs:line 134
    at MySqlConnector.Core.ResultSet.GetName(Int32 ordinal) in /_/src/MySqlConnector/Core/ResultSet.cs:line 288
    at System.Data.ProviderBase.SchemaMapping.GenerateFieldNames(DataReaderContainer dataReader)
    at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
    at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
    at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
    at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)  

Code sample

            var dataAdapter = new MySqlDataAdapter();
            dataAdapter.SelectCommand = command; // prepared MySqlCommand with one or more MySqlParams 
            using (var dataSet = new DataSet())
            {
                dataAdapter.Fill(dataSet);
                var dataTable = dataSet.Tables[0];                
                return dataTable;
            }

Expected behavior A stable version as in previous versions or at least a correct error message.

[bgrainger]

I don't understand how this exception and message can be thrown from the specified line of code:

Value cannot be null. (Parameter 'Data table may not be null.
    at MySqlConnector.Protocol.Payloads.ColumnDefinitionPayload.ReadNames() in /_/src/MySqlConnector/Protocol/Payloads/ColumnDefinitionPayload.cs:line 134

Additionally, the unbalanced single quote and missing closing parenthesis is very strange. Is this the exact exception message and call stack from the error?

[Michel20367]

@bgrainger

Sorry, my mistake. I stupidly copied the following error message from calling method in my program code. The correct error message for the stacktrace is "Specified argument was out of the range of valid values" I have now also corrected this above. I have now also output the names and values of the command.parameters in the catch. These are correct.

I will now test IgnorePrepare = true in the connection properties to rule out that the problem has something to do with the Prepare on the server.

[Michel20367]

@bgrainger UPD: Yes, this has something to do with the "real" Prepare on the server. After I set IgnorePrepare = true in the connection properties, I don't get this error. So in this case the problem is solved for me. However, I would like to get to the cause of the problem. Is there any easy way to log variable "originalData"? Since the error in the line m_physicalName = Encoding.UTF8.GetString(reader.ReadLengthEncodedByteString()); I assume that the structure of originalData does not correspond to the expected one.

[bgrainger]

Are you able to construct a consistent repro for this, or is it still kind of "random" when it happens? (But known to never happen if IgnorePrepare=true?)

[Michel20367]

No, it's still random. It can happen after half an hour or after several hours. After setting IgnorePrepare=true, this error no longer occurred for a week.

[bgrainger]

I ran a stress-test of 70 concurrent threads each creating 10,000 DataTables using your code above:

        using var connection = await dataSource.OpenConnectionAsync();
        using var command = connection.CreateCommand();
        command.CommandText = "select rowid, byte, int16, int32, int64 from datatypes_integers where rowid < @rowid and int32 < @int32";
        command.Parameters.AddWithValue("@rowid", 10);
        command.Parameters.AddWithValue("@int32", 1000000);
        command.Prepare();

        var dataAdapter = new MySqlDataAdapter();

No exceptions were thrown when running against MariaDB 10.11.

Can you provide a more realistic example (e.g., with table schema, some mock data, etc.) for me to test?

[Michel20367]

@bgrainger The queries concerned are quite different, e.g. var sql = $“SELECT * FROM JREMAILIMPORT WHERE popaccount = @popaccount AND active = 1 ORDER BY ordernum”; The Table for this query with 5-10 rows:

CREATE TABLE JREMAILIMPORT (
    id INT NOT NULL,
    ordernum INT,
    label VARCHAR(50),
    documentation VARCHAR(255),
    active INT,
    popaccount INT,
    incident_per_email INT,
    start_option INT,
    subtable_emails VARCHAR(50),
    subtable_attachments VARCHAR(50),
    subtable_recipients VARCHAR(50),
    subtable_headers VARCHAR(50),
    processname VARCHAR(255),
    step INT,
    initiator VARCHAR(50),
    username VARCHAR(50),
    jobfunction VARCHAR(50),
    filter_header VARCHAR(255),
    filter_subject VARCHAR(255),
    filter_emailtext VARCHAR(255),
    filter_sender_name VARCHAR(255),
    filter_sender_email VARCHAR(255),
    filter_recipient_name VARCHAR(255),
    filter_recipient_email VARCHAR(255),
    filter_attachment VARCHAR(255),
    created_by VARCHAR(50),
    create_date DATETIME,
    modified_by VARCHAR(50),
    modify_date DATETIME,
    filter_folder VARCHAR(255),
    att_min_filesize INT,
    att_file_extentions VARCHAR(255),
    PRIMARY KEY (id)
);

The problem has nothing to do with the server load. It feels like it is some problem on the DB server side, where the prepared statement on the server side breaks due to a “hiccup”. Normally this happens after several hours, the queries are called every 10 seconds. IgnorePrepare = false has reliably suppressed the problem so far. I have agreed with the Ops that I can get an environment identical to the productive one (MariaDb 10.6) and experiment there. However, not until the week after next. I will still try out whether it has anything to do with pipelining. Is there any easy way to dump the variable “originalData”?

[bgrainger]

The queries concerned are quite different,

Thanks for the details.

Is there any easy way to dump the variable “originalData”?

Not from MySqlConnector. You would need to run Wireshark (or similar) to capture the network packets. (Note that if you're using SSL, the packets will be encrypted and thus not usable.)