Geert JM Vanderkelen

In Pylons v1.0 you can define global variables by adding them to the Globals-class. If you want a variable called spam and you want it to be globally available, your lib.app_globals.Globals-class would look like this:

class Globals(object):
  def __init__(self, config):
    self.cache = CacheManager(**parse_cache_config_options(config))
    self.spam = False

To use it in a model-module for example, you have to import app_globals from the pylons module, like this:

from pylons import app_globals as g

print g.spam

It took me a while to figure this out.. And I’m starting to like Pylons somehow.

MySQL Connector/Python is (or should be) compliant with the Python DB-API 2.0 specification. This means that you can use DBUtils’ PooledDB module to implement database connection pooling.

Here below you’ll find an example which will output the connection ID of each connection requested through the pooling mechanism.

from DBUtils.PooledDB import PooledDB
import mysql.connector

def main():
    pool_size = 3
    pool = PooledDB(mysql.connector, pool_size,
        database='test', user='root', host='127.0.0.1')

    cnx = [None,] * pool_size
    for i in xrange(0,pool_size):
        cnx[i] = pool.connection()
        cur = cnx[i].cursor()
        cur.execute("SELECT CONNECTION_ID()")
        print "Cnx %d has ID %d" % (i+1,cur.fetchone()[0])
        cur.close()

    for c in cnx:
        c.close()

The output will be something like this:

Cnx 1 has ID 42
Cnx 2 has ID 41
Cnx 3 has ID 40

This post explains how to disable Arbitration when using MySQL Cluster. It gives a case where this could be useful.

First, a piece of advice: you do not want to run MySQL Cluster with arbitration disabled. But if you must, e.g. because of an oversight in your implementation, you can.
Arbitration is very important in MySQL Cluster. It makes sure you don’t end up with a Split Brain situation: 2 halves working independently, continuing changing data, making it impossible for them to work together later on.

However, Arbitration comes with a price: you need an extra machine. “Sure, what’s the big deal?”. It’s not that easy when you lack the money, or more problematic, when you lack the real-estate in your rack.

Everyone running MySQL Cluster should know that you should not run the ndb_mgmd on the same machines on which the data node processes, ndbd or ndbmtd, are running. The Management Nodes need to be on a separate machine so it can act as an Arbitrator.

Here’s an example why: If you have two hosts A and B and both are running a management and data node process. Host A’s ndb_mgmd is currently the Arbitrator. Now unplug host A *BANG*: one data node and the arbitrator down. The other data node on Host B notices this, and tries to figure out if it can continue. So it checks if it can reach the Arbitrator: but it’s gone as well! So, the data node on host B goes faithfully down. This all happens in a few seconds, there is no time to elect a new Arbitrator. “Cluster’s dead, Jim”.

What if you can’t get a 3rd machine? There’s an option for that.. Data nodes can be configured with setting the Arbitration-option to WaitExternal. This means you will have to develop your own arbitration application or script. How cool is that? Well, it might be cool, but it’s a pain in the butt.

[ndbd default]
Arbitration = WaitExternal
ArbitrationTimeout = 3

What happens with our 2 host setup with above changes: When Host A, which has the Arbitrator, goes down, the data node on Host B will wait for 3 seconds, i.e. ArbitrationTimeout. It will block all incoming transactions, refusing changes. An application, the External Arbitrator, running on Host B (actually on all hosts running MySQL Cluster proceses) has 3 seconds to figure out whether Host B can continue running it’s ndbd process(es), or not. In this case, it should find out that Host A is down and that Host B should continue keeping the data available.

“Ah, easy! Problem solved!”, you might joyfully exclaim. No, it isn’t. It’s more complicated than that. What happens when Host A doesn’t go down, but both hosts can’t see each other due to a network issue between them? Both External Arbitrators would figure out that they need to continue: you end up again with a split brain. So you still need someway to handle that.

