Sort fail in CosmosDB

[Yoan Dinkov]

Tested with:

NuGet Package version: 2.0.0

‌

Explanation:

Hi guys, great tool and kudos for the effort you are putting into this open-source project. Recently I decided to use that in my work-related project. So we are hosting our application in Azure using CosmosDB. They do classify themselves as a document-oriented database with MongoDB dialect. However, up until now, we treated it as a normal MongoDB instance.

It appears that they are supporting MongoDB 3.6, but have problems with some operations. This is an operation sort for example.

In their documentation sort looks like it’s partially supported, but from my experience, it only works fine on ObjectId and if you try to sort on other properties, it just fails with long and quite generic/nondescriptive error. After some investigation, I found this issue in SO, which actually helped me with running your tool.

So what was the actual problem, from my debugging session, I understand that you do something like this

‌

db.runCommand("find" : "_migrations", "filter" : { }, "sort" : { "applied" : -1 }, "limit" : 1 })

This, however, because of “sort” fails, but it creates the collection. Afterwards, a person should create an index on an applied field in this collection manually and then your tool works fine.

I am not sure if this is an actual bug as it is not directly related to MongoDB, but I wanted to share that with you so people that try to use your tool on CosmosDB will not have to spend several hours/days of debugging why is not working. Again maybe after some months, this will be fixed from Azure (you never know) but of the current moment, I think it’s worth mentioning (that prerequisite step of creating the index/or collection + index) in your readme.

[Arthur Osmokiesku]

Thank you for reporting this issue. I'll try to replicate this issue and fix it. But I can't say the exact time when an update will be ready.

[Yoan Dinkov]

@Arthur Osmokiesku Thanks for the reply Arthur, no worries - whenever you have time, your tool works great and at the end, we just ensured that index existed just before executing any migrations, now it works like a charm. Here is my code:

        public static IServiceCollection ApplyMongoDbMigrations(this IServiceCollection services, Action<IMongoConfiguration> config)
        {
            var configuration = new MongoConfiguration();
            config?.Invoke(configuration);

            EnsureCosmosDbIndexCreated(configuration);

            new MigrationEngine()
                .UseDatabase(configuration.ConnectionString, configuration.Database)
                .UseAssembly(Assembly.GetExecutingAssembly())
                .UseSchemeValidation(false)
                .Run();

            return services;
        }

        /// <summary>
        /// As CosmosDB is actually not exactly MongoDb, but rather it's own document database
        /// with MongoDB 3.6 API dialect, some of the operations are not supported as in
        /// native MongoDB database. Such command is `sort`, it's working for `_id` field
        /// by default and any other property should be indexed first. Thus this method
        /// ensures that `sort` operation on `applied` field will function properly
        /// for `_migrations` collection. Creating index operation in idempotent, so if index
        /// is existing it will not be recreated, but reused. Same goes for `_migrations` collection
        /// if it's not existing, index creation will ensure that the collection will exist as well.
        /// </summary>
        private static void EnsureCosmosDbIndexCreated(IMongoConfiguration config)
        {
            var mongoSettings = MongoClientSettings.FromUrl(new MongoUrl(config.ConnectionString));
            var mongoClient = new MongoClient(mongoSettings);
            var database = mongoClient.GetDatabase(config.Database);
            var collection = database.GetCollection<BsonDocument>("_migrations");

            var indexOptions = new CreateIndexOptions();
            var indexKeys = Builders<BsonDocument>.IndexKeys.Ascending("applied");
            var indexModel = new CreateIndexModel<BsonDocument>(indexKeys, indexOptions);
            collection.Indexes.CreateOne(indexModel);
        }

‌

[Arthur Osmokiesku]

Resolved in 6fc499d. Will be released in Nov 2020