Building MySQL universal binaries using MacOS X 10.6 (Snow Leopard)

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!

A chessboard in MySQL: make your moves

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:

BEGIN;
UPDATE chessboard SET e='♙' WHERE x = 4;
UPDATE chessboard SET e='' WHERE x = 2;
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:

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,'','','','','','','',''),<br />  (3,'','','','','','','',''),
  (2,'♙','♙','♙','♙','♙','♙','♙','♙'),
  (1,'♖','♘','♗','♕','♔','♗','♘','♖');

A chessboard in MySQL

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!

Using character sets with MySQL Connector/Python

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.

By |December 23rd, 2009|Python|1 Comment|

MySQL Connector/Python 0.1.2-devel available!

MySQL Connector/Python 0.1.2-devel is a quick follow-up release for 0.1.1 fixing a few problems around character sets and unicode.

You can download Connector/Python from LaunchPad.

Release notes for MySQL Connector/Python 0.1.2-devel


o Fixing unicode usage for both Python 2.4+ and 3.1
* Setting 'use_unicode' at connection time is now
working.
* conversion.py: removing regular expression for
quoting backslashes.
* Adding test case for bug lp:499410

Py3k specific:
* Strings from MySQL are decoded to the given character
when use_unicode is false
* The statement is encoded just before sending it to the
MySQL server. Internally, all is done in unicode.
* In conversion.py: removing _unicode_to_mysql, adding
_bytes_to_mysql
* MySQLCursor.__unicode__ is obsolete and replaced
with __str__
* Removing tests for which the methods were deleted.

o Fix setting character set at connection

* mysql.connector.Connect(charset='latin1') now works
as expected
* Default character set is (still) UTF-8.
* SET NAMES is only used when changing character set
after connecting.
Use MySQL.set_charset(charsetname) to change.
* Test case added for bug report; fixing test case in
test_protocol.py to reflect the new default character
set 'utf-8'.

Please report problems on LaunchPad. Thanks!