Developer Articles

MySQL/Sun released a new storage engine "Falcon" in January, 2007. Falcon is a high performance transactional (fully compliant with ACID) storage engine, which is beta at this time (June 2008). In this article, I describe Falcon's features and its architecture in detail.

"Telephony is just yet another Internet application." - MySQL talks with Juha HeinÃ¤nen

By MySQL Telecom Team (MySQL in Communications Blog) | 2008.05.23

During 2008 we are planning to run a series of interviews with interesting persons somehow related to the telecom field. In this first installment, we will have a chat with Juha HeinÃ¤nen from Finland.

MySQL Workbench for Database Change Management

By Robin Schumacher | 2008.05.08

Managing database change is an incredibly important discipline that very few database professionals overtly talk or worry about until they're in the thick of things with a particular database - moving it from development to production, making changes to a newly installed production database, or implementing an updated version of the database (new tables, modifications to existing objects, etc.) in a SaaS application. It's at that point where change management becomes very important because if you don't do things right the first time, you can make a royal mess of things and even (in a small number of cases) reach the point of no return where you've completely torched your database.

A look at Falcon Diagnostic Tables

By Robin Schumacher | 2008.04.04

Performance tuning is one of the top disciplines (if not THE top discipline) that database professionals want to excel at. Being able to take a system that's running sluggish and turn it into one that's running as fast as a scalded dog is a talent that's part art and part science, but whatever the combination necessary to make it happen, there will always be strong demand for folks who are good at it.

How the MySQL Enterprise Upgrade Advisor Helps DBAs Avoid Being Bitten by Known Bugs

By Rob Young | 2008.04.01

In an earlier article I described how MySQL Enterprise takes the guesswork out of deciding which version of the MySQL server customers should be running by providing alerts around regularly scheduled Monthly Rapid Update and Quarterly Service Pack releases of the Enterprise Server. Being of an old school "if it ain't broke don't fix it" mindset, I understand the conservative approach most DBAs take when deciding if a new release of any software is relevant to their environment. In fact, given the monthly frequency of Enterprise maintenance releases and the work involved with upgrading, I completely understand how recipients can begin to ignore Update Alerts (unless of course a known fix is on the way). Based on feedback from customers, MySQL colleagues, and my own field experience, I recognize that while notifications around the regular Enterprise Server drops is a good thing, upgrading an existing MySQL implementation is no small task and that a major part of removing guesswork around new releases involves helping those receiving notifications better understand how they are affected.

Why you want to be good at data modeling

By Robin Schumacher | 2008.03.27

To be really successful at working with databases, there are many different disciplines to become proficient in - things like disaster recovery, security management, data integration, and more. But there are less than a handful of things you want to be really good at; becoming super-skilled in them will help you enormously in your database career. And believe it or not, one of these key areas is data modeling and its kissing cousin, physical database design.

Reporting MySQL Internals with Information Schema plug-ins

By Roland Bouman | 2008.02.21

Last week, I described how to use the MySQL plug-in API to write a minimal 'Hello world!' information schema plug-in. The main purpose of that plug-in is to illustrate the bare essentials of the MySQL information schema plug-in interface.

MySQL Information Schema Plugins: the best kept secret of MySQL 5.1

By Roland Bouman | 2008.02.12

MySQL 5.1 offers an extremely useful feature called information_schema plugins. This feature allows dynamic runtime loading of a shared library into the MySQL server to implement a table in the information_schema database. The SQL standard (ISO/IEC 9075-11:2003) allows database implementations to extend the information_schema. MySQL 5.1 transfers the possibility to do this directly to privileged database users so they can extend the information_schema themselves, in any way they see fit.

A Q&A Session with MÃ¥rten Mickos, CEO of MySQL AB, about the Sun Acquisition

By Colin Charles | 2008.02.06

We got a chance to speak with MÃ¥rten Mickos, CEO of MySQL AB, about Sun's planned acquisition of MySQL, and we asked him some questions, that the community at large, might have.

MySQL 5.1 partitions in practice

By Giuseppe Maxia | 2007.12.19

This article explains how to test the performance of a large database with MySQL 5.1, showing the advantages of using partitions. The test database uses data published by the US Bureau of Transportation Statistics. Currently, the data consists of ~ 113 million records (7.5 GB data + 5.2 GB index).

Using XML in MySQL 5.1 and 6.0

By Jon Stephens | 2007.12.13

In this article, we discuss the XML functionality available in MySQL, with an emphasis on new features coming online in MySQL 5.1 and MySQL 6.0. We assume that you already have a working knowledge of XML, and that you know what the terms â€œvalidâ€ and â€œwell-formedâ€ mean. We also assume that you have some knowledge of XPath.

MySQL Failover Strategy using State Management, introducing MPP - Part 3

By Russell E Glaue | 2007.12.12

In part 2 we discussed the internals of MPP and how MPP can be used with LVS to create a failover strategy. In this part 3 we will discuss how to configure and operate MPP, and additionally use MPP with MySQL Proxy to create a failover strategy.

Developing MySQL Applications with Eclipse CDT

By Mark Schoonover | 2007.11.26

In September, the Developer Zone featured Chapter 6 of Paul DuBois' MySQL, 3rd edition on Writing C Programs with the MySQL C API. This month, we continue in this series on developing MySQL applications in C with a tutorial from Mark Schoonover about using the Eclipse CDT (C Development Toolkit) as an IDE for developing C applications for MySQL.

MySQL Failover Strategy using State Management, introducing MPP - Part 2

By Russell E Glaue | 2007.11.10

In Part 1 we have seen how the concept of state management works. Now it's time to apply that concept to a load-balancer. In this Part 2 we will look at a strategy for using Linux Virtual Server with MPP for failover, and also take a closer look at the mechanics of MPP itself.

