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

[15 May 23:52] Fethi SABTA
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()
[16 May 21:55] Bradley Grainger
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.
[20 May 11:41] Umesh Shastry
Hello Fethi SABTA,

Thank you for the report.

regards,
Umesh
[20 May 20:49] Fethi SABTA
Here is another way to work around.
modify query as follows  "SELECT CAST(ID AS UNSIGNED) AS ID FROM dbp_table;"