I am trying to insert into the mysql database the following record in a Create() method in a Repository class:
MySqlCommand command = new MySqlCommand("INSERT INTO `documentdetails` (?dtoDocumentId);", _connection);
command.Connection = _connection;
command.CommandType = CommandType.Text;
command.Parameters.Add("?dtoDocumentId", MySqlDbType.Int64).Value=documentDetail.DocumentId;
command.ExecuteNonQuery();
The documentId is a BigInt datatype which is retrieved from the documentDetails DTO which is of datatype long. The DocumentDetails table has 2 fields: Id (auto increments) and DocumentId whcih is what I am trying to insert in the code.
When calling the API endpoint which is associated with the above Create() method, the following errors are raised in the logs:
2024-04-09 10:27:06.106 +03:00 [ERR] Fatal error encountered during command execution. 2024-04-09 10:27:06.109 +03:00 [ERR] at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(Boolean execAsync, CancellationToken cancellationToken) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at DocumentProcessing.Database.Repositories.DocumentDetailsRepository.CreateUpdateDocumentDetail(DocumentDetailDTO documentDetailDTO) in C:\Users\source\repos\DocumentProcessing\Database\Repositories\DocumentDetailsRepository.cs:line 123 2024-04-09 10:27:06.111 +03:00 [ERR] Fatal error encountered attempting to read the resultset. 2024-04-09 10:27:06.113 +03:00 [ERR] at MySql.Data.MySqlClient.MySqlDataReader.NextResultAsync(Boolean execAsync, CancellationToken cancellationToken) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken) 2024-04-09 10:27:06.114 +03:00 [ERR] Reading from the stream has failed. 2024-04-09 10:27:06.115 +03:00 [ERR] at MySql.Data.MySqlClient.MySqlStream.LoadPacketAsync(Boolean execAsync) at MySql.Data.MySqlClient.MySqlStream.ReadPacketAsync(Boolean execAsync) at MySql.Data.MySqlClient.NativeDriver.GetResultAsync(Int32 affectedRow, Int64 insertedId, Boolean execAsync) at MySql.Data.MySqlClient.Driver.GetResultAsync(Int32 statementId, Int32 affectedRows, Int64 insertedId, Boolean execAsync) at MySql.Data.MySqlClient.Driver.NextResultAsync(Int32 statementId, Boolean force, Boolean execAsync) at MySql.Data.MySqlClient.MySqlDataReader.NextResultAsync(Boolean execAsync, CancellationToken cancellationToken) 2024-04-09 10:27:06.117 +03:00 [ERR] Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.. 2024-04-09 10:27:06.118 +03:00 [ERR] at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count) at MySql.Data.MySqlClient.TimedStream.ReadAsync(Byte[] buffer, Int32 offset, Int32 count, Boolean execAsync) at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count) at MySql.Data.MySqlClient.MySqlStream.ReadFullyAsync(Stream stream, Byte[] buffer, Int32 offset, Int32 count, Boolean execAsync) at MySql.Data.MySqlClient.MySqlStream.LoadPacketAsync(Boolean execAsync) 2024-04-09 10:27:06.119 +03:00 [ERR] A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. 2024-04-09 10:27:06.120 +03:00 [ERR] at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count) 2024-04-09 10:27:06.121 +03:00 [DBG] UnitOfWork:SaveChanges 2024-04-09 10:27:06.122 +03:00 [ERR] Connection must be valid and open to commit transaction 2024-04-09 10:27:06.123 +03:00 [ERR] at MySql.Data.MySqlClient.MySqlTransaction.CommitAsync(Boolean execAsync, CancellationToken cancellationToken) at MySql.Data.MySqlClient.MySqlTransaction.Commit() at DocumentProcessing.Database.Repositories.UnitOfWork.SaveChanges() in C:\Users\source\repos\DocumentProcessing\Database\Repositories\UnitOfWork.cs:line 64
After some investigation, I found out that when calling the create API endpoint using Postman (with documentID=1165078) the record gets created successfully. However when I do this programmatically and the documentID is assigned through code processing, the above errors are raised in the logs. Could this be an error related to the documentId datatype and the datatype of the database? Could you please help me understand how to fix this?
Could you please tell me why would
command.CommandText = String.Format("INSERT INTO `DocumentDetails` (DocumentId) VALUES ({0});", documentDetail.DocumentId);
would fail and
command.CommandText = String.Format("INSERT INTO `DocumentDetails` (DocumentId) VALUES ({0});", 1165078);
would succeed please?
Many thanks,
Managed to identify the root cause of the issue, it was a logic bug. The reason the insert command was failing is because the documentDetail.DocumentID was holding an ID which was created but not committed to the database yet. Therefore due to the fact that DocumentDetails had a foreign key to a not-created ID yet, it was failing during command execution. Maybe this will help someone in the future. Many thanks everyone