Devart Forums

KW

This script is generated using ef migration commands in package manager console.

For some reason it generates a cast function for default value, which is illegal. Only constants are accepted.

KW

dotnet ef migrations script --context ConfigurationDBContext
-- Script was generated by Devart dotConnect for MySQL, Version 8.12.1357
-- Product home page: http://www.devart.com/dotconnect/mysql
-- Database version: MySQL 8.0.14
-- Script date 4/10/2019 6:14:51 PM

ALTER TABLE ApiResources
ADD COLUMN Created datetime DEFAULT CAST('0001-01-01 00:00:00' AS DATETIME) NOT NULL;

The cast() is unnecessary. The value is automatically converted to the correct type:

ALTER TABLE Table1 ADD COLUMN Created datetime DEFAULT '0001-01-01 00:00:00' NOT NULL;
MySQL allows constants for default values but does not permit expressions.

KW

Does dbForge Studio for MySQL support live query profiling? So I can see in real time queries being executed?

KW

Any update on this?

KW

The results are much better

Connector/NET executes 544 milliseconds
Devart dotConnect for MySQL executes 376 milliseconds

However, I'm unsure this solves anything. How do you implement a fix in asp.net core where multiple threads are spun up for each request to a controller If you look at my first post there is no threading declared - it's all being handled by asp.net cores kestrel (I believe).

Also, why is this even necessary and why does this slow down oracles results?

KW

1. You get these results in a simple console application, don't you? Tell us Target framework specified in the properties of your project.

Yes, I was able to reproduce in the above posted code in a simple console application. Target Framework is 4.7.1

2. There are two sets of assemblies:
a) .NET Framework Devart.* assemblies (provided with installation)We are using assemblies provided by installation, we have a professional license.

3. Describe your MySQL Server:
a) version 8.011
b) edition Community
c) the name of operating system and its capacity where MySQL Server is installed - Ubuntu Server 32gb of ram, 1tb SSD
d) if possible, send us your my.ini file

KW

First, the server I'm using enforces SSL so the mode is not required for the client and adding it does not change the results.

require_secure_transport = ON

Second, you're not reproducing the problem. I acknowledge calling single is fine. But change to threading. Oracle provider open/close is fine - Devarts open/close is VERY slow.

Code: Select all

class Program
    {
        static void Main(string[] args)
        {

            TestConnector();

            TestDevart();

            Console.ReadKey();
        }

        private static void TestConnector()
        {
            var watch = System.Diagnostics.Stopwatch.StartNew();

            List<System.Threading.Tasks.Task> tasks = new List<System.Threading.Tasks.Task>();

            for (int i = 0; i < 30; i++)
            {
                
                 
                    tasks.Add( Task.Factory.StartNew(() =>
                    {
                        using (var connnection = new MySql.Data.MySqlClient.MySqlConnection("User Id=root;Password=****;Host=localhost;Port=3306;Database=mysql;SslMode=Required;"))
                        {
                            try
                            {
                                connnection.Open();
                                try
                                {

                                }
                                finally
                                {
                                    connnection.Close();
                                }
                            }
                            finally
                            {

                            }
                        }

                    }));
                
                
                
           }

            Task.WaitAll(tasks.ToArray());

            watch.Stop();
            Console.WriteLine("Connector/NET executes {0} milliseconds", watch.ElapsedMilliseconds);
        }

        private static void TestDevart()
        {
            var watch = System.Diagnostics.Stopwatch.StartNew();

            List<System.Threading.Tasks.Task> tasks = new List<System.Threading.Tasks.Task>();

            for (int i = 0; i < 30; i++)
            {


                tasks.Add( Task.Factory.StartNew(() =>
                {
                    using (var connnection = new Devart.Data.MySql.MySqlConnection("User Id=root;Password=****;localhost;Port=3306;Unicode=True;Compress=False;Protocol=Ssl;Persist Security Info=True;Connection Timeout=60;Pooling=True;Min Pool Size=0;Connection Lifetime=20;Validate Connection=False;Found Rows=True;SSL TLS Protocol=1.2"))
                    {
                        try
                        {
                            connnection.Open();
                            try
                            {

                            }
                            finally
                            {
                                connnection.Close();
                            }
                        }
                        finally
                        {

                        }
                    }

                }));


            }

            Task.WaitAll(tasks.ToArray());

            watch.Stop();   

            Console.WriteLine("Devart dotConnect for MySQL executes {0} milliseconds", watch.ElapsedMilliseconds);
        }
         
    }

Connector/NET executes 411 milliseconds
Devart dotConnect for MySQL executes 24815 milliseconds

