Rebuild All Couchbase N1QL Indexes After Restore

Overview

When restoring a Couchbase cluster from a backup, the restore utility is kind enough to recreate the N1QL indexes for you.  To improve speed and efficiency, the indexes are only created, they are not built automatically.  Before they can be used, you must execute a build command such as this:

BUILD INDEX ON BucketName (IndexName1, IndexName2, IndexName3)

It is important that this query be issued as a single command for all indexes on a bucket.  This allows the indexes to be built together, resulting in only one read of the data from the cluster while building multiple indexes.

The Problem

Unfortunately, N1QL doesn’t currently offer a wildcard option, so there is no quick way to rebuild all indexes without typing the all of their names.  If you’re trying to script your environments for development or QA this can be particularly problematic, as the list of indexes may not be constant. It could also be a problem when creating scripts for a disaster recovery plan.

The Solution

If you’re running on Linux (you should be for production clusters), the solution is to use this script:

#!/bin/sh

QUERY_HOST=http://localhost:8091

for i in "BucketName1" "BucketName2" "BucketName3"
do
  /opt/couchbase/bin/cbq -e $QUERY_HOST -s="$( \
    echo "BUILD INDEX ON $i (\`$( \
      /opt/couchbase/bin/cbq -e $QUERY_HOST -q=true -s="SELECT name FROM system:indexes where keyspace_id = '$i' AND state = 'deferred'" | \
        sed -n -e '/{/,$p' | \
        jq -r '.results[].name' | \
        sed ':a;/.*/{N;s/\n/\`,\`/;ba}')\`)")"

  # Wait for completion
  until [ `/opt/couchbase/bin/cbq -e $QUERY_HOST -q=true -s="SELECT COUNT(*) as unbuilt FROM system:indexes WHERE keyspace_id = '$i' AND state <> 'online'" | sed -n -e '/{/,$p' | jq -r '.results[].unbuilt'` -eq 0 ];
  do
    sleep 5
  done
done

Replace the QUERY_HOST parameter as needed to point to a query node, and replace the BucketName values with the names of the buckets where indexes must be built.  It will process each bucket one at a time, waiting for the indexes to be built before continuing to the next bucket.

The only depedency is the jq utility, which is a command line JSON parser. On Ubuntu, this can be installed via:

sudo apt-get install -y jq

The script isn’t pretty, but it gets the job done. Hopefully N1QL will get a wildcard BUILD INDEX command in the future.

Note: Revised 9/15/2016 to better strip header information from the query output before the JSON object. Previously it only stripped the first line, now it strips all lines until it encounters the curly brace starting the JSON result.

Couchbase Server and Windows 10 Anniversary Edition Problems

Update: This issue has been resolved in Couchbase Server 4.6 Developer Preview. You can certainly continue to use Docker, but there is no longer a requirement with Windows 10 Anniversary Edition.

The Problem

Recently, I ran into some problems with my Couchbase Server 4.5 installation on my Windows 10 development box. The memcached process would crash over and over again with an error code 255.

After doing some research (and getting some assistance, thanks @ingenthr), I determined it’s a known bug in Couchbase Server introduced by the recent release of Windows 10 Anniversary Edition. Apparently, Couchbase Server uses a third party library which incorrectly uses some private Windows APIs for memory allocation. The Windows 10 Anniversary Edition update removed these API calls, causing the crashes. The bug report is filed with Couchbase as MB-20519.

The Workaround

The only known direct workaround is to uninstall the Windows 10 Anniversary Update. Personally, I don’t find this to be a very good solution. Additionally, based on the bug report, I’m not optimistic about a quick fix from Couchbase. It seems like there’s a lot of work involved, and it understandably isn’t urgent because Windows is only supported for development, not production.

I decided instead to play with Docker, and I was very pleasantly surprised at how easy it was to use Docker to get Couchbase Server running on a Windows box. It only took me a few minutes.

  1. Be sure that Hyper-V is installed on your machine via “Turn Windows features on or off” in Control Panel
  2. Install Docker for Windows (I used the Stable Channel)
  3. Start Docker (I did this as the last step of the installation)
  4. Right click the Docker icon in your system tray (next to the clock), and open Settings.  Go to Shared Drives, and share your C drive.  This will require your WIndows password.
  5. Open Powershell and run this command to make a data folder:

    mkdir $env:userprofile\Couchbase

  6. Then run this command to startup the Docker container:

    docker run -d --name db -p 8091-8094:8091-8094 -p 11207:11207 -p 11210-11211:11210-11211 -p 18091-18093:
    18091-18093 -v $env:userprofile/Couchbase:/opt/couchbase/var couchbase

  7. Once complete, open http://localhost:8091/ to complete server configuration

Notes

This configuration will always create the Docker container with the latest version of Couchbase Server, currently 4.5.  Command line arguments can be used to alter this, see the Docker pages for Couchbase for more information.

This configuration puts all Couchbase data in your C:\Users\myusername\Couchbase folder.  If you remove the Docker container and recreate, it will start up with your configuration and data already intact.  If you want to start from scratch, delete this folder before recreating the Docker container.

There are a few of compatibility requirements for this solution:

  1. Hyper-V is incompatible with VirtualBox. If you are using VirtualBox, you should use a different solution.
  2. The client and management ports used by Couchbase must be available on your local machine.
  3. This setup only supports running a single Couchbase node, otherwise there would be network port contention.