Bug #65452 MySqlCommand.LastInsertedId can only have 32 bit values but has type long
Submitted: 29 May 2012 16:44 Modified: 28 Sep 2012 18:51
Reporter: Michael Ortmann Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.4.5, 6.5.4 OS:Microsoft Windows (7)
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any
Tags: LastInsertedId

[29 May 2012 16:44] Michael Ortmann
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.
[30 May 2012 12:10] Valeriy Kravchuk
Verified by code review.
[28 Sep 2012 18:51] John Russell
Added to changelog for 6.4.6, 6.5.5, 6.6.3: 

Although the member variable MySqlCommand.LastInsertedId was a 64-bit
long, its value was effectively capped at the maximum value of Int32
(2,147,483,647). If a primary key exceeded this value, the value of
LastInsertedId was wrong. This mismatch could be an issue for tables
with large numbers of rows.