MySQL Failover Strategy using State Management, introducing MPP - Part 1

By Russell E Glaue | 2007.11.01

Having a strategy for failover has become almost standard in the business world. Whether it is a total site failover from a NOC in one city to another, or just a simple redundant server. Over the years many technology solutions have been created for or adopted to MySQL to provide this type of strategy.

A look at MySQL on ZFS

By John David Duncan | 2007.10.26

When Sun Microsystems released the ZFS filesystem in 2004, they set out to remove all of the practical limits on filesystem storage, and to make the arcane details of storage management a thing of the past. ZFS is a 128-bit filesystem with - to name just a few features - copy-on-write transactional semantics, fast snapshots, and optional compression.

Load and Search MySQL Data Using VB.NET 2005 in Windows Applications

By Ernest Bonat, Ph.D. | 2007.10.18

Call Pantek today for Open Source Technical Support at 1-877-546-8934 - 24/7/365

MySQL data load and search are very important business requirements in any Windows or Internet web application development. In general, any application needs to show a result set of data and/or a single record to the end-users. In Windows applications it is very popular to show a result set of data by using the DataGridView, ListView or TreeView controls. A single record can be shown by the simple combination of the following controls: TextBox, ComboBox, ListBox, CheckBox, RadioButton, etc. MySQL data search is provided by using the required ADO.NET data objects and by refreshing the controls if necessary. These two processes, data load and search, should be fast and should be done with the proper code which depends on the controls in the Windows Form or Web Page. In this article I will show you how load and sort MySQL data using the DataGridView control. To search MySQL data the LIKE SQL operator will be used. Both programming implementations are done by using stored procedures for MySQL 5.0 database engine.

CTO Interview: Christopher Cordray, Co-Founder and CTO, ScienceLogic

Christopher Cordray, a ScienceLogic founder and the principal software architect behind the EM7 appliances, has more than fifteen years experience with networking, management systems and technology-related operations. Passionate about improving IT operations and application efficiency, Cordray has architected and developed various commercial and custom solutions for companies including Verizon Global Networks, Interliant, Inc and Edison International.

Paul DuBois - Writing MySQL Programs Using C

By Paul DuBois | 2007.09.28

Many web developers view development of C programs as a bit of a black art. Development of programs in a compiled language such as C is vastly different from the development of applications in a scripting language. To familiarize more developers with the MySQL C API, we present a three-part series of articles tailored to developers looking to get into C program development with MySQL. This first article is the full chapter from Paul DuBois' industry-standard work, MySQL, Third Edition, from Pearson Publishers. The second article, from Mark Schoonover, to be published next week, will cover development with the C API using the Eclipse CDT to write a sample program. The third article, also from Mark Schoonover, will go into more depth and expand the example program using more advanced features of the API.

A Primer on Falcon Tablespaces

By Robin Schumacher | 2007.09.18

The Falcon transactional storage engine uses the concept of user-defined tablespaces that are more in line with what one would find in Oracle (tablespaces) or SQL Server (filegroups). Letâ€™s take a quick look at how tablespaces are used within Falcon and what you can and canâ€™t do with them.

Using MySQL with PDO

By Peter Lavin | 2007.09.18

PDO is a PHP extension providing a data-access abstraction layer that can be used with a variety of databases. This gives you the flexibility of changing the database backend without having to alter your access methods. Even if you use MySQL exclusively, PDO can provide advantages; you can use the same data-access methods regardless of the MySQL version. This does away with the need for using the standard MySQL extension with older versions of MySQL and using the MySQLi extension for later versions. An additional advantage is the ability to use object-oriented code regardless of the version of MySQL.

Confused? Frustrated?

How MySQL Treats Security Vulnerabilities

By Kaj ArnÃ¶ | 2007.09.18

The phrase "security vulnerability" gives most of us the creeps. We are afraid of them. Looking at them rationally, they share a trait with other bugs: All are a pain, but some are a bigger pain than others. And instead of trying to avoid the evil by not talking about it, let's take a look at MySQL's security vulnerability track record over the years, and explain the rationale behind our actions using actual cases from past years.

MySQL 5.1 Article Recap

By Jay Pipes | 2007.09.11

So, someone had a great idea to go back through the Developer Zone, which has become a bit disorganized of late, and put together an article with links and summaries for all the Dev Zone articles that discussed MySQL 5.1 features. I have done so, and also linked in many community member's blog articles and tutorials to give everyone a good dose of the features that make MySQL 5.1 worth investigating.

Preparing for MySQL 5.x Certification

By Mark Schoonover | 2007.09.10

You've decided to obtain MySQL certification and that's great! Now it's a matter of how to go about it? You can go the traditional routes and take a class, or you can prepare on your own. I did the latter, and 2 exams later during MySQL Conference 2007, I passed the Certified MySQL Database Administrator (CMDBA) exams. I wanted to share my exam preparation experience, as I've never found this information in any other place. I took what I learned from how to study math and applied it to preparing for the MySQL exams. These techniques will probably work with other certifications, and I'm sure you'll come up with some of your own techniques to share.

Migrating From MS Access To MySQL

By Christopher Keene | 2007.08.22

According to recent survey, over 20% of MySQL users plan to migrate a Microsoft Access applications to MySQL over the next 12 months. However there are few documents available that describe best practices for performing such a migration.
This document summarizes discussion from the â€œMS Access Migrationâ€ session at the 2007 MySQL User Group meeting in California. That session brought together a number of MySQL users with a goal of identifying key success factors for moving MS Access applications to MySQL.

Getting started with MySQL Proxy

By Giuseppe Maxia | 2007.08.17

