search

yodeski / dapper-dot-net

Automatically exported from code.google.com/p/dapper-dot-net
Other
0 stars 0 forks source link

Invalid cast when returning mysql LAST_INSERT_ID() (mysql version >= 5.5.32) #151

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?
1. Install mysql version >= 5.5.32
2. Execute insert cmd and then select select LAST_INSERT_ID(). Exemple:
using (var items = SqlMapper.QueryMultiple(_db_connection,
                @"insert into images set image_extension = @extension, image_group = @group, image_main = @main; select LAST_INSERT_ID();",
                new { extension = extension, group = group, main = main }
                ))
                {
                    imageID = items.Read<Int64>().Single();
                }
3. if you do the same thing, only with mysql version 5.5.28 then all will work 
fine

I used a machine with windows server 2008 x64. MySql Server versions 5.5.32 and 
5.5.28. Dapper version 1.13. MySql Connector for .NET version 6.7.4

Original issue reported on code.google.com by vyachesl...@gmail.com on 14 Aug 2013 at 2:49

GoogleCodeExporter commented 9 years ago 
Yes, the problem is solved with the use of cast functions in mysql

Original comment by vyachesl...@gmail.com on 15 Aug 2013 at 10:43

GoogleCodeExporter commented 9 years ago 
mysql v.5.5 above return last_insert_id() UInt64 instead of Int64

Original comment by anton.he...@gmail.com on 15 Aug 2013 at 3:05

GoogleCodeExporter commented 9 years ago 
Thought I'd throw my two cents in here. The MySQL team changed the return type 
of LAST_INSERT_ID() across all versions of MySQL, from 5.1 to 5.6. I wrote up 
an answer to a SO question about it here:

http://stackoverflow.com/a/21303992/419

In summary:

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.

See also:

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

Original comment by kevin.e....@gmail.com on 23 Jan 2014 at 10:52