create index on existing collection on a MongoDB replica set (PRIMARY)

Question

I want to add an index on an existing collection in a MongoDB replica set. This replica set has only a Primary, at the moment, and is configured in this way to be able to use MongoDB change streams.
I log into Mongo shell using:
mongo -u <Account>
where <Account> is an account with role 'userAdminAnyDatabase '.
Then issue the commands:

use dbOfInterest 
db.collectionOfInterest.createIndex({fieldOne:-1})

But after issuing the command above the console seems not responding anymore (after several minutes I had to stop it using CTRL+C).
The considered collection is a test one with very few documents.
What am I doing wrong?

What version of MongoDB are you using? userAdminAnyDatabase is a role that according to the MongoDB documentation does not have createIndex privileges for its versions prior to 2.6.4. The roles that do have privileges for index modification are, for any version of MongoDB: readWriteAnyDatabase, readWrite, dbAdmin. Maybe you can try one of them. — MauriRamone, Jan 11 '21 at 15:58
Another suggestion. Start the MongoDB terminal by adding --verbose to increase the verbosity of the output of the shell during the connection process. For example: mongo -u --verbose — MauriRamone, Jan 11 '21 at 16:02
I tried in Compass and the result is the same; the GUI stops responding. After killing Compass GUI and restarting it I found the index created but not sure that it is effective/complete. @MauriRamone, I use mongo v4.4.3 and the account used has the following roles : "userAdminAnyDatabase", "readWriteAnyDatabase", "clusterManager". — EanX, Jan 12 '21 at 06:58

score 2 · Answer 1 · answered Jan 13 '21 at 09:27

Finally solved!!.
The issue is indeed related to "replica set node with auth can connect to itself" (more here). I had to modify my mongod (file /etc/mongod.conf) configuration as follow:

# mongod.conf
# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# Where and how to store data.
storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1


# how the process runs
processManagement:
  timeZoneInfo: /usr/share/zoneinfo

security:
  keyFile: /home/user1/Desktop/mongo.keyfile
  authorization: enabled
#operationProfiling:

replication:
  replSetName:  AirHeritage

#sharding:

## Enterprise-Only Options:

#auditLog:

#snmp:

Note keyFile section into YAML mongod.conf file (pay attention to white spaces). To correctly generate this mongo.keyfile I used:

openssl rand -base64 756 > <path-to-keyfile>
chmod 400 <path-to-keyfile>

Then do:

sudo chown mongodb:mongodb /home/user1/Desktop/mongo.keyfile

Check that results are something as:

ls -al /home/user1/Desktop/mongo.keyfile                                                           
-r-------- 1 mongodb mongodb 1024 gen 13 09:19 /home/user1/Desktop/mongo.keyfile

Then stop and restart mongod using:

sudo systemctl stop mongod 
sudo systemctl start mongod

Check status:

 sudo systemctl status mongod
● mongod.service - MongoDB Database Server
     Loaded: loaded (/lib/systemd/system/mongod.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2021-01-13 09:25:59 CET; 44min ago
       Docs: https://docs.mongodb.org/manual
   Main PID: 10156 (mongod)
     Memory: 348.6M
     CGroup: /system.slice/mongod.service
             └─10156 /usr/bin/mongod --config /etc/mongod.conf

gen 13 09:25:59 eanx-XPS-13-9350 systemd[1]: Started MongoDB Database Server.

Then log into mongo console as root (not sure it is necessary root, should suffice ClusterAdmin role):

mongo -u root
use dbOfInterest
db.collectionOfInterest.createIndex({field:1})

Having done all that before, index creation should have worked without hanging the console with a result as:

 db.collectionOfInterest.createIndex({'field':1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "commitQuorum" : "votingMembers",
        "ok" : 1,
        "$clusterTime" : {
                "clusterTime" : Timestamp(1610526522, 5),
                "signature" : {
                        "hash" : BinData(0,"qSqkVswHQA/IzWGYCd8HwNhXoQk="),
                        "keyId" : NumberLong("6877458498892857349")
                }
        },
        "operationTime" : Timestamp(1610526522, 5)
}

To check MongoDB logs use:

sudo tail /var/log/mongodb/mongod.log | jq

(if not installed in your system use sudo apt install jq, jq is very useful to pretty print json files)

Finally check indexes on collection with:

 db.collectionOfInterest.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_"
        },
        {
                "v" : 2,
                "key" : {
                        "field" : 1
                },
                "name" : "field.name_1"
        }

Note that two key fields are reported: "_id" (by default on collection creation) and "field"!! Hope this can help someone else having a similar issue. Only move the mongo.keyfile to a more suitable location (someone can suggest where?)

score 0 · Answer 2 · answered Jan 12 '21 at 14:09

0

What you do is creating index "foreground" and that blocks usage of DB, until creation is ready. You need to do it "background".

db.collectionOfInterest.createIndex({fieldOne:-1},{background:1})

answered Jan 12 '21 at 14:09

JJussi

1,540
12
12

Since MongoDB 4.2, `background` is [deprecated](https://docs.mongodb.com/manual/reference/method/db.collection.createIndex/) and is ignored if specified. – EanX Jan 12 '21 at 17:54

create index on existing collection on a MongoDB replica set (PRIMARY)

2 Answers2