MySQL Bugs: #71242: Entity Framework Inserts broken on tables with unsigned bigint as auto increment

Bug #71242	Entity Framework Inserts broken on tables with unsigned bigint as auto increment
Submitted:	28 Dec 2013 18:45	Modified:	6 Mar 2014 23:34
Reporter:	Andy Heitke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.8.3	OS:	Any
Assigned to:	Francisco Alberto Tirado Zavala	CPU Architecture:	Any
Tags:	EF, EF6, entity framework

Description:
Tables that have a primary key of unsigned bigint, autoincrement are causing the entity framework to throw a NullReferenceException on insert.

How to repeat:
Create the following database:

create database testdb;

CREATE TABLE testdb.testtbl (
  testID bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  test varchar(255) DEFAULT NULL,
  PRIMARY KEY (testID)
)
ENGINE = INNODB
AUTO_INCREMENT = 5
AVG_ROW_LENGTH = 5461
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

Add a model file and attempt to add a record:

testdbEntities dbContext = new testdbEntities();
dbContext.testtbls.Add(new testtbl() { test = "testing" });
dbContext.SaveChanges();

The following exception is produced:
Object reference not set to an instance of an object.

   at MySql.Data.Entity.ListFragment.WriteSql(StringBuilder sql)
   at MySql.Data.Entity.SelectStatement.WriteSql(StringBuilder sql)
   at MySql.Data.Entity.InsertStatement.WriteSql(StringBuilder sql)
   at MySql.Data.Entity.SqlFragment.ToString()
   at MySql.Data.Entity.InsertGenerator.GenerateSQL(DbCommandTree tree)
   at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommand(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.CreateCommand(DbModificationCommandTree commandTree)
   at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.CreateCommand(Dictionary`2 identifierValues)
   at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2(UpdateTranslator ut)
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction, Boolean throwOnClosedConnection)
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update(Boolean throwOnClosedConnection)
   at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__33()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass28.<SaveChanges>b__25()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at ConsoleApplication1.Program.Main(String[] args) in c:\Users\Andy\Documents\Visual Studio 2013\Projects\ConsoleApplication1\ConsoleApplication1\Program.cs:line 16
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

Suggested fix:
The problem is that EF doesn't support unsigned primitive types directly.  And so you are forced to map UInt64 to Decimal in order to have a big enough number to hold the value.  When MySql.Data.Entity.InsertGenerator.GenerateReturningSql() is called, you attempt to build the query to fetch the last_insert_id().  Since Decimal isn't on this list (InsertGenerator.cs, Line 95) "value" never gets, remains null and causes the null reference exception.

I'm not sure what the side-effects will be, but one fix could be to add 
(type == PrimitiveTypeKind.Decimal) to the if.

If that is not an option, I wouldn't care if my uint64 was mapped to an int64.  Perhaps there is a way for me to override this behavior without a recompile (editing the ProviderManifest.xml?)

Finally, if no fix can be found the function should at least throw an "unsupported" exception to let the user know that this type of primary key is not supported.

Hello Andy.

I'm using the following entity model and the exception is not present:
  public class testtbl
  {
    [Key]
    public long testID { get; set; }
    public string test { get; set; }
  }

As you can see I'm using data type "long" as equivalent for the "bigint" data type used in the table creation. I'm able to insert, read and update data without problems, and I have the same result using "Int64". 

Why I'm not using "UInt"? Because as you know EF discard that data type as a PK because there is no equivalent in MS SqlServer, and your program will fail with the following exception when EF try to map the Entity Model with the Table Schema:
  EntityType 'testtbl' has no key defined. Define the key for this EntityType.
  EntityType: EntitySet 'testtbls' is based on type 'testtbl' that has no keys defined.

If you can share with us the project or complete code that you are using will be great, and we can have more details and info to try to reproduce the issue.

Thanks for your time.

Hi Francisco,

In order to see the bug happen, you'll need to use the "database first" style of Entity Framework instead of "code first."  I've sent my sample console application that I used to verify the bug via sftp.  The file should be called "mysql-bug-data-71242.zip."  You'll need to fix up the connection string to run and probably fix up the references.  Also you will need to create the database as defined in the schema on my original post.

Please let me know if you run into any problems with the code.

Thanks!
Andy

Hello Andy.

Thanks for your quick reply, I'll test using DB First, I created the DB and the schema and then use Code First to use the schema created previously, I misunderstood the "model file" part in your post, I create a class rather than edmx file, sorry.

By the way, did you already uploaded the zip file? I can't find it in the sftp server.

Thanks for your time.
Thanks for your time.

Hi Francisco,

Not sure why the stfp file never showed up.  I deleted out some of the binaries so it would fit under the 3mb limit and uploaded it again on the website instead.

Oh and by the way - I found that I could just edit the edmx file and change it to a "bigint" instead of a "ubigint."  Since I don't care about preserving the unsigned part that works for me. (And I am afraid to change the database for fear of breaking existing code.) The only downside is that I will likely need to remember to do this again if when I refresh the edmx file.

However, I still think it would be good to throw a better exception rather than the strange behavior we have right now - or if you can support autoincrement for the "decimal" type that'll be even better.

Hello Andy.

We are glad that you found a workaround for your issue, and thank you for sharing it.
Nevertheless, I'll check your code and look for a fix for the issue that you are reporting.

Thanks for your time.

Hi,
this problem affects also me, db first with bigint unsigned, primary autoincrement.
Any chance to resolve it, on 6.8.4 release, please ?
Thanks

Hello Salvo.

The fix for this bug will be delivered soon.

Thank'you.

Added the following entry into the Connector/Net 6.6.7, 6.7.5, and 6.8.4 changelogs:

"Entity Framework threw a NullReferenceException on insertion of a record into tables that had an auto-increment, unsigned, bigint primary key."

Don't know if it's related, but with the following table I'm also getting NullReferenceException when trying to insert.

CREATE TABLE `tb_s57_pendingupdates` (
  `token_id` int(11) NOT NULL DEFAULT '0',
  `pending_since` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_type` tinyint(4) NOT NULL DEFAULT '0',
  `terminal_id` int(11) NOT NULL DEFAULT '0',
  `is_urgent` tinyint(4) NOT NULL DEFAULT '0',
  `update_state` tinyint(4) NOT NULL DEFAULT '0',
  `description` varchar(60) NOT NULL DEFAULT '',
  `plu_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`token_id`,`update_type`,`terminal_id`,`plu_id`,`pending_since`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;