MySQL - Entity : The value for column 'IsPrimaryKey' in table 'TableDetails' is DBNull

I am using Visual Studio 2013 with Entity Framework 5 and MySQL Server 5.7.9.

When trying to create a Model from the database (or 'Update Model From Database') the following message appears:

> 'System.Data.StrongTypingException: The value for column > 'IsPrimaryKey' in table 'TableDetails' is DBNull . ---> System.InvalidCastException: Specified cast is not valid.

I know that this question has been asked before, but i haven't find any solution. Also i don't have the option of downgrading to MySQL 5.6.

The problem occurs even for a simple table.

The sample table

CREATE TABLE new_table
(
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(45) NOT NULL,
  PRIMARY KEY (id)
) 
ENGINE = InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

If the table consists only from the Primary Key then the model is being created as it should.

EDIT: If i make both the fields PRIMARY Keys the model is being created without any errors.

Does anyone have any idea about this?

Kind Regards.

The full error stack:

> Unable to generate the model because of the following exception: > 'System.Data.StrongTypingException: The value for column > 'IsPrimaryKey' in table 'TableDetails' is DBNull. ---> > System.InvalidCastException: Specified cast is not valid. at > Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.TableDetailsRow.get_IsPrimaryKey() > --- End of inner exception stack trace --- at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.TableDetailsRow.get_IsPrimaryKey() > at > Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateProperties(IList1 > columns, IList1 errors, List1& keyColumns, List1& excludedColumns, > List1& invalidKeyTypeColumns) at > Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntityType(IList1 > columns, Boolean& needsDefiningQuery) at > Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntitySets(IEnumerable1 > tableDetailsRows, EntityRegister entityRegister, IList1 > entitySetsForReadOnlyEntityTypes, DbObjectType objectType) at > Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntitySets(IEnumerable1 > tableDetailsRowsForTables, IEnumerable1 tableDetailsRowsForViews, > EntityRegister entityRegister) at > Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.Build(StoreSchemaDetails > storeSchemaDetails) at > Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.CreateStoreModel() > at > Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List1 > errors) at > Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String > storeModelNamespace, List1 errors) at > Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(EdmxHelper > edmxHelper)'. Loading metadata from the database took > 00:00:00.5856317.

Entity Framework (version 6.1.3) and MySQL Server (>= 5.7.6)

One way to resolve the issue is,

1. Open Services (services.msc) and restart MySQL57 service.
2. Execute the following commands in MySQL.
   use <<database name>>;
   set global optimizer_switch='derived_merge=OFF';
3. Update the .edmx.

It's a late reply. But hope it will help somebody.

Thanks.

This is a know bug: http://bugs.mysql.com/bug.php?id=79163

Run the command on mysql console:

> set global optimizer_switch='derived_merge=off'

OR

> set @@optimizer_switch='derived_merge=OFF'

(this one worked for me)

Verify that the change is effective with this command:

> SELECT @@optimizer_switch\G

https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html

Every answer above works fine, but when the server or dev-machine is restarted, we need to make all steps again and again. To solve completely, we can put this attibute 'derived_merge=OFF' in the mysql configuration. Ok, to do that, you need to access your MySQL server with de MySQL Workbench, and open "Option File" > Select Performance tab. In Optimizer group, look for optimizer_switch, click the checkbox to enable a field, and put 'derived_merge=OFF'. Restart your server in services.msc and voilá!

The workaround SET GLOBAL optimizer_switch='derived_merge=off';

The following sql worked for me:

use adventureworks;
set global optimizer_switch='derived_merge=off';
set optimizer_switch='derived_merge=off';

select @@optimizer_switch;
select @@GLOBAL.optimizer_switch;

My MySQL version is 5.7.15 and I used "EF Designer From Database" option from Visual Studio 2015.

I hope it helps others :)

The value for column 'IsPrimaryKey' in table 'TableDetails' is DBNull

That's what happens when you try to use MySql among with Entity Framework.

I have solved this way:

Open MySQL 5.7 Command Line Client, type your password, press enter and then type:

set global optimizer_switch='derived_merge=off';
set optimizer_switch='derived_merge=off';

select @@optimizer_switch;
select @@GLOBAL.optimizer_switch;

Reboot Visual Studio you're good to go, I've tried with VS 2013 and VS 2017, and worked in both!

We've encountered same error. But,I think this is MySQL's server problem.

status:

• EF5
• MySQL VS Tools 1.2.5
• VS community 2015
• C#
• from Database create EF Designer(I'm using japanese edition,so menu name is my english...)

result:

• MySQL 5.7.9 => error.
• MySQL 5.6.26 => no error.

In my case:

Modules Used:

Entity Framework 6 MySql Data Connector 6.9.9 MySQL VS Tools 1.2.6 EF6 Tools for VS2015

I have to do this => SET GLOBAL optimizer_switch='derived_merge=off'; But if loggin with "root" user the error continues. When loggin with "common" user, it create the model OK.

Run this script, it worked perfectly for me while using MySql.Data.Entity nugget package

use YourDB;
   set global optimizer_switch='derived_merge=OFF';

From today, this is a verified MySql Community Server 5.7.9 bug with ID 79163.

As a workaround, i reshaped the application to use Code First practise

> (ADO.NET Entity Data Object -> Empty Code First model)

and manually added all the objects and their properties.

Modules Used:

• Entity Framework 6
• MySql Data Connector 6.8.9
• MySQL VS Tools 1.2.5
• EF6 Tools for VS2013

i have found a good workaround for this type of error.

1. Solution ( database first) Instead of trying to update the existing model in the .edmx File, right-click one of your entities in edmx and choose -> go to modelbrowser ( a new register appears in the project map explorer). Now choose the entity you want to update , right-click and do the update. In my case it worked every time without the need to switch anything off in mysql. But it may happen that you will be forced to change some relationships manually. If anything gets wrong here ( in my case there are no problems with non fk / pk key. If you delete a fk and try to order all relationsships by yourself your db might be dropped. But this never occures if you just update not primary / foreign key attributes or just adding a new table.

EDIT: This Solution works great for deleting, updating and adding new relations. It causes problems when updates are performed on fk /pk attributes!

If your table has been dropped so just create a new one with same preferences.

2. Solution (database first, when pk / fk keys must be updated) drop the table and create a new one. This takes 3 min all together

I hope i could help a little bit, this bug drove me crazy the past 2 weeks !

p.s. i did never experienced this type of bug until i have overwritten! a foreign key in an existing relation in mysql. Then i tried to update the relation with entity framework (database first) and since then on the dbNullBug appeared. I was able to manage my entities manually by adding and removing existing attributes but it was really exhausting. The solution 1 works well for me and if i need to update foreign key i just drop the existing Model and create a new one.