At this point, I would like to say: “Goodluck!”. Every situation is going to be different. Everyone will have his own External Arbitrator requirements or ways to check if a host or blade chassis is up or not. It’s a great option, and it puts you more in control of your MySQL Cluster, but it adds a lot of complexity.

So, my advice: revise and correct your MySQL Cluster setup when you think you need to disable Arbitration.

Using MacOSX, when your ISP or wireless access point is changing your hostname, make it sticky editing /etc/hostconfig.

Add or change the following line in /etc/hostconfig to reflect your hostname:

HOSTNAME="your hostname"

You could use Terminal.app to do this, but using Finder works too.

1. Open a Finder window.
2. Using the Menu: Go > Go to Folder..
3. In the dialog that’s opening, type: /etc/
4. /etc/ should now be available in your Finder window.
5. Locate the hostconfig-file and open it using your favorite text editor (e.g. TextEdit)
6. Add or change this line to reflect your hostname: HOSTNAME="your hostname"
7. Save it, enter your password, and be happy.

You are using MySQL Cluster and crazy enough to digest NDB API? Sick of SQL? Here’s a treat: a function to make C/C++ strings ready for inserting into a VARCHAR field. The special thing about them is that the length is prefixed in the first 2 bytes.

void make_ndb_varchar(char *buffer, char *str)
{
  int len = strlen(str);
  int hlen = (len > 255) ? 2 : 1;
  buffer[0] = len & 0xff;
  if( len > 255 )
    buffer[1] = (len / 256);
  strcpy(buffer+hlen, str);
}

Yes, you can use memcpy. Whatever floats your boat.

Lets use this function for a table t1, defined as follows (note: latin1!):

CREATE TABLE t1 (
  id INT UNSIGNED NOT NULL,
  vc VARCHAR(128),
  vclong VARCHAR(1280),
  PRIMARY KEY (id)
  ) ENGINE=NDB DEFAULT CHARSET=latin1

Here is part of the code, simplified for this post:

 char vc[128+1]; // Size of 'vc', +1 for length info
 char vclong[1280+2]; // Size of 'vclong', +2 for length info
 ..
 make_ndb_varchar(vc, "NDB API kicks ass");
 operation->setValue("vc", vc);
 ..

The above example uses latin1. You could use Unicode, but that would probably mean converting from one encoding to the other using iconv. That’s another story.

This post complements Johan Andersson’s blog entry. Thanks to my colleagues Mats and Roger who helped me with a silly problem today regarding this function.

This post explains how to compile oursql and install it on MacOS 10.6. oursql is a Python database interface for MySQL, an alternative to MySQL for Python (i.e. MySQLdb) and MySQL Connector/Python.

First, find out which MySQL you installed. This can be either the 32-bit or the 64-bit version. To make sure, find the mysqld (e.g. in /usr/local/mysql/bin) and do the following in a Terminal window:

shell> file /usr/local/mysql/bin/mysqld
.../mysqld: Mach-O 64-bit executable x86_64

If you see x86_64, you got 64-bit, otherwise 32-bit. If you see both, then you have a universal build. This is important for specifying the ARGSFLAG when building.

Download oursql from Launchpad and unpack it into some directory. Using the information from above, you’ll have to do following for 64-bit platform (or universal build) in a Terminal window:

shell> ARCHFLAGS="-arch x86_64" python setup.py build
shell> sudo python setup.py install

For 32-bit, you’ll have to do:

shell> ARCHFLAGS="-arch i386" python setup.py build
shell> sudo python setup.py install

Following error will be reported when you don’t specify the correct ARCHFLAGS:

ld: warning: in .../lib/libmysqlclient.dylib,
file is not of required architecture

Tips:

• When building failed, it is good to remove oursql, unpack it and try again.
• If you don’t want to compile anything, or run into more troubles, give MySQL Connector/Python a try (alpha releases). It’s a pure Python implementation of the MySQL Client/Server protocol and doesn’t need compiling or a MySQL installation.
• You can download MySQL from either www.mysql.com or dev.mysql.com.

On the eve of 2010.. and your boss wants to stick to these MacOS X 10.5 machines, too stubborn or chicken to upgrade. Some developers still have their old PowerBook laptops and they need MySQL flying on PowerPC machines. To top it all, one guy said he wanted to have 32 and 64-bit in one bite. *Sigh* .. But there is an easy way out! A universal binary!

This post shows you a way to create MySQL universal binaries using MacOS X 10.6 so you can run them on MacOS X 10.5/10.6 whether it is PowerPC or Intel, or 32bit or 64bit.

However, if you need libmysqld (Embedded MySQL), this post will not work for you.

Requirements:

• You have MacOS X 10.6 with latest Xcode (fully) installed.
• The MySQL source unpacked somewhere. Get it on the MySQL download website under Source Downloads, package named Compressed GNU TAR archive (tar.gz).
• And some nerves for when the build process fails.

Most complete Universal Binary

First, here is away to build MySQL so it runs on MacOS X 10.5 Intel/PowerPC and 10.6 32 or 64-bit.

Here is the source of the build script names build.sh. Executed it while located inside the source directory of MySQL.

#!/bin/bash

SDK="-isysroot /Developer/SDKs/MacOSX10.5.sdk"
SDKLIB="-Wl,-syslibroot,/Developer/SDKs/MacOSX10.5.sdk"
export MACOSX_DEPLOYMENT_TARGET="10.5"
PREFIX=/opt/mysql/mysql-5.1.42-universal-macosx-10.5

ARCH="-arch i386 -arch x86_64 -arch ppc"

export CFLAGS="-O2 -fPIC $ARCH $SDK"
export CXXFLAGS="-O2 -fPIC $ARCH $SDK"
export LDFLAGS="$ARCH $SDKLIB"

CC="/usr/bin/gcc-4.2"
CXX="/usr/bin/g++-4.2"
OBJC="/usr/bin/gcc-4.2"

INSTALL="/usr/bin/install -c"

./configure --prefix=$PREFIX \
--disable-dependency-tracking \
--mandir=$PREFIX/share/man --infodir=$PREFIX/share/info \
--localstatedir=$PREFIX/var/ --libdir=$PREFIX/lib \
--bindir=$PREFIX/bin --libexecdir=$PREFIX/bin \
--includedir=$PREFIX/include \
--datadir=$PREFIX/share/ --sysconfdir=$PREFIX/etc \
--with-extra-charsets=complex \
--with-mysqld-user=mysql \
--without-docs \
--with-plugins=all \
--enable-thread-safe-client --without-embedded-server \
--with-pic --with-libedit

if [ $? -eq 0 ]; then
    make clean
    time make -j 2
fi

Here is what file shows for the mysqld binary:

Black:mysql-5.1.42 geert$ file sql/mysqld
sql/mysqld: Mach-O universal binary with 3 architectures
sql/mysqld (for architecture i386): Mach-O executable i386
sql/mysqld (for architecture x86_64): Mach-O 64-bit executable x86_64
sql/mysqld (for architecture ppc7400): Mach-O executable ppc

These binaries were tested and work on MacOS X 10.6 Intel and MacOS X 10.5 PowerPC.

32/64-bit Universal binaries for MacOS X 10.6

Same as above, but with the following changes:

SDK="-isysroot /Developer/SDKs/MacOSX10.6.sdk"
SDKLIB="-Wl,-syslibroot,/Developer/SDKs/MacOSX10.6.sdk"
export MACOSX_DEPLOYMENT_TARGET="10.6"
PREFIX=/opt/mysql/mysql-5.1.42-universal-macosx-10.6

Setting the above is probably not needed when you are already on a Mac running 10.6, but it doesn’t hurt to be explicit.

Need it for MacOS X 10.4?

I gave this a spin:

SDK="-isysroot /Developer/SDKs/MacOSX10.4u.sdk"
SDKLIB="-Wl,-syslibroot,/Developer/SDKs/MacOSX10.4u.sdk"
export MACOSX_DEPLOYMENT_TARGET="10.4"
ARCH="-arch i386 -arch ppc"

But it failed with

/Developer/SDKs/MacOSX10.4u.sdk/usr/include/stdarg.h:4:25:
  error: stdarg.h: No such file or directory

.. but I lack intrest making stuff for MacOS X 10.4 (Tiger). Consider this your homework!

You’re not scared of writing clusterious code and eavesdropping is your favorite pastime at work? You want to know what’s going on in your MySQL Cluster but were afraid asking? The MySQL Cluster Management API can help you!

Below you’ll find example C-code that will get you started with MGM API. It’s rather dull at first, but imagine you, instead of printing the event information, taking action. Imagine you starting another thread where you run some procedure which tells a monitoring system: “Hey! Some error happend!”. Imagine you spending countless hours of clusterious fun with this API!

The example listens for events in 4 categories (Statistic, Info, Error and Checkpoint) with the greatest level of 15. The code only prints out what event was received, and it also shows how to interprete the event.category, which proves to be a bit tricky.

#include <stdlib.h>
#include <unistd.h>
#include <mgmapi/mgmapi.h>

int main()
{
  NdbMgmHandle handle = ndb_mgm_create_handle();

  NdbLogEventHandle logevent;
  struct ndb_logevent event;

  int res, category;
  int timeout = 1000; // in ms
  int filter[] = {
    15, NDB_MGM_EVENT_CATEGORY_STATISTIC,
    15, NDB_MGM_EVENT_CATEGORY_INFO,
    15, NDB_MGM_EVENT_CATEGORY_ERROR,
    15, NDB_MGM_EVENT_CATEGORY_CHECKPOINT,
    0
  };

  ndb_mgm_set_connectstring(handle,"localhost");
  ndb_mgm_connect(handle,0,0,0);

  logevent = ndb_mgm_create_logevent_handle(handle, filter);
  while(true) {
    res = ndb_logevent_get_next(logevent,&event,timeout);
    category = event.category + CFG_MIN_LOGLEVEL;
    if (category == NDB_MGM_EVENT_CATEGORY_CHECKPOINT) {
      printf("We got a checkpoint event!\n");
    }
    printf("Event type %d category %d\n",
       event.type, event.category);
    usleep(500); // temporize!
  }

  ndb_mgm_destroy_handle(&handle);
}

Note that the above is very bare bone without error handling, but it works. Also, run it on the machine which runs your Management Node (ndb_mgmd), or change the connection string in the code.

Compiling can be a bit of a chore, unless you read “Want to compile a MySQL Cluster MGM API application?“.

Work says that we need to store sensitive data like email and customer files on some encrypted media. This is a good thing. My laptop has my home directory secured, but I don’t want to encrypt everything on my desktop. The solution to this is to create an encrypted Disk Image (using Disk Utility) and make Mail.app store my email there. This all works great!

The problem surfaced this week when I decided it would be good to shutdown my desktop to save energy (which I usually did, but I got sloppy). What happened? When Mac OS X shuts down, and there is still some application doing something with your opened Disk Image, it will not remove the mount-point, e.g. in my case /Volumes/FileVaultBlack. Later, if you open it again, Mac OS will create the directory /Volumes/FileVaultBlack 1 and use that as mount-point. The issue here is that I configured the AccountPath in Mail.app to a location in /Volumes/FileVaultBlock.. In mortals speech: “Email is broken”.

The solution is to mount the Disk Image when logging in using a predefined mounting point. This involves making a directory and creating an AppleScript application. So lets get busy!

First, eject the Disk Image using the Finder application. It will complain when applications still need it, so you have to make sure that nothing is accessing it.

Create the mountpoint. The idea is to create a directory in /Volumes/ (it doesn’t really matter where you do it, but I like to keep them in one place). I suggest opening Terminal for this and do the following:

 shell> mkdir /Volumes/FileVaultBlack

