"Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported" error on startup with Postgres install #2038
Comments
|
Confirming on a brand new fresh install (with Postgres) that the same issue is occurring before I've even logged in for the first time - So UNRELATED to Survey
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details. |
|
So, this issue seems to be related to a breaking change with NPGSQL V6 - https://www.npgsql.org/doc/types/datetime.html I cannot find a way to apply the suggested switch, I think because the dbContext is using all generic methods and a copied assembly? I guess the other way to resolve is to ensure all dates are converted to UTC before being saved. I may try this in coming days. |
|
So the error message is " System.InvalidCastException: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported" And the suggested fix is to preserve the legacy behavior: AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true); In theory this could be accomplished by modifying the PostgreSQL database provider. You need to open the Oqtane.Databases.sln and find the Oqtane.Database.PostgreSQL project. There is a class PostgreSQLDatabase.cs which contains all customizations required for this database. Perhaps the UseDatabase() method could be modified to include the switch? |
|
OK, shall investigate. |
|
I forgot what happened here.. I didn't succeed.. |
|
PostgreSQL introduced a breaking change at some point and the suggested fix for EF Core is to preserve the legacy behavior using something like: AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true); My understanding was that you were going to investigate this further - as it seemed that PostgreSQL was your preferred database (I generally use SQL Server or SQLite). In theory this fix could be included in the PostgreSQL database provider - I provided more details above. |
In trying to discover why the Survey module has failed exclusively on a Postgres install, I discovered the following error in the server startup messages
Microsoft.EntityFrameworkCore.Infrastructure: Information: Entity Framework Core 6.0.0 initialized 'MasterDBContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL:6.0.0+025fc55189ae41e3f2b98bcbe4eb44c505653700' with options: using snake-case naming (culture=) Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT j.job_id, j.created_by, j.created_on, j.end_date, j.frequency, j.interval, j.is_enabled, j.is_executing, j.is_started, j.job_type, j.modified_by, j.modified_on, j.name, j.next_execution, j.retention_history, j.start_date FROM job AS j Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (24ms) [Parameters=[@p15='?' (DbType = Int32), @p0='?', @p1='?' (DbType = DateTime), @p2='?' (DbType = DateTime), @p3='?', @p4='?' (DbType = Int32), @p5='?' (DbType = Boolean), @p6='?' (DbType = Boolean), @p7='?' (DbType = Boolean), @p8='?', @p9='?', @p10='?' (DbType = DateTime), @p11='?', @p12='?' (DbType = DateTime), @p13='?' (DbType = Int32), @p14='?' (DbType = DateTime)], CommandType='Text', CommandTimeout='30'] UPDATE job SET created_by = @p0, created_on = @p1, end_date = @p2, frequency = @p3, interval = @p4, is_enabled = @p5, is_executing = @p6, is_started = @p7, job_type = @p8, modified_by = @p9, modified_on = @p10, name = @p11, next_execution = @p12, retention_history = @p13, start_date = @p14 WHERE job_id = @p15; Microsoft.EntityFrameworkCore.Infrastructure: Information: Entity Framework Core 6.0.0 initialized 'MasterDBContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL:6.0.0+025fc55189ae41e3f2b98bcbe4eb44c505653700' with options: using snake-case naming (culture=) Microsoft.EntityFrameworkCore.Infrastructure: Information: Entity Framework Core 6.0.0 initialized 'MasterDBContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL:6.0.0+025fc55189ae41e3f2b98bcbe4eb44c505653700' with options: using snake-case naming (culture=) Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT j.job_id, j.created_by, j.created_on, j.end_date, j.frequency, j.interval, j.is_enabled, j.is_executing, j.is_started, j.job_type, j.modified_by, j.modified_on, j.name, j.next_execution, j.retention_history, j.start_date FROM job AS j Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT j.job_id, j.created_by, j.created_on, j.end_date, j.frequency, j.interval, j.is_enabled, j.is_executing, j.is_started, j.job_type, j.modified_by, j.modified_on, j.name, j.next_execution, j.retention_history, j.start_date FROM job AS j Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (5ms) [Parameters=[@p0='?', @p1='?' (DbType = DateTime), @p2='?' (DbType = DateTime), @p3='?', @p4='?' (DbType = Int32), @p5='?' (DbType = Boolean), @p6='?' (DbType = Boolean), @p7='?' (DbType = Boolean), @p8='?', @p9='?', @p10='?' (DbType = DateTime), @p11='?', @p12='?' (DbType = DateTime), @p13='?' (DbType = Int32), @p14='?' (DbType = DateTime)], CommandType='Text', CommandTimeout='30'] INSERT INTO job (created_by, created_on, end_date, frequency, interval, is_enabled, is_executing, is_started, job_type, modified_by, modified_on, name, next_execution, retention_history, start_date) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14) RETURNING job_id; 'Oqtane.Server.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\6.0.2\System.Diagnostics.StackTrace.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled. 'Oqtane.Server.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\6.0.2\System.Reflection.Metadata.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled. Microsoft.EntityFrameworkCore.Update: Error: An exception occurred in the database while saving changes for context type 'Oqtane.Repository.MasterDBContext'. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details. ---> System.InvalidCastException: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior. at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter) at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter) at Npgsql.NpgsqlParameter.ValidateAndGetLength() at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior) at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable1 commandBatches, IRelationalConnection connection)at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList
1 entries) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList1 entriesToSave)at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.b__104_0(DbContext _, ValueTuple
2 t) at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func`3 verifySucceeded)at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
---> System.InvalidCastException: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter)
at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
at Npgsql.NpgsqlParameter.ValidateAndGetLength()
at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable
1 commandBatches, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList1 entries)at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList
1 entriesToSave) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__104_0(DbContext _, ValueTuple2 t)at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func
3 operation, Func3 verifySucceeded)at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
Exception thrown: 'Microsoft.EntityFrameworkCore.DbUpdateException' in Microsoft.EntityFrameworkCore.dll`
The text was updated successfully, but these errors were encountered: