Entity Framework with MySQL - Timeout Expired while Generating Model

Question

I've constructed a database in MySQL and I am attempting to map it out with Entity Framework, but I start running into "GenerateSSDLException"s whenever I try to add more than about 20 tables to the EF context.

An exception of type 'Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine+GenerateSSDLException' occurred while attempting to update from the database. The exception message is: 'An error occurred while executing the command definition. See the inner exception for details.'

Fatal error encountered during command execution.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

There's nothing special about the affected tables, and it's never the same table(s), it's just that after a certain (unspecific) number of tables have been added, the context can no longer be updated without the "Timeout expired" error. Sometimes it's only one table left over, and sometimes it's three; results are pretty unpredictable. Furthermore, the variance in the number of tables which can be added before the error indicates to me that perhaps the problem lies in the size of the query being generated to update the context which includes both the existing table definitions, and also the new tables that are being added to it. Essentially, the SQL query is getting too large and it's failing to execute for some reason.

If I generate the model with EdmGen2 it works without any errors, but the generated EDMX file cannot be updated within Visual Studio without producing the aforementioned exception.

In all likelihood the source of this problem lies in the tool within Visual Studio given that EdmGen2 works fine, but I'm hoping that perhaps others could offer some advice on how to approach this very unique issue, because it seems like I'm not the only person experiencing it.

One suggestion a colleague offered was maintaining two separate EBMX files with some table crossover, but that seems like a pretty ugly fix in my opinion. I suppose this is what I get for trying to use "new technology". :(

Answer 1

Try dotConnect for MySQL with Entity Developer.
We have made some improvements in the model generation process in our tools. You can add the Devart Entity Model to your project, which is similar to the ADO.NET Entity Framework model, but has some improvements and does not have the timeout problem.

Answer 2

Two possibilities spring to mind:

First is that it is EF version 1 (which shipped with .NET 3.5 SP 1). See this and this.

The other is that this feels roughly like the same symptoms one got with SQL Server and pre-ODBC drivers (circa 1991) where the wrong type of call was used: one kind is used with queries returning results (select), and the other for statements—not returning a result (create table). Eventually the connection became hopelessly unsynchronized trying to match up SELECT results to the corresponding query. (In those days, the Blue Screen Of Death didn't exist: the computer tended to reboot voluntarily instead.)

I wonder if the tool is confusing the connection mode in the variety of operations being performed: creating tables, verifying the created structure, adding a new column, populating rows, and verifying or validating the row content after being filled. If this is the cause, then it might be averted by being "purer" about the sequence of operations: do nothing but complete table creates one after another—that is, do nothing which would cause it to create a table then alter table to add new columns.

Answer 3

Check out:

http://efvote.wufoo.com/forms/ado-net-entity-framework-vote-of-no-confidence/

Oops, just realised that this link was already posted! sorry

I would also strongly consider "One suggestion a colleague offered was maintaining two separate EBMX files with some table crossover"

It may be ugly, but it should work!

Answer 4

I just had headache on this problem in the whole afternoon. However, I found the solution that you can just add a statement in app.config or web.config where your EF desinger connection exists as 'Default Command Timeout=300000;'. The problem's gone.

Answer 5

I had exactly the same problem. The default command timeout did'nt fix it either. After updating to the latest mySql .Net connctor it worked!

Answer 6

You guys are weak not explaining how to fix the problem easily:

1. Delete all your Data Connections
2. Download the latest MySql Connector (6.3.x)
3. Open Visual Studio >Sever Explorer>Right Click "Data Connections" >Add Connection
4. Choose MySQL Database provider
5. Enter connection details
6. Click on "Advance"
7. Find Connect Time out and make it something like 30,000
8. Find Default Command Timeout and make it something like 30,000

Save everything and then try and update your EF model again. I tested this with EF 4.0 and Vs2010 so I know it works.

Answer 7

Well, that all sounds very easy! I still have an issue doing so as the connection details dialog just closes down immediately as a single character is entered! Any suggestions on why that is happening?

Microsoft is definitely NOT trying to make it easy using anything but SQL Server... Or is it a MySQL issue? Hmmmm....

Can't wait to see what issues i might run into when trying to deploy my solution to my webhotel!! Hahahaha...

Any info on the dialog shutting down is GREATLY appreciated!!!

Thanks, D

Answer 8

The above advice is not correct.

Default Command Timeout is the only connection string parameter you need to change. Connect Time just regulates the amount of time to wait to get a connection in the first place; that is not your problem.

Default Command Timeout appears to have no effect in the connection string with Connector/Net 6.3.4. I think this is a bug in Connector/Net, and I filed a bug report with Oracle.

The only way I got around this was to change my ObjectContext object's CommandTimeout property to something other than null. If it's null, it's supposed to use the value in the "underlying provider" per MSDN. If not null, it is the authoritative value for number of seconds before a timeout.

For example:

var context = new CitationData.de_rawEntities();
context.CommandTimeout = 180;