mikewarriner
2024-07-04 03:42

MySQL 9.0 and Docker Image

Hi,

MySQL since about version 8 has been deprecating native authentication and version 9 has removed it completely.  Does anyone know the right connection string to connect to MySQL 9.0 as a server please?

I suspect the .NET code needs to be recompiled with the latest .net version to support this, would you be able to do this and update the image please?


Many thanks,

Mike

0
Thread Status
4
103
2
0
0
Sort replies by:
Wilson @guanvee
2024-07-04 08:57

Hi,

I just created a MySQL instance using MySQL 9.0.0 in Docker. Our local monitoring program can directly support it. I'm not sure what issue you encountered that caused the connection failure. Could you please send the specific error?

0
mikewarriner
2024-07-09 00:24

So...

I'm using Docker.  To get anything to work I had to change the connection string to have SslMode=Required at the end.

Then, while it seems to be 'working' I'm getting constant connection errors in the logs.  I can send you the logs (not sure I can attach here?)


Some extracts:


1. ^[[39;49m^[[30m[^[[39;49m^[[39;49m^[[37m12:53:15^[[39;49m^[[39;49m^[[30m ^[[39;49m^[[39;49m^[[37m^[[39;49m^[[37m^[[41mFTL^[[39;49m^[[39;49m^[[30m] ^[[39;49m^[[39;49m^[[37mHost terminated unexpectedly^[[39;49m^[[39;49m^[[30m ^[[39;49m^[[39;49m^[[30m{^[[39;49m^[[39;49m^[[30m}^[[39;49m

^[[39;49m^[[37mMySqlConnector.MySqlException (0x80004005): Couldn't connect to server^[[39;49m
^[[39;49m^[[37m ---> System.IO.EndOfStreamException: Expected to read 4 header bytes but only received 0.^[[39;49m
^[[39;49m^[[37m at MySqlConnector.Protocol.Serialization.ProtocolUtility.<DoReadPayloadAsync>g__AddContinuation|5_0(ValueTask`1 readPacketTask, BufferedByteReader bufferedByteReader, IByteHandler byteHandler, Func`1 getNextSequenceNumber, ArraySegmentHolder`1 previousPayloads, ProtocolErrorBehavior protocolErrorBehavior, IOBehavior ioBehavior) in /_/src/MySqlConnector/Protocol/Serialization/ProtocolUtility.cs:line 494^[[39;49m
^[[39;49m^[[37m at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 951^[[39;49m
^[[39;49m^[[37m at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int32 startTickCount, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 456^[[39;49m
^[[39;49m^[[37m at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int32 startTickCount, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 600^[[39;49m
^[[39;49m^[[37m at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, String logMessage, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 403^[[39;49m
^[[39;49m^[[37m at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, String logMessage, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 408^[[39;49m
^[[39;49m^[[37m at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 98^[[39;49m
^[[39;49m^[[37m at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 128^[[39;49m
^[[39;49m^[[37m at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int32 startTickCount, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 929^[[39;49m
^[[39;49m^[[37m at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 423^[[39;49m
^[[39;49m^[[37m at MySqlConnector.MySqlConnection.Open() in /_/src/MySqlConnector/MySqlConnection.cs:line 382^[[39;49m
^[[39;49m^[[37m at Microsoft.EntityFrameworkCore.ServerVersion.AutoDetect(String connectionString)^[[39;49m
^[[39;49m^[[37m at IAMMETER.Local.Web.Startup.<ConfigureServices>b__4_0(DbContextOptionsBuilder options) in /Users/guanvee/myspace/project/pm/IammeterDocker/IAMMETER/Local/Server/IAMMETER.Local.Web/Startup.cs:line 63^[[39;49m
^[[39;49m^[[37m at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.<>c__DisplayClass1_0`2.<AddDbContext>b__0(IServiceProvider _, DbContextOptionsBuilder b)^[[39;49m
^[[39;49m^[[37m at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.CreateDbContextOptions[TContext](IServiceProvider applicationServiceProvider, Action`2 option


.. and 


MySqlConnector.MySqlException (0x80004005): The Command Timeout expired before the operation completed.
---> MySqlConnector.MySqlException (0x80004005): Query execution was interrupted
at MySqlConnector.Core.ResultSet.<ScanRowAsync>g__ScanRowAsyncAwaited|9_0(ResultSet resultSet, Task`1 payloadTask, Row row, CancellationToken token) in /_/src/MySqlConnector/Core/ResultSet.cs:line 256
at MySqlConnector.Core.ResultSet.<ScanRowAsync>g__ScanRowAsyncAwaited|9_0(ResultSet resultSet, Task`1 payloadTask, Row row, CancellationToken token) in /_/src/MySqlConnector/Core/ResultSet.cs:line 256
at MySqlConnector.Core.ResultSet.ReadAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ResultSet.cs:line 215
at MySqlConnector.Core.ResultSet.Read() in /_/src/MySqlConnector/Core/ResultSet.cs:line 191
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.Initialize(DbDataReader reader, IReadOnlyList`1 columns)
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.Initialize(IReadOnlyList`1 columns)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.MoveNext()
at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
at lambda_method689(Closure, QueryContext)
at IAMMETER.Local.BLL.MeterRepository.SaveMeterEnergy(BIZ_MeterInfo meterInfo, DateTime rtcNow, UploadDataDTO dto)
at IAMMETER.Local.BLL.MeterRepository.SaveDataSingle(BIZ_MeterInfo meterInfo, UploadDataDTO dto, DateTime now) in /Users/guanvee/myspace/project/pm/IammeterDocker/IAMMETER/Local/Server/IAMMETER.Local.BLL/Repository/Place/MeterRepository.cs:line 126
at IAMMETER.Local.BLL.MeterRepository.UploadData(UploadDataDTO dto)
at IAMMETER.Local.Web.Controllers.SensorController.UploadSensor(UploadDataDTO dto) in /Users/guanvee/myspace/project/pm/IammeterDocker/IAMMETER/Local/Server/IAMMETER.Local.Web/Controllers/V1/SensorController.cs:line 64
at lambda_method129(Closure, Object, Object[])
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)
[09:06:20 ERR] An exception occurred while iterating over the results of a query for context type 'IAMMETER.Local.Models.MyContext'.
MySqlConnector.MySqlException (0x80004005): The Command Timeout expired before the operation completed.
---> MySqlConnector.MySqlException (0x80004005): Query execution was interrupted
at MySqlConnector.Core.ResultSet.<ScanRowAsync>g__ScanRowAsyncAwaited|9_0(ResultSet resultSet, Task`1 payloadTask, Row row, CancellationToken token) in /_/src/MySqlConnector/Core/ResultSet.cs:line 256
at MySqlConnector.Core.ResultSet.<ScanRowAsync>g__ScanRowAsyncAwaited|9_0(ResultSet resultSet, Task`1 payloadTask, Row row, CancellationToken token) in /_/src/MySqlConnector/Core/ResultSet.cs:line 256
at MySqlConnector.Core.ResultSet.ReadAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ResultSet.cs:line 215
at MySqlConnector.Core.ResultSet.Read() in /_/src/MySqlConnector/Core/ResultSet.cs:line 191
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.Initialize(DbDataReader reader, IReadOnlyList`1 columns)
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.Initialize(IReadOnlyList`1 columns)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.MoveNext() {"EventId": {"Id": 10100, "Name": "Microsoft.EntityFrameworkCore.Query.QueryIterationFailed"}, "SourceContext": "Microsoft.EntityFrameworkCore.Query", "ActionId": "7678953f-4661-4a13-bd5a-7e01db3a5894", "ActionName": "IAMMETER.Local.Web.Controllers.SensorController.UploadSensor (IAMMETER.Local.Web)", "RequestId": "0HMTLLST67R63:00000001", "RequestPath": "/api/v1/sensor/uploadsensor", "ConnectionId": "0HMTLLST67R63"}
MySqlConnector.MySqlException (0x80004005): The Command Timeout expired before the operation completed.
---> MySqlConnector.MySqlException (0x80004005): Query execution was interrupted
at MySqlConnector.Core.ResultSet.<ScanRowAsync>g__ScanRowAsyncAwaited|9_0(ResultSet resultSet, Task`1 payloadTask, Row row, CancellationToken token) in /_/src/MySqlConnector/Core/ResultSet.cs:line 256
at MySqlConnector.Core.ResultSet.<ScanRowAsync>g__ScanRowAsyncAwaited|9_0(ResultSet resultSet, Task`1 payloadTask, Row row, CancellationToken token) in /_/src/MySqlConnector/Core/ResultSet.cs:line 256
at MySqlConnector.Core.ResultSet.ReadAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ResultSet.cs:line 215
at MySqlConnector.Core.ResultSet.Read() in /_/src/MySqlConnector/Core/ResultSet.cs:line 191
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.Initialize(DbDataReader reader, IReadOnlyList`1 columns)
at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.Initialize(IReadOnlyList`1 columns)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.MoveNext()
[09:06:20 ERR] Connection id "0HMTLLST67R63", Request id "0HMTLLST67R63:00000001": An unhandled exception was thrown by the application. {"EventId": {"Id": 13, "Name": "ApplicationError"}, "SourceContext": "Microsoft.AspNetCore.Server.Kestrel", "RequestId": "0HMTLLST67R63:00000001", "RequestPath": "/api/v1/sensor/uploadsensor"}
MySqlConnector.MySqlException (0x80004005): The Command Timeout expired b^C
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.MoveNext()
[09:06:27 ERR] Connection id "0HMTLLST67R64", Request id "0HMTLLST67R64:00000001": An unhandled exception was thrown by the application. {"EventId": {"Id": 13, "Name": "ApplicationError"}, "SourceContext": "Microsoft.AspNetCore.Server.Kestrel", "RequestId": "0HMTLLST67R64:00000001", "RequestPath": "/api/v1/sensor/uploadsensor"}
MySqlConnector.MySqlException (0x80004005): The Command Timeout expired before the operation completed.
---> MySqlConnector.MySqlException (0x80004005): Query execution was interrupted
at MySqlConnector.Core.ResultSet.<ScanRowAsync>g__ScanRowAsyncAwaited|9_0(ResultSet resultSet, Task`1 payloadTask, Row row, CancellationToken token) in /_/src/MySqlConnector/Core/ResultSet.cs:line 256

mike@nas:~$




0
mikewarriner
2024-07-09 00:37

PS.  Happy to debug offline if that's helpful.

mikewarriner
So...I'm using Docker.  To get anything to work I had to change the connection string to have SslMode=Required at the end.Then, while it seems to be 'working' I'm getting constant connection errors in the logs.  I can send you the logs (not sure I can attach here?)Some extracts:1. ^[[39;49m^[[30m[^[[39;49m^[[39;49m^[[37m12:53:15^[[39;49m^[[39;49m^[[30m ^[[39;49m^[[39;49m^[[37m^[[39;49m^[[37m^[[41mFTL^[[39;49m^[[39;49m^[[30m] ^[[39;49m^[[39;49m^[[37mHost terminated unexpectedly^[[39;49m
0
Wilson @guanvee
2024-07-10 14:45

Hi,

Your requirements cannot be met solely through our program settings; however, you can manually adjust the /data/config.json file as follows:

{
"ConnectionStrings": {
"MySqlConnectionString": "server=your_mysql_host;port=3306;database=your_database;user=your_user;password=your_password;sslmode=Required;CertificateFile=/path/to/client-cert.pfx;CertificatePassword=your_pfx_password;CACertificateFile=/path/to/ca.pem"

}
}

0
Looks like you are new here. Register for free, learn and contribute.