Problem with Default Security with MySQL database

PeterS · August 17, 2020, 5:42pm

I have set up a simple project using a local MS SQL database and activated Default Security and all works well. I have then tried the exact same using MySQL but get the following error log (below).

Thanks for your help.

Regards

Peter

dotnet: watch : Exited
watch : File changed: C:\Data\Code\RadzenTest\server\App.razor

dotnet: watch : Started

dotnet: CSC : warning CS8034: Unable to load Analyzer assembly C:\Users\peter.nuget\packages\microsoft.aspnetcore.components.analyzers\3.1.0\analyzers\dotnet\cs\Microsoft.AspNetCore.Components.Analyzers.dll : Assembly with same name is already loaded [C:\Data\Code\RadzenTest\server\RadzenTest.csproj]

dotnet: info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[0]
User profile is available. Using 'C:\Users\peter\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.

dotnet: info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 3.1.4 initialized 'ApplicationIdentityDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: None

dotnet: info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (18ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='MYSQL5_875864_radzentest' AND TABLE_NAME='__EFMigrationsHistory';

dotnet: info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (15ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE __EFMigrationsHistory (
MigrationId varchar(95) NOT NULL,
ProductVersion varchar(32) NOT NULL,
CONSTRAINT PK___EFMigrationsHistory PRIMARY KEY (MigrationId)
);

dotnet: info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='MYSQL5_875864_radzentest' AND TABLE_NAME='__EFMigrationsHistory';

dotnet: info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT MigrationId, ProductVersion
FROM __EFMigrationsHistory
ORDER BY MigrationId;

dotnet: info: Microsoft.EntityFrameworkCore.Migrations[20402]
Applying migration '00000000000000_CreateIdentitySchema'.

dotnet: fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE AspNetRoles (
Id varchar(255) CHARACTER SET utf8mb4 NOT NULL,
ConcurrencyStamp longtext CHARACTER SET utf8mb4 NULL,
Name varchar(256) CHARACTER SET utf8mb4 NULL,
NormalizedName varchar(256) CHARACTER SET utf8mb4 NULL,
CONSTRAINT PK_AspNetRoles PRIMARY KEY (Id)
);

dotnet: crit: Microsoft.AspNetCore.Hosting.Diagnostics[6]
Application startup exception
MySql.Data.MySqlClient.MySqlException (0x80004005): Specified key was too long; max key length is 1000 bytes
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Specified key was too long; max key length is 1000 bytes
at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 130
at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391 at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 226
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 74
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Pomelo.EntityFrameworkCore.MySql.Migrations.Internal.MySqlMigrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
at RadzenTest.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env, ApplicationIdentityDbContext identityDbContext) in C:\Data\Code\RadzenTest\server\Startup.cs:line 126
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.b__0(IApplicationBuilder builder)
at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass13_0.b__2(IApplicationBuilder app)
at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.b__0(IApplicationBuilder app)
at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)

dotnet: Unhandled exception.
dotnet: MySql.Data.MySqlClient.MySqlException (0x80004005): Specified key was too long; max key length is 1000 bytes
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Specified key was too long; max key length is 1000 bytes
at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 130
at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391 at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 226
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 74
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Pomelo.EntityFrameworkCore.MySql.Migrations.Internal.MySqlMigrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
at RadzenTest.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env, ApplicationIdentityDbContext identityDbContext) in C:\Data\Code\RadzenTest\server\Startup.cs:line 126
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.b__0(IApplicationBuilder builder)
at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass13_0.b__2(IApplicationBuilder app)
at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.b__0(IApplicationBuilder app)
at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)
at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)
at RadzenTest.Program.Main(String[] args) in C:\Data\Code\RadzenTest\server\Program.cs:line 18

dotnet: watch : Exited with error code -532462766

dotnet: watch : Waiting for a file to change before restarting dotnet...

enchev · August 17, 2020, 7:25pm

Is it a server-side Blazor or WebAssembly?

PeterS · August 17, 2020, 8:03pm

It's Server Side but I did encounter the same problem with WebAssembly. My preferred technology is WebAssembly but I have found in testing that Server Side appears more robust (I assume this is because it is not so recent technology). For this reason I have decided for the time being to concentrate on Server Side and take a look at WebAssembly at some point in the future.

enchev · August 18, 2020, 4:39am

I've just tried new app with Sakila database and everything worked as expected on my end:

Please check if you are using latest Radzen.

PeterS · August 18, 2020, 8:38am

OK I believe I may have found the reason for this. When trying to create the Salika database I get a 1044 error. Having googled this I find the following:

The CREATE DATABASE and USE commands cannot be processed by the MySQL server in shared hosting because customers do not have administrative access to them; therefore, the import terminates with error 1044 .

I imagine it is this same permission issue that stops the additional tables being created. Radzen has created all forms for security but when I check the database none of the additional tables are created.

I shall continue testing/learning Radzen using MS SQL.

Thanks for your help.

Regards

Peter

PeterS · August 18, 2020, 8:42am

Just a thought further to the earlier post. I have a system used by 30 clients that uses a shared MySQL database (an individual MySQL shared database for each). Is it possible to write a Radzen app that could point to each individual MySQL database for each while using a single hosted MS SQL database to cater for security.

Thanks again.

Peter

enchev · August 18, 2020, 9:38am

Hi @PeterS,

I’m not sure I understand your setup fully however single/shared MS SQL with MySQL is not supported by default out-of-the-box.

korchev · August 18, 2020, 9:47am

Hi @PeterS,

I think you can. You can create two data sources - оne MSSQL and one MySQL in Radzen and specify the required one when configuring the application security.