The launch of MySQL Proxy has caused quite a commotion in the community. And with reason. For feature hungry people, this is undeniably the most exciting addition to MySQL set of tools.
If the last statement has left you baffled, because you don't see the added value, don't worry. This article aims at giving you the feeling of what the Proxy can do.

From Visions to Reality - an interview with David Axmark, Co-Founder of MySQL AB

By Lenz Grimmer | 2007.08.02

David Axmark co-founded MySQL AB together with Michael "Monty" Widenius and Allan Larsson in 1995. Today, he is a member of the MySQL Community Relations team and has an advisory role in the MySQL Management. He travels all across the globe to advocate for MySQL and Open Source Software in general and has just recently moved from Uppsala, Sweden to Ascot in the United Kingdom, where he lives with his wife and two children.

How to write a successful patch

By Giuseppe Maxia | 2007.07.28

Submitting patches to Open Source code doesn't come naturally to everyone. It is not easy, since it requires coding skills that the average user does not have. Moreover, code patches require a fair amount of additional documentation, without which the patch itself is virtually useless. These notes will walk you through the requirements of a good patch for MySQL server and perhaps other projects.

The Clash of the DB Egos

By Kaj ArnÃ¶ | 2007.07.26

One of the most popular keynotes of the MySQL Conference & Expo 2007 was called "The Clash of the DB Egos". It was a fight amongst seven database luminaries, all playing an important role either within MySQL AB or as providers of Storage Engines that work closely with MySQL. This article attempts at giving a picture of what the fight was about, through reciting the egos and the questions posed to them by the referee.

Follow Up on Rolling Sums - Self Joins vs User Variables

By Jay Pipes | 2007.07.12

Damien Seguy had an interesting observation on my use of self joins for generating rolling sums.

MySQL bugs processing explained

By Giuseppe Maxia | 2007.07.04

What's behind MySQL bugs processing? Who handles your bug report? Why does it take so long? These, and many other questions are answered in this article about the internals of MySQL organization.

MySQL as universal server

By Giuseppe Maxia | 2007.06.28

MySQL users often dream about a more powerful yet stable database server. They want a server with more features, like expanding macros, customized logging, access to shell commands, access to live tables from other DBMS. Every feature, given enough development time, can be eventually implemented, but users want them now, with the current GA server. It can be done. Using a proxy server, some of those dreams come true. Today.

3 Tips for Customizing MySQL Enterprise to Achieve Monitoring Best Practices

By Rob Young | 2007.06.27

During my years as a Developer/DBA hearing the words "customizable" or "extensible" always brought a slight smile to my face, particularly when they were used in conjunction with a software sales pitch or evaluation. In fact, more often than not, while presented as product features, these words often really mean, "you can do some additional things with this software if you are willing to invest significant time and resources into doing so". Even today, some of the most popular open source and commercial software solutions provide application programming interfaces (APIs) and plug-ins that are really nothing more than containers that allow you to provide your own code. The goal being to make an off-the-shelf product more applicable to the problems you are attempting to solve without having to write a customized application from scratch.

MySQL Data Loading with Lookup Tables

By Ernest Bonat, Ph.D. | 2007.06.25

Lookup tables contain, in general, a fixed list of data. This data doesnâ€™t change very often in database business applications. Examples of this data could be a product list, category type, supplier list, state name, zip code, phone area code, etc. In Windows and Internet web business applications, most of these lookup tables are graphically implemented by using ComboBox, ListBox or CheckListBox read-only controls. These controls are loaded with data using two main columns, ID and Name. For example, the USA state table, the ID could be â€˜CAâ€™ and the Name â€˜Californiaâ€™. Some times, for standard Windows form and Internet web page we need to show data to the end-users from many of these lookup tables. A fast data loading process and defining the main column values for each lookup table is required. In this article I will show you standard lookup data loading procedure and the generic classes object to store and read-only the values of the ID and Name columns from the lookup tables. Selecting and finding the ID and Name values will be provided. Executing stored procedures with input/output parameters in MySQL 5.0/VB.NET 2005 will be covered in detail too.

Rolling Sums in SQL - A Practical Example

By Jay Pipes | 2007.06.21

Today, Lenz was putting together some stats on PlanetMySQL feeds added since January this year, and asked in an email whether he should include totals in the stats. I responded yes, and offered a quick SQL solution to get those numbers out of the database. I thought it might be useful for others, so here goes...

MySQL on Windows? Absolutely!

By Robin Schumacher | 2007.06.13

The admission I'm about to make will likely cause me to lose some friends/colleagues in the open source world, but I have to be honest: I like using Microsoft Windows. Except for Windows 3.0-3.1, I always have. Further, I personally much prefer managing databases on Windows than Unix and will also give Windows the nod over Linux in certain areas. See, I came from managing DB2 on the mainframe to running data warehouses with Teradata on their proprietary platform, and then did a long run with Oracle on UNIX (AIX and HPUX).

Interview with Jan Kneschke, Developer at MySQL AB

By Lenz Grimmer | 2007.06.08

Jan Kneschke works for the MySQL Development Team and is the author of the very popular lighttpd web server. I recently had the opportunity to ask him a few questions about himself and his work.

Debunking GROUP BY Myths

By Roland Bouman | 2007.06.06

There is a popular myth about the SQL GROUP BY clause. The myth holds that 'standard SQL' requires columns referenced in the SELECT list of a query to also appear in the GROUP BY clause, unless these columns appear exclusively in an aggregated expression. MySQL is often accused of violating this standard. In this article I will attempt to debunk this myth, and to provide a more balanced view regarding MySQL's treatment of GROUP BY at the same time.

How Automated Updates Provided by MySQL Enterprise Can Save You Time

By Rob Young | 2007.05.21

