We would rather handle the creation of these in another process using an account with the appropriate privileges

Unfortunately SQL Server has a bug with transaction isolation levels that could lead to invalid isolation levels being used. When getting a connection from the connection pool, the isolation level is not reset to the default (ReadCommitted) so isolation levels from previous connections can leak into new connections.

I created a rempro sample that showcases the problem (see attached files):
NEventStoreIsolationLevelReproSample.zip

PostgreSql uses a Serial data type, but LongCheckpoint is a Long so it is more correct to use a BigSerial.

From @thefringeninja on August 19, 2014 13:58

https://github.com/NEventStore/NEventStore/blame/master/src/NEventStore/Persistence/Sql/SqlDialects/CommonSqlDialect.cs#L193

Copied from original issue: NEventStore/NEventStore#367

Consider the following initialization code I used for evaluating NEventStore:

Wireup
.Init()
.UsingSqlPersistence("Core NEventStore")
.WithDialect(new MsSqlDialect())
.UsingJsonSerialization()
.Build();

It all ran fine in a standalone console applicaiton. However, when I tried to use it in the initialization routines of an ASP.NET web application. The root cause of the error is, of course, missing CREATE TABLE permissions on the IIS user within my database.

It took me some good four hours to detect this, because the initialization method of the SqlPersistenceEngine actively suppresses exceptions. I used a custom ILog with breakpoints in each method to jump through all log messages. It seems that the following things happen:

1. attempting to CREATE TABLE
2. fails due to insufficient permissions, but the exception is suppressed
3. transaction gets completed
4. attempting to CREATE TABLE again
5. fails due to transaction being already completed

In the end I was confronted with an exception that had really nothing much to do with the actual root cause of the error.

Since new feature development of System.Data.SqlClient pretty much stopped and some features like Authentication via Workload identities is already not supported, why not switch to Microsoft.Data.SqlClient?

Trying to get it to use the new version is very simple since all currently supported versions are supported by the new SqlClient. Trying to do it in the project pretty much only needed a 2 line change.

I can also create a PR for this if its something that would be acceptable.

Thanks!

As explained in this dba.stackexchange.com answer, the IDENTITY-based checkpointing mechanism used by the SqlPersistenceEngine with MsSqlDialect is not safe: commits could be skipped when using GetFrom or CommitPollingClient under high load. The probability of running into this scenario is probably very low, but it does exist.

See also NEventStore/NEventStore#425 for a discussion of possible solutions.

I have encountered an issue with the NEventStore reference in NEventStore.Persistence.SQL. When trying to run my .net core web app I receive the following exception.

Application startup exception: System.IO.FileNotFoundException: Could not load file or assembly 'NEventStore, Version=7.1.0.0, Culture=neutral, PublicKeyToken=null'. The system cannot find the file specified.
File name: 'NEventStore, Version=7.1.0.0, Culture=neutral, PublicKeyToken=null'

Inspecting the assembly using ILSpy I see that NEventStore.Persistence.SQL references
// NEventStore, Version=7.1.0.0, Culture=neutral, PublicKeyToken=null

As the assembly is not strong named I cannot add an assembly binding redirect to get around the issue as that requires a non null PublicKeyToken.

It is also worth noting that NEventStore.Persistence.SQL has an assembly version of 0.0.0.0

Hi!

