MySqlDataAdapter.Update fails - MySqlCommand disposed object

[patrik-osgnach]

Hi, I'm porting some code which uses MySql.Data to MySqlConnector and I'm facing an issue when I want to run an insert operation
I have an instance of MySqlDataAdapter, I call the Update method and I pass a DataTable.
The DataAdapter has the InsertCommand property defined.
After the Update() call, the HasErrors property of the DataTable is true. The last row has RowState=Added, the ItemArray has the values I want to insert but the RowError property is

"Cannot access a disposed object.\r\nObject name: 'MySqlCommand'."
The InsertCommand is not null after the call to Update()

Any idea how can I debug this further?
I'm using MySqlConnector 2.2.5 and the DB is MariaDB 10.6.11

[bgrainger]

Are you able to write a minimal repro that demonstrates the problem?

There are some tests of inserting via MySqlDataAdapter here, but perhaps your code is structured slightly differently?https://github.com/mysql-net/MySqlConnector/blob/master/tests/IntegrationTests/DataAdapterTests.cs#L210

[patrik-osgnach]

Mmm it's quite a big app, I'll try to build such repro.
Through the debugger I found out that AllowUserVariables for the InserCommand is false, even if I set it to true in the ConnectionString
CommandText is

INSERT INTO lab.lab_dcg_equ (manufacturer, model, description, track_nr, costs, power, os, connector, options, ponumber, serial, capital, etag, calib_last, calib_cylce, notes, qr_id, weblink, fw_version, image_id, row_timestamp, row_user, status, document_id) VALUES (@p1, @p2, @p3, @P4, @p5, @p6, @P7, @p8, @p9, @p10, @p11, @p12, @P13, @p14, @P15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24); SELECT LAST_INSERT_ID() as 'id';

Is there any difference in terms of how things must be "set up", compared to MySql.Data?

[bgrainger]

Is there any difference in terms of how things must be "set up", compared to MySql.Data?

There shouldn't be.

I'm not actually a heavy user of MySqlDataAdapter; the integration tests I wrote (linked above) are the most I've ever used the class. So it's quite possible there's some discrepancy in how it works compared to MySql.Data. That's why some code to reproduce the problem would be very helpful to me.

[patrik-osgnach]

worker.txt

I use a BackgroundWorker to push the updates to the DB and attached is the function that's executed when I call RunWorkerAsync()
UpdateWorkerDataAdapter is the instance of MySqlDataAdapter and _dt is a DataTable

I don't really need to use MySqlDataAdapter... that's what the code that I inherited uses.
I just need a mechanism to push to the DB the rows that were changed/added to that DataTable

[p10]

LOL

[bgrainger]

worker.txt

I don't see InsertCommand in that code sample. I assumed I would, based on the OP. Am I missing something?

[patrik-osgnach]

adapterinit.txt
Sorry, that was just the "update" function I have in my app.
Commands are set in another function (now attached) which initializes UpdateWorkerDataAdapter
This init is done only once, when the app starts

[patrik-osgnach]

Since the Initialization happens only once, the insertions/updates happen in another worker function and the MySqlCommandBuilder is a local variable of the init funcion, I believe that the CommandBuilder gets garbage collected together with the commands it built
For now, I solved my problem by cloning the commands returned by the three GetXCommand() before assigning them to the DataAdapter

[bgrainger]

It sounds like a MySqlCommand assigned to the MySqlDataAdapter was disposed before the MySqlDataAdapter had finished using it. This is likely a difference between MySql.Data and MySqlConnector: MySqlConnector does not permit a disposed MySqlCommand (or any MySqlXyz object) to be used but MySql.Data probably doesn't enforce such a restriction.

[patrik-osgnach]

The only object that's explicitly disposed is the MySqlCommandBuilder. I assumed that when I assign the result of GetInsertCommand(), the command would be deep copied, since a copy constructor is implemented

[bgrainger]

A quick test shows that disposing the MySqlCommandBuilder is causing the problem:

var connection = new MySqlConnection("server=localhost;user=root;password=pass;database=mysqltest");
connection.Open();

var da= new MySqlDataAdapter("select * from command_builder_insert", connection);
var cb = new MySqlCommandBuilder(da);
var cmd = cb.GetInsertCommand();

// this line causes the problem
cb.Dispose();

cmd.Parameters["@p1"].Value = 3;
cmd.Parameters["@p2"].Value = "test";
cmd.ExecuteNonQuery();

Since there's no Dispose logic in MySqlCommandBuilder, this must be something in the base class DbCommandBuilder implementation in ADO.NET.

Cloning the commands that are returned seems like a good workaround.