Databases

Mar

2019

How to fix ElasticSearch ‘Fielddata is disabled on text fields by default’ for keyword field

Problem:

You have a field in ElasticSearch named e.g. patterns of type keyword. However, when you query for an aggregation of this field e.g.

es.search(index="strings", body={
    "size": 0,
    "aggs" : {
        "patterns" : {
            "terms" : { "field" : "pattern" }
        }
    }
})

you see this error message:

elasticsearch.exceptions.RequestError: RequestError(400, 'search_phase_execution_exception', 'Fielddata is disabled on text fields by default. Set fielddata=true on [pattern] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead.'

Solution:

This error message is confusing since you already have a keyword field. However, the ElasticSearch fielddata documentation tells us that you need to to use pattern.keyword in the query instead of just pattern.

Full example:

es.search(index="strings", body={
    "size": 0,
    "aggs" : {
        "patterns" : {
            "terms" : { "field" : "pattern.keyword" }
        }
    }
})

Mar

2019

ElasticSearch docker-compose.yml and systemd service generator

Just looking for a simple solution with a single ElasticSearch node? See our new post Simple Elasticsearch setup with docker-compose

New: Now with ElasticSearch 7.13.4

This generator allows you to generate a systemd service file for a docker-compose setup that is automatically restarted if it fails.

Continue reading →

Mar

2019

How to fix ‘elasticsearch exited with code 78’

Problem:

You want to run ElasticSearch using docker, but the container immediately stops again using this error message

elasticsearch exited with code 78

elasticsearch2 exited with code 78

Solution:

If you look through the entire log message, you’ll find lines like

elasticsearch     | [1]: max virtual memory areas vm.max_map_count [65530] is too low, increase to at least [262144]

Therefore we need to increase the vm.max_map_count limit:

sudo sysctl -w vm.max_map_count=524288

Now we need to edit /etc/sysctl.conf so the setting will also be in effect after a reboot.

Look for any vm.max_map_count line in /etc/sysctl.conf. If you find one, set its value to 524288. If there is no such line present, add the line

vm.max_map_count=524288

to the end of /etc/sysctl.conf

Original source: GitHub

Mar

2019

MongoDB: How to run db.adminCommand() in NodeJS

Problem:

You want to run a db.adminCommand() in NodeJS using the node-mongodb-native client, e.g. you want to run the NodeJS equivalent of

db.adminCommand({setParameter: 1, internalQueryExecMaxBlockingSortBytes: 100151432});

Solution:

Use conn.executeDbAdminCommand() where db is a MongoDB database object.

db.executeDbAdminCommand({setParameter: 1, internalQueryExecMaxBlockingSortBytes: 100151432});

Full example:

// To install, use npm i --save mongodb
const MongoClient = require('mongodb').MongoClient;

async function configureMongoDB() {
    // Connect to MongoDB
    const conn = await MongoClient.connect('mongodb://localhost:27017/', { useNewUrlParser: true });
    const db = await conn.db('mydb');
    // Configure MongoDB settings
    await db.executeDbAdminCommand({
        setParameter: 1,
        internalQueryExecMaxBlockingSortBytes: 100151432
    });
    // Cleanup
    return conn.close();
}

// Run configureMongoDB()
configureMongoDB().then(() => {}).catch(console.error)

Mar

2019

How to fix NodeJS MongoDB ‘Cannot read property ‘high_’ of null’

When encountering an error message like

TypeError: Cannot read property 'high_' of null
    at Long.equals (/home/uli/dev/NMUN/node_modules/bson/lib/bson/long.js:236:31)
    at nextFunction (/home/uli/dev/NMUN/node_modules/mongodb-core/lib/cursor.js:473:16)
    at Cursor.next (/home/uli/dev/NMUN/node_modules/mongodb-core/lib/cursor.js:763:3)
    at Cursor._next (/home/uli/dev/NMUN/node_modules/mongodb/lib/cursor.js:211:36)
    at nextObject (/home/uli/dev/NMUN/node_modules/mongodb/lib/operations/cursor_ops.js:192:10)
    at hasNext (/home/uli/dev/NMUN/node_modules/mongodb/lib/operations/cursor_ops.js:135:3)
    (...)

you likely have code like this:

const cursor = db.getCollection('mycollection').find({})
while (cursor.hasNext()) {
    const doc = cursor.next();
    // ... handle doc ...
}

The solution is quite simple: Since find(), cursor.hasNext() and cursor.next() all return Promises, you can’t use their results directly.

This example shows you how to do it properly using async/await:

const cursor = await db.getCollection('mycollection').find({})
while (await cursor.hasNext()) {
    const doc = await cursor.next();
    // ... handle doc ...
}

In order to do this remember that the function containing this code will need to be an async function. See the Mozilla documentation or google for Javascript async tutorial in order to learn about the details!

Mar

2019

How to install MongoDB CE on Ubuntu in 1 minute

Quick install using

wget -qO- https://techoverflow.net/scripts/install-mongodb.sh | bash

Run these shell commands on your Ubuntu computer to install the current MongoDB community edition and automatically start it (both instantly and on bootup)

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 4B7C549A058F8B6B
echo "deb [ arch=amd64 ] https://repo.mongodb.org/apt/ubuntu bionic/mongodb-org/4.2 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-4.2.list
sudo apt-get update
sudo apt-get install -y mongodb-org
sudo systemctl enable mongod
sudo systemctl start mongod

Source: Official MongoDB documentation

Mar

2019

MongoDB equivalent of SQL ‘SELECT * FROM …’

Whereas in SQL you might run

SELECT * FROM mytable;

to view all entries in a table, you can run

db.getCollection("mytable").find({})

in MongoDB. Also see the official SQL to MongoDB mapping chart.

In order to run that (assuming the default configuration of mongod), you can run

mongo [name of database]

in your preferred shell.

Jun

2018

How to make mongodump export JSON instead of BSON

mongodump exports your MongoDB database to a BSON format, but if you want to use JSON instead, you need to use the mongoexport tool.

Examples:

mongoexport -d mydatabase -c mycollection # Prints to stdout
mongoexport -d mydatabase -c mycollection -o mycollections.json # Write to mycollections.json

Note that some datatypes that can be stored in MongoDB can only be represented in BSON – so in some cases, information may be lost by exporting to JSON.

May

2017

Exporting campaign contact CSVs from VTiger using SQL

In Exporting campaign account CSVs from VTiger using SQL we investigated how to use direct SQL database access to export names and emails for organizations for a given campaign.

This post shows how to do the same thing. Please refer to the previous post for a detailed explanation.

SELECT CONCAT(cont.firstname, " ", cont.lastname) AS "Name", cont.email AS "EMail" FROM vtiger_campaigncontrel AS ccr JOIN vtiger_contactdetails cont ON (cont.contactid = ccr.contactid) WHERE `campaignid` =(SELECT postvalue FROM `vtiger_modtracker_detail` WHERE fieldname = "record_id" AND `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no")) AND cont.email <> ''

Besides joining on vtiger_contactdetails and vtiger_campaigncontrel instead of the account-related equivalent, the only major difference is in assembling the name. For contacts, first name and last name are stored separatedly. Therefore we use CONCAT(cont.firstname, " ", cont.lastname) to get a single name string out of that information.

As shown in the previous post, you can use phpmyadmin to export the result of the query to CSV or to Excel directly. While this might seem like a tedious process, it is much less tedious than copy-and-pasting hundreds of contacts manually.

Apr

2017

Exporting campaign account CSVs from VTiger using SQL

Although VTiger works great as CRM, its campaign management feature is missing one of most important features: It doesn’t support exporting

This post aims to provide technically oriented users a guide on how to use SQL queries to perform that task. We assume basic knowledge of SQL syntax, MySQL and PHPMyAdmin.

The goal for our example is to export a table of Account name and E-Mail. For the email, the primary email will be used, unless it’s empty in which case the secondary email is used.

The input for the query is the campaign name (in our example, this will be MyCampaign).

Querying the campaign number

The campaign number is a string, e.g. CAM4 or CAM9 which is stored in vtiger_campaign.

SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign"

Finding the campaign record ID

In the campaign account relations n-to-n relations table, the campaign number is not used directly – instead, the campaign record ID is used. We need to query that from vtiger_modtracker_detail, which contain metadata key-value-data about the campaigns (among other things). One of the K-V pairs contain the campaign identifier (key campaign_no) whereas another one contains the record ID (key record_id).

SELECT postvalue FROM `vtiger_modtracker_detail` WHERE `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = <<campaign number>> and fieldname = "campaign_no") AND fieldname = "record_id"

Full query:

SELECT postvalue FROM `vtiger_modtracker_detail` WHERE `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no") AND fieldname = "record_id"

The result of this query is the numeric campaign record ID, e.g. 315.

Querying campaign-to-account relations

We can now use the campaign record ID to query the n-to-n table vtiger_campaignaccountrel. There are other, similar tables for leads (vtiger_campaignleadrel) and contacts (vtiger_campaigncontrel).

SELECT accountid FROM vtiger_campaignaccountrel WHERE `campaignid` = <<campaign record ID>>

Full query:

SELECT accountid FROM vtiger_campaignaccountrel WHERE `campaignid` = (SELECT postvalue FROM `vtiger_modtracker_detail` WHERE fieldname = "record_id" AND `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no"))

The result of this query is a list of account IDs related to the campaign. The order is usually the same as displayed in VTiger.

Querying account information

Using the list of account IDs, we can JOIN on vtiger_account to query the account details. Note that you need to use the appropriate table for leads or contacts if you used an alternative relations table in the previous step.

SELECT acc.accountname AS "Name", COALESCE(acc.email1, acc.email2) AS "EMail" FROM vtiger_campaignaccountrel AS car JOIN vtiger_account acc ON (acc.accountid = car.accountid) WHERE `campaignid` =(SELECT postvalue FROM `vtiger_modtracker_detail` WHERE fieldname = "record_id" AND `id` = (SELECT `id` FROM `vtiger_modtracker_detail` WHERE postvalue = (SELECT campaign_no FROM vtiger_campaign WHERE `campaignname` = "MyCampaign") and fieldname = "campaign_no")) AND (acc.email1 <> '' OR acc.email2 <> '')

Note that besides the SELECT column list there are some aspects which are specific to the Name and Email query:

COALESCE(acc.email1, acc.email2) uses email2 if email1 is empty
AND (email1 <> '' OR email2 <> '') ignores accounts that have neither email1 nor email2 set

If you want a custom list of columns, you need to modify that appropriately.

Exporting CSV

When entering the query into a tool like PHPMyAdmin, you can click the Export button in order to export the result to different formats, including CSV and Excel.

While this might not be the most convenient option, it’s a very flexible way of generating and exporting the data. Any more convenient ways are likely to be less flexible.

Feb

2013

Compiling LevelDB as LLVM binary on Linux

Some time ago I wrote a guide on how to compile and install LevelDB on Linux.

Recently I’m desperately trying to get into LLVM and a tutorial series on how to use LLVM with C/C++ is coming shortly.

As I’m using LevelDB in many of my projects I’d like a way of generating a LLVM IR (intermediate representation) of the LevelDB C++ source – I could link a LLVM program to the native binary, but in order to profit from LLVMs features I suppose using IRs for as many dependencies as possible is the way to go.

Generally there are two ways to go:

Use the g++ LLVM backend
Use clang++

I usually tend to use clang++ for LLVM tasks because even with colorgcc and some recent improvements in gcc error message generation I prefer the clang++ error messages, even if I have way more experience with gcc error messages. Additionally the g++ with LLVM backend does seem to have some bugs, including interpreting -emit-llvm as -e -m -i …, plus recent distribution versions don’t work too well with the LLVM gold plugin and it has proved difficult to tell GCC reliably that it shall use llvm-ld as linker.

Continue reading →

Dec

2012

Compiling & Installing LevelDB on Linux

Update: Please also take a look at this followup article for an automatic compilation script that builds Ubuntu DEB packages!

Problem:

You want to compile and install LevelDB (including development headers) on your Linux computer. ./configure && make && make install does not work so you don’t know how to do this.

or:

You have successfully compiled LevelDB, but make install doesn’t work (there is no official installation procedure yet) and you don’t know how to install it to your system

Continue reading →

Problem:

Solution:

Problem:

Solution:

Problem:

Solution:

Full example:

Querying the campaign number

Finding the campaign record ID

Querying campaign-to-account relations

Querying account information

Exporting CSV

Problem:

Search

Categories