I ran into a problem with NEventStore (we're currently using NES7): when I have two commits that have the same CommitStamp the order in which they are returned when I call IPersistStream.GetFrom(string bucketId, DateTime start) (or the FromTo variant) is random, since they are only ordered by CommitStamp, StreamId, CommitSequence (see https://github.com/NEventStore/NEventStore.Persistence.SQL/blob/master/src/NEventStore.Persistence.Sql/SqlDialects/CommonSqlStatements.resx#L150).

Is there any reason for this behavior? Why are not all queries sorted by CheckpointNumber?

Thanks in advance!

Hi,

We have built a system that is using the PollingClient to feed around 25 commit observing projection builders.
Occupationally in production we have simultaneously received crashes in several of our projection builders at the same time, at the very same commit checkpoint.
When we reset the checkpoint, and feed the projection builders with the entire event stream again, it passes perfectly.
So it appears it sometimes skips a few events!
It only seem to happen during highly intense read+write periods during imports of data.

SQL Azure's default transaction level is different from vanilla MSSQL.

If I write event+0 and event+1 to the database, is it possible that event+1 is available before event+0 in SQL Azure?

In MSSQL we haven't been able to reproduce this result.

Any clues?

When EnlistInAmbientTransaction is called in wire-up, the default isolation level of the SQL Persistence for SQL Server is changed from the database default of READ COMMITTED to SERIALIZABLE. This can and probably should be fixed directly in NEventStore (NEventStore/NEventStore#414), but if it isn't, a workaround should be added in NEventStore.Persistence.SQL because the SQL persistence doesn't work as nicely with the SERIALIZABLE isolation level (we experienced database-level deadlocks with multiple readers/writers), and it's also much less efficient.

Reason for the SERIALIZABLE isolation level: If the TransactionScopeOption.Required is used (via code in PersistenceWireup) without specifying an isolation level, the TransactionScope default isolation level of SERIALIZABLE is used if there is no ambient transaction.

From @Jared314 on February 17, 2015 5:5

The NEventStore.Serialization.SqlPersistanceFactory constructor that accepts a database connection string name, automatically resolve the sql dialect if non is provided. But, the constructor that accepts an IConnectionFactory requires an explicit sql dialect. The IConnectionFactory interface contains a method to get the provider type information and could also automatically resolve the dialect.

I currently utilize IConnectionFactory to wrap custom database resolution logic, and had to implement custom dialect detection. Is the behavior difference intentional? Should I not be using the UsingSqlPersistence(IConnectionFactory factory) overload for wireup?

Copied from original issue: NEventStore/NEventStore#395

I was planning to set up my bounded contexts asynchronously and though setting up a PollingClient for each one would be easiest.

Update 3: NEventStore/NEventStore#387

Update 2: After more investigation, eliminating the ClientPoller, it seems like the culprit is calling OpenStream() with a different bucketId. As far as I can tell, this is the very first call on this bucket, so why is there an open DataReader!?

Update: I was too hasty on this one. Ran a new test today, the synchronous code runs just fine. The problem starts when the poller interacts with asynchronous code!

Soon after my code runs, I get the following exception:

An exception of type 'NEventStore.Persistence.StorageUnavailableException' 
occurred in NEventStore.dll but was not handled in user code

Additional information: There is already an open DataReader associated with 
this Command which must be closed first.

As far as I can tell, I don't have direct access to any DataReader and find this error strange when I use the api. There's no way I can close it. That said, I haven't found any documentation for the PollingClient, so it could be experimental for what I know.

The PollingClient takes an IPersistStreams in its constructor, and the only way I have found to do that, is to pass store.Advanced:

var streams = Wireup.Init()
                    .UsingInMemoryPersistence()
                    .UsingSqlPersistence("EventStore").WithDialect(new MsSqlDialect())
                    .InitializeStorageEngine()
                    .UsingJsonSerialization()
                    .Build().Advanced;

poller = new PollingClient(streams, 500);

Each bounded context have their own instance of the store, so that shouldn't be the root cause:

• One context work with a separate service, asynchronously, but the exception is still thrown after I deactivate it.
• The other context using the PollingClient updates a database table directly without any additional async code.

Now, if an IPersisStreams is supposed to be disposed after each use, then I am kind of stuck because there is nowhere I can dispose it.

From @snissim on March 16, 2015 23:58

The CommitStamp parameter needs to be added to the command explicitly with DbType.DateTime2. I will create a PR.

Here's a before and after:

Copied from original issue: NEventStore/NEventStore#399

Running tests we get the following errors:

when_a_commit_is_successfully_persisted

• should_correctly_persist_the_commit_identifier - Failed

Expected _persisted.CommitId to be {02fa367b-6c91-4597-b871-5440c9d411ad}, but found {00000000-0000-0000-0000-000000000000}.

this is probably due to a braking change on how guids are handled.

The last working version is Microsoft.Data.Sqlite 2.2.6.

NEventStore/NEventStore#437

From @CoreyKaylor on August 27, 2013 23:2

The queries are always starting with a base row of 0. Here is one example where this happens, but it's not the only place assuming 0 as the starting index. Things seem to work fine until you get into very large datasets.

https://github.com/NEventStore/NEventStore/blob/master/src/NEventStore/Persistence/SqlPersistence/SqlDialects/CommonDbStatement.cs#L129

Copied from original issue: NEventStore/NEventStore#230

with the split (NEventStore/NEventStore#408) we need a new package configuration

• TeamCity build
• MyGet
• NuGet

I seems that I do not understand the concept of the IConnectionFactory.

You have the SqlPersistenceEngine and there is a method Initialize which creates the database tables during the bootup of the application. How do you know for which tenant. IConnectionFactory is not able to figure this out without the bucketId. This can't be done during startup of the application. The IConnectionFactory.Open should get the bucketId passed like mentioned here #2

IConnectionFactory could also have a method IsInitialized(buckedId) which must be implemented by different providers so that you know if the tables should be created or not. Another option is that you cache the IDbConnnection.ConnectionString to figure out if the initialization is required.

• remove the methods: 'GetUndispatchedCommits', 'MarkCommitAsDispatched' from the ISqlDialect interface and implementation
• remove the methods: 'GetUndispatchedCommits', 'MarkCommitAsDispatched' from the SqlPersistenceEngine class
• cleanup the database schemas and queries from the dispatched field references
• clean the resx file from the unneeded pieces

See NEventStore/Discussions#1

The schema initialization SQL string (https://github.com/NEventStore/NEventStore.Persistence.SQL/blob/master/src/NEventStore.Persistence.Sql/SqlDialects/MsSqlStatements.resx) has a bug: it uses "SYS.types", however, it should actually use "sys.types" (casing!). By default that is not immediately a problem, as the default collation of SQL Server is case insensitive. As soon as you use a collation which is not case insensitive (we need that for our application), the script fails.

Fix: simply use the correct schema "sys" instead of "SYS".

The fix implemented in #35 ruined the performance of the GetFrom and GetFromTo queries. It's still required to use CommitStamp, CheckpointNumber as ORDER BY clause in order that the corresponding index can be used.

Unfortunately I don't have the time for a pull request, but it should be a fairly minor change anyways, most effort will be to test that the index is now indeed used by the query.

Best regards,
D.R.

copied from: NEventStore/NEventStore#377

also check the original story for more comments.

From @di97mni on March 5, 2015 9:48

Add bucket id to IConnectionFactory.Open() to be able to separate DBs per tenant

In current solution we need to wire up one eventstore per tenant instead of implementing a custom connection factory that can resolve DB per bucket id/tenant

Copied from original issue: NEventStore/NEventStore#397

NEventStore's SqlPersistenceEngine relies on database-level locks when reading commits. When one enables SQL Server's READ COMMITTED SNAPSHOT option at the DB level, this stops working and there's a high possibility of skipping commits. On Azure SQL this option is always on, so one will easily experience lost commits on Azure SQL. This was reported previously, e.g., in #14 or #21 (comment).

To cope with that, the MsSqlDialect should use READCOMMITTEDLOCK table hints when reading commits to enforce locking.

NEventStore/NEventStore#414

If you look at the code of method SqlPersistenceEngine::ExecuteQuery you can notice that nor connection nor transaction gets disposed if the code is successful.

Compare ExecuteQuery with the implementation of SqlPersistenEngine::ExecuteCommand that correctly disposes everything.

This can lead to serious problem, and actually probably it did not generated error because the standard implementation of OpenTransaction returns null.

I think that all the disposable object should be wrapped in using clause.

Enable documentation file generation adding the following line to all csproj that generare NuGet packages

<GenerateDocumentationFile>True</GenerateDocumentationFile>

From @jskrepnek on February 4, 2015 21:33

Hi,

I'm working with a DB requirement (Oracle) that tables be owned by a user ("schema") that is different from the user that accesses those tables. I'm trying to understand how, if possible, to support this with NEventStore and/or Oracle.

For example:

Given a user APPLICATION
and a schema EVENTSTORE

The commits table would need to be 'owned' by EVENTSTORE; i.e.: EVENTSTORE.COMMITS.

Then the user APPLICATION would be granted the necessary privileges to access EVENTSTORE.COMMITS.

However, the APPLICATION user accesses the Commits table implicitly; i.e.: SELECT * FROM COMMITS. As far as I know, the Commits table would be assumed to be owned by the APPLICATION user.

What I need is to be able to have NEventStore explicitly use the owning schema: i.e.: SELECT * FROM EVENTSTORE.COMMITS

I appreciate that this may as much be a problem that we solve outside of NEventStore; so I'm hoping to connect with others that have worked around a similar situation.

Thanks,

Joel

Copied from original issue: NEventStore/NEventStore#386

From @adamralph on May 7, 2014 10:55

Perhaps an edge case which you don't care about, but I thought I'd put it here FWIW.

If I have a Commits table in a Foo schema, this line shortcircuits the schema creation

IF EXISTS(SELECT * FROM sysobjects WHERE name='Commits' AND xtype = 'U') RETURN;

I guess it needs to check if it's in the target schema, not just it's name.

Copied from original issue: NEventStore/NEventStore#343

From @danbarua on February 13, 2015 10:21

I'm not sure how relevant this is to V5/6 but this massively speeds up replays on MSSQL:

CREATE NONCLUSTERED INDEX [IX_Commits_Ordered] ON [dbo].[Commits]
(
    [CommitSequence] ASC,
    [StreamId] ASC,
    [StreamRevision] ASC
)

Copied from original issue: NEventStore/NEventStore#391

Due to the Breaking change introduced by version 6 of the driver dealing with timestamp (see the release notes at: https://www.npgsql.org/efcore/release-notes/6.0.html#timestamp-rationalization-and-improvements) there's an error while persisting the commits to the database.

The 'ColumnStamp' colum is of type 'timestamp' while it should be 'timestamptz'.

To use this version of the driver you manually need to update the table schema following the guidelines provided in the driver release notes and breaking changes.

The PostgreSQL driver should be updates in order to generate the correct schema when version 6 of the driver is used.

A possible solution is introduce a custom PostgreSQL dialect.