Bug #105768 MySqlCommandBuilder does'nt support tables with a bigint unsigned as primary key
Submitted: 1 Dec 2021 20:28 Modified: 19 Jan 21:05
Reporter: Steve Mettraux Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.27 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[1 Dec 2021 20:28] Steve Mettraux
Description:
It seems that since 1 year, the MySqlCommandBuilder doesn't accept BIGINT unsigned as a primary key.

I have classified this as S1 because if we update some applications they simply stops to work.

We get the error message : 
System.InvalidOperationException: 'Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.'

If you run the same code but without keyword "UNSIGNED" in the create table. Then it works.

How to repeat:
To reproduce just run this code on a test database.

/// run this code
/// create a connection on a test database
connection.Open();
var createTableSql = @"
DROP TABLE IF EXISTS aatabletest2;
CREATE TABLE `aatabletest2` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `field1` VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY(`id`));";
var command = new MySql.Data.MySqlClient.MySqlCommand(createTableSql, connection);
command.ExecuteNonQuery();
var adapter = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM aatabletest2", connection);
var commandBuilder = new MySqlCommandBuilder(adapter);
var myCommand = commandBuilder.GetUpdateCommand();
Console.WriteLine(myCommand.CommandText); // fail here

Suggested fix:
Modify MySqlCommandBuilder to make it accept a primary key with a BIGINT UNSIGNED, or make a workaround so that we can give the primary key as a hint in paramaters.
[8 Dec 2021 12:55] MySQL Verification Team
Hello Steve Mettraux,

Thank you for the bug report.
Verified as described.
Workaround is to use WB/CLI to create the table.

Regards,
Ashwini Patil
[19 Jan 21:05] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.29 release, and here's the proposed changelog entry from the documentation team:

A primary key having a column or columns of type unsigned BIGINT (unsigned
64-bit integer) was not supported when the table was used with the
MySqlCommandBuilder class to generate single-table commands.

Thank you for the bug report.