| Bug #79163 | VS Entity Framework DataBase First returns Error | ||
|---|---|---|---|
| Submitted: | 6 Nov 2015 22:32 | Modified: | 23 Feb 2018 20:31 |
| Reporter: | Konstantinos Magarisiotis | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 6.9.8 | OS: | Windows (8.1) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | entity framework, mysql server 5.7.9 | ||
[6 Nov 2015 22:41]
Konstantinos Magarisiotis
Problem with VS and entity.
[6 Nov 2015 22:42]
Konstantinos Magarisiotis
Problem with Entity Framework.
[9 Nov 2015 10:32]
Chiranjeevi Battula
Hello Konstantinos Magarisiotis, Thank you for the bug report and test case. Verified this behavior on Visual Studio 2013 (C#.Net) with MySQL Connector/Net 6.9.8 and MySQL 5.7.9 version. Thanks, Chiranjeevi
[9 Nov 2015 10:32]
Chiranjeevi Battula
screenshot.
Attachment: 79163.PNG (image/png, text), 113.45 KiB.
[16 Nov 2015 5:21]
Peter Hanna
Urgently need a fix for this please! I am facing the same problem MySql.Data.Entity.EF6 6.9.8.0 MySql.Data 6.9.8.0 mysql-for-visualstudio-1.2.5 Visual Studio 2013 with Update 5 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(IList`1 columns, IList`1 errors, List`1& keyColumns, List`1& excludedColumns, List`1& invalidKeyTypeColumns) at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntityType(IList`1 columns, Boolean& needsDefiningQuery) at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntitySets(IEnumerable`1 tableDetailsRows, EntityRegister entityRegister, IList`1 entitySetsForReadOnlyEntityTypes, DbObjectType objectType) at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntitySets(IEnumerable`1 tableDetailsRowsForTables, IEnumerable`1 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(List`1 errors) at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, ModelBuilderSettings settings, List`1 errors) at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(ModelBuilderSettings settings, IVsUtils vsUtils, ModelBuilderEngineHostContext hostContext)'.
[16 Nov 2015 9:42]
Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=79289 marked as duplicate of this one.
[17 Nov 2015 0:09]
Peter Hanna
If it helps in resolution, this problem only occurs when paired with MySQL 5.7.9.1 (for me at least). So a bug is definitely present in MySQL 5.7.9.1 The same install below works fine when paired with mysql-5.6.27-winx64. MySql.Data.Entity.EF6 6.9.8.0 MySql.Data 6.9.8.0 mysql-for-visualstudio-1.2.5 Visual Studio 2013 with Update 5
[18 Nov 2015 21:34]
Josh Engler
I am also having the same issue specifically with MySQL Server 5.7.9 However, it does work on MySQL Server 5.6.26 Also using: MySql.Data.Entity 6.9.8 MySql.Data 6.9.8 MySQL for Visual Studio 1.2.5 EntityFramework 6.1.3 Visual Studio 2015
[20 Nov 2015 14:25]
Noman Khan
Is there any workaround? When will the fix be available?
[28 Dec 2015 1:20]
Younes CHEIKH
The bug wasn't fixed in the version mysql 5.7.10 winx64 I am having the same problem using this mysql version with Visual Studio 2013 Update 5 & Entity Framework 6
[1 Jan 2016 15:27]
Noman Khan
Try the following workaround.... Run the following command on the MySQL DB and then try if this works. set global optimizer_switch='derived_merge=off'
[5 Jan 2016 11:56]
Arnd Gebhard
The workaround (set global optimizer_switch='derived_merge=off') was successful at my place.
[5 Jan 2016 12:45]
William L Brigham
Although the workaround worked to add and update entities, the new JSON type is ignored and doesn't show on my entities. That is, when i have a JSON type and add the table to the entities, the JSON column doesn't exist in the entities.
[5 Jan 2016 13:19]
Arnd Gebhard
I took the generated model and tried to generate a controller via 'MySQL New MVC Item' --> Crash of Visual Studio! (Unfort. no message or further info) Problem of Model? 'MySQL New MVC Item' Template? Visual Studio? Me??
[15 Jan 2016 12:32]
Darren Zai
Running VS 2015 Community MySQL Server 5.7.10 EF6 was having the same problem, the workaround (set global optimizer_switch='derived_merge=off') fixed the problem for me.
[18 Jan 2016 12:58]
Martijn Mol
(set global optimizer_switch='derived_merge=off') did the trick for me as well
[26 Jan 2016 14:10]
Stefano Gualandi
This fix the bug for me too (Visual Studio 2015 Community): (set global optimizer_switch='derived_merge=off')
[5 Feb 2016 2:43]
Diego Toro
JSON column is ignored on Entity Model, is planned to support new JSON type using MySql connector and Entity Framework ?
[10 Mar 2016 5:45]
Gustavo Garcia
I´m also having the problem creating the MVC Item 'MySQL New MVC Item' --> Crash of Visual Studio! When it shows me the dialog when I choose the Model the dialog disapear Problem of Model? 'MySQL New MVC Item' Template? Visual Studio? Me??
[4 May 2016 3:57]
Jason Skowronek
Both of these are necessary: set global optimizer_switch='derived_merge=off' set optimizer_switch='derived_merge=off'
[18 May 2016 7:38]
Shen Chen
A workaround based on oracle student works on VS2015 + EF6.1.3 + MySql 5.7.12 use <<database name>>; set global optimizer_switch='derived_merge=off'; set optimizer_switch='derived_merge=off'; If you don't see results go check if both variables are correctly set by: select @@optimizer_switch; select @@GLOBAL.optimizer_switch;
[11 Jun 2016 18:18]
MIki Kola
set global optimizer_switch='derived_merge=off' xxx set optimizer_switch='derived_merge=off' From my experience only one is necessary, the global one. After executing command Visual Studio restart is sometimes necessary to force creating new connection for the wizard, I guess. :/
[22 Aug 2016 7:38]
Keyur Patel
I am having the same problem: Unable to generate the model because of the following exception: 'System.Data.StrongTypingException: The value for column 'IsPrimaryKey' in table 'TableDetails' is DBNull. I am using mysql 5.7.13 with Entity Framework 6 (tried with EF 5 also) on Visual Studio. Does anyone know a solution other than these: - change mysql version to 5.6 (I am using web hosting and cannot change mysql version) - set global optimizer_switch='derived_merge=off' (I don't have SUPER privilege on the web hosting phpmyadmin) - set @@optimizer_switch='derived_merge=off' (it will change back to on, and I cannot run this query from VS, Entity Framework won't allow it) If anyone has a workaround other than these above ones, it is an urgent problem that I need to find a solution for.
[14 Sep 2016 14:46]
Nir Amber
Any progress on this? its been almost a year since it has been reported and still not fixed
[14 Sep 2016 18:26]
Javier Treviño
Posted by developer: Reassigning to the Connector/NET team since this looks like a problem in the EF library shipped with the connector.
[1 Nov 2016 19:13]
Mladen Mihajlovic
I just hit this as well. Is there a url to the Connector teams bug?
[8 Dec 2016 8:45]
Somebody Somewhere
Bug still exist. Thanks for hint to disable derived_merger and for note, that Visual Studio restart is required.
[9 Dec 2016 19:59]
Nicholas Cornish
https://bugs.mysql.com/bug.php?id=79163#c445530 worked for me
[10 Dec 2016 19:52]
James W
Seems restarting VS doesn't work while SQL Bulder is still open. After applying the work around make sure to close SQL Bulder before restarting VS.
[11 Dec 2016 5:10]
James W
Sorry, not "SQL Builder", my bad. Seems restarting VS doesn't work while MySQL Workbench is still open. After applying the work around make sure to close Workbench before restarting VS.
[8 Feb 2017 9:29]
Mohamed ARRIOUACH
This fix the bug for me too (Visual Studio 2015 Community): (set global optimizer_switch='derived_merge=off')
[7 May 2017 3:31]
Jose Herreros Alvarez
This fix the bug for me too: set global optimizer_switch='derived_merge=off' MySql Server 5.7.17 Visual Studio 2015
[10 Jun 2017 23:03]
Royce Aquino
When is this going to be fixed?
[15 Jul 2017 23:01]
Dimitriy T
The mentioned workaround does not work for me. I use VS 2017 Community and MySQL 5.7.18.1.
[15 Jul 2017 23:06]
Dimitriy T
BUT This solution worked for me: 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. Source: https://medium.com/@alonbukai/entity-framework-error-700c2b4ddc8a
[16 Jul 2017 2:16]
James W
That is correct. It seems that the main thing to do is restart the service FIRST, then apply the SQL workaround, and try again. I found the same steps worked for me in the past. I wish I could install and test with the new MySQL/.Net Connector, but the MySQL for Visual Studio development release only supports 6.9.9 still at the moment (obviously these developments are not in sync), so I can't open an edmx file to test it.
[26 Jul 2017 17:01]
Andrew Carney
The solution proposed by Dimitriy did not work for me: Visual Studio 2017 Community MySQL 5.7.18 Server dotnet connector 6.9.9 mysql for visual studio 1.2.7
[10 Aug 2017 2:59]
Brayan Holguin
set global optimizer_switch='derived_merge=off' esta linea soluciono el problema en mi caso
[22 Feb 2018 21:31]
Daniel Valdez
Posted by developer: CASE clause was causing that the server takes NULL value instead of "0", therefore the error. Now the CASE clause takes into consideration this kind of statements in order to set the correct value.
[22 Feb 2018 23:39]
Daniel Valdez
Posted by developer: CASE clause was causing that the server takes NULL value instead of "0", therefore the error. Now the CASE clause takes into consideration this kind of statements in order to set the correct value.
[23 Feb 2018 5:16]
Jan D
Will that be fixed with a new MySQL Connector or with a new MySQL Server version?
[23 Feb 2018 20:31]
Christine Cole
Posted by developer: Fixed as of the upcoming MySQL Connector/NET 6.9.12 release, and here's the changelog entry: Attempting to generate an Entity Framework model from a MySQL 5.7 database using either EF5 or EF6 produced an exception that prevented the operation from generating the expected model. Thank you for the bug report.
[11 Apr 2018 20:31]
Carlos Emmons
Hi, does anyone knows when will this version be ready to download? I'm having the same issue described here.
[11 Apr 2018 21:05]
Javier Treviño
Posted by developer: We are not allowed to disclose release dates, but it's going to be released soon. Please monitor forums and social media where we make release announcements.
Description: MySQL Server 5.7.9 doesn't work with Entity Framework (both versions 5 and 6). When trying to create a model from a database it returns : 'System.Data.StrongTypingException: The value for column 'IsPrimaryKey' in table 'TableDetails' is DBNull. ---> System.InvalidCastException: Specified cast is not valid. This doesn't occur with previous MySQL Versions. How to repeat: 1. Create a sample database with a sample table: CREATE TABLE `new_table` ( `id` bigint(20) unsigned NOT NULL, `name` varchar(80), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2. Create a sample VS13 WinForm Application 3. Add Entity Framework 5 / 6 and connectors 4. Create model from database 5. Error occurs.