The value of timely information regarding maintenance releases, including specific information of what bugs have been fixed and when and where to get the upgrade, proves very compelling for those DBAs and developers with little to no bandwidth. The good news is MySQL Enterprise takes the guesswork out of deciding which version of the MySQL server you should be running by providing regularly scheduled Monthly Updates and quarterly Service Packs for the Enterprise Server.

A Developer's Guide to the MySQL User's Conference

By Jay Pipes | 2007.04.17

Robin Schumacher recently gave us an excellent rundown on sessions at the upcoming MySQL Conference and Expo (April 23-26th in Santa Clara) for DBAs. As a proud developer I wanted to make sure that our enormous developer community had a similar guide. Now, developers will encounter the same problem that Robin pointed out: with so many excellent sessions, you'll be required to make some difficult choices sometimes in what sessions you attend. This guide will hopefully allow you to navigate the waters. I've broken the guide down into a general section and also into programming language specific sections.

YouTube, Flickr, and Wikipedia to Share their Secrets of Success at the 2007 MySQL Conference & Expo

Sebastopol, CA -- April 10, 2007 -- The 2007 MySQL Conference & Expo is gearing up for April 23-26 at the Santa Clara Convention Center in Santa Clara, California. This year's program includes more than 110 tutorials and sessions to be delivered by some of the most successful companies and fast growing web sites in today's Internet economy -- including YouTube, Flickr, and Wikipedia, whose participation underscores the influence of MySQL's open source database. According to Tim O'Reilly, CEO and founder of O'Reilly Media, Inc., "Every killer app on the Internet is a database application. And that makes MySQL the 'Intel Inside' of the next-generation of computer applications."

Using the New MySQL Query Profiler

By Robin Schumacher | 2007.04.05

One of the great things about MySQL is the superior innovation model thatâ€™s used to deliver database server software. Rather than relying solely on internal engineers who create and maintain a piece of software (as in a traditional software company), MySQL partners with the millions of active users across the world who take advantage of the open source model and daily extend the MySQL server to do new and pioneering things. These innovations can then be submitted to MySQL AB, tested, validated, and rolled into the database server so everyone can benefit from the creativity of the very active MySQL community.

A DBA's Guide to the MySQL User's Conference

By Robin Schumacher | 2007.03.13

I live in absolute and abject poverty. No, I don't mean financially - I've been blessed beyond what I deserve and MySQL pays me very well. What I mean is that I am utterly time poor. Isn't it amazing how you can be so free in terms of lifestyle and yet have so little time to enjoy and benefit from it all? There are so many options and choices in the world that many times you have a hard time knowing just what to say "Yes" to and what to turn down. Trust me - you'll face this dilemma at the upcoming MySQL User's Conference. Our chairman, Jay Pipes, has outdone himself and put together the most engaging and power-packed agenda that we've ever had. The presenter's list reads like a who's who in the open source and modern database world, with sessions that address everything from deep dives down into database internals to the business side of implementing online database applications. You will find yourself hard-pressed to put together a personal attendee schedule because there will be so much you'll want to see and hear.

Daylight Saving Time Changes

By By Peter Gulutzan and Dmitri Lenev | 2007.03.13

This year many American states and Canadian provinces will switch to daylight saving time on March 11 (the second Sunday of March) instead of switching on the first Sunday of April as happened during previous years.

Define and Store MySQL ADO Connection String in VB.NET 2005

By Ernest Bonat, Ph.D. | 2007.03.08

In Windows and Internet web business applications development the connection to the MySQL database server is critical and requires high-level of security. In ADO.NET database technology the connection is defined in the Connection String property of the connection object. Defining and storing the MySQL Connection String properly is an important application setting task for developers today. In this article I'll show you how to setup and secure MySQL Connection String for Windows application development using VB.NET 2005. Also the difference of programming code between VB.NET 2003 and 2005 will be provided for data loading using MySQL Connector/NET 5.0.3 database driver.

A Look at the PBXT Storage Engine

By Robin Schumacher | 2007.02.28

Call Pantek today for Open Source Technical Support at 1-877-546-8934 - 24/7/365

Every now and then I get a big reminder of how great it is to be a part of the open source way of doing things. Having a strong community of MySQL lovers who use the free and open source MySQL database server and pay MySQL AB with code contributions and bug reports is truly a great way to develop rock solid and innovative software. Some contributions that come back to MySQL are small, but others are very large indeed. What would be large, you ask? How about a new, powerful transactional storage engine?

Understanding the Falcon Transaction Storage Engine â€“ Part 3

By Robin Schumacher | 2007.02.19

In Part 1 of this article series, we looked at how the Falcon transactional storage engine was architected and how it compared to some of the other MySQL storage engines. Part 2 covered how Falcon handles transaction management and concurrency. In this final article in the series, Iâ€™ll look at how Falcon manages tables and indexes, and also cover backup/recovery along with migration topics.

Understanding the Falcon Transaction Storage Engine â€“ Part 2

By Robin Schumacher | 2007.02.09

In Part 1 of this article series, we looked at how the Falcon transactional storage engine was architected and how it compared to some of the other MySQL storage engines. In this article, Iâ€™ll focus on how Falcon performs transaction management, including some special points on where Falcon differs from some of MySQLâ€™s other transactional engines.

Understanding the Falcon Transaction Storage Engine â€“ Part 1

By Robin Schumacher | 2007.02.02

If youâ€™ve been using MySQL for any time at all, you know that a unique advantage afforded MySQL users is the ability to use multiple storage engines to manage the underlying data stored on the server. This efficient and modular architecture provides large performance and manageability benefits for those wishing to specifically target a particular application need â€“ such as data warehousing, transaction processing, high availability situations, etc. â€“ while enjoying the advantage of utilizing a set of interfaces and services that are independent of any one storage engine. The MySQL server supplies engines that are internally developed, provided by third-party software vendors, and also allows a user to build their own storage engine if they desire.

