PostgreSQL has a wonderful feature which is called User-Defined types. It allows anyone to create a new datatype, along with the relevant operators, which are then managed almost natively by Postgres.

I decided to use this feature to manage chessboard positions in a more efficient manner than the usual ASCII storage which looks like “rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR”

I named this new type EPD, for “Extended Position Description”. I know that all the relevant data is not present (I shall typically store information like the active color, the casting availability, and a few other fields), but it didn’t sound like a bad choice for a name.

It allows to do some cool things like:

– Create a table with the epd type

chess=# CREATE TEMPORARY TABLE test_epd(my_epd epd);
CREATE TABLE

– Insert a string - it gets automatically converted to a native format and only takes 32 bytes
chess=# INSERT INTO test_epd VALUES (’rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR’);
INSERT 0 1

– And converted back into a string

chess=# SELECT * FROM test_epd;
my_epd
———————————————
rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR
(1 row)

– What’s on A2 ?
chess=# SELECT epd_piece_at(my_epd, ‘a2′) FROM test_epd;
epd_piece_at
————–
n
(1 row)

chess=#

The code is available here. The installation process is:

1. Compile: make
2. Install the .so: make install
3. Configure postgresql: cat epd.sql | psql

Looking for a way to check your global MySQL performance, to identify missing indexes and other possible optimization points?

Today’s featured link should solve all your issues: mysqlsla.

The transaction log grows, grows, grows and quite often fills up.

I know two ways of shrinking it:

• With backup:

backup log netmeter(db_name) to disk=’specify the filename’

• Without backup:

backup log netmeter with truncate_only

The easy way…

Log on to the slave and do a “load data from master” :

host:/home/mac# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 131 to server version: 4.0.24_Debian-10sarge2-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> load data from master;

ERROR 1189: Net error reading from master

This means that there is most likely a corruption in your logfiles…

What to do ?

* On the direct parent :

mysqldump -a --add-drop-table -e -x -p --master-data

-u root -p -B DB_to_Replicate > /tmp/master.sql

* Copy the file to the slave
* On the slave :

mysql> stop slave;

mysql> . /tmp/master.sql

mysql> start slave;

mysql> show slave status;

Wait a bit and check that the replication catches on…