search

PomeloFoundation / Pomelo.EntityFrameworkCore.MySql

Entity Framework Core provider for MySQL and MariaDB built on top of MySqlConnector
MIT License
2.68k stars 382 forks source link

Scaffolding stops early and hangs #310

Closed gingters closed 6 years ago

gingters commented 7 years ago

Steps to reproduce

I want to update my ef core model on my local dev database. I'm issuing this command:

dotnet ef dbcontext scaffold "server=localhost;database=ruinsdb_dev;user id=ruinsdb;password=ruinsdb;" "Pomelo.EntityFrameworkCore.MySql" --context RuinsContext --output-dir DatabaseModels --force -verbose

The database structure for the database I want to re-engineer to a db model is attached in this file: ruinsdb.sql.txt

The issue

The process starts, but then hangs. I don't see any changes in process memory anymore, also the dotnet.exe process idles at < 0.01 CPU.

This is, what is printed on the console up to the point where the hanging occurs:

D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web (feature/poi)
λ dotnet ef dbcontext scaffold "server=localhost;database=ruinsdb_dev;user id=ruinsdb;password=ruinsdb;" "Pomelo.EntityFrameworkCore.MySql" --context RuinsContext --output-dir DatabaseModels --force --verbose
Using project 'D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\CanonnApi.Web.csproj'.
Using startup project 'D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\CanonnApi.Web.csproj'.
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\sebas\AppData\Local\Temp\tmpBB80.tmp /verbosity:quiet /nologo D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\CanonnApi.Web.csproj
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\sebas\AppData\Local\Temp\tmpBD27.tmp /verbosity:quiet /nologo D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\CanonnApi.Web.csproj
dotnet build D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\CanonnApi.Web.csproj /verbosity:quiet /nologo

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:01.96
dotnet exec --depsfile D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\bin\Debug\netcoreapp1.1\CanonnApi.Web.deps.json --additionalprobingpath C:\Users\sebas\.nuget\packages --runtimeconfig D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\bin\Debug\netcoreapp1.1\CanonnApi.Web.runtimeconfig.json C:\Users\sebas\.nuget\packages\microsoft.entityframeworkcore.tools.dotnet\1.0.0\tools\netcoreapp1.0\ef.dll dbcontext scaffold "server=localhost;database=ruinsdb_dev;user id=ruinsdb;password=ruinsdb;" Pomelo.EntityFrameworkCore.MySql --context RuinsContext --output-dir DatabaseModels --force --assembly D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\bin\Debug\netcoreapp1.1\CanonnApi.Web.dll --startup-assembly D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\bin\Debug\netcoreapp1.1\CanonnApi.Web.dll --project-dir D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\ --content-root D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\ --data-dir D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\bin\Debug\netcoreapp1.1\ --verbose --root-namespace CanonnApi.Web
Using assembly 'CanonnApi.Web'.
Using startup assembly 'CanonnApi.Web'.
Using application base 'D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\bin\Debug\netcoreapp1.1'.
Using content root 'D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\'.
Using root namespace 'CanonnApi.Web'.
Using project directory 'D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\'.
Using data directory 'D:\Dev\sg\CanonnApi\CanonnApi.Backend\CanonnApi.Web\bin\Debug\netcoreapp1.1\'.

Further technical details

MySQL version: 10.2.6-MariaDB Operating system: Windows 10 Pro x64 Pomelo.EntityFrameworkCore.MySql version: 1.1.2

mguinness commented 7 years ago

This could be an issue with platform, tooling or config and therefore difficult to repo. You could try using the general query log to see if any statements from the scaffold are being executed. If you don't, then try using the CodeTrack profiler to try to see where the process is stalling which was used in https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/263#issuecomment-305906885.

gingters commented 7 years ago

No statements are being executed. Here I attached the codetrack trace file: 79117e2346c84fe1a6281e1e0b50d82e.zip

I am afraid I don't really understand whats (or whats not) happenening there.

mguinness commented 7 years ago

One of the first things the library does when scaffolding is to run SHOW FULL TABLES against the database so no SQL activity shows it's stalling early.

Your trace is showing no methods, can you select Tracing instead of Sampling profiling mode? You don't need to run it for long, say a minute after it stalls.

The only other suggestion I can make at this stage is to also use the --table option and run a scaffold for an individual table and see if that completes.

gingters commented 7 years ago

Okay. Good news first: I started with the system table from the model above, and it worked for a single table. I then worked my way up and added the body table (which has a relation to system), and it stalled again, immediately.