You want to name it differently. I’m using the name of the Disk Image, just like MacOS would do when you double click the .dmg-file.

Open AppleScript Editor and save the following script into a file called, for example, FileVaultBlack_mountatlogin.

do shell script "hdiutil attach /Users/geert/FileVaultBlack.dmg -mountpoint /Volumes/FileVaultBlack"

The above script will execute the hdiutil command-line utility telling it to mount the given Disk Image (.dmg-file) on the give mount-point (the one we created earlier).

Test this script by pushing the Run-button in the AppleScript Editor and see whether your Disk Image is visible in Finder. If not, check the locations of both the Disk Image and the mount-point you gave in the script.

When you saved it, save it again as an application. Still within the AppleScript Editor do the following:

1. Choose Save As from the File menu
2. Save As: save it using slighly different name, for example MountFileVaultBlack
3. File Format: Application
4. You don’t have to check Run Only

We save it 2 times, once as a script, once as an application. This way, you can easily edit the script later, and save it again as an application. (I’m using different names because it’s less confusing when writing this post.)

Lets now add it to our Login Items:

1. Open System Preferences and go to Accounts
2. Make sure you choose My Account, i.e. your account, and go to Login Items
3. Add an item to the list, clicking the +-sign
4. Locate the application you created above, in our exampled named MountFileVaultBlack, and press Add.
5. Check also the Hide option next to the new item.

That should be it. Log out and in and check if the Disk Image is mounting. There should be an icon happily jumping in the Dock while the mounting is on-going. Open Finder and see if your files are accessible.

Here is a quick way to compile a simple MGM API application. The example will get the state of all nodes in MySQL Cluster and print whether they are connected or not.

All this without a Makefile, we just want to have some simple example on Linux to see how it works. It’s basic, maybe, but sometimes useful to just have a peek.

Requirements! We assume that:

1. you installed MySQL Cluster 6.3 or higher, preferably under /usr/local/mysql,
2. your cluster is up and shiny,
3. and ndb_mgmd runs on the same machine you are compiling the MGM API test application on.

The code, save it in a file called mgmapi_test.cc (from the MySQL manual):

#include <stdlib.h>
#include <mgmapi/mgmapi.h>

int main()
{
  NdbMgmHandle handle = ndb_mgm_create_handle();
  ndb_mgm_set_connectstring(handle,"localhost");
  ndb_mgm_connect(handle,0,0,0);
  struct ndb_mgm_cluster_state *state = ndb_mgm_get_status(handle);
  for(int i=0; i < state->no_of_nodes; i++)
  {
    struct ndb_mgm_node_state *node_state= &state->node_states[i];
    printf("node with ID=%d ", node_state->node_id);

    if(node_state->version != 0)
      printf("connected\n");
    else
      printf("not connected\n");
  }
  free((void*)state);
  ndb_mgm_destroy_handle(&handle);
}

Lets compile it! Note that it goes all on one line, but you could also paste the following lines in a shell script:

(
 MYBASE="/usr/local/mysql";
 PATH="$MYBASE/bin:$PATH";
 g++ `mysql_config --libs` `mysql_config --cflags` -lndbclient \
  -I$MYBASE/include/storage/ndb \
  -o mgmapi_test mgmapi_test.cc
)

No errors? Lets run it! Setting LD_LIBRARY_PATH doesn’t hurt:

(
  export LD_LIBRARY_PATH="/usr/local/mysql/lib";
  ./mgmapi_test
)

The result should be something like this:

node with ID=1 connected
node with ID=2 connected
node with ID=3 connected
node with ID=4 connected
node with ID=10 connected
node with ID=11 not connected
node with ID=12 not connected
node with ID=13 not connected
..

FAQ: Wouldn’t it be better to have a Makefile? Absolutely. Will there be problems doing it above? Sure, but the error message should help; start by checking if the locations are OK.