The other night I was thinking about MySQL cluster and whether or not a high speed interconnect would be worth the investment. Doing some research lead me to the conclusion that ethernet latency isn't discussed very often except in niche mailing list threads. I had to do a lot of research to find even a basic discussion of the problem (Note. If you have any good resources on the subject please add a comment.)

In order to help other developers avoid mistakes I've made in the past I figure a good blog post on the subject was in order.

What's ethernet latency? When you send a packet to a remote machine over gigabit ethernet it's not just instant. There's overhead involved in sending the packet back and worth. The data has to go from user space into the kernel, out the network interface, onto a switch, and then back up the kernel and into userland …

Innochecksum is a small undocumented (at least in the manual) utility that verifies the checksum of ibdata file pages. I stumbled across it the other day while wandering through the source tree. It has a small bug that prevents it from being able to check files larger than 4G. This patch fixes it in linux. Someone that knows more about large file support in different OSs please comment on the patch.

It lives in the extras folder in the MySQL source. Here is the credits comment:

/*
InnoDB offline file checksum utility. 85% of the code in this file
was taken wholesale fron the InnoDB codebase.

The final 15% was originally written by Mark Smith of Danga
Interactive, Inc.

Published with a permission.
*/

I think as a community we need to make sure that tools like this are kept in the forge and actively …

No, not for te Elton John concert - though that's made finding accomodation very "interesting", almost everything is booked out on the Wednesday... anyway, I'll be in Wellington NZ all next week (3-8 December) to teach an in-house training course.

If anyone from around Wellington would like to catch up during that time, please do drop me a line!

• MySQL Bugs: #24601: MySQLd crashes upon creation of an index Bug report I just submitted because MySQL is crashing when I try to create an index. So much for spending the afternoon tweaking performance, eh? (tags: mysql bugs)

A kind soul has contributed a Debian/Ubuntu package for the innotop MySQL and InnoDB monitor. Thanks Sebastien Estienne! Sebastien wrote me that he tested it on Ubuntu Dapper Drake, and I just verified that it works on Edgy Eft. Since I use Ubuntu on my own machine now, I may integrate this into my build process for future releases of innotop. But for right now, at least there’s another installation option.

Maybe the last XAMPP release of 2006: XAMPP 1.5.5 for Windows and Linux is out. New in both releases of XAMPP are MySQL (5.0.27), PHP (5.2.0), phpMyAdmin (2.9.1) and a Japanese translation. The Windows version also contains up-to-date versions of: FileZilla FTP Server (0.9.20) and ADOdb (4.93).

And today we have yet another scoop for you: The first and long awaited Intel version of XAMPP for Mac OS X. Also updated with all the recent versions of Apache, PHP, MySQL and Co.

Download XAMPP 1.5.5 here

The standard Pentaho demo download is super quick and easy: there’s no installation and it just works.  You double click start-pentaho.bat and then it’s running in http://localhost:8080.

However, sometimes you may want to share this demo with others.  Roland Bouman has a nice blog entry on the specifics of how to change the demo install into a server. 

I add the following line to my start-pentaho.sh to make the hostname changing transparent. 

sed -i -e “s/http:\/\/.*:8080/http:\/\/`hostname -f`:8080/” jboss/server/default/deploy/pentaho.war/WEB-INF/web.xml

This allows one to move this “pentaho” to any system and it will startup properly with the …

DBA’s working in environments where MySQL database is for production critical applications are constantly pushed to their limits. Database Users and DBA’s are humans too. So, a dropped table here or a misconstrued MySQL statement there is not out of ordinary. Recovering from these kinds of user errors is extremely vital to production databases. Zmanda Recovery Manager (ZRM) for MySQL, is a good way to recover from user errors. Consider this example - You [DBA] find out that an user has made an unforced error which is affecting your production database. You further find out that this has happened in the last half hour. Lucky for you, you use ZRM for MySQL in your environment to make nightly backups. You spring into action! You first verify that a nightly backup was run. You then make an incremental backup now. Using ZRM for MySQL, you identify the point at which the user …

When using a uniqie index on a text field in mysql, the column collation setting is very important. The collation settings of a column does not only affect sorting and comparsion, but also unique indexes. So you can not insert "a" and "A" into a table that has a unique index on a column that has a case-insensitive collation. The mysql manual about collations: "A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set."

Here is an example:
The column text in table text1 has a case-sensitive collation (_cs suffix), the column in text2 has a case-insensitive collation (_ci suffix).

PLAIN TEXT CODE:

1.  
2. CREATE TABLE text1 (
3.   `text` varchar(50) character set latin1 collate latin1_general_cs NOT NULL …

As per this post on Devshed forums I'm pasting here some sample code (taken from Mike Hillyer's excellent "Accessing MySQL BLOB columns using Visual Basic 6"), hope it helps.
Code uses a simpler table structure and due to the fact that I didn't bother about reproducing the "autoincrement" field it will get you into troubles if trying to run the sample multiple times.
You'll notice that basically the only different thing is the connection string!

1. 'CREATE CONNECTION OBJECT AND ASSIGN CONNECTION STRING
2. Dim conn As ADODB.Connection
3. Set conn = New ADODB.Connection
4. conn.ConnectionString = "DRIVER={Firebird/Interbase(r) Driver};DBNAME=localhost:C:\Programmi\Firebird\Firebird_2_0\examples\empbuild\test.fdb;UID=SYSDBA;PW D=masterkey"
5. conn.CursorLocation = adUseClient …