Interview with Andrey "Poohie" Hristov, Developer at MySQL AB

By Lenz Grimmer | 2007.01.18

Confused? Frustrated?

One of the new features in upcoming MySQL 5.1 Server will be the Event Scheduler. This feature was implemented by Andrey Hristov within the scope of a master's thesis for the University of Applied Sciences in Stuttgart. We incorporated his contribution into our code base - you can read more about this feature in this article from Peter Gulutzan. Andrey is now employed by MySQL AB as a Software Developer. Lenz Grimmer asked him if he would be interested in sharing his story and provide some more background about himself.

Automating MySQL Best Practices Management

By Robin Schumacher | 2006.12.11

One thing I learned fairly early in my DBA career was that if I was ever going to excel in managing the databases under my care, I needed to do one thing: smartly automate as much of my job as I could. This became particularly important in my last full-time DBA spot where one other guy and I had responsibility for about 120 Oracle, SQL Server, and DB2 databases. A lot of these databases ran critical applications with lots of users, so we were rarely bored at work. To keep our heads above water, we needed to do something to ensure we always had our finger on the pulse of all our servers, plus we needed to make sure that any performance issue was recognized as early as possible.

Interview with Paddy Sreenivasan from Zmanda

By Colin Charles | 2006.11.17

Zmanda specializes in backup and recovery solutions. First they gave the world Amanda, and now they've given MySQL users a new reason to rejoice with the launch of Zmanda Recovery Manager (ZRM) for MySQL. I recently got to speak to Paddy Sreenivasan, the Vice President of Engineering and co-founder of Zmanda Inc., amidst his busy schedule, about what they do and why MySQL users should be jumping towards ZRM for MySQL for all their backup & recovery needs.

Beginning MYSQL 5 with Visual Studio.NET 2005

By Anil Mahadev | 2006.11.16

This tutorial gives you a practical introduction to MySQL 5 Application Development using the MYSQL Connector for .NET with Microsoft Visual Studio.NET 2005 and how to build a simple Database Application using VC# and MySQL 5.

MySQL Scale-Out With Application Partitioning

By Oli Sennhauser | 2006.11.08

Introduction: Eventually every database system hit its limits. Especially on the Internet, where you have millions of users which theoretically access your database simultaneously, eventually your IO system will be a bottleneck. Conventional solutions: In general, as a first step, MySQL Replication is used to scale-out in such a situation. MySQL Replication scales very well when you have a high read/write (r/w) ratio. The higher the better. But also such a MySQL Replication system hits its limits when you have a huge amount of (write) access. Because database systems have random disk access, it's not the throughput of your IO system that's relevant but the IO per second (random seek). You can scale this in a very limited way by adding more disks to your IO system, but here too you eventually hit a limit (price).

Interview with Brian Moon, from the Phorum project

By Jay Pipes | 2006.09.11

If you've spent any amount of time on the forums, you probably will have recognized Brian among the forum users who most frequently answer posts -- particularly SQL and performance-related questions. Some time ago, I had a chance to ask Brian a little bit about his experience writing Phorum, about his thoughts on open source and MySQL in particular, and on his vision for Phorum in the future...

Interview with Rohit Nadhani, Co-founder of Webyog

By Jay Pipes | 2006.09.08

Webyog is the company behind the popular SQLyog GUI tool for managing and developing MySQL databases. They have a great history of partnership with MySQL and, recently, decided to open source part of their product line. I had a chance to interview Rohit Nadhani, co-founder of Webyog, about their decision to move to open source, the changes they've made to the code as a result of that move, and various other things.

Tuning MySQL for eZ publish

By eZ Systems | 2006.08.24

This article explains how to tune a MySQL database for use with eZ publish. MySQL is a database engine used by eZ publish to store content. While other databases are supported by eZ publish (such as PostgreSQL and Oracle), for performance reasons we recommend MySQL. This article has suggestions for optimizing for either read or write operations, and also discusses some other optimization techniques such as DNS and filesystem configuration.

MySQL 5.1 New Features: MySQL Partitions

By Jon Stephens and Peter Gulutzan | 2006.08.08

Partitioning is a way of pre-organizing table storage. You can say "some of the table's rows will go here, some will go there, still others will go to to still other places". Often, depending on the storage engine, the effect is to spread the table's rows over different files or even different disks.

Interview with Stephan Uhrenbacher, Managing Director of Qype GmbH

By Lenz Grimmer | 2006.08.04

Stephan Uhrenbacher is the Managing Director of Qype GmbH, the company behind the Qype.com web site, which allows users to recommend places in Germany and has developed into one of Germanyâ€™s most interesting web 2.0 applications.

Partitioning with Dates in MySQL 5.1

By Robin Schumacher | 2006.07.20

Many of you have responded very positively on the first and second partitioning articles that I wrote some time back. Itâ€™s clear that a lot of you are excited about the upcoming MySQL 5.1 release, and in particular, the new data partitioning feature thatâ€™s being offered. Thatâ€™s no surprise because, as we covered in the previous two articles, partitioning has some excellent performance and management benefits for anyone whoâ€™s building data warehouses or other large databases with MySQL.

Enforcing Foreign Keys Programmatically in MySQL

By Philippe Campos | 2006.07.10

In this article we explore how to program foreign keys into a MySQL Server. 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 NDB (Cluster), does not support this functionality natively. We should note that InnoDB and the upcoming code-named â€œFalconâ€ storage engine, both support foreign keys.

MySQL Cluster 5.0 ALTER TABLE In-Depth

By Martin Skold | 2006.07.06