I then tried the artifact table alone, which worked again. Then I added the table codex_catgeory to the artifact table, and now it stalled again.

After that I tried multiple tables in a go, and as long as I scaffold only tables that are only referenced by other tables and not have FKs pointing other tables, it works.

As soon as I add a table to the mix that has an FK to other tables, it stalls.

I see different outputs on the console depending on which tables I want to scaffold, as if the tooling tries to do the tables in alphabetical order and stalls on the first one it encounters that has outgoing relations. In the second test, it seems artifact is generated correctly and then it starts hanging at codex_category, and in the first test, even if I state the argument order with system first and then body, it seems to try to do the body first and stalls there.

I also did a new trace. The file is located here: https://www.dropbox.com/sh/glce5iqaunsf2fz/AADdpvuS6acC9FObC-vnfz87a?dl=0

gingters commented 7 years ago

Is there anything else I can provide in terms of additional info for that issue?

I looked in the repo, but I do not see a tag for the 1.1.2 release, just a 1.1.2-rtm branch that seems like being in the middle of the conversion to 2.0.

If you could point me to the commit that resulted in the 1.1.2 build, I could try and locally debug the issue, but a bit of guidance of where I should start and what to take care of would be great.

mguinness commented 7 years ago

I ran your schema against MySQL 5.6.27 and it worked fine, see DatabaseModels.zip.

The trace file had exceptions System.IO.FileNotFoundException & System.MissingMethodException, so I'm wondering if there is a missing assembly. Can you provide your csproj file?

Also, would you be able to test against MySQL server instead of MariaDB to see if that works?

gingters commented 7 years ago

Here's my .csproj:

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <!-- main project info -->
    <TargetFramework>netcoreapp1.1</TargetFramework>
    <RuntimeIdentifiers>win8-x64</RuntimeIdentifiers>
  </PropertyGroup>
  <PropertyGroup>
    <!-- pack related info -->
    <Authors>Sebastian Gingter</Authors>
    <Description>The backend API web project for the Canonn API project.</Description>
    <Company>Canonn Science</Company>
    <Copyright>Copyright © 2017, Sebastian Gingter, Canonn Science</Copyright>
    <PackageId>CanonnApi.Web</PackageId>
    <PackageTags>Canonn; science; api;</PackageTags>

    <PackageLicenseUrl>https://opensource.org/licenses/MIT</PackageLicenseUrl>
    <PackageRequireLicenseAcceptance>False</PackageRequireLicenseAcceptance>
    <PackageProjectUrl>https://github.com/canonn-science/canonnapi</PackageProjectUrl>
    <PackageTargetFallback>portable-net45+win8</PackageTargetFallback>
    <IncludeBuildOutput>false</IncludeBuildOutput>
    <NoPackageAnalysis>true</NoPackageAnalysis>
  </PropertyGroup>

  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|AnyCPU'">
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <TreatSpecificWarningsAsErrors />
    <DocumentationFile>bin\Debug\netcoreapp1.1\CanonnApi.Web.xml</DocumentationFile>
    <NoWarn>1701;1702;1705;1591</NoWarn>
  </PropertyGroup>

  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Release|AnyCPU'">
    <DocumentationFile>bin\Release\netcoreapp1.1\CanonnApi.Web.xml</DocumentationFile>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Autofac.Extensions.DependencyInjection" Version="4.1.0" />
    <PackageReference Include="AutoMapper" Version="6.1.1" />
    <PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="2.0.1" />
    <PackageReference Include="Microsoft.ApplicationInsights.AspNetCore" Version="2.1.0" />
    <PackageReference Include="Microsoft.AspNetCore" Version="1.1.2" />
    <PackageReference Include="Microsoft.AspNetCore.Authentication.JwtBearer" Version="1.1.2" />
    <PackageReference Include="Microsoft.AspNetCore.Cors" Version="1.1.2" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.3" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc.Formatters.Json" Version="1.1.3" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc.Formatters.Xml" Version="1.1.3" />
    <PackageReference Include="Microsoft.AspNetCore.StaticFiles" Version="1.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="1.1.1" />
    <PackageReference Include="Microsoft.VisualStudio.Web.BrowserLink" Version="1.1.2" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="1.1.1" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="1.1.2" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Design" Version="1.1.2" />
    <PackageReference Include="Serilog" Version="2.5.0" />
    <PackageReference Include="Serilog.Extensions.Logging" Version="1.4.0" />
    <PackageReference Include="Serilog.Settings.Configuration" Version="2.4.0" />
    <PackageReference Include="Serilog.Sinks.File" Version="3.2.0" />
    <PackageReference Include="Serilog.Sinks.Literate" Version="3.0.0" />
    <PackageReference Include="Serilog.Sinks.Trace" Version="2.1.0" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="1.0.0" />
    <PackageReference Include="Swashbuckle.AspNetCore.Swagger" Version="1.0.0" />
    <PackageReference Include="Swashbuckle.AspNetCore.SwaggerGen" Version="1.0.0" />
    <PackageReference Include="Swashbuckle.AspNetCore.SwaggerUI" Version="1.0.0" />
    <PackageReference Include="System.Text.Encodings.Web" Version="4.3.1" />
    <PackageReference Include="System.ValueTuple" Version="4.3.1" />
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.1" />
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="1.0.1" />
  </ItemGroup>

  <ItemGroup>
    <!--pack information -->
    <Content Include=".\bin\Release\netcoreapp1.1\win8-x64\publish\*">
      <Visible>false</Visible>
      <PackagePath>web</PackagePath>
      <Pack>true</Pack>
    </Content>
  </ItemGroup>

  <!-- for dotnet publish command we need to include the documentation .xml file in the publish output -->
  <Target Name="CopyDocumentationFile" AfterTargets="ComputeFilesToPublish">
    <ItemGroup>
      <ResolvedFileToPublish Include="@(FinalDocFile)" RelativePath="@(FinalDocFile->'%(Filename)%(Extension)')" />
    </ItemGroup>
  </Target>

</Project>
mguinness commented 7 years ago

The only difference I can see is I have this in my project:

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="1.1.2" />

gingters commented 7 years ago

@mguinness Thanks a lot for that hint regarding MySQL. I just installed MySQL side by side with my mariadb instance, and it worked there... partially.

TL:DR; Using it on MySQL does not show a warning / error when you scaffold in a directory with an existing model, and just stalls when you omit the --force flag. Using it on MariaDb stalls always, assumption is a difference in datetime default constraint syntax when the table is shown.

Detailed info:

What I noticed during my lasts tests is: In general, it does work on MySQL but not on MariaDb.

Also, on MySQL I can bring it to stall when I want to scaffold the model in a folder where a model already exists while omitting the force flag. In that case it does not show an error and also no hint to use the --force flag in this case.

For the MariaDb stall: I let SHOW CREATE TABLE name on both systems, and the only difference I can tell there is that MySQL generates 

    `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

whereas MariaDb shows

  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `updated` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

instead. Note the difference in caseing and especially the additional braces for MariaDb.

Could that difference in generated syntax be a problem for scaffolding?

mguinness commented 7 years ago

@gingters Thanks for helping to troubleshoot. I'm not able to reproduce what you see when you have an existing directory and omit the force flag. I get "The following file(s) already exist in directory..." error shown in the console.

Also I don't believe that the SHOW CREATE TABLE output is the cause with MariaDB - IIRC you said that the scaffold isn't even hitting the query log, meaning those results aren't even returned to the library. Can you confirm?

The only other thing that springs to mind is that you have some corruption in your nuget cache. You could try using dotnet nuget locals all --clear, see dotnet nuget for details.

fauriemma commented 7 years ago

Hi, I have same issue but after analyze mysql-slow-query i found a solution.

In my case problem is that i have two schema with different name but with same tables definition.

Pomelo scaffold execute a quary that retrieve table information as:

SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME, (SELECT DELETE_RULE FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = KEY_COLUMN_USAGE.CONSTRAINT_NAME) AS DELETE_RULE FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = 'DbTest' AND TABLE_SCHEMA = 'DbTest' AND TABLE_NAME = 'TableTest' AND CONSTRAINT_NAME <> 'PRIMARY' AND REFERENCED_TABLE_NAME IS NOT NULL;

in this part of subquery SELECT DELETE_RULE FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = KEY_COLUMN_USAGE.CONSTRAINT_NAME

is omitted SCHEMA_NAME as WHERE filter and in my case query fails because subquery return more than one result.

If you drop one of two similar schemas, or rename all constraint with different name, scaffold work without any problem!!!

gingters commented 7 years ago

I had not the time to double check and verify the problems / differences with MariaDb vs. MySQL and the full query logs on both for now. I will retest that over the next weekend and come back with detailed info. Sorry.