Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Inserting data after update results in "Column unknown" or "Must declare the variable '@px' errors [DNET945] #866

Closed
firebird-automations opened this issue Aug 4, 2020 · 13 comments

Comments

@firebird-automations
Copy link

Submitted by: Jan Pross (jan)

Is duplicated by DNET974

Attachments:
FbNetProviderIssue.zip

Error is basically the same as in issue described here https://stackoverflow.com/questions/56357771/how-to-fix-must-declare-the-variable-with-firebirdsql-entityframeworkcore-fire

I've created simple web application where you can reproduce the issue https://filebin.net/cpfsuoh16xdl28xi
Steps to reproduce:
1. Build and run the web app
2. Repeat the same request e.g. 50 times by using some tool (if the requests are generated by refreshing the browser, the issue will not occur)
2. a. Capture the request e.g. with fiddler
2. b. Repeat the request from fiddler 50 times
3. An error occurs:
3. a.

Dynamic SQL Error
SQL error code = -206
Column unknown
P0
At line 2, column 9

at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__32.MoveNext()

OR

3. b.

Must declare the variable '@p1'

at FirebirdSql.Data.FirebirdClient.FbCommand.UpdateParameterValues()
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet)
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__32.MoveNext()

Names of the columns and parameters can be different each time. Some requests will pass successfully some will result in beforehand mentioned errors. When an error occurs any other following request will result in an error unless the whole server is restarted.

We encountered this issue after switching some other parts of our solution to firebird. We are using 7.1.1 driver and we can not update to 7.5.0 for now (as it depends on EF core >= 3.0).

Commits: aaecd10

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Given the 2.2 is already EOL (and my limited resources), I'll investigate only if you'll replicate this on 3.1 (and I think I fixed issue around this). Thanks for understanding.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Author

Commented by: Jan Pross (jan)

We will be able to upgrade to ef core >= 3.1 in the end I suppose, so I modified the test project by upgrading the ef core dependency to 3.1.1 and firebird to 7.5.0. I run the test case (50 requests) and it passed without any problem. I tried it again (clean test, delete database file etc.) and I hit the same error.

Dynamic SQL Error
SQL error code = -206
Column unknown
P0

I forgot to mention, that we are using Firebird-3.0.5.33220_0_x64

Modified web app: https://filebin.net/u8xdiohf9kkerpwr

@firebird-automations
Copy link
Author

Commented by: Mattias Wijkström (omwijk)

We have seen the exact same issue with our .NET Framework 4.8 desktop application using Firebird .NET Provider 7.5.0 and Entity Framework Core 3.1.3. We run Firebird 3.0.5 in Super server mode.

We have observed the errors with the following stacktraces:

Dynamic SQL Error
SQL error code = -206
Column unknown
P0
At line 2, column 9

FirebirdSql.Data.Client.Native.FesDatabase.ProcessStatusVector
FirebirdSql.Data.Client.Native.FesStatement.Prepare
FirebirdSql.Data.FirebirdClient.FbCommand.Prepare
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute
Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges
Microsoft.EntityFrameworkCore.DbContext.SaveChanges

-and-

Must declare the variable '@p3'

FirebirdSql.Data.FirebirdClient.FbCommand.UpdateParameterValues
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand
FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader
System.Data.Common.DbCommand.ExecuteDbDataReaderAsync
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification
Microsoft.EntityFrameworkCore.Storage.RelationalCommand+<ExecuteReaderAsync>d__17.MoveNext
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw
Microsoft.EntityFrameworkCore.Storage.RelationalCommand+<ExecuteReaderAsync>d__17.MoveNext
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw
Microsoft.EntityFrameworkCore.Storage.RelationalCommand+<ExecuteReaderAsync>d__17.MoveNext
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch+<ExecuteAsync>d__29.MoveNext
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw
Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor+<ExecuteAsync>d__8.MoveNext
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw
Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor+<ExecuteAsync>d__8.MoveNext
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager+<SaveChangesAsync>d__97.MoveNext
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager+<SaveChangesAsync>d__101.MoveNext
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification
Microsoft.EntityFrameworkCore.DbContext+<SaveChangesAsync>d__54.MoveNext

@firebird-automations
Copy link
Author

Commented by: @cincuranet

The https://filebin.net/u8xdiohf9kkerpwr does not give me anything. Please upload the file(s) directly here.

@firebird-automations
Copy link
Author

Commented by: Mattias Wijkström (omwijk)

Attaching a console app that produces the errors above.

@firebird-automations
Copy link
Author

Modified by: Mattias Wijkström (omwijk)

Attachment: FbNetProviderIssue.zip [ 13480 ]

@firebird-automations
Copy link
Author

Commented by: Mattias Wijkström (omwijk)

Reproducable with Firebird .NET provider 6.6.0 and Entity Framework Core 2.2.4.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

priority: Blocker [ 1 ] => Critical [ 2 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Version: 7.5.0.0 [ 10915 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: vNext [ 10920 ]

@firebird-automations
Copy link
Author

Commented by: Benjamyn Williams (ben_williams)

This is probably caused by the changing of sqlGenerationHelper.ParameterNameMarker in DbUpdateSqlGenerator within methods AppendUpdateOperation and AppendDeleteOperation when queries are being constructed concurrently.
This has caused mismatches between parameters and the query text where the query would be referring to "@p0" while the parameters object has ":p0".

This needs to be solved so that changing the ParameterNameMarker will not affect other concurrent query generation.

Edit: Confirmed this by redirecting those particular parts (and extracting the internal methods that EF uses) to a new version of sqlGenerationHelper with ":" instead of using the current one.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is duplicated by DNET974 [ DNET974 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants