Geert JM Vanderkelen

Update 2010-08-27: Comments indicated that what I did here is not the best solution. Like noted in my original post, a set would be better in this case. I eventually used set(r).issubset(set(l)). Marius also pointed out to set(r) <= set(l), but I like the issubset one more.

I wanted to check if every element of one list or tuple is part of another one using Python. A set has the issubset()-method, but I couldn't find anything build-in for a tuple. It was, however, rather quickly done:

>>> r = (1,2)
>>> l = (3,4,1,5,2)
>>> False not in [ e in l for e in r ]
True
>>> r = (1,9)
>>> False not in [ e in l for e in r ]
False

Why I'm posting this? I just found it cute code, somehow.

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.

The following AppleScript will:

1. Prompt for a destination folder
   
2. Ask for an album name, which will be a folder in destination folder
   
3. Get currently selected photo(s) from Aperture
   
4. Store the image files using the Image ID of Aperture
   
5. Store some META information in a text file, with similar name as image file
   

The export format is PNG 1024x1024. I needed only that, so I hardcoded it.

Note dependencies: You need the renameFile, joinList and splitString functions found in earlier blog entries.

property defExportSetting : "PNG - Fit within 1024 x 1024"

on apertureExport(fldAlbum)
 tell application "Aperture"
  set imageSel to (get selection)
 end tell
 
 set out to {}
 repeat with img in imageSel
  tell application "Aperture"
   set kwList to (get id of every keyword of img)
   set imgDate to value of EXIF tag "ImageDate" of img
  end tell
  set imgId to id of img
  
  set imgName to name of img
  set imgTags to my joinList(kwList, ",")
  
  copy "Name: " & imgName to end of out
  copy "Tags: " & imgTags to end of out
  copy "ApertureId: " & imgId to end of out
  
  set dstFileMeta to (fldAlbum & imgId & ".meta") as string
 end repeat
 
 tell application "Aperture"
  set dstFile to (export imageSel using export setting defExportSetting to fldAlbum)
 end tell
 set dstFile to my renameFile(dstFile, imgId)
 copy "File: " & (POSIX path of dstFile) to end of out
 tell application "Finder"
 end tell
 
 set fp to open for access file dstFileMeta with write permission
 write (joinList of out given delimiter:return) to fp
 close access fp
 
end apertureExport

on run
 set dstFolder to (choose folder with prompt "Choose an destination") as text
 set dlgAlbum to display dialog "Album name" buttons {"OK", "Cancel"} default answer "Picture pool"
 
 set dstAlbum to (text returned of dlgAlbum) as text
 
 tell application "Finder"
  if not (exists alias (dstFolder & dstAlbum)) then
   make new folder at alias (dstFolder) with properties {name:dstAlbum}
  end if
 end tell
 set fldAlbum to (dstFolder & dstAlbum) as alias
 
 apertureExport(fldAlbum)
end run

This script is safe, but I don't take any responsibility when it screws up you Aperture library. It shouldn't really, I coded it on my main library: hardcore!

Two small helper AppleScript functions for joining a list and splitting a string given delimiter. Sure, quite an easy task, but it involves setting a global delimiter. Bit like FS in shell. I hope this helps a few folks out there starting out with AppleScript.

to joinList(aList, delimiter)
 set retVal to ""
 set prevDelimiter to AppleScript's text item delimiters
 set AppleScript's text item delimiters to delimiter
 set retVal to aList as string
 set AppleScript's text item delimiters to prevDelimiter
 return retVal
end joinList

to splitString(aString, delimiter)
 set retVal to {}
 set prevDelimiter to AppleScript's text item delimiters
 log delimiter
 set AppleScript's text item delimiters to {delimiter}
 set retVal to every text item of aString
 set AppleScript's text item delimiters to prevDelimiter
 return retVal
end splitString

Here a few lines showing how to use them:

set tmp to my splitString(oldAlias as text, ":")
set imgTags to my joinList(kwList, ",")

Again, no comments! Enjoy!

The following AppleScript function renames a file. If you thought this to be a simple thing, try to write it without looking here below. I spend a lot time on this, I might not even use it, but here it is for other mortals wishing to lose weight exercising AppleScript:

to renameFile(oldAlias, newFileName)
 tell application "Finder"
  set f to item (oldAlias as text)
  tell f
   set ext to its name extension
   set nFn to (newFileName & "." & ext)
   set its name to nFn
  end tell
 end tell
 set tmp to my splitString(oldAlias as text, ":")
 set the last item of tmp to nFn
 return my joinList(tmp, ":")
end renameFile

No comments in code, I do not want to spoil your fun!

I needed while writing an export script for Aperture. Here is how I used it after a photo was exported from Aperture:

tell application "Aperture"
 set dstFile to (export imageSel using export setting defExportSetting to fldAlbum)
end tell
set dstFile to my renameFile(dstFile, imgId)

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.

About 2 hours and more than 500 shots later I finally did it: I shot lighting! One needs patience, lots of nerves and luck. I didn't readup on how to do it before, but in the end I pretty much figured it out. The full battery ran out, that much I was using the camera.

Exposure time is not so important, especially when you are exposed to the light polution of the city. I kept it low, from 6 to 10 seconds. I tried with 30 seconds (maximum) but it was just to bright.

What I found, is that getting the Aperture correct is the way to get it right. Increase it when the storm is further way, decrease when it's closing in. In the end, when the storm reached our block of flats, I did set it around f/5.6 and f/6.3. This was good. I was continously shooting every 6 seconds keeping the trigger down (didn't have my cable handy).

Except for the camera technique, there were a few more challenges, e.g. the actual danger when the storm got closer: keeping windows open is no good. Also the incoming rain was making everything wet. Great fun however, even though only 20 shots did contain actual lighting.

My gear at the time of shooting: Canon 450D equipped with the EF24-105mm f/4L IS USM lens. I thought putting my 30D to work too, but I lacked a second tripod.

This is a friendly reminder to check the publication date and discussed version you MySQL books before starting out hacking or even posting about limitations. Lots of old books are still going around. Maybe it's good to destroy them rather than giving them to students or newbies.

Few days ago (28 May 2010), for example, we had a word-for-word copy of a book on a blog post (now removed) which was discussing MySQL Cluster limitations from years ago. Well, it was funny at first and we had a good laugh. But it's a bit worrisome. My colleague Matthew posted a rebuttal post.

How would you recycle the old, technical books? It's not worth giving them to public libraries, it's maybe unhealthy to burn them? How would you do it?

Lots, if not everyone goes crazy about privacy. Well, putting pictures or videos of your baby or kids online is actually invading theirs. Our kid will have the choice later to do whatever he/she likes with the millions of shots we've made, just like us, the pre-born mass-Internet kiddoes.

So, this is a call: respect your kid's privacy and ask if you can expose them when they reached an age they actually understand the consequences. Ask yourself if you'd agree your parents suddenly start to post nude pictures of you in the bathtube when you were 5.

We just released MySQL Connector/Python 0.1.5 which includes a critical bug fix. It was impossible to read big result sets. The files for 0.1.4-release have been removed.

You can download MySQL Connector/Python from Launchpad.

Highlights:

• It was impossible to retrieve big result sets. (bug lp:551533 and lp:586003)
• Changing copyright from Sun to Oracle (also fixing silly typo)

A very Big Thanks goes to the reporters of bug lp:551533 and lp:586003. Apologies for not being able to reproduce the bug earlier, before releasing 0.1.4.

About MySQL Connector/Python: MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python. No MySQL libraries are needed, and no compilation is necessary to run this Python DB API v2.0 compliant driver. It is compatible with Python v2.5 and later as well as Python v3.1 and later.

For a long time, the Dragon of Kraków has been fiercely protecting its den under the Wavel Hill spewing flames. An attraction for lots, a legend for many: day in, day out it is admired.

From time to time however, flames are no match for water. When rain reigns the sky and falls down to grow the mighty rivers, the dragon stands powerless. People and cameras turn their backs on it to watch the Vistula raise and flow rapidly.

The Dragon of Kraków stands there, forgotten, ignored.. Probably wondering how flooded its cave is.

Next development release 0.1.4 of MySQL Connector/Python is now available for download. This will be the last in the 0.1-series as we move on to 0.2. The aim is to release more often to get to v1.0. Hurray!

Highlights:

• Reading from network was broken for bigger packages.
• Reimplementing protocol.MySQLProtocol marking simpler and easier to maintain.
• It is now possible to send multiple statements to MySQL using MySQLCursor.execute(). The results are accessible by calling the method next_resultset().
• MySQLCursor.callproc() will now store all result sets as a MySQLCursorBuffered. They are accessible using the next_proc_resultset() method. The result of the stored procedure is returned by callproc() itself as defined by PEP249.
• MySQL DATETIME conversion to Python datetime.datetime is now much faster.
• Some overall performance improvements.
• Copyright notice changes.

Big thanks to everyone using and reporting bugs found in MySQL Connector/Python. Don't hesitate to ask questions and report problems or feature requests using Launchpad.

About MySQL Connector/Python: MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python. No MySQL libraries are needed, and no compilation is necessary to run this Python DB API v2.0 compliant driver. It is compatible with Python v2.5 and later as well as Python v3.1 and later.

Today I walked along the Wisła (Vistula river) in Kraków. The past week it didn't stop raining and it starts to show.

It is the first time that I personally see such event. The river seems to be taking over the city. The Wavel castle is still safe standing up the hill. The Dragon on the other hand is suffering from lack of attention as all eyes and cameras are now on the Vistula river.

Meanwhile, the construction of the new pedestrian bridge is on hold. At this moment they are trying to build and maintain a wall so the water is not streaming directly on the site. Seems like the bridge gets baptized prematurely.

More pictures on Flickr.

To apply a keyword to all your selected images using Apple Aperture 3, make sure to turn off the Primary Only mode using either the Edit-Menu, or the button in the right bottom corner that says [1]. It should be unselected like in the screenshot here below.

You can then use whatever method to add keywords, e.g. using the Control Bar (press D) and its Keywords Controls (press shift D).

This has been bugging me for a week. Maybe I pushed the Primary Only button by mistake. Yes, you can do it using a batch change. But luckily, you don't have to do the simplest thing the hardest way. Just, careful where you push!

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.

This blog posts explains how to add a new line in strings in MySQL Stored Procedures and how to output the result using the MySQL client tool.

Today I was fooling around with some stored procedure making it more fancy and stuff. What I wanted was the OUT variable to contain a newline. Easy of course, using CONCAT:

mysql> SELECT CONCAT('foo','\n','bar');
+--------------------------+
| CONCAT('foo','\n','bar') |
+--------------------------+
| foo
bar                  |
+--------------------------+

Now, if youconcat strings in a stored procedure, it doesn't work as expected when you run it through the MySQL client tool mysql:

DELIMITER //
CREATE PROCEDURE sp1(OUT pres VARCHAR(6000))
BEGIN
  SET pres = CONCAT('foo','\n','bar');
END;
//
DELIMITER ;

SET @res = 'foo ';
CALL sp1(@res);
SELECT @res;

When we execute it, we get this:

shell> mysql -N test < foo.sql
foo\nbar

What on earth is wrong? After some looking, we found a not so often used option called --raw. This produces the the desired effect:

shell> mysql -Nr test < foo.sql
foo
bar

But that's not all! Use \G when selecting the OUT-variable and it is also working. The output is not so useful though.

Ah.. The things you find out while having the day off..

Imagine you are sitting at some high-tech computer and you are given the opportunity to send everyone on Earth 1 SMS. What would you say using only 140 characters?

It's an unlikely event, but it's technically not impossible. Not everyone will receive it at the exact same moment, but it will eventually reach almost everyone on this planet. SMS is probably the most direct way to contact people. Forget e-mail, forget Twitter or Facebook, gosh, forget Television! Most people on Earth have a some mobile communication device and they have it usually all the time with them.

It's an exercise. It's fun. It is also powerful, maybe scary. But if you could send 1 SMS to everyone on Earth, what would it say?

Sometimes I say things worth blogging:

If you know something is coming, don't wait for it.

Last week I was struggling to find an easy way to simulate a troubled Data Node (ndbd process) using MySQL Cluster. It's as simple as pancackes: using the kill command!

To freeze a process you just need to kill the process using the SIGSTOP signal. To let the processes continue, use SIGCONT. Here's an example shell script showing how you would use these two signals on a data node:

# 2010-05-03 08:11:46 [ndbd] INFO     -- Angel pid: 542 ndb pid: 543
 NDBDPID=`grep 'Angel pid' ndb_3_out.log | tail -n1 | awk '{ print $11 }'`
 kill -STOP $NDBDPID
 sleep 10
 kill -CONT $NDBDPID

I'm using the out-log because the file ndb_3.pid contains only the PID of the Angel process. The sleep command is something variable which you can set as low or as high as you want.

In the above example the script sleeps long enough for data node to fail with an Arbitration Error. If you would set options HeartbeatIntervalDbDb and TimeBetweenWatchDogCheck to a lower value than the default, you would only be able to sleep for a few seconds. The result:

[MgmtSrvr] WARNING  -- Node 2: Node 3 missed heartbeat 2
 [MgmtSrvr] WARNING  -- Node 2: Node 3 missed heartbeat 3
 [MgmtSrvr] ALERT    -- Node 1: Node 3 Disconnected
 [MgmtSrvr] ALERT    -- Node 1: Node 3 Disconnected
 [MgmtSrvr] WARNING  -- Node 2: Node 3 missed heartbeat 4
 [MgmtSrvr] ALERT    -- Node 2: Node 3 declared dead due to missed heartbeat
 [MgmtSrvr] INFO     -- Node 2: Communication to Node 3 closed
 [MgmtSrvr] ALERT    -- Node 2: Network partitioning - arbitration required
 [MgmtSrvr] INFO     -- Node 2: President restarts arbitration thread [state=7]
 [MgmtSrvr] ALERT    -- Node 2: Arbitration won - positive reply from node 1
 [MgmtSrvr] ALERT    -- Node 2: Node 3 Disconnected
 [MgmtSrvr] INFO     -- Node 2: Started arbitrator node 1 [ticket=019b00025cc8aad8]
 [MgmtSrvr] ALERT    -- Node 3: Forced node shutdown completed. 
  Caused by error 2305:  'Node lost connection to other nodes and can not
  form a unpartitioned cluster, please investigate if there are error(s)
  on other node(s)(Arbitration error). Temporary error, restart node'.

How is this useful? Well, for simulating a data node which is having problems while having load for example. Maybe you would like to see what happens if you tune the WatchDog or Hearbeat parameters. Or maybe you want to give a demonstration to your management without going through hassel of overloading a disk or CPU or pulling network cables (e.g. for prove of concept).

In any case, I think it's a cool use of the kill-command. One I didn't know of.

Today I tried MobileMe again registring for the 60-day trial. I cancelled it 15 minutes later. Expensive and useless. If you ask 99 EUR per year for a service, you would imagine a personal domain for your e-mail. But no, Apple thinks it's doing it correctly, but it's laughable. I'm sure they don't lack the tallent to do it, just the balls at management level.

Lets hope Apple puts it in soon, I might consider paying for it. It must be great to sync data over MobileMe because most other tools are just almost-working. Now it's back to Google where all features are there (for 'free'), but online..

I'm really pissed off, again, every year. Syncing contacts and calendars offline is a pain if you got a few Macs, iPhone and iPad. Nothing is working as it should, MobileMe would be great.

I'm getting tired of this: Apple has a brilliant OS, great hardware and some great software. But Apple just painfully blows it at service level.

Last week, my colleague Massimo and I discussed how to handle big result sets coming from MySQL in Python. The problem is that MySQL doesn't support server-side cursors, so you need to select everything and then read it. You can do it either buffered or not. MySQL Connector/Python defaults to non-buffered, meaning that you need to fetch all rows after issuing a SELECT statement. You can also turn on the buffering, mimicking what MySQL for Python (MySQLdb) does.

For big result sets, it's better to limit your search. You can do this using an integer primary key or some temporal field for example. Or you can use the LIMIT keyword. The latter solution is what is used in the MySQLCursorServerSide cursor-class. Using the SELECT it creates a temporary table from which the fetch-methods will get the information. It is something people have probably implemented in their applications, but I hope this new class will make it easier since it's done transparently.

The code is not pushed yet, but expect it to be available in next release. Here is an example how you could use it. This code selects cities staring with Z, loops over the result getting the country (yes, this is a simple join made difficult):

cnx = db.connect(user='root',db='world')
    cur = cnx.cursor()
    curCity = cnx.cursor(db.cursor.MySQLCursorServerSide)
    
    curCity.execute("SELECT ID,Name,CountryCode FROM City "\
        "WHERE NAME LIKE 'Z%' ORDER BY ID")
    
    for city in curCity:
        cur.execute("SELECT Code,Name FROM Country WHERE CODE = %s",
            (city[2],))
        country = cur.fetchone()
        print "%s (%s)" % (city[1], country[1])
    
    cur.close()
    cnx.close()

I guess the main advantage is that you can use two or more cursor objects with the same connection without the need of buffering everything in Python. On the MySQL side, the temporary table could go to disk when to big. It's maybe slower, but keeping big result sets in memory ain't good either.

Comments are welcome!

Latest code of MySQL Connector/Python on launchpad has support for multiple result sets when you execute a stored procedure. We also changed the way the actual result of the routine is returned to conform to PEP249.

Here is some example code: it creates a stored procedure which generates 2 result sets. You can get the result by calling next_resultset(), which is returning a MySQLCursorBuffered.

    cur = cnx.cursor()

    cur.execute("DROP PROCEDURE IF EXISTS multi")
    proc = """
      CREATE PROCEDURE multi(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
      BEGIN
        SELECT 1,'a' as FooBar;
        SELECT 2;
        SET pProd := pFac1 * pFac2;
      END"""

    cur.execute(proc)
    result = cur.callproc("multi", (5, 6, 0))
    print "Result:", result
    
    extcur = cur.next_resultset()
    i = 1
    while extcur:
        rows = extcur.fetchall()
        print "Result set #%d:" % i
        print rows
        extcur = cur.next_resultset()
        i += 1
        
    cur.close()

The output:

Result: ('5', '6', 30)
Result set #1:
[(1, u'a')]
Result set #2:
[(2,)]

As mentioned above: this will be part of 0.1.4-devel release due next week. Comments are welcome through the associated bug report.

The USA is not particulary famous for its cuisine. I must admit that it has been quite good the past 10 days. The Angus Burger at Hyatt, with mushrooms? Yummy!

Today I saw KFC advertising a new sandwish. All chicken, with cheese, with bacon in the middle. That's right: bacon.

This evening I went for pasta at the restaurant. I started with chowder, which was great. Then the entree (maindish in US, go figure). Tagiatelle topped with prawns, jumbo ones. Ladies & gents, the sea creatures were wrapped in bacon.

It was good though.

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.

Day in, day out, I speak, read, write, love, in English. My mother tongue, however, is Dutch (not Flemish *spit*). Today, I was finalizing the handwritten letter to my grandmother and reading up a message from my sister, I came up with a new Dutch word. Oh, it might already exist, but Google doesn't know about it yet (unless for the typos).

avonkuren: avonturen + kuren .. doing crazy stuff while going through life

Oh, and now back cooking.

You've see them, those product/website names meaning nothing but making millions. Names which seem to come from stupid teenagers writing short messages on their phones.. Today I figured out a possible reason why we have such names.

I'm brainstorming a new project and came up with a good name to brand it. The problem? Assholes have parked any combination of the name for most top domains out there.

There you go: a good idea down the drain.. Or I'll just come up with a weird name and hope people will forgive me. Buying the domain? LOL.

Vacation, and the mind is free. Then I scribble something, unleash it upon Earth where only a handful might read it:

Stop for a moment
And watch around
Hush for a second
And hear the sound

Think of your past step
And watch the road ahead
Pick up a fight
And try to flight

Break your habits
Stop living by one's wits
Take another curve
And make some surf

Since a few decades, humanity got more and more cursed and/or blessed with a little device now fitting perfectly in a trousers' pocket or womon's purse. But how do you call it these days? Here are some possibilities which crossed my mind:

• Mobile
• GSM
• Handy (German)
• iPhone
• MP3 player
• Walkman
• PDA
• Alarm
• some kind of berry
• Book
• .. or.. Phone?

I just read my first book using Amazon Kindle on my iPhone. Although odd at first, it was surprisingly pleasant. Old books mind you, like ‘Adventures of Sherlock Holmes’. I still prefer the normal, papered books, but reading using one's phone is sometimes handy (pun intended). Suddenly, my phone turned into a .. a book.

Few days ago, the folks at SQLAlchemy pushed some proposed modification to the MySQL Connector/Python dialect. Before this patch, previous report yielded 72 errors and 11 failures. Now we got down to 9 errors, but the failures are still lingering. Is this an improvement? Yes and no, failures should go down, but there are some SQLAlchemy tests I just can't figure out, yet.. clues are welcome!

Here are some detailed results which also included MySQLdb and oursql. I used SQLAlchemy revision 6788 (i.e. from svn trunk) which has now 2143 unittests:

• mysql.connector rev216: SKIP=1, errors=9, failures=11 (355.707s)
• MySQLdb 1.2.3c1: SKIP=2, errors=8, failures=1 (315.884s)
• oursql 0.9.1: SKIP=1, errors=8, failures=2 (322.318s)

Software used: MacOSX v10.6.2, MySQL v5.1.42 and Python v2.6.1.

Using SQLAlchemy might not be the best way to messure how mature MySQL Connector/Python is, but it sure helps lots.

To far fetched (pun inteded), some might think.. Below you'll find a procedure to get a list of MySQL supported character sets and their collations. The output is Python and can be used to build a (big) tuple.

The problem is that character set IDs in MySQL have 'gaps'. For example hebrew has ID 16, and the next character set in the COLLATIONS-table, tis620, has ID 18. Not a big problem, just a bit annoying. This list is hardcoded in MySQL Connector/Python and I needed something to easily maintain it.

Solutions were using text editing skills, parsing it in Python to produce a list, etc.. But why not a Stored Routine? The following stored procedure is looping over a cursor, calculating the number of rows the gap has, and inserts blanks as needed.

DROP PROCEDURE IF EXISTS python_getcharsets;
DELIMITER //
CREATE PROCEDURE python_getcharsets()
BEGIN
  DECLARE i,diff,cid,done INT DEFAULT 0;
  DECLARE chname,coname VARCHAR(32);
  DECLARE cur CURSOR FOR SELECT ID,CHARACTER_SET_NAME,
    COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
    ORDER BY ID;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur;
  loop_cur: LOOP
    FETCH cur INTO cid,chname,coname;
    IF done THEN LEAVE loop_cur; END IF;
    SET i = i + 1; SET diff = cid - i;
    WHILE diff DO
      SELECT "None,";
      SET diff = diff - 1; SET i = i + 1;
    END WHILE;
    SELECT CONCAT('("',chname,'","',coname,'"), # ',cid);
  END LOOP loop_cur;
  CLOSE cur;
END//
DELIMITER ;

CALL python_getcharsets();
DROP PROCEDURE IF EXISTS python_getcharsets;

You would save the above to a file called getcharsets.sql for example, and execute it like this:

shell> mysql -N test < getcharsets.sql 
..
("cp1251","cp1251_bulgarian_ci"), # 14
("latin1","latin1_danish_ci"), # 15
("hebrew","hebrew_general_ci"), # 16
None,
("tis620","tis620_thai_ci"), # 18
("euckr","euckr_korean_ci"), # 19
..

I'll spare you the complete output, but as you can see from above sample: the gap has been stuffed with a None-Python value. Taking this output, you'll inserted it your code:

desc = (
    None,
    ("big5","big5_chinese_ci"), # 1
    ("latin2","latin2_czech_cs"), # 2
    ("dec8","dec8_swedish_ci"), # 3
    ("cp850","cp850_general_ci"), # 4
..

Silly? Definitely basic stuff, but I got my code a bit faster, and cleaner!

Is there a performance penalty using a pure Python database interface? Yes there is. But how much? .. also depends on who wrote it.

I started implementing some benchmarking for MySQL Connector/Python. Main reason is to identified bottlenecks or just plain bad coded. Another reasons: it's cool and the question was raised during my talk at FOSDEM and also online.

Oh, yes, MySQL Connector/Python ain't fast right now. Just compare a script spawning 10 threads opening 1000 connections. It indeed just connects.

mysql.connector 0.1.3-devel average: 0.0091820
MySQLdb 1.2.3c1             average: 0.0026477
oursql 0.9.1                average: 0.0007394

(MacOSX 10.6.2, MySQL 5.1.42, Python 2.6.1)

I'm not surprised with how bad Connector/Python did. I'm more intrigued by how oursql is performing compared to MySQLdb. Nice!

Well, it's a start. Now off for a walk!

Yesterday, my new website went online and it caused a bit of problems with aggregators. My old domain was set to redirect to my new website, but left my old website with blog entries. That's now fixed.

New website is http://geert.vanderkelen.org. No ads, completely on Blogger. This way I can eventually get rid of my 'root' server and reduce costs.

Sorry for the noise.

A preliminary schedule is now available for the MySQL Conference & Expo 2010 (in Santa Clara, California, USA). I have two talks and a tutorial, currently scheduled as follows:

• MySQL Cluster Tutorial, Mon 08:30 on 12 Apr 2010, together with Andrew Hutchings and Andrew Morgan
• MySQL Cluster: An Introduction, Tue 11:55 on 13 Apr 2010
• Connecting MySQL and Python, Tue 15:05 on 13 Apr 2010

Schedule might change a bit, so I suggest keeping an eye on the conference website.

And please remember: we might have changed colors a few times past 2 years, but we are still the nice and friendly people we were back in MySQL AB!

Yes, MySQL has transactions if you use InnoDB or NDB Cluster for example. Using these transactional storage engines, you'll have to commit (or roll back) your inserts, deletes or updates.

I've seen it a few times now with people being surprised that no data is going into the tables. It's not so a silly problem in the end. If you are used to the defaults in MySQL you don't have to commit anything since it is automatically done for you.

Take the Python Database Interfaces for MySQL. PEP-249 says that, by default, auto-commit should be turned off. You could turn it back on, but it's good practice to be explicit and commit in your code. Remember the Zen of Python!

Here is just a small example to show it. Uses MySQL Connector/Python, but it should also work with the other MySQL database interfaces:

import mysql.connector
cnx = mysql.connector.connect(db='test')
cur = cnx.cursor()
cur.execute("""CREATE TABLE innodb_t1 (
id INT UNSIGNED NOT NULL,
c1 VARCHAR(128),
PRIMARY KEY (id)
) ENGINE=InnoDB""")
ins = "INSERT INTO innodb_t1 (id,c1) VALUES (%s,%s)"
cur.execute(ins,
(1,'MySQL Support Team _is_ already the best',))
cnx.commit()
cur.close()
cnx.close()

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.

Apparently, my talk at FOSDEM 2010 about Connecting MySQL and Python was the only one about Python? There should be more, or?

I have a hand-out ready in PDF. The slides are not usable without my chatter. It contains a few examples and links. Any comments, corrections, criticism.. are welcome!

The longer version of this talk will be given at the O'Reilly MySQL Conference&Expo 2010 in Santa Clara, California (USA).

Today we made a change in the schedule of talks held in the MySQL Developer Room at FOSDEM 2010, swapping two talks. Change is:

• 12:15 CET - Giuseppe's 'Sharding for the Masses
• 14:25 CET - Geert's Connecting MySQL and Python (title also changed)

The printed booklets found at the conference will not reflect the change, but the printable schedule has already been updated.

MySQL Connector/Python v0.1.3-devel is now available for download from Launchpad.org. Please note that this is a development (i.e. alpha, unstable, ..) release and we welcome everyone to test and report problems.

Highlights for this v0.1.3-devel:

• Important memory leak fixed when closing cursors.
• Warnings can now be raised as exceptions.
• Fixing unicode usage and broken error message when MySQL chops them
• Client flags can now be set correctly when connecting
• Conversion fixes for BIT/YEARSET and Python to DATE/DATETIME
• Adding MySQL Client Errors and raising better exceptions based on errno.

Enjoy!

Using SQLAlchemy unit test cases to further develop MySQL Connector/Python. It's probably debatable whether that's a good method or just lame. But it sure helps lots!

We've been pushing some code past days that makes Connector/Python almost pass all tests. Well, 4% is still failing, but I'm confident that in a few most problems will be dealt with. I had to make some changes to the SQLAlchemy v0.6 dialect as well, and some test cases had to be corrected. Hopefully those corrections will also go in the SQLAlchemy trunk later on.

shell> nosetests --dburi=mysql+mysqlconnector://root:@localhost/sqlalchemy
..
----------------------------------------------------------------------
Ran 2092 tests in 314.656s

FAILED (errors=72, failures=11)

If you run the same tests against MySQLdb, you'll see (of course) less failures (9 to be exact).

FOSDEM 2010, Sunday 7 February, the MySQL Developer Room packed with 12 talks! And this year we serve Python just before the lunch break.

In 20 minutes I'll try to give an overview of the drivers currently available for connecting your Python applications with MySQL. Incase you wonder, this will not evolve around MySQL Connector/Python alone!

We'll also go over some frameworks and tools like SQLAlchemy.

20 minutes, it's not much, but should be enough. I hope to get a similar talk accepted for the MySQL Conference&Expo 2010.

Last week I took SQLAlchemy v0.6 out of its trunk and tested it again with our MySQL Connector/Python. And surprise! SQLAlchemy comes with a 'dialect' supporting it! Nice!

However, we're still a long way on making it work. I found some additional problems which need to be fixed first.

shell> nosetests --dburi=mysql+mysqlconnector://root:@localhost/sqlalchemy \
    test/dialect/test_mysql.py
..
Ran 32 tests in 4.507s

FAILED (errors=6, failures=1)

Oh, there were more errors and failures and all that due to bugs in MySQL Connector/Python. There were already some modification to the dialect (e.g. name change), and there might be some more.

If we got this sorted, Turbogears should work too!

A few days ago MySQL 5.1.42 got released and it is now available with builds for MacOS X 10.6 (Snow Leopard)! The download website doesn't show it yet, but if you are burning to try, you can get it from the mirror-picking-website.

As usual, don't forget to checkout the changelog before upgrading!

If you want to compile it yourself, and need a universal binary, you could try my previous blog entry «Building MySQL universal binaries using MacOS X 10.6 (Snow Leopard)».

Saturday 2 January, 13:05 CET: "This is your captain speaking. We are ready to start but we'll have to wait another 5 to 10 minutes because a bag was apparently loaded to much. Security regulations say we have to get it off the plane." Fair enough, the passengers were silently agreeing.
20 minutes later, the voice from the cockpit brought some update, which really wasn't one: "Bag not found yet.."

A few passengers, including me, were already getting annoyed, some a bit angry. Lots of people had connecting flights in Munich (Germany), and staying longer on the ground in Kraków (Poland) was just not helping the flight schedule.

"People, the bag has.. not been found." Silence in the plane as few had already wondered why the bags were getting lined up on the airport's Tarmac.. in the snow.. getting wet. The captain spoke again: "We ask you kindly to get off the plane using the front exit, walk past the luggage and identify yours. Please re-enter through the rear entrance."
Laughter, a few people clapping hands but failing to get more folks doing the same. Most passengers were tired waiting and wanted to get it over with. This identification was probably going to be our fasted way up.
Few of us having only carry-on luggage didn't care, and we were watching from the plane what was happening outside. It was like a carousel, only the music and the chairs were missing as I picture me stopping the music.

After about 2 hours, the bag was eventually found, the voice out of the cockpit explained. "Now we can continue our flight.. but we'll need another 5 to 10 minutes to de-ice the airplane." Sighs and moaning rushed through the belly of the machine, but people already accepted their faiths.

We arrived like 2 hours late in Munich. Lufthansa had their service center well staffed and were helping people as fast as they could. It's not easy to rebook as passengers also need to think about the consequences when getting a day later at their destination.
I had luck: there was another flight at 19:05 to Brussels. However, the queue was really slowly dissolving and time was critically getting toowards my hopefully new departure. But after a jolly hour and a half waiting, my ticket got rebooked in 10 seconds and I was on my way!

During the waiting in line at the service center I learned from other passengers that it wasn't apparently one bag, but a whole bunch of them! They saw a chariot full of bags driving away from the plane. There might be lots of people missing their toothbrush next day..

Lufthansa is a great company. They have a very, good service, both on the ground and in the air. Planes are nice and mostly on time. I admire companies that have to endure human behavior, make it somehow work out and get people there. Kudos to the folks at the service center keeping their cool.
This incident wasn't Lufthansa's fault, I think. It might have been some mistake from the Krakaw airport luggage services. Shit happens..

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!

Playing chess within MySQL? Over the network? In the .. cloud? Yes!

This is a follow-up post of my 'A chessboard in MySQL' where we create and populate a chessboard. But pieces need to move, and a few wondered how. Easy!

As an example, white openes with 1.e4:

  mysql> BEGIN;
  mysql> UPDATE chessboard SET e='♙' WHERE x = 4;
  mysql> UPDATE chessboard SET e='' WHERE x = 2;
  mysql> COMMIT;

Pretty obvious. Now lets put it in a stored procedure (source included in post) so the next move is easier on the fingers and more fun to play. Lets do a 1...e5:

  mysql> CALL move_piece('e','7','e',5);

The result is the following:

mysql> SELECT * FROM chessboard;
+---+------+------+------+------+------+------+------+------+
| x | a    | b    | c    | d    | e    | f    | g    | h    |
+---+------+------+------+------+------+------+------+------+
| 8 | ♜    | ♞    | ♝    | ♛    | ♚    | ♝    | ♞    | ♜    |
| 7 | ♟    | ♟    | ♟    | ♟    |      | ♟    | ♟    | ♟    |
| 6 |      |      |      |      |      |      |      |      |
| 5 |      |      |      |      | ♟    |      |      |      |
| 4 |      |      |      |      | ♙    |      |      |      |
| 3 |      |      |      |      |      |      |      |      |
| 2 | ♙    | ♙    | ♙    | ♙    |      | ♙    | ♙    | ♙    |
| 1 | ♖    | ♘    | ♗    | ♕    | ♔    | ♗    | ♘    | ♖    |
+---+------+------+------+------+------+------+------+------+

Here is the stored procedure. It's very, very basic, and of course, one can add much more!

DROP PROCEDURE IF EXISTS move_piece;
delimiter //
CREATE PROCEDURE move_piece(
    psrcCol CHAR(1),
    psrcRow TINYINT,
    pdstCol CHAR(1),
    pdstRow TINYINT)
BEGIN
    SET @srcCol = psrcCol;
    SET @srcRow = psrcRow;
    SET @dstCol = pdstCol;
    SET @dstRow = pdstRow;
    SET @piece = 0;
    SET @blank = '';
    
    -- Get the piece we are moving
    SET @stmt = CONCAT('SELECT ',@srcCol,
        '+0 INTO @piece FROM chessboard WHERE x = ?');
    PREPARE preStmt FROM @stmt;
    EXECUTE preStmt USING @srcRow;
    DEALLOCATE PREPARE preStmt;
    
    IF ((@piece > 1 AND @piece <= 14) AND @piece is not NULL)
    THEN
        -- Move the piece
        SET @stmt = CONCAT('UPDATE chessboard SET ',
            @dstCol,'=? WHERE x = ?');
        PREPARE preStmt FROM @stmt;
        EXECUTE preStmt USING @piece,@dstRow;
        DEALLOCATE PREPARE preStmt;
        SET @stmt = CONCAT('UPDATE chessboard SET ',
            @srcCol,'=? WHERE x = ?');
        PREPARE preStmt FROM @stmt;
        EXECUTE preStmt USING @blank,@srcRow;
        DEALLOCATE PREPARE preStmt;
    ELSE
        SELECT "No piece found at given position." AS Error;
    END IF;
END;
//

Some thoughts for future expansion:

• You could save the moves in a separate table to record time it took.
• Implement some other movement notation.
• Lock a player after a move.
• Build some other stored routines to populate and reset the chessboard.
• Have multiple chessboard tables.
• Event scheduler can be used to implement the non-human player!

Anyway, this was all about fun and Unicode testing. There will be probably no follow-up on this post. If somebody is crazy enough to actually implement a chess game in MySQL: awesome!

(Disclaimer: I am not a chess player.)

UPDATE 2009-12-31: Here is the insert statement for populating the chessboard:

INSERT INTO `chessboard` VALUES
  (8,'♜','♞','♝','♛','♚','♝','♞','♜'),
  (7,'♟','♟','♟','♟','♟','♟','♟','♟'),
  (6,'','','','','','','',''),
  (5,'','','','','','','',''),
  (4,'','','','','','','',''),
  (3,'','','','','','','',''),
  (2,'♙','♙','♙','♙','♙','♙','♙','♙'),
  (1,'♖','♘','♗','♕','♔','♗','♘','♖');

Something to keep you warm during cold winter nights, or cool during hot summer days: a chessboard in MySQL!

Note: You should see chess pieces here below. If not, you're not watching it using UTF-8, or get yourself a good browser!

CREATE TABLE `chessboard` (
  `x` tinyint unsigned NOT NULL,
  `a` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `b` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `c` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `d` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `e` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `f` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `g` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `h` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟')
) DEFAULT CHARSET=utf8;

Populating it with Python using MySQL Connector/Python (only piece of script shown):

def create_board(db):
    c = db.cursor()
    
    table = """
        CREATE TABLE chessboard (
        x tinyint unsigned not null,
        a ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        b ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        c ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        d ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        e ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        f ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        g ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        h ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F')
        ) default charset='utf8'
    """
    c.execute("DROP TABLE IF EXISTS chessboard")
    c.execute(table)

def set_start_position(db):
    c = db.cursor()
    # Numbers correspondent to the ENUM fields
    wdata = { 'x' : 1,
        'a': 4, 'b': 6, 'c': 5,
        'd': 3, 'e': 2,
        'f': 5, 'g': 6, 'h': 4}
    bdata = { 'x' : 8,
        'a': 10, 'b': 12, 'c': 11,
        'd': 9, 'e': 8,
        'f': 11, 'g': 12, 'h': 10}
    
    stmt = """UPDATE chessboard SET a=%(a)s,b=%(b)s,c=%(c)s,
        d=%(d)s,e=%(e)s,f=%(f)s,g=%(g)s,h=%(h)s WHERE x = %(x)s"""
    c.executemany(stmt, [wdata,bdata])
    
    stmt = """UPDATE chessboard SET a=7,b=7,c=7,d=7,e=7,f=7,g=7,h=7
        WHERE x = 2"""
    c.execute(stmt)
    stmt = """UPDATE chessboard SET a=13,b=13,c=13,d=13,e=13,f=13,g=13,h=13
        WHERE x = 7"""
    c.execute(stmt)

Now you can select the chessboard:

mysql> select * from chessboard;
+---+------+------+------+------+------+------+------+------+
| x | a    | b    | c    | d    | e    | f    | g    | h    |
+---+------+------+------+------+------+------+------+------+
| 8 | ♜    | ♞    | ♝    | ♛    | ♚    | ♝    | ♞    | ♜    |
| 7 | ♟    | ♟    | ♟    | ♟    | ♟    | ♟    | ♟    | ♟    |
| 6 |      |      |      |      |      |      |      |      |
| 5 |      |      |      |      |      |      |      |      |
| 4 |      |      |      |      |      |      |      |      |
| 3 |      |      |      |      |      |      |      |      |
| 2 | ♙    | ♙    | ♙    | ♙    | ♙    | ♙    | ♙    | ♙    |
| 1 | ♖    | ♘    | ♗    | ♕    | ♔    | ♗    | ♘    | ♖    |
+---+------+------+------+------+------+------+------+------+

The possibilities.. oh yeah!

Here is two small examples showing the wonderful world of character sets and unicode using MySQL Connector/Python (using 0.1.2-devel and up) in both Python v2.x and v3.1.

The following table will be used with default character set latin7, i.e. ISO-8859-13. Just setting it to UTF-8 would be bit boring!

CREATE TABLE `latin7test` (
  `c1` varchar(60) DEFAULT NULL
) DEFAULT CHARSET=latin7

Things to note for the code listed below are:

• We're using charset='latin7' as connection option. This is important!
• We set use_unicode=True so the results coming from MySQL are encoded to unicode. For testing, we disable this later.

Python v2.x

Here is the code which will insert (Polish) latin7 text and selects them again from the table.

db = mysql.connect(user='root',db='test',
    buffered=True,
    charset="latin7",
    use_unicode=True)

latin7 = [
    # Hello in Polish
    'dzie\xf1 dobry!',
    'cze\xfa\xe3!'
    ]

cur = db.cursor()
stmt = 'INSERT INTO latin7test VALUES (%(c1)s)'
cur.execute(stmt, { 'c1' : latin7[0] } )
stmt = 'INSERT INTO latin7test VALUES (%s)'
cur.execute(stmt, (latin7[1],) )

stmt = 'SELECT * FROM latin7test'
cur.execute(stmt)
rows = cur.fetchall()
print(rows)

db.set_unicode(False)
cur.execute(stmt)
rows = cur.fetchall()
print(rows)

cur.close()
db.close()

The result:

[(u'dzie\u0144 dobry!',), (u'cze\u015b\u0107!',)]
[('dzie\xf1 dobry!',), ('cze\xfa\xe3!',)]

The above might look weird, but if you put this in a webpage with proper encoding or print it in a terminal which supports UTF8 or latin1, it should look nice.

Python v3.1

db = mysql.connect(user='root',db='test',
    charset="latin7",use_unicode=True)

latin7 = [
    # Hello in Polish
    b'dzie\xf1 dobry!',
    b'cze\xfa\xe3!'
    ]

cur = db.cursor()
stmt = 'INSERT INTO latin7test VALUES (%(c1)s)'
cur.execute(stmt, { 'c1' : latin7[0] } )
stmt = 'INSERT INTO latin7test VALUES (%s)'
cur.execute(stmt, (latin7[1],) )

stmt = 'SELECT * FROM latin7test'
cur.execute(stmt)
rows = cur.fetchall()
print(rows)

db.set_unicode(False)
cur.execute(stmt)
rows = cur.fetchall()
print(rows)

cur.close()
db.close()

The result:

[('dzień dobry!',), ('cześć!',)]
[(b'dzie\xf1 dobry!',), (b'cze\xfa\xe3!',)]

The above looks nicer than the Python v2.4+ one. That's because in Python v3.x every string is now unicode. The second line shows the same data, but encoded in latin7 and returned as bytes-objects since use_unicode is set to False.