MySQL Bugs: #105768: MySqlCommandBuilder does'nt support tables with a bigint unsigned as primary key

Bug #105768	MySqlCommandBuilder does'nt support tables with a bigint unsigned as primary key
Submitted:	1 Dec 2021 20:28	Modified:	19 Jan 2022 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:	Windows
Assigned to:		CPU Architecture:	Any

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.

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

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.