MySqlCommandBuilder fail to create InsertCommand for columnStore

[WonderGnagno]

Software versions MySqlConnector version:2.3.6 Server type (MySQL, MariaDB, Aurora, etc.) and version: MariaDB11.3.2-1.el8 MariaDB-columnstore-cmapi.x86_64 23.10.0-1
MariaDB-columnstore-engine.x86_64 11.3.2_23.10.0-1.el8

.NET version: .NET 8 (Optional) ORM NuGet packages and versions:

Describe the bug MySqlCommandBuilder.GetInsertCommand(); fails the execution in case the table is a ColumnStore

Exception System.InvalidOperationException: Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information. at System.Data.Common.DbCommandBuilder.BuildInformation(DataTable schemaTable) at System.Data.Common.DbCommandBuilder.BuildCache(Boolean closeConnection, Boolean useColumnsForParameterNames) at System.Data.Common.DbCommandBuilder.GetInsertCommand(DataRow dataRow, Boolean useColumnsForParameterNames) at MySqlConnector.MySqlCommandBuilder.GetInsertCommand() in /_/src/MySqlConnector/MySqlCommandBuilder.cs:line 66 at IssueMySql.Test.DoTest() in F:\butta\IssueMySql\IssueMySql\Test.cs:line 28 at IssueMySql.Test.Run() in F:\butta\IssueMySql\IssueMySql\Test.cs:line 39 at Program.

$(String[] args) in F:\butta\IssueMySql\IssueMySql\Program.cs:line 7

Code sample

using System.Data;
using MySqlConnector;

namespace IssueMySql;

public class Test
{
    private MySqlConnection conn;

    private string Store;
    public void DoTest()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = " drop table if exists test";
        cmd.ExecuteNonQuery();
        cmd.CommandText =
            $" CREATE TABLE test ( id int,  message varchar(200)\n) ENGINE={Store} ";
        cmd.ExecuteNonQuery();

        MySqlDataAdapter adapter = new MySqlDataAdapter();

        adapter.SelectCommand = new MySqlCommand("select * from test", conn);
        MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);

        builder.GetInsertCommand();
    }

    public void Run()
    {
        conn = new MySqlConnection("server=devel;uid=DD_SIMULATOR;pwd=simulator;database=DD_SIMULATOR");
        conn.Open();
        Store = "InnoDb";
        DoTest();
        Store = "ColumnStore";
        DoTest();

    }
}

Expected behavior Works as InnoDb

Additional context

[bgrainger]

Thanks for the repro.

DbCommandBuilder is using the BaseTableName from the schema table returned by DbDataReader.GetSchemaTable. If this is empty, it throws: https://github.com/dotnet/runtime/blob/be3ad0d6ed572a1bdbdb3036a83e769b7607e693/src/libraries/System.Data.Common/src/System/Data/Common/DBCommandBuilder.cs#L724-L726.

If you run the following code, you'll see a difference between InnoDB and ColumnStore:

using (var reader = adapter.SelectCommand.ExecuteReader())
{
    var table = reader.GetSchemaTable();
    Console.WriteLine(table.Rows[0]["BaseColumnName"]); // "id"
    Console.WriteLine(table.Rows[0]["BaseTableName"]);  // "test" for InnoDB, "" for ColumnStore 
}

The schema (created by GetSchemaTable) is populated from the column definition payloads returned by the server. It appears that ColumnStore doesn't return this information. Thus, MySqlConnector doesn't report it, and DbCommandBuilder is not supported for tables backed by this engine.

I think you would need to report this as a bug against MariaDB ColumnStore, noting that fields in the Column Definition Payload are left blank. (A Wireshark packet capture would probably be the best way to demonstrate this bug.)