Description:
When executing an insert command with the MySqlCommand class, the last inserted id is stored in its "LastInsertedId" field. It has the type "long" which is 64 bit. I discovered a design flaw in MySql.Data which prevents its value to rise above the maximum value of Int32 which is 2,147,483,647. If a primary key exceeds this value, the value of "LastInsertedId" will be wrong.
The cause is a type cast in NativeDriver.cs where the original 64 bit value is casted to "int". In Connector Version 6.5.4 it is in line 702 of NativeDriver.cs:
public int GetResult(ref int affectedRow, ref int insertedId)
{
...
insertedId = (int)packet.ReadFieldLength();
...
}
This wrongly casted value is then written into an instance of the class ResultSet. ResultSet has an internal member with the name insertedId and type int (32 bit). The value of this member will finally be written into the "LastInsertedId" field of the MySQLCommand object which has the type "long".
Summary:
The connector receives a 64 bit integer, casts it into a 32 bit integer to finally write it into a 64 bit integer again.
For most databases this isn't an issue, but for large databases it may become one.
How to repeat:
Create a database "test" and run the folowing two SQL commands:
CREATE TABLE longids (id BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));
alter table longids AUTO_INCREMENT = 2147483640;
Now create a C# console application with the following code and run it:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
namespace mysqlOverflowTest
{
class Program
{
static void Main(string[] args)
{
MySqlConnection con = new MySqlConnection("Server=localhost;Uid=XXXX;Pwd=XXXX;Database=test;");
con.Open();
for (int i = 1; i < 10; ++i)
{
MySqlCommand cmd = new MySqlCommand("INSERT INTO longids VALUES ();", con);
cmd.ExecuteNonQuery();
Console.WriteLine("Last inserted id: " + cmd.LastInsertedId.ToString());
}
con.Close();
}
}
}
The console output of this program will be:
Last inserted id: 2147483644
Last inserted id: 2147483645
Last inserted id: 2147483646
Last inserted id: 2147483647
Last inserted id: -2147483648
Last inserted id: -2147483647
Last inserted id: -2147483646
Last inserted id: -2147483645
Last inserted id: -2147483644
If you call "SELECT last_insert_id();" the values for the id will be correct.
Suggested fix:
Users of Connector .NET that have large databases should use the SQL query "SELECT last_insert_id();" to get the last insert id.
I suggest to fix the MySql.Data code parts described above. The type long should always be used to store the last inserted id.
Description: When executing an insert command with the MySqlCommand class, the last inserted id is stored in its "LastInsertedId" field. It has the type "long" which is 64 bit. I discovered a design flaw in MySql.Data which prevents its value to rise above the maximum value of Int32 which is 2,147,483,647. If a primary key exceeds this value, the value of "LastInsertedId" will be wrong. The cause is a type cast in NativeDriver.cs where the original 64 bit value is casted to "int". In Connector Version 6.5.4 it is in line 702 of NativeDriver.cs: public int GetResult(ref int affectedRow, ref int insertedId) { ... insertedId = (int)packet.ReadFieldLength(); ... } This wrongly casted value is then written into an instance of the class ResultSet. ResultSet has an internal member with the name insertedId and type int (32 bit). The value of this member will finally be written into the "LastInsertedId" field of the MySQLCommand object which has the type "long". Summary: The connector receives a 64 bit integer, casts it into a 32 bit integer to finally write it into a 64 bit integer again. For most databases this isn't an issue, but for large databases it may become one. How to repeat: Create a database "test" and run the folowing two SQL commands: CREATE TABLE longids (id BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)); alter table longids AUTO_INCREMENT = 2147483640; Now create a C# console application with the following code and run it: using System; using System.Collections.Generic; using System.Linq; using System.Text; using MySql.Data.MySqlClient; namespace mysqlOverflowTest { class Program { static void Main(string[] args) { MySqlConnection con = new MySqlConnection("Server=localhost;Uid=XXXX;Pwd=XXXX;Database=test;"); con.Open(); for (int i = 1; i < 10; ++i) { MySqlCommand cmd = new MySqlCommand("INSERT INTO longids VALUES ();", con); cmd.ExecuteNonQuery(); Console.WriteLine("Last inserted id: " + cmd.LastInsertedId.ToString()); } con.Close(); } } } The console output of this program will be: Last inserted id: 2147483644 Last inserted id: 2147483645 Last inserted id: 2147483646 Last inserted id: 2147483647 Last inserted id: -2147483648 Last inserted id: -2147483647 Last inserted id: -2147483646 Last inserted id: -2147483645 Last inserted id: -2147483644 If you call "SELECT last_insert_id();" the values for the id will be correct. Suggested fix: Users of Connector .NET that have large databases should use the SQL query "SELECT last_insert_id();" to get the last insert id. I suggest to fix the MySql.Data code parts described above. The type long should always be used to store the last inserted id.