In this Developers Zone article we take an in-depth look at how the ALTER TABLE command is implemented in MySQL Cluster 5.0.

MaxDB Synchronization at United Drugs' Annual Convention

By Mark Thomas and C.J. Collier | 2006.06.03

This document is a user report on the MaxDB Synchronization Manager. The article describes the use of the MaxDB Synchronization Manager during United Drugs Annual Convention from a technical standpoint. It gives an example of the magnitude of possible application of the MaxDB Synchronization Manager. Written for developers, the user report contains warnings on common pitfalls and gives hints how to circumvent the problems.

Interview with Paul McCullagh, developer of the PrimeBase XT Storage Engine

By Lenz Grimmer | 2006.06.02

The PrimeBase XT Storage Engine (PBXT) is a new transactional database engine for MySQL. It has been designed for modern, web-based, high concurrency environments. In May 2006, I had the opportunity to meet with Paul McCullagh, the head developer of PBXT for lunch at the Hamburg offices of Snap Innovation GmbH, the company behind PrimeBase. It was co-founded by Paul in February 1996 and is focussed on the development and sales of client/server database technology, specializing in inter- and intranet systems.

My Dinner with Monty

By Zack Urlocker | 2006.05.31

This interview took place with Monty Widenius, co-founder of MySQL, at his house outside of Helsinki, Finland. In Montyâ€™s usual understated Finnish style, we talked about MySQL 5.0, coding styles and cooking.

MaxDB series: transactions

By Ulf Wendel | 2006.05.31

In this issue of the MaxDB series we will introduce you to some selected aspects of transactions. The topic of transactions seems pretty boring. But in fact it depends very much on the point of view you take. We have tried to put a light on the subject that shows some rather advanced aspects of transactions which are a little less often discussed in database classes.

MySQL Clustering: Chapter 5 Performance

By Harrison Fisk, Alex Davies | 2006.05.24

One of the main reasons to use a MySQL Cluster is the ability to get better performance and scalability compared to using a database that is confined to a single host. This chapter discusses many of the concepts related to performance and scalability, as well as how to ensure that you get the maximum performance from your MySQL Cluster setup.

MySQL 5.0 - Ready for Prime Time Business Intelligence with Pentaho

Business intelligence is the key to fact-based decision making in the intelligent enterprise. Techniques and tools are designed to unlock knowledge from data.

More on MySQL 5.1 Partitioning

By Robin Schumacher | 2006.05.23

In my first article on partitioning, I demonstrated that you can get some pretty impressive performance benefits when you properly design partitioned tables to match the SQL queries that are routinely shot across the bow of your database server. Iâ€™d now like to continue in that vein and show you how easy and efficient partitioning is in terms of managing how partitions are created and removed within tables.

A Look at the MySQL CSV Storage Engine

By Robin Schumacher | 2006.05.05

In MySQL 5.1, a new engine has been provided that assists with the management and use of delimited data found in common flat files. Robin Schumacher takes a quick look at this new engine, along with the features and benefits it provides users of MySQL.

Advanced MySQL Replication Techniques

By Giuseppe Maxia | 2006.04.24

You may know about the MySQL Cluster, which is a complex architecture to achieve high availability and performance. One of the advantages of MySQL Cluster is that each node is a peer to the others, whereas in a normal replicating system you have a master and many slaves, and applications must be careful to write only to the master. In this article, Giuseppe Maxia, CTO of Stardata IT, provides unique insight into circular replication (multi-master replication) with failover.

MySQL 5.1 New Features: MySQL Events

By Peter Gulutzan | 2006.04.13

Andrey Hristov added "Events" for MySQL version 5.1 when he was working on his master's thesis for the University of Applied Sciences in Stuttgart. We liked the feature a lot, and now Andrey works for MySQL. An inspiring story, eh? And now MySQL 5.1 is getting stabler, it's "beta" instead of "alpha". So we can try some real work with this Events feature.

The MaxDB series on PlanetMySQL

By Ulf Wendel | 2006.03.31

MySQL has started a MaxDB series on its corporate blog aggregator. Since the beginning of Februar the MaxDB team tries to write one MaxDB article per week for you. The articles get published on our english language web site http://www.planetmysql.org. All articles together will make a complete MaxDB class. The class describes the use of MaxDB as a stand-alone enterprise database. We do not discuss the use of MaxDB with SAP applications.

Interview with Ian Wilkes, Director of Operations at Linden Lab

By Lenz Grimmer | 2006.03.03

Linden Lab is the company behind the virtual world simulation Second Life (SL) in wich users (the residents) can freely roam around and actively participate in the creation and development of the virtual world and their own appearance (their avatars). To facilitate this, Second Life provides powerful in-world 3D construction tools as well as a special scripting language, LSL. Second Life also provides its own economy and currency (Linden Dollars), that residents can use to purchase goods and objects that other residents created. Interestingly, it’s even possible to exchange Linden Dollars into US dollars and vice versa.

Call Pantek today for Open Source Technical Support at 1-877-546-8934 - 24/7/365

Improving Database Performance with Partitioning

By Robin Schumacher | 2006.02.22

