Timeout error on running Scaffold-DbContext

Question

We use data-first approach, and use Scaffol-Dbcontext to reverse-engineer the data context.

Database is MySQL and we use Pomelo as provider.

It has been working fine for 283 database versions, but now the command crashes with the error:

The Command Timeout expired before the operation completed.

This is the command I use:

Scaffold-DbContext "server=localhost;userid=user;pwd=pass;port=3306;database=db_name;sslmode=none;" "Pomelo.EntityFrameworkCore.MySql" -d -o "OutputFolder" -v -f -context "ContextName"

And this is the full stack trace:

MySql.Data.MySqlClient.MySqlException (0x80004005): The Command Timeout expired before the operation completed. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): The Command Timeout expired before the operation completed.
   at MySqlConnector.Protocol.Serialization.BufferedByteReader.ReadBytesAsync(IByteHandler byteHandler, ArraySegment`1 buffer, Int32 totalBytesToRead, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Protocol\Serialization\BufferedByteReader.cs:line 36
   at MySqlConnector.Protocol.Serialization.ProtocolUtility.ReadPacketAsync(BufferedByteReader bufferedByteReader, IByteHandler byteHandler, Func`1 getNextSequenceNumber, ProtocolErrorBehavior protocolErrorBehavior, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Protocol\Serialization\ProtocolUtility.cs:line 406
   at MySqlConnector.Protocol.Serialization.ProtocolUtility.DoReadPayloadAsync(BufferedByteReader bufferedByteReader, IByteHandler byteHandler, Func`1 getNextSequenceNumber, ArraySegmentHolder`1 previousPayloads, ProtocolErrorBehavior protocolErrorBehavior, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Protocol\Serialization\ProtocolUtility.cs:line 459
   at MySqlConnector.Protocol.Serialization.StandardPayloadHandler.ReadPayloadAsync(ArraySegmentHolder`1 cache, ProtocolErrorBehavior protocolErrorBehavior, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Protocol\Serialization\StandardPayloadHandler.cs:line 37
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 645
--- End of stack trace from previous location where exception was thrown ---
   at MySqlConnector.Core.ServerSession.TryAsyncContinuation(Task`1 task) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 1230
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 42
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 74
   at MySql.Data.MySqlClient.MySqlDataReader.ReadFirstResultSetAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 299
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(MySqlCommand command, CommandBehavior behavior, ResultSetProtocol resultSetProtocol, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 284
   at MySqlConnector.Core.TextCommandExecutor.ExecuteReaderAsync(String commandText, MySqlParameterCollection parameterCollection, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 37
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 252
   at Pomelo.EntityFrameworkCore.MySql.Scaffolding.Internal.MySqlDatabaseModelFactory.GetConstraints()
   at Pomelo.EntityFrameworkCore.MySql.Scaffolding.Internal.MySqlDatabaseModelFactory.Create(DbConnection connection, IEnumerable`1 tables, IEnumerable`1 schemas)
   at Pomelo.EntityFrameworkCore.MySql.Scaffolding.Internal.MySqlDatabaseModelFactory.Create(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, String namespace, String language, String contextDir, String contextName, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

Related package versions:

• Microsoft.AspNetCore.All 2.1.4
• Pomelo.EntityFrameworkCore.MySql 2.1.2

It's important to note that this only happens on my computer (VS Professional 2017 v15.8.5). The other coworkers can run the same command, with a backup of my database, without any error.

Can you point me in the right direction? So far I have tested (without success):

• Update all packages.
• Update Visual Studio
• Delete nuget folder and restore packages
• Tried with several databases (some work, some don't, but not sure what's the difference between them)
• Added the "Connect timeout=300" property to the connection string (same error, but it takes 5 minutes)

Answer 1

In .NET 6 you can't add default command timeout but you can use command timeout
I resolved this by running into this post dotnet sqlclient github

As mentioned in the link above, you can set the timeout values both to 0 to completely disable timeouts.
I had to do this because the number of tables of the database was over 1000 and it would make it really complicated to scaffold the db schema by schema.

Answer 2

Next time, try to add

1. Connection Timeout=300 (Time in seconds)
2. default command timeout=300 (Time in seconds)

try the following connection string:

dotnet ef dbcontext scaffold "server=simycotest.ddns.net;userid=xxxxx;password=xxxxx;database=xxxxx;Connection Timeout=300;default command timeout=300" "Pomelo.EntityFrameworkCore.MySql" -o .\DbModels -f

For more info about connection string in MySql

Answer 3

Sorry this answer doesn't solve the problem as expected, but i needed this working for monday, so I went down the cowards road:

Uninstalling everything related to Visual Studio and MySQL and reinstall again makes everything work fine.

It also decreased the time needed by Scaffold-DbContext to run from ~3 minutes to ~10 seconds.

Just for the record, my last test proved that importing only 1 table with more than 5 FK constraints, the process fails. Removing ANY of the FK makes the process work...

My intuition tells me some corrupted package somewhere was causing this.