Today we put the finishing touches on another article for the Developer's Zone page. This one centered on how to implement foreign keys via triggers into MySQL. This is important when your application requires referential integrity and the storage engine you'd like to use, as in the case of MyISAM or more specifically NDB (Cluster), does not support this functionality natively.

In the article we cover:

• Restricting INSERTS
• Restricting UPDATES and DELETES
• Cascading UPDATES and DELETES

Some of the advantages of foreign key enforcement include:

• Assuming the proper design of the relationships, foreign key constraints make it more difficult for a programmer to introduce an inconsistency into the database.
• Centralizing the checking of these constraints by the database server makes it unnecessary to perform these checks on the application side. This …

Sometime it is needed to handle a lot of rows on client side. Usual way is send query via mysql_query and than handle the result in loop mysql_fetch_array (here I use PHP functions but they are common or similar for all APIs, including C).
Consider table:

PLAIN TEXT SQL:

1. CREATE TABLE `longf` (
2.   `f1` int(11) NOT NULL AUTO_INCREMENT,
3.   `f2` date DEFAULT NULL,
4.   `f3` date DEFAULT NULL,
5.   `f4` varchar(14) DEFAULT NULL,
6.   `f5` varchar(6) DEFAULT NULL,
7.   `f6` date DEFAULT NULL,
8.   `f7` smallint(6) DEFAULT NULL,
9.   `f8` smallint(6) DEFAULT NULL,
10.   `f9` varchar(13) DEFAULT NULL,
11.   `f10` varchar(39) DEFAULT NULL,
12.   `f11` int(11) DEFAULT NULL,
13.   `f12` float DEFAULT …

From Monday to Wednesday we at eZ systems AS had our internal developers conference in Skien, Norway.

Since I joined eZ systems only recently, this was the opportunity for me to get to know all employees that gathered from all over the world. Most of the Tuesday was reserved for a "Crew Day" with some "team-building" which took place in a forest nearby.



On Thursday and Friday the eZ publish conference 2006 took place.

During the conference I met with …

From Monday to Wednesday we at eZ Systems AS had our internal developers conference in Skien, Norway.

Since I joined eZ systems only recently, this was the opportunity for me to get to know all employees that gathered from all over the world. Most of the Tuesday was reserved for a "Crew Day" with some "team-building" which took place in a forest nearby.



On Thursday and Friday the eZ publish conference 2006 took place.

During the conference I met with Sébastien Hordeaux of WaterProof SARL, the …

After seeing Frank’s script in “Finding out how far behind are slaves” I figured I would post some nagios plugins I wrote a while ago, that we use at our company. They’re already up at http://www.nagiosexchange.org/, the 3rd party repository for nagios plugins.

So I figured I’d point you to:
Replication Lag Time
and
InnoDB Free Space

I’ll note that backups from a slave server can cause replication lag time to fall behind, for 2 reasons — …

BusinessWeek has been covering the rise of open source and Web 2.0 companies for a while.  In a special series of articles, they show how Web 2.0 is going to change the landscape of not only consumer software, but also enterprise software.  Not surprisingly most of these web 2.0 companies are powered by open source software.  Whether it's the large public companies like Google, NetFlix or Yahoo or startups like JigSaw, Soonr, SixApart, SlashDot, Second Life, FaceBook, Flickr, Meetup, Technorati, Wikia or Craigslist, they are all built on a scale-out architecture with MySQL. Open source scale out is the architecture for the modern enterprise.  And its not because open source is cheaper.  Open source gives startups and large companies the freedom they need to scale as their business grows.

The next …

As we know build in full text search is currently limited only to MyISAM search engine as well as has few other limits.

Today Sphinx Search plugin for MySQL was released which now provides fast and easy to use full text search solution for all storage engines. This version also adds a lot of other new features, including boolean search and distributed searching.

A while ago I already wrote about Sphinx Search Egine, comparing it to built in FullText search and Mnogosearch. I guess I should soon repeat tests, adding Lucene to the list for complete picture.

And if you do not feel like patching MySQL or use MySQL 5.1 beta to use sphinx as MySQL Storage Engine you can still use it old fashion way as separate server.

Many of you have experienced performance problems with JOINs in MySQL Cluster. However, in MySQL 5.1, it is possible to address this using MySQL replication and create a read slave where the JOINs are performed.

It is possible to set a MySQL Cluster looking like this (very schematic):
To read more on how to setup the replication link beetween the Master and the Slave, see http://dev.mysql.com/doc/refman/5.1/en/replication.html .

The trick here is to make sure that the tables created using the NDBCluster engine are created on the slave MySQL server as MyISAM or in another storage engine more performant resolving JOINs.

This way you can now issue complex JOINs on the slave instead of on the cluster and the replication …

The most powerful and efficient way to access data stored in the NDB storage engine is to use the NDBAPI, a C++ API which gives you direct access to data stored in MySQL Cluster.

Instead of having to go through the MySQL server to access data, you can access the data directly from your C++ application. Why would you like to do this? There are some reasons:

1. Data access with minimal overhead
   
2. Flexibility - posssible to hand-optimize queries. You control exactly when you want to send data to NDB Cluster.
   
3. Performance - Shorter response times, because of fewer network hops (no MySQL server as a proxy) and (1,2) above.

Typically, application developers that want soft realtime characteristics on data access use the NDBAPI, and use SQL (going through the MySQL server) for OAM type applications.

In this blog, I will try to show you how to start programming …

Today I uploaded version 1.01 of my e-book, No Lobbyists As Such - The War over Software Patents in the European Union. I just corrected a few minor errors and would like to express my gratitude for the corrections submitted by Alberto Barrionuevo and Péter Somogyi.