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

Question

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.

Try changing int(11) to BIGINT or just INT. .Net will map the mysql types to .Net data types, and in .Net there is no INT(11), that could be breaking entity framework. Also upgrade to EF6 if you can, it has better mysql support. — Ryan Mann, Nov 06 '15 at 20:53
Chaning the ID's type makes no difference. If i leave only one column (even if it is INT(10) ), the model is created sucesfully. Regarding EF6 - i have problems in using it. VS complains about not finding the appropriate connector. — magarisi, Nov 06 '15 at 21:23
Hi.. please refer here : https://bugs.mysql.com/bug.php?id=79163 A workaround has been provided, although i haven't tested it yet. Quote: Run the following command on the MySQL DB. set global optimizer_switch='derived_merge=off' — magarisi, Jan 10 '16 at 16:59

score 166 · Accepted Answer · edited Sep 07 '16 at 20:32

166

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.

edited Sep 07 '16 at 20:32

Drew

24,851
10
43
78

answered Feb 16 '16 at 02:22

Saravanan Sachi

2,572
5
33
42

This fixed the error I was receiving, I updated any new tables I wanted to add and now the tables originally there are erroring: > Entity type 'tbl_name' is not mapped any suggestions? – Jaquarh Feb 16 '16 at 09:05
@KyleE4K, sorry I never faced such issue. When I update the .edmx file, the new tables and modified tables get updated properly. – Saravanan Sachi Feb 17 '16 at 01:29
I had to delete them all and re-update them! That fixed the mapping issue :) – Jaquarh Feb 17 '16 at 08:43
5

Hello..this fixed my issue..But it would be nice if you could also explain your answer for why this worked. – Abhishek Mar 14 '16 at 11:24
Great it works also when creating the Model from the BD.... I agree with @Abhishek, will be nice to understend what happend under the hood. – NFRiaCowboy Sep 19 '16 at 11:47
2

Sorry, I code but not an expert on internal process. I was trying many ways and this method worked. I would appreciate any explanation on this method. Thanks. – Saravanan Sachi Sep 20 '16 at 00:02
3

This worked for me in Visual Studio 2017 when trying to create the model from database. – noCodeMonkeys Apr 12 '17 at 14:50
Works with Visual Studio 2015, MSQL 5.7.9, EF 6 and .NET Framework 4.5. – Core May 03 '17 at 09:17
I had to reboot Visual Studio before updating the EDMX. But it is working! – fharreau Aug 10 '17 at 08:42
I am sure this will work. But Kindly tell me what is the impact of this script in whole database. Would it work fine ? – Moeez Feb 01 '18 at 10:43

score 23 · Answer 2 · answered Feb 05 '16 at 04:25

23

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

answered Feb 05 '16 at 04:25

martin

376
2
4

2

It is VERY important to run this from the MySQL Console and not the Workbench. I speak from hours of experience. :( – David Bachmann Jeppesen Feb 18 '17 at 19:56
Worked from Workbench 6.3 against MySQL v5.7.18 – Yumi Koizumi Apr 28 '17 at 18:24
1

Worked perfectly but was reset every time I restarted my computer. Took your solution and entered Options File in the Workbench and added "derived_merge=off" in the optimizer_switch field in the Performance tab. Works after restart now. – m4ttsson Jun 29 '17 at 06:54

score 9 · Answer 3 · answered Apr 01 '17 at 15:56

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á!

score 5 · Answer 4 · answered Jan 16 '16 at 11:47

5

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

answered Jan 16 '16 at 11:47

Caner

813
1
12
26

score 5 · Answer 5 · answered Sep 12 '16 at 22:25

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 :)

score 2 · Answer 6 · edited Jul 31 '17 at 16:12

2

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!

edited Jul 31 '17 at 16:12

brduca

3,573
2
22
30

answered Jul 31 '17 at 13:58

FelipeFalanque

63
6

Felipe.. 1. You provided the same answer as many others have. 2. You write in spanish (?) and most of us cannot understand. – magarisi Jul 31 '17 at 16:11

score 1 · Answer 7 · answered Nov 24 '15 at 05:44

1

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.

answered Nov 24 '15 at 05:44

modeverv

54
4

Yes, it is a MySQL error. See my post below. I have declared it as a bug. – magarisi Nov 24 '15 at 17:15
To prevent any update to Mysql Server, disable automatic catalog update in the Community Installer settings. – Gaʀʀʏ Apr 24 '16 at 22:31

score 1 · Answer 8 · answered Jul 14 '16 at 16:06

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.

Frank Odoom · Answer 9 · 2017-03-07T12:52:53.203

1

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

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

edited Mar 07 '17 at 12:52

answered Mar 07 '17 at 12:47

Frank Odoom

1,545
18
19

score 0 · Answer 10 · answered Nov 09 '15 at 17:27

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_zaski08 · Answer 11 · 2016-09-11T20:08:56.230

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.

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

11 Answers11

status:

result:

Linked