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:
Category:Connector / NET Severity:S2 (Serious)
Version: OS:Microsoft Windows
Assigned to: CPU Architecture:x86
Tags: Auto-increment, BIGINT, DataReader, DataTable, Load(), UNSIGNED

[15 May 23:52] Fethi SABTA
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,

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)

        Using reader = myCommand.ExecuteReader

        End Using

[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.

[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;"