MySQL Bugs: #95382: Load a DataTable from a datareader doesn't work for large BIGINT UNSIGNED

Bug #95382	Load a DataTable from a datareader doesn't work for large BIGINT UNSIGNED
Submitted:	15 May 2019 23:52	Modified:	3 Apr 2020 18:41
Reporter:	Fethi SABTA	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.9.12.0	OS:	Windows
Assigned to:		CPU Architecture:	x86
Tags:	Auto-increment, BIGINT, DataReader, DataTable, Load(), UNSIGNED

Description:
When I try to load a DataTable directly from a datareader using the Load() method from a table with column type auto-increment, unsigned, bigint primary key,  i get an an exception  "Value was either too large or too small for an Int32...."

the exception occurs if column value is larger than 2147483647.
I work around this issue by changing column to unsigned.

How to repeat:
MySQL server

CREATE TABLE `dbp_table` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8;

INSERT INTO dbp_table (`ID`) VALUES ('2147483648');
INSERT INTO dbp_table (`ID`) VALUES ('2147483649');

Vb.net  program:

        Dim eDataSet As New DataSet
        Dim eTableName As String = "Table1"
        Dim mySelectQuery As String = "SELECT * FROM dbp_table;"
        Dim myConnection As New MySqlConnection("server=server;uid=user;database=database;port=3306;password=XXXXXXXX")
        Dim myCommand As New MySqlCommand(mySelectQuery, myConnection)
        myConnection.Open()
	eDataSet.Tables.Add(eTableName)

        Using reader = myCommand.ExecuteReader
            
            eDataSet.Tables(eTableName).Load(reader)

        End Using

        myConnection.Close()

I'm not sure this is a Connector/NET bug. The full callstack is all DataTable code:

 at System.Data.DataColumn.set_Item(Int32 record, Object value)
   at System.Data.DataTable.NewRecordFromArray(Object[] value)
   at System.Data.DataTable.LoadRow(Object[] values, LoadOption loadOption, Index searchIndex)
   at System.Data.DataTable.LoadDataRow(Object[] values, LoadOption loadOption)
   at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
   at System.Data.DataTable.Load(IDataReader reader)

It looks like DataTable doesn't infer the type of the "ID" column, even though MySqlDataReader.GetFieldType(0) correctly returns UInt64.

I found a workaround: after you add the table (eDataSet.Tables.Add(eTableName)), add an ID column (this is in C# so translate to VB as necessary):

eDataSet.Tables[eTableName].Columns.Add("ID", typeof(ulong), null);

After doing this, the DataTable will load correctly.

Hello Fethi SABTA,

Thank you for the report.

regards,
Umesh

Here is another way to work around.
modify query as follows  "SELECT CAST(ID AS UNSIGNED) AS ID FROM dbp_table;"

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.21 release, and here's the proposed changelog entry from the documentation team:

Columns of type BIGINT in a table that was loaded using MySqlDataReader
did not include the UNSIGNED flag, even though UNSIGNED was specified in
the CREATE TABLE statement. An exception was generated if the value of
such a column exceeded 2147483647.

Thank you for the bug report.