oqtane.framework icon indicating copy to clipboard operation
oqtane.framework copied to clipboard

Published 20 hours ago • verified publisher icon oqtane

"Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported" error on startup with Postgres install

Open ghhv opened this issue 2 years ago • 6 comments

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(IList1 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.<SaveChanges>b__104_0(DbContext _, ValueTuple2 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(IEnumerable1 commandBatches, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList1 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.<SaveChanges>b__104_0(DbContext _, ValueTuple2 t) at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func3 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`

ghhv avatar Mar 04 '22 03:03 ghhv

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.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(IList1 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.<SaveChanges>b__104_0(DbContext _, ValueTuple2 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(IEnumerable1 commandBatches, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList1 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.<SaveChanges>b__104_0(DbContext _, ValueTuple2 t) at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func3 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`

ghhv avatar Mar 04 '22 03:03 ghhv

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.

ghhv avatar Mar 06 '22 04:03 ghhv

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?

sbwalker avatar Mar 07 '22 14:03 sbwalker

OK, shall investigate.

ghhv avatar Mar 08 '22 03:03 ghhv

I forgot what happened here.. I didn't succeed..

ghhv avatar Jun 20 '22 08:06 ghhv

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.

sbwalker avatar Jun 20 '22 11:06 sbwalker