24 seconds to open and close connection in a multi-threaded application is abysmal. All the MVC calls are potentially multi-threaded as multiple clients can hit those controllers at the same time.

To clarify, I've tried every connection string change to Devart mysqlconnection and still the same results.

Devart.Data.mysql version 8.12.1229.0

KW

Code: Select all

 public ActionResult Get()
        { 
  using (var connnection = new Devart.Data.MySql.MySqlConnection("User Id=xxx;Password=xxx;Host=192.168.1.2;Port=3306;Database=xxx;Unicode=True;Compress=False;Protocol=Ssl;Persist Security Info=True;Connection Timeout=60;Pooling=True;Connection Lifetime=20;Validate Connection=False;Found Rows=True;SSL TLS Protocol=1.2"))
            {
                try
                {
                         connnection.Open();                    
                    try
                    {
                         
                    }
                    finally
                    {                         
                        connnection.Close();
                    }


                }
                finally
                {

                }
        }

Execute Code block that is called asynchronously 30 times from .net client:

Code: Select all

 
 public async Task<string> CallWeb()
        {
            HttpClient client = new HttpClient();             
            HttpResponseMessage response = await client.GetAsync(APIURL + "/v1/Test");
            response.EnsureSuccessStatusCode();
            return await response.Content.ReadAsStringAsync();
           
        }

Using Devarts mysql connection is 38 seconds for the last call to exit.

If I call this method, just one at a time the time it takes to open and close each connection is:

.031 SECONDS according to db monitor.

Mysql Oracle Client:

Code: Select all

using (var connnection = new MySql.Data.MySqlClient.MySqlConnection("User Id=xxx;Password=xxx;Host=192.168.1.2;Port=3306;Database=xxx;"))

            {
                try
                {


                    connnection.Open();
 
                    try
                    {
                        

                       
                    }
                    finally
                    {                       
                         
                        connnection.Close();
                    }


                }
                finally
                {

                }
            }

The longest running connection is 2.2 seconds using Oracles connector, Devarts is 38 seconds.

I've tried adjusting Devarts connection string to every permutation I can think of and still the same results. In the debugger it literally feels like Devarts mysqlconnection is waiting on something. This is not the same when using Oracles mysql connector client for .net:

Install-Package MySql.Data -Version 8.0.13

KW

From the notes it looks like my issue was generating the nullable values.

When I get a chance I'll update to the latest version and see if it resolves the issue.

KW

I was attempting to upgrade from version 8.10.1061.0 to 8.12 (the latest version)

Right-click and run custom tool on an .edml model creates a massive list of errors.

DateTime columns no longer have .HasValue, among other items.

Is there an upgrade strategy for older edmls? My project had over 2000 errors on the new edml generation.

KW

I have a column named Grouping in one of my entities which works correctly using mysql 5.7

I have updated mysql 8.0, so now when I use entity framework to save changes to this entity it throws a syntax exception.

https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Grouping is now a keyword, so my column needs to be quoted - is this something that I can force entityframework to do or can you update entity developer to use ` ` around the grouping column?

Thanks.

KW

Does your component support the new authentication plugin?

caching_sha2_password is the default authentication plugin rather than mysql_native_password

https://dev.mysql.com/doc/refman/8.0/en ... ation.html

I tried to log into a new installation but received the following error:

InvalidOperationException: Unknown authentication plugin 'caching_sha2_password'.

Using Version 8.10.1061.0

KW

No idea why that started happening.

Deleted bin folders tens of times and rebuilt and finally it just started working.

I have a build on nugget that I can pull that is broken - still can't reproduce the fix.

KW

Code: Select all

InvalidOperationException: Can not find compilation library location for package 'Devart.Data'
Microsoft.Extensions.DependencyModel.CompilationLibrary.ResolveReferencePaths()
Microsoft.AspNetCore.Mvc.ApplicationParts.AssemblyPart+<>c.<GetReferencePaths>b__8_0(CompilationLibrary library)
System.Linq.Enumerable+<SelectManyIterator>d__16.MoveNext()
Microsoft.AspNetCore.Mvc.Razor.Compilation.MetadataReferenceFeatureProvider.PopulateFeature(IEnumerable<ApplicationPart> parts, MetadataReferenceFeature feature)
Microsoft.AspNetCore.Mvc.ApplicationParts.ApplicationPartManager.PopulateFeature<TFeature>(TFeature feature)
Microsoft.AspNetCore.Mvc.Razor.Internal.RazorReferenceManager.GetCompilationReferences()
System.Threading.LazyInitializer.EnsureInitializedCore<T>(ref T target, ref bool initialized, ref object syncLock, Func<T> valueFactory)
Microsoft.AspNetCore.Mvc.Razor.Internal.RazorReferenceManager.get_CompilationReferences()
Microsoft.AspNetCore.Mvc.Razor.Internal.DefaultRoslynCompilationService.CreateCompilation(string compilationContent, string assemblyName)
Microsoft.AspNetCore.Mvc.Razor.Internal.DefaultRoslynCompilationService.Compile(RelativeFileInfo fileInfo, string compilationContent)
Microsoft.AspNetCore.Mvc.Razor.Internal.RazorCompilationService.Compile(RelativeFileInfo file)
Microsoft.AspNetCore.Mvc.Razor.Internal.CompilerCache.CreateCacheEntry(string relativePath, string normalizedPath, Func<RelativeFileInfo, CompilationResult> compile)
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Mvc.Razor.Internal.CompilerCache.GetOrAdd(string relativePath, Func<RelativeFileInfo, CompilationResult> compile)
Microsoft.AspNetCore.Mvc.Razor.Internal.DefaultRazorPageFactoryProvider.CreateFactory(string relativePath)
Microsoft.AspNetCore.Mvc.Razor.RazorViewEngine.CreateCacheResult(HashSet<IChangeToken> expirationTokens, string relativePath, bool isMainPage)
Microsoft.AspNetCore.Mvc.Razor.RazorViewEngine.OnCacheMiss(ViewLocationExpanderContext expanderContext, ViewLocationCacheKey cacheKey)
Microsoft.AspNetCore.Mvc.Razor.RazorViewEngine.LocatePageFromViewLocations(ActionContext actionContext, string pageName, bool isMainPage)
Microsoft.AspNetCore.Mvc.Razor.RazorViewEngine.FindView(ActionContext context, string viewName, bool isMainPage)
Microsoft.AspNetCore.Mvc.ViewEngines.CompositeViewEngine.FindView(ActionContext context, string viewName, bool isMainPage)
Microsoft.AspNetCore.Mvc.ViewFeatures.Internal.ViewResultExecutor.FindView(ActionContext actionContext, ViewResult viewResult)
Microsoft.AspNetCore.Mvc.ViewResult+<ExecuteResultAsync>d__26.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeResultAsync>d__30.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeNextResultFilterAsync>d__28.MoveNext()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ResultExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeNextResourceFilter>d__22.MoveNext()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ResourceExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeAsync>d__20.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Builder.RouterMiddleware+<Invoke>d__4.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
IdentityModel.AspNetCore.ScopeValidation.ScopeValidationMiddleware+<Invoke>d__4.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__18.MoveNext()
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__18.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
IdentityServer4.AccessTokenValidation.IdentityServerAuthenticationMiddleware+<Invoke>d__7.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
IdentityServer4.Hosting.IdentityServerMiddleware+<Invoke>d__4.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
IdentityServer4.Hosting.FederatedSignOutMiddleware+<Invoke>d__6.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
IdentityServer4.Hosting.AuthenticationMiddleware+<Invoke>d__2.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Cors.Infrastructure.CorsMiddleware+<Invoke>d__7.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
IdentityServer4.Hosting.BaseUrlMiddleware+<Invoke>d__2.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__18.MoveNext()
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__18.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__18.MoveNext()
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__18.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__18.MoveNext()
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__18.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__18.MoveNext()
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__18.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMiddleware+<Invoke>d__5.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware+<Invoke>d__6.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware+<Invoke>d__6.MoveNext()
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware+<Invoke>d__7.MoveNext()

Visual studio version 4.7.02046
Dotconnect mysql version 8.9.958.0

KW

I think I may have created a .net standard class library to host the entity framework model. Can you try that project type?

Devart Forums

Search found 135 matches

Re: 8.12.1357 script error EF migration

8.12.1357 script error EF migration

Simple question for dbforge for mysql

Re: async Call MVC controller to open/close connection is slow

Re: async Call MVC controller to open/close connection is slow

Re: async Call MVC controller to open/close connection is slow

Re: async Call MVC controller to open/close connection is slow

async Call MVC controller to open/close connection is slow

Re: .edml model fails on version 8.12

.edml model fails on version 8.12

Upgraded to mysql 8.0 - table now fails to update thorugh entity framework

Support for new default authentication plugin in mysql 8.0

Re: Can not find compilation library location for package 'Devart.Data' after ugprade to visual studio 2017 latest version

Can not find compilation library location for package 'Devart.Data' after ugprade to visual studio 2017 latest version

Re: Debug / Relase in Visual studio and missing embeded files