A few years ago, I wrote an article entitled "The Foundation of Excellent Performance" (still available at http://www.tdan.com/i016fe03.htm) where I argued against the notion that SQL code was the number one contributor to performance in a database-driven system. Instead, I stated in the article that I firmly believed how good physical database design was far and away the leading component of superior database performance. In addition, I showed that Oracle's own research illustrated how poor design was the main culprit behind database downtime (planned or unplanned). In the years since then, I've not changed my stance and still think that any DBA who wants a high-performance database has got to invest in intelligent and savvy physical design to produce the kind of response times that make end users smile instead of scream.

Interview with David Heinemeier Hansson from Ruby on Rails

By Lenz Grimmer | 2006.02.11

The Ruby scripting language was first published in 1995, and has a very dedicated and active community of users around it. And with the release of the Ruby on Rails web framework in July 2004, Ruby has emerged as a serious alternative to PHP and Java for developing database-driven web sites. MySQL is one of the most popular choices as a database backend for Ruby on Rails applications and we support the Rails community with a dedicated forum about MySQL and Rails.

Accessing Distributed Data with the Federated Storage Engine

By Robin Schumacher | 2006.02.09

It's no secret that corporations are swimming in more data than ever before. IDC has recently pegged data growth at 48% a year, which means that corporate data nearly doubles every two years at many companies. In addition, purchased applications and custom built systems continue to bring in new databases that require attention, and business intelligence remains a red-hot area for modern enterprises with many companies building analytic data warehouses or data marts that continually feed information to key decision makers.

Interview with Kai 'Oswald' Seidler from the XAMPP project

By Lenz Grimmer | 2006.01.17

I recently had the pleasure to get in touch with Kai "Oswald" Seidler from the Apache Friends team. He is one of the people behind 'XAMPP', an easy to install software distribution containing MySQL, PHP and Perl which is available for Linux and Windows. The project also added support for Mac OS X and Solaris recently, but these ports is still in an early beta stage.

Confused? Frustrated?

New Event Feature in MySQL 5.1.6

By Trudy Pelzer | 2006.01.13

In this article, I'll give a preliminary description of a new MySQL feature for scheduling and executing tasks. In version 5.1.6, MySQL has added support for events. That is, you can now say: "I want the MySQL server to execute this SQL statement every day at 9:30am, until the end of the year" -- or anything similar that involves any number of SQL statements, and a schedule.

MySQL 5.1's new XML Functions

By Alexander Barkov and Peter Gulutzan | 2006.01.12

MySQL version 5.1.5 has functions for searching and changing XML documents. This article has examples.

MySQL 5.0 Certification Exams

By Carsten Pedersen | 2006.01.03

MySQLs Certification Manager outlines the upcoming changes in the MySQL certification program, and why those changes were made.

Essential PHP Security: Chapter 2, Forms and URLs

By Chris Shiflett | 2005.12.21

Security is a hot topic, particularly for web applications. Essential PHP Security is a book by PHP security expert Chris Shiflett (O'Reilly Media, Inc, October 2005, ISBN 059600656X). Since we already covered SQL injection recently, we chose to publish chapter 2 from this book, "Forms and URLs".

Guaranteeing Data Integrity with MySQL 5.0

By Robin Schumacher | 2005.12.01

The IT crown jewels of any organization lies in their database infrastructure. Obviously, well-designed front end applications play a leading role when it comes to ensuring that critical information is captured or reviewed, but the buck stops at the database when it comes to data retention and protection.

MaxDB for SAP Hosting - Technical Whitepaper

By Ulf Wendel | 2005.11.24

MaxDB has a unique, top position in the list of open source database. The SAP-certified database management system combines ease of use, high availability and exceptional performance with low costs. This whitepaper presents a technical analysis of how MaxDB is designed to meet the requirements of hosting business critical SAP applications.

Stored Procedures in MySQL 5.0

By Carsten Pedersen | 2005.11.17

MySQL's stored procedures do not provide for direct support of passing and using object names in the procedure. But it's pretty easy to work around this limitation.

Using BIRT To Report On Bugzilla in MySQL

BIRT is rich reporting technology powered by the Eclipse BIRT project. Actuate BIRT inlcudes support and subscription sevcies, is MySQL Network Certified and is a recommended reporting and analytics component of a MySQL Data Warehouse Scale-Out Solution Set.

Here is some how-to information on using BIRT with MySQL.

Flash demo on building a complex report with BIRT on MySQL »
Example of building a report against Bugzilla on MySQL »

Installing MySQL MaxDB Synchronization Manager

By Carl "C.J." Collier | 2005.11.09

This series of articles describes how to install and configure the MySQL MaxDB Synchronization Manager. In a step by step approach the author guides you through the process of obtaining, installing, testing, configuring and running MaxDB and the Synchronization Manager.

MySQL 5.0's Pluggable Storage Engine Architecture, Part 2: A Quick Test Drive

By Robin Schumacher | 2005.10.26

Let's take a quick test drive to show how simple it is to use MySQL's pluggable storage engine architecture and see the impact different storage engines can have on performance.

MySQL 5.0's Pluggable Storage Engine Architecture, Part 1: An Overview

By Robin Schumacher | 2005.10.19

The reasons for the huge popularity and increasing adoption of MySQL as a serious database platform can many times be boiled down to two, but surprisingly contrasting, considerations:
1. How much MySQL is like other database platforms.
2. How much MySQL is different than other database platforms.

php|architect's Guide to PHP Security

php|architect's Guide to PHP Security, a book by PHP developer Ilia Alshanetsky (Marco Tabini & Associates, September 2005, ISBN 0973862106), discusses the important topic of how to make PHP applications secure. Chapter 3, for which the MySQL Developer Zone received permission to reprint, covers "SQL Injection".

Dispelling the Myths

By By Robin Schumacher and Arjen Lentz | 2005.10.05

As anyone will tell you, competition is a good thing because it gives you a choice. Acquiring current and accurate information about your options enables you to spend your time and money in the most efficient way and is certainly time well spent.

An Atlas for Ireland's Artists (using MySQL GIS)

By Daniel McAlister, CTO, Integrated Spatial Solutions Inc. | 2005.09.18

Portraits of Ireland Ltd is a web based Irish company aiming to help connect the diaspora to people and places in Ireland through Art. This startup company required an online interactive atlas to assist both artists and those commissioning their work to find not only the island's more well known locations but also its remote and smaller place locations. To this end an Artist Atlas was created. Some of the primary requirements were a cartographically rich, atheistically pleasing vector based system, fully searchable, and with as extensive a database as possible.

The MySQL 5.0 Archive Storage Engine

By Robin Schumacher | 2005.09.06

More than at any other time, database professionals are being aggressively challenged by mushrooming volumes of data in corporate business systems. While some industry analysts project data growth at an average rate of 42% a year, that figure is conservative in some installations where growth rates are several hundred percent, with no end in sight.

Managing Hierarchical Data in MySQL

By Mike Hillyer | 2005.08.17

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.

Interview with Marc Delisle

By Arjen Lentz | 2005.08.15

Marc Delisle is one of the lead developers of the very popular phpMyAdmin project, a web/browser-based tool for MySQL Administration. I talked with him about MySQL, phpMyAdmin (of course), and being a developer in general.

Creating a DBA Dashboard for MySQL

By Scott Sanders and Teodor Danciu | 2005.08.05

The open source DBA Dashboard for MySQL automates the statistical data collection, reporting and monitoring of the internals of your MySQL Servers. Information such as server health, schema information, database size, and usage statistics are aggregated on a periodic basis to give you insight into your MySQL databases. This JasperReports application answers questions such as: How many queries per day? Which user has the most resource usage? What tables are growing the fastest?

Pro MySQL, Chapter 6: Benchmarking and Profiling

By Michael Kruckenberg and Jay Pipes | 2005.08.03

Pro MySQL, a brand new book by Mike Kruckenberg and Jay Pipes (Apress, July 2005, ISBN 1-59059-505-X), provides intermediate and advanced MySQL users with a comprehensive guide to the array of features available in MySQL upto and including MySQL 5.0
Since the MySQL Developer Zone has had quite a few articles related to the new MySQL 5.0 features, we chose to publish chapter 6 covering another hot topic, "Benchmarking and Profiling".
This is one of the first books to cover MySQL 5 in depth. It foregoes the usual reiteration of basics that you'll find in other books, instead concentrating on MySQL's advanced applications in enterprise environments. Further, this book doubles as a quick reference guide for configuration directives, commands, and features.
The book's own companion web site is at http://www.promysql.com/

A Practical Look at the MySQL Query Cache

By Robin Schumacher | 2005.07.19

All robust relational database management systems utilize memory caching to help improve the overall responsiveness of their underlying engine. While certainly not free, accessing application data, query plans, or database metadata in RAM is normally much quicker than repetitively retrieving that same information from disk or building it from scratch.

Book Review: High Performance MySQL

By Russell J.T. Dyer | 2005.07.18

O'Reilly has published a fabulous book on MySQL. The authors, Jeremy Zawodny and Derek Balling, have drawn on their extensive experience as MySQL administrators at Yahoo to write High Performance MySQL for fellow administrators. Instead of getting bogged down with the basics of MySQL, the book focuses on understanding performance factors in MySQL and how the different ways of creating databases or of modifying existing databases will affect speed and efficiency. While this is an advanced topic, the text and concepts are easy to follow. It's written on the assumption, however, that the reader already knows MySQL but is unaware of how databases can perform better and how tables can be constructed, joined, and indexed optimally.

Book Review: MySQL Tutorial

By Russell J.T. Dyer | 2005.07.18

MySQL Press is a relatively new imprint for the book publisher Pearson Education. They are billed as "the exclusive publisher of technology books and materials that have been authorized by MySQL AB, the company that develops and markets the MySQL database." As part of their initial set of MySQL books, they have published an excellent book on learning the basics of MySQL called MySQL Tutorial. It's written by Luke Welling and Laura Thomson, the authors of PHP and MySQL Web Development (Sams 2003). Their tutorial has a simple style and a relaxed pace that's ideal for MySQL beginners.

Book Review: Beginning MySQL Database Design and Optimization

By Russell J.T. Dyer | 2005.07.18

Apress is fairly new to the open source book publishing business. However, in a short amount of time they've put together a nice collection of books. They've done this by buying many titles from Wrox Press, but more importantly by seeking out some good writers with expertise in their field to write new books. So, if you're not familiar with Apress books, you might want to take a look at them. As part of their expansion into the open source arena, they've been publishing some new books on MySQL. One book that I found to be very interesting is Beginning MySQL Database Design and Optimization by Jon Stephens and Chad Russell.

MySQL Database Design and Tuning, Chapter 18: Case Study - Solving a broad Collection of Performance Problems

By Robert Schneider | 2005.07.15

The MySQL Developer Zone received permission to publish excerpts from the book "MySQL Database Design and Tuning" by Robert Schneider, published in 2005 by Sams (ISBN 0672327651). Chapter 18 is a case study that reviews a broad-based collection of performance problems, along with their solutions.

Embedding the MySQL Server Library

By embedding the MySQL server library in MySQL applications, MySQL doesn't have to be installed on the end user's computer. It's particularly useful with small devices and public kiosks. The embedded MySQL server also has the advantage of running faster with MySQL applications since there isn't a network connection or any passing of data between the application and MySQL. It's able to avoid these factors because the server library is embedded in the compiled program. Best of all, the embedded server library is part of the MySQL distribution.

MySQL: The Complete Reference, chapter 14: Security, Access Control, and Privileges

The MySQL Developer Zone received permission to publish excerpts from the book "MySQL: The Complete Reference" by Vikram Vaswani, published in 2004 by McGraw-Hill/Osborne (ISBN 0-07-222477-0). Our first selection is chapter 14 about Security, Access Control, and Privileges. The book's own companion web site is at http://www.mysql-tcr.com/