Bug #120347 "IsAutoIncrement" in GetSchemaTable is False in 9.7.0 (regression)
Submitted: 27 Apr 4:05 Modified: 8 Jul 23:10
Reporter: Bradley Grainger (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:9.7.0 OS:Windows (11)
Assigned to: CPU Architecture:x86

[27 Apr 4:05] Bradley Grainger 
Description:
The MySqlDataReader.GetSchemaTable method returns a schema table for the current result set. If an "auto_increment" column is being queried, it is expected that the "IsAutoIncrement" field in the row in the schema table representing that column will be True.

It was True in MySQL Connector/NET 9.6.0 and earlier. In 9.7.0 it now incorrectly returns False.

How to repeat:
Create test.cs as follows and execute 'dotnet run test.cs' (with .NET 10):

#:property PublishAot=false
// use 9.6.0 below to see the expected behavior in the previous version
#:package MySql.Data@9.7.0

using MySql.Data.MySqlClient;

using var connection = new MySqlConnection("...");
connection.Open();

using var command = new MySqlCommand("""
	DROP TABLE IF EXISTS test;
	CREATE TABLE test(rowid integer not null primary key auto_increment);
	""", connection);
command.ExecuteNonQuery();

command.CommandText = "select rowid from test";
using (var reader = command.ExecuteReader())
{
	// prints False; expected True
    Console.WriteLine(reader.GetSchemaTable().Rows[0]["IsAutoIncrement"]);
}
await using (var reader = await command.ExecuteReaderAsync())
{
	// prints False; expected True
	Console.WriteLine((await reader.GetSchemaTableAsync()).Rows[0]["IsAutoIncrement"]);
}
[27 Apr 4:08] Bradley Grainger 
The same regression (change from True to False) also happens for the "IsKey" column.
[25 May 18:57] Bradley Grainger 
Based on https://github.com/mysql/mysql-connector-net/commit/7cfff05e780e2a579fd89345c0d054a1b1c16b... this appears to be a deliberate change to fix bug #118978 although it's certainly not clear to me why the default behavior should have changed such that CommandBehavior.KeyInfo is now required.
[8 Jul 23:10] Jose Ramirez Ruiz 
Hello Bradley,
Thank you for your report.
The change in 9.7.0 was intentional. It was made to prevent APIs such as DataTable.Load() from unintentionally inferring key metadata from a regular reader and collapsing rows that should remain distinct.

When key metadata is required, the reader should be opened with CommandBehavior.KeyInfo.

That said, we will review this in more detail. It may be possible for IsAutoIncrement to continue reporting the column’s auto-increment status without reintroducing the row-collapsing behavior, as long as IsKey remains gated behind CommandBehavior.KeyInfo.