13

This question has been covered for MSSQL here:

How do I perform an insert and return inserted identity with Dapper?

but this solution does not work with mysql.

To cast the LAST_INSERT_ID() to integer with mysql you have to do this:

SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);

The stack trace is:

Dapper.<QueryInternal>d__13`1.MoveNext() in sqlMapper.cs:607
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +159
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +36
   Dapper.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in sqlMapper.cs:535

Has anyone resolved this issue in MySQL?

EDIT:

I've managed to make this work with the following:

var id = connection.Query<ulong>("SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);").Single();

Perhaps not ideal, but it works.

Community
  • 1
  • 1
Chris W
  • 1,792
  • 15
  • 32

3 Answers3

16

I'll leave this here as an answer for anyone else who might search on this problem.

I've managed to make this work with the following:

var id = connection.Query<ulong>("SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);").Single();

Perhaps not ideal, but it works.

Chris W
  • 1,792
  • 15
  • 32
  • Was having the same issue. For some reason it won't cast to an int using Dapper. You have to use long, than downgrade it to an int in .net :( Don't know if it is a bug with the Connector, MySQL, or Dapper. – Sam Jan 19 '12 at 21:08
  • Just got the same issue and found out that it returned ulong too :) – Tien Do Mar 01 '12 at 17:28
  • 1
    You can cast as long instead of ulong and then int. Another weirdness I found is that bit(1) columns are passed as UInt64 (ulong) as well. My suspicion is the driver. – Vinko Vrsalovic Mar 05 '12 at 14:01
  • 1
    Sorry...not stalking you here :) I find another odd thing is the way that `CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER)` or `CAST(1 AS UNSIGNED INTEGER)` still casts to a 64 bit integer and not a 32 bit one. Hoping the MySQL guru's over on dba.SE can explain that behaviour: http://dba.stackexchange.com/q/57363/2136 – Kev Jan 23 '14 at 14:58
9

I can actually shed some additional light on this because I just spent the last hour wondering why my SELECT LAST_INSERT_ID() query worked on one MySQL server but not another. One server is running MySQL 5.5.11 (production) and the other 5.5.31 (local dev).

Prior to versions 5.1.67, 5.5.29 and 5.6.9 (in each respective release) LAST_INSERT_ID() used to return a signed integer.

Now LAST_INSERT_ID() returns an unsigned BIGINT which meant this code which worked on my 5.5.31 server worked:

var id = cn.Query<ulong>("SELECT LAST_INSERT_ID();").First();

...but is broken when executed against the older 5.5.11 server.

It's documented here:

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

The value has a type of BIGINT UNSIGNED as of MySQL 5.5.29, BIGINT (signed) before that.

My initial solution was to cast the result of LAST_INSERT_ID() to an unsigned BIGINT to make the code portable across both these server versions but (surprise, surprise) the MySQL team added a roadblock.

You can't cast LAST_INSERT_ID() directly to an unsigned (or even signed) BIGINT using the CAST() function because it's not supported. The only integer types you can cast to are SIGNED INTEGER and UNSIGNED INTEGER. This is a pain because if for whatever reason you really need an auto incrementing BIGINT id which increments past 4294967295 then an unsigned integer won't be a large enough type to cast to.

Kev
  • 118,037
  • 53
  • 300
  • 385
1

Use of Convert.ToInt64(value) solved it for me.

David Jiboye
  • 511
  • 8
  • 19