Opinions, Expertise, Passion.

Being a CVS Version Control Person, I’ve had to learn Subversion as part of Open Source Contribution. Both MySQL and JMeter use SVN.

Steps for integration of SVN into Eclipse IDE. Refer to Subclipse for more information.

Installation of JMeter via SVN

• Start Eclipse
• Help | Software Updates | Find and Install
• Search For New Features To Install [Next]
• [New Remote Site]
• URL: http://subclipse.tigris.org/update_1.0.x
• [Finish]

• Tick Subclipse [Next]
• I accept Terms and Condtions [Next]
• Finish

Operation

• Restart Eclipse
• Window | Open Perspective | Other | SVN Repository Exploring
• Right Click New | Repository Location
• URL http://svn.apache.org/repos/asf/jakarta/jmeter/trunk/ [Finish]
• Right Click on Node, Checkout
• Check out as project in Workspace [Finish]

I don’t think I’ve ever installed MySQL via .rpm I have always installed via .tar.gz primarilarly because I’m an /opt system administrator from my old UNIX days. so my first experience installing on my CentOS 4.2 (aka RHEL 4).


$ rpm -Uvh MySQL-server-standard-5.0.19-0.rhel4.i386.rpm
warning: MySQL-server-standard-5.0.19-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
perl(DBI) is needed by MySQL-server-standard-5.0.19-0.rhel4.i386
Suggested resolutions:
perl-DBI-1.40-8.i386.rpm
$ rpm --import /usr/share/doc/centos-release-4/RPM-GPG-KEY
$ yum install perl-DBI-1.40-8
$ rpm -Uvh MySQL-server-standard-5.0.19-0.rhel4.i386.rpm
warning: MySQL-server-standard-5.0.19-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-server-standard ########################################### [100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h lamda.arabx password ‘new-password’
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
Starting MySQL……………………………..[FAILED]

$ system-config-securitylevel
# shows that I have SELinux enabled.
$ /usr/sbin/selinuxenabled && echo "yes"
$ restorecon -R -v /var/lib

rpm -ivh MySQL-client-standard-5.0.19-0.rhel4.i386.rpm

As mentioned in my earlier post Emulating Oracle Output Functionality, I’ll be speaking at the MySQL Users Conference on the topic of MySQL for Oracle Developers. Here is the second in a series of points regarding current MySQL Stored Procedures and Functions functionality.

• 3. Named Parameters
• 4. Procedure Overloading

3. Named Parameters

Parameters passed to Procedures under normal operations can be considered positional parameters. For each parameter, a calling statement is required to pass the same number of parameters and in the same order. Named parameters allows the user to pass parmeters in the order of their choosing, and also not pass all parameters.

For Example, consider the following Oracle Stored Procedure definition.

  EXAMPLE_PROCEDURE(num IN NUMBER DEFAULT 0,
                    dte IN DATE DEFAULT SYSDATE,
                    str IN VARCHAR2 DEFAULT NULL,
                    str2 IN VARCHAR2 DEFAULT NULL);

This can be called in several ways, here are few examples.

  EXAMPLE_PROCEDURE();
  EXAMPLE_PROCEDURE(9,  '01-MAR-2006', 'Hello World', NULL);
  EXAMPLE_PROCEDURE(dte => '01-MAR-2006');
  EXAMPLE_PROCEDURE(dte => '01-MAR-2006', str2 => 'Hello World');

The key to being able to use named parameters, is the addition of the DEFAULT syntax in the variable definition.

Giuseppe Maxia has many MySQL stored procedures, and I applaud his initiate with The MySQL General Purpose Stored Routines Library. Included here is an implementation of handling named parameters. I can’t say I’ve used it personally, however I did browse the code (another Open Source benefit).

4. Procedure Overloading

Oracle provides the capability like Java, which allows for multiple procedures to have the same name, but have a different set of parameter arguments.

Oracle provides the ability to overload a Procedure call with the same number of arguments and varying datatypes, for example, going with the earlier DBMS_OUTPUT.PUT_LINE analogy, Oracle has the following specification.

  DBMS_OUTPUT.PUT_LINE (item IN NUMBER);
  DBMS_OUTPUT.PUT_LINE (item IN VARCHAR2);
  DBMS_OUTPUT.PUT_LINE (item IN DATE);

Likewise, it possible to have procedures with different numbers and types of arguments.

  EXAMPLE_PROCEDURE(num IN NUMBER);
  EXAMPLE_PROCEDURE(num IN NUMBER, str IN VARCHAR2);
  EXAMPLE_PROCEDURE(num IN NUMBER, dte IN DATE);
  EXAMPLE_PROCEDURE(num IN NUMBER, dte IN DATE, str IN VARCHAR2, str2 IN VARCHAR2);
etc.

Notes

As MySQL growth continues and developers take up placing greater business logic in the database with Stored Procedures, there may be merit to consider these points. On the flip side, 10 years ago, business logic was placed in the database for many reasons including the extent of capable client programming languages, a Client/Server architecture and of course tight coupling with Oracles GUI products. One could argue a justification as to how much business logic is stored in the database, and how much is managed within an application. Given the advent of the Web, and multiple applications and clients assessing coporate data, it’s logical to place essential logic as close to the source of the data as possible, and the introduction of Stored Procedures in MySQL 5 released last year provided the capacity to consider this.

At this time, I would err on the conservative side with the use of Stored Procedures. The type of application, requirements and signficantly the age of the product (being a new product or existing legacy product) all affect the outcome. As mentioned, Giuseppe feels strongly regarding named parameters, and has provided a workaround. The benefit with Open Source is this FREEDOM clearly exists when the community contributes. This can only benefit MySQL in areas such as Stored Procedures.

Myself, only a few days ago over the weekend, I took an Java Open Source product, that lacked the capacity to support the calling of Stored Procedures via JDBC, developed it myself and then submitted my work back to the Apache community. I was even more happy when it was accepted unaltered, committed and in the nightly build for the next day. Read More. I’m now working on the next contribution to the same project, providing JDBC Transaction Support. While this may seem a sidetrack, I’m actually specifically using this product in testing and usability of MySQL, so ultimately everybody wins.

I stand corrected on my earlier post Emulating Oracle Output Functionality (which I’ve updated) when I made a reference to MySQL catching up. That was not what I was implying, that MySQL had to catchup to Oracle. I was indeed making reference that MySQL is a 10 year old product, Oracle almost 30, and with a far greater historical R&D budget, MySQL as an organisation has for lack of a better term, entered the race at a later time. One could also argue it’s not a race, I’m just using this analogy.

For those that might have thought this in reading my earlier article, I’d encourage you to re-read my updated introduction for my clarification and correction.

The comment was made to me, “Oracle is very good at being Oracle” which is totally correct, and the “MySQL’s market now overlaps that of Oracle, and so there is competition.” Indeed Oracle, as a leader in the RDBMS industry has directed development in functionality and features.

We (being the MySQL product and Open Source) are indeed in competition (with other RDBMS products, both commercial and open source) , and will always be, however those that know me would also know that differing products serve to provide differing features and functionality in enterprise solutions. Some have strengths over others, and also weaknesses.

I am a strong advocate of larger enterprises currently using Oracle to embrace both Oracle and MySQL within an organisation for the benefit of serving differing purposes. I do not believe they are mutually exclusive. I believe there needs to be a looser coupling in the emerging marketplace. Of course, if a new company was starting tomorrow, and wanted to use Oracle, I would question the requirements as MySQL will serve the needs for most practical enterprise solutions. Had that question been asked 5 years ago, it would depend signficantly on the requirements, 10 years ago it would not have been a consideration.

Given a new Linux Installation, the following is my recommendation for installation of MySQL for a experienced software developer giving flexibility in a development environment.

1. Under normal circumstances, most distros include MySQL either in a default server installation or on the distribution CD’s. You should first ensure MySQL is not installed.

2. All products can be downloaded from the MySQL Downloads page.

3. Download MySQL 5.1 Beta - Linux (non RPM, Intel C/C++ compiled, glibc-2.3), this product is close to production release and stable. This also includes both Server and Client as well as provides flexibility in installation location and multiple installations.

3. Download MySQL Administrator 1.1.6.

4. Download MySQL Query Browser 1.1.18.

5. Download MySQL 5.1 Manual

Installation

su -
groupadd mysql
useradd -g mysql mysql
cd /opt
tar xvfz mysql-5.1.7-beta-linux-i686-icc-glibc23.tar.gz
ln -s mysql-5.1.7-beta-linux-i686-icc-glibc23 mysql
tar xvfz mysql-administrator-1.1.6-linux-i386.tar.gz
tar xvfz mysql-query-browser-1.1.18-linux-i386.tar.gz
tar xvfz refman-5.1-en.html-chapter.tar.gz
mv html-chapter mysql-manual-5.1
cd mysql
echo "[mysqld]
user=mysql
basedir=/opt/mysql
datadir=/opt/mysql/data
port=3306″ > my.cnf
scripts/mysql_install_db
cd /opt
chown -R root /opt/mysql
chown -R mysql /opt/mysql/data
chgrp -R mysql /opt/mysql
chown -R root /opt/mysql/bin
cd /opt/mysql
./bin/mysqld_safe &
./bin/mysqladmin -u root password ‘new-password’
./bin/mysqladmin -u root -h `hostname` -pnew-password password ‘new-password’
./bin/mysqladmin -pnew-password shutdown
echo “PATH=/opt/mysql/bin:/opt/mysql-administrator/bin:/opt/mysql-query-browser/bin:$PATH; export PATH” > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh   # Just for this session
cp support-files/mysql.server /etc/rc.d/init.d
chkconfig mysql.server on

Operation

The MySQL server will start automatically on System boot

To run the MySQL Client

mysql -uroot -pnew-password mysql

To run the MySQL Administrator

mysql-administrator

To run the MySQL Query Browser

mysql-query-browser

Now that I remember all this, perhaps RPM’s are the way to go, I just don’t like the structure in deployed files.

I was asked a simple question today by a collegue who is an experiened Java Developer and Oracle user and had just installed SUSE on his personal laptop, to align closer with his work environment. What MySQL should I install, the MySQL web site has this and this and this?

Simple question, but the answer isn’t as simple, especially when MySQL now has a number of different products encompassing client functionality in addition to the MySQL server.

Oracle got it right with the latest edition Oracle 10g Express Edition (XE). A simple one rpm install that includes the Oracle Server, Oracle client, a Web Based Administrator, Query Tool and Application Development tool. I guess as we are all experienced, the simple question isn’t something we have a some notes on handy, hence the purpose of my entry A Basic MySQL Developer Installation. NOTE: This is for a developer environment, installation would be simplier using RPM’s for a compatible Distro.

On that note, It would be good for MySQL to provide a more complete installation of it’s suite of products rather then letting 3^rd parties package it all together. Problem is there are so many possible combinations, what do you do.

I’m a data modeller. I specialise in this, and for a number of years on large projects I’ve been able to focus on this single task within the System Development Life Cycle of software development for several months at a time. Unfortunately what depresses me the most, is I can’t get a full time position in what I’m an expert in. It’s not a specialised skill that an organisation can use on a full-time basis, unless it’s a large organisation, and quite frankly, Brisbane isn’t a market that can support the diversity of large organisations. (caveat, large organisations that are proactive in software development, not just large organisations that have significant IT requirements, but do not work proactively). This is why I can also do Software Development, Database Administration, and even System Administration. Again, I’m not good enough to fill one of these positions in a larger organisation as an expert, but I can generally hold my own, usually even with surpising results. (Side note, even this week, I was providing a possible solution and tool for system adminstration across a large organisation, and it was 5 mins work. Something the paid full-time system administrators were not providing????)

I only started looking at Domas Mituzas wordpress: friendly look at query log. I didn’t have to read far to see where it was going, and well I quite quickly turned off, sorry Domas, I’m sure your concluding points were valid. This is my point, and it has been echoed in our local MySQL users group as well, the lack of appropiate database design in open source projects. There are several contributors, but one I put down to the “Hobbist and the Professional Syndrome”. A topic for further discussion, but in summary here are the bullet points from a slide in a presentation I prepared.

Hobbist

• Downloadable software and examples
• Online tutorials
• Books like Learn in 24 hours/For Dummies

Professional

• Formal Qualifications
• Grounding in sound programming practices
• Understanding of SDLC principles
• Worked in team environment

Middle Ground Developer

• Time to skill verses output productivity
• Depends on environment and requirements

And on a final note, I guess why doing some raving. I find it criminal that organisations encourage at times a level of incompetence by promoting people that develop bad code into positions where they can continue to ensure that bad code stays, and further business decisions only engrain an organisation down the continued wrong path. There is already enough poor software developers out there that give the industry a bad name, but the good ones are few and far between.

What do we do, how can we solve this problem? I don’t think it can be solved now in the Open Source community. Adopting an Agile Development methodology such as Extreme Programming (XP) for example, it a very good start in organisations, something I’ve been working with now, or working with the principles for 6 years.

PS: Modelling is actually spelt both Modeling and Modelling (2 l’s) across the various English derivitives. Just incase somebody wanted to make comment.

fdisk /dev/hdb
mkfs.ext3 /dev/hdb1
mkdir /u03
mount -t ext3 /dev/hdb1 /u03
ls /u03
umount /u03
vi /etc/fstab
mount /u03

As part of my using JMeter for the purpose of testing a new Transactional storage engine PBXT for MySQL, I’ve been investigating the best approach for handling transactions. Read more about earlier decisions at my earlier post Testing a new MySQL Transactional Storage Engine.

I found that the JMeter JDBC Sampler only supports SELECT and UPDATE Statements, and not calls to stored procedures. This is just one approach I’m considering taking.

Well, I guess it’s time to contribute code to an Apache Project. I’ve modified code and logged bugs before for Tomcat, but this will be my first attempt of modify code and submit.

A summary of what I did (really for my own short term memory):

• Read the guidelines at http://jakarta.apache.org/site/guidelines.html
• Join the DevelopersList at http://jakarta.apache.org/site/mail2.html#JMeter (Subscribe)
• Review Source Code Repository Guidelines at http://www.apache.org/dev/version-control.html
• Checkout code SVN Trunk Online View
• Make the Changes I wanted
• Submit SVN patch changes to the Developer List

Now I just have to wait to see if it’s accepted. Regardless, it works for me. And that’s Open Source. FREEDOM

svn checkout http://svn.apache.org/repos/asf/jakarta/jmeter/trunk/ jmeter


$ svn diff JDBCSampler.java > JDBCSampler.java.patch
$ cat  JDBCSampler.java.patch
Index: JDBCSampler.java
===================================================================
--- JDBCSampler.java    (revision 388876)
+++ JDBCSampler.java    (working copy)
@@ -23,6 +23,7 @@
 import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Statement;
+import java.sql.CallableStatement;

 import org.apache.avalon.excalibur.datasource.DataSourceComponent;
 import org.apache.jmeter.samplers.Entry;
@@ -45,6 +46,8 @@

        public static final String QUERY = "query";
        public static final String SELECT = "Select Statement";
+       public static final String UPDATE = "Update Statement";
+       public static final String STATEMENT = "Call Statement";

        public String query = "";

@@ -69,6 +72,7 @@
                log.debug("DataSourceComponent: " + pool);
                Connection conn = null;
                Statement stmt = null;
+               CallableStatement cs = null;

                try {

@@ -88,14 +92,19 @@
                                        Data data = getDataFromResultSet(rs);
                                        res.setResponseData(data.toString().getBytes());
                                } finally {
-                                       if (rs != null) {
-                                               try {
-                                                       rs.close();
-                                               } catch (SQLException exc) {
-                                                       log.warn("Error closing ResultSet", exc);
-                                               }
-                                       }
+                                       close(rs);
                                }
+                       // execute stored procedure
+                       } else if (STATEMENT.equals(getQueryType())) {
+                               try {
+                                       cs = conn.prepareCall(getQuery());
+                                       cs.execute();
+                                       String results = "Executed";
+                                       res.setResponseData(results.getBytes());
+                               } finally {
+                                       close(cs);
+                               }
+                       // Insert/Update/Delete statement
                        } else {
                                stmt.execute(getQuery());
                                int updateCount = stmt.getUpdateCount();
@@ -112,20 +121,8 @@
                        res.setResponseMessage(ex.toString());
                        res.setSuccessful(false);
                } finally {
-                       if (stmt != null) {
-                               try {
-                                       stmt.close();
-                               } catch (SQLException ex) {
-                                       log.warn("Error closing statement", ex);
-                               }
-                       }
-                       if (conn != null) {
-                               try {
-                                       conn.close();
-                               } catch (SQLException ex) {
-                                       log.warn("Error closing connection", ex);
-                               }
-                       }
+                       close(stmt);
+                       close(conn);
                }

                res.sampleEnd();
@@ -164,6 +161,38 @@
                return data;
        }

+       public static void close(Connection c) {
+               try {
+                       if (c != null) c.close();
+               } catch (SQLException e) {
+                       log.warn("Error closing Connection", e);
+               }
+       }
+
+       public static void close(Statement s) {
+               try {
+                       if (s != null) s.close();
+               } catch (SQLException e) {
+                       log.warn("Error closing Statement", e);
+               }
+       }
+
+       public static void close(CallableStatement cs) {
+               try {
+                       if (cs != null) cs.close();
+               } catch (SQLException e) {
+                       log.warn("Error closing CallableStatement", e);
+               }
+       }
+
+       public static void close(ResultSet rs) {
+               try {
+                       if (rs != null) rs.close();
+               } catch (SQLException e) {
+                       log.warn("Error closing ResultSet", e);
+               }
+       }
+
        public String getQuery() {
                return query;
        }

$ svn diff JDBCSamplerBeanInfo.java > JDBCSamplerBeanInfo.java.patch
$ cat JDBCSamplerBeanInfo.java.patch
Index: JDBCSamplerBeanInfo.java
===================================================================
--- JDBCSamplerBeanInfo.java    (revision 388876)
+++ JDBCSamplerBeanInfo.java    (working copy)
@@ -50,7 +50,7 @@
                p.setValue(NOT_UNDEFINED, Boolean.TRUE);
                p.setValue(DEFAULT, JDBCSampler.SELECT);
                p.setValue(NOT_OTHER,Boolean.TRUE);
-               p.setValue(TAGS,new String[]{JDBCSampler.SELECT,”Update Statement”});
+               p.setValue(TAGS,new String[]{JDBCSampler.SELECT,JDBCSampler.UPDATE,JDBCSampler.STATEMENT});

                p = property(”query”);
                p.setValue(NOT_UNDEFINED, Boolean.TRUE);

Update

Good to know somebody read my post, and responded positively. The quickest way for patches is to log a Bugzilla request. Seemed somebody already had, so it was easy for me to just to contribute to Bug #38682

I’ve recently started a new contract position, and the combination of more restrictive working hours, and at least 1 hour travel each way each day, has lead to me not being about to write like I have of late.

I seem to have far more draft blogs at moment, many things I want to write, and I have to temper this with a number of extra projects but I guess that’s life.

Updated 28-mar-2006
There really is no way to do a comparision by numbers in features and functionality when it comes to Oracle and MySQL in the area of Stored Procedures and Triggers. Oracle does provide a far greater and extensive features list, however having more only means it has more.

We all know this, MySQL is a growing evolving database product, having just celebrated it’s tenth anniversary, Oracle on the other hand, will next year be 30, and has had significant funding in R&D being up until recently the second largest software company world wide. However, being open source, MySQL has the advantages of being lean and mean, providing only functionality users actually want and use, and has the community and organisation that is focussed clearly on providing new and improved functionality that users need.

I’ll have a lot more to speak about on this topic at my MySQL User Conference presentation MySQL for Oracle Developers next month, however in the preceeding weeks I thought I’d open some discussion and debate on various features, particularly in the area of Stored Procedures.

Here are some initial points, I’d like to talk about today. The purpose of this discussion is to highlight the difference between these two products, and possibilly other RDBMS products. While a RDBMS offers a feature, I am not stating that MySQL should necessarily also have this feature, however as part of comparing products in these articles, I am comparing existing Oracle Functionality to MySQL, so it will generally involve the description of functionality that is not presently comparable.

1. Package Space
2. Provider Supplied Procedures

1. Package Space

MySQL provides for Stored Procedures and Functions only the definition of individual procedures and functions. There is no way to group them into logical groupings.

Oracle provides the functionality of Package Space. Groups of procedures and functions can be defined within a package. Consider it like a wrapper of a group of procedures and functions. The package definition consists of 2 parts, the header section, which defines the scope of the procedures and functions, and the body which defines the actual procedures and functions. An added advantage of this that within Oracle you can protect your package body code (making it unavailable for view). I’ll discuss this more at a later time.

I’d like to see MySQL consider a package syntax particularly as businesses start to work more extensively with stored procedures and functions in larger enterprise applications. UPDATE: As stated by Roland in the comments, you can register your support for this functionality at MySQL under Bug #11696.

Within MySQL you can emulate package name, at least in syntax. You can use a dot ‘.’ within a procedure name, unlike this restriction for tables. The down side is the call must be quoted. I’ll discuss this more next.

2. Provider Supplied Procedures

Oracle provides an extensive list of supplied procedures and functions, defined in many packages. One of the earliest you learn in Oracle development is DBMS_OUTPUT.PUT_LINE. As the name indicates, this allows your to output a line of information. This is a common means of debugging output. Andrew Gilfrin post on Debugging Stored Procedures in MySQL raises this point, and an interesting solution using a debugging table. While this functionality works, one of my goals is to provide compability functions so that any migration of code could be simplified. Here is a much simplier solution.

$ more dbms_output.put_line.mysql
DELIMITER //
DROP PROCEDURE `dbms_output.put_line`
//
CREATE PROCEDURE `dbms_output.put_line` (output  VARCHAR(255))
BEGIN
  SELECT output;
END;
//
DELIMITER ;

A MySQL Usage:

mysql> CALL `dbms_output.put_line` ('hello world');


A comparision Oracle Usage:

SQL> SET SERVER OUTPUT ON
SQL> BEGIN
SQL> DBMS_OUTPUT.PUT_LINE ('hello world');
SQL> END;


This is as close the syntax can be. With MySQL you require the CALL and the backqoutes (`). Both things that can be easily added in an automated way.

References:
DBMS_OUTPUT Note: this is a public web version of the Version 8i manual online. Oracle provides online documentation, but you require a login (which is free) to the Oracle Technology Network.

Oracle8i Supplied Packages Reference. Again this is dated information, being an older version, but gives you an indication. For reference Oracle 7 released in 1993 first provided Package/Procedure/Function functionality.

Other

On a side note, I’d really like to see a IF EXISTS syntax for DROP PROCEDURE. IF EXISTS is an excellent MySQL extension. Oracle for stored procedures has the CREATE OR REPLACE syntax.

I was trying to find an old article at Planet MySQL. One about a MySQL UDF to write to /var/log/messages. No luck.

Anyway, there is no search option on the site, and the latest addition of 10 entries per page makes it difficult to review pages. The RSS feed doesn’t give me a full option.

Anyway, it led me to look back in time to just how many articles are listed on Planet MySQL, and read some old stuff. I only came across it after I stumbled across the Brisbane MySQL Users Group back in Sep 2005.

Anyway the count was 2146. I’d like to see a stat on the home page of how many articles, and perhaps how many, last day, last week, last month.

I was reading Zack Urlocker’s MySQL Workbench Beta article and was keen to look at the Extensible architecture. Not much detail yet in the Figure Stylesheets, Scripts and Plugins, which will be good when it’s there, however it lead me to another secret.

The MySQL Forge. This was mentioned at the Brisbane MySQL Users Group Meeting with Brian Aker in January. There isn’t much content at present, but there is a Call for Content.

• Register your favorite projects
• Create a Wiki article
• Suggest categories for the Project Directory
• Add a code snippet
• Send us your suggestions

I don’t recall anybody blogging about it, or maybe I just missed it. Giuseppe Maxia should get his MySQL General Purpose Stored Routines Library up there. On that thought, I’ve been looking for a post several months ago, about a UDF that wrote to the system log /var/log/messages. Does anybody remember this?

Another slightly disappointing article regarding MySQL, this one from a printed magazine. Below are my comments to the editor of Linux Format. The Dear Editor is an email link should others wish to make any comments. (Previous article comments What makes your blood boil?, Review of Database Magazine Article - “The Usual Suspects”)

Dear Editor,

I’ve recently subscribed to LXF, and have generally been very happy with the content in past months. I’m disappointed in your recent LXF77 article “Harness a database” Pg 57. Being a strong MySQL supporter, your article includes a number of practices which are less then ideal, and especially for the newly initiated, overly complicated when simplier alternatives exist.

I am happy to see that you had the current version 5.0.18. However in 7 years of using MySQL I’ve never had to compile from source, a binary has always been available, yet in your article you take the compile from source approach. While you may have done this to appease all possible readers of all POSIX variants, the binary for at least Linux could have been included.

In addition, much like your earlier article for Samba where you make reference to “most distros include this”, indeed MySQL is also included in most popular Linux distros.

You could have also made reference to the MySQL downloads area website. Infact, the MySQL 5.0 download area provides some 70+ dedicated distro versions for optimal usage. The site also states “For maximum stability and performance, we recommend that you use the binaries we provide.”

The second point that I do believe is completely unnecessary, and not recommended by MySQL, is the manual creation of user security. MySQL provides the CREATE USER command to achieve this. In your article, you would execute CREATE USER lxf@localhost IDENTIFIED BY ‘orangutan’; rather then an INSERT INTO user and a FLUSH PRIVILEGES.

Throughout your article you make no reference to the MySQL website, perhaps this is a policy, however it’s always ideal to have online references. At worst, a link to say www.linuxformat.co.uk/products/mysql (or appropiate) that provides links to the product information, in this case MySQL. This will allow you to undertake your necessary advertising, as well as track traffic.

I’m also sure, that contacting the MySQL Community Relations Manager for review of any MySQL content in future would be openly accepted, I’m even happy to review MySQL content in your magazine.

ACID is the key transaction processing feature for a RDBMS. Without this, the integrity of the database cannot be guaranteed.

In Summary.

Atomicity is an all-or-none proposition.
Consistency guarantees that a transaction never leaves your database in a half-finished state.
Isolation keeps transactions separated from each other until they’re finished.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

A clearer definition from the Wikipedia.

• Atomicity refers to the ability of the DBMS to guarantee that either all of the tasks of a transaction are performed or none of them are. The transfer of funds can be completed or it can fail for a multitude of reasons, but atomicity guarantees that one account won’t be debited if the other is not credited as well.
• Consistency refers to the database being in a legal state when the transaction begins and when it ends. This means that a transaction can’t break the rules, or integrity constraints, of the database. If an integrity constraint states that all accounts must have a positive balance, then any transaction violating this rule will be aborted.
• Isolation refers to the ability of the application to make operations in a transaction appear isolated from all other operations. This means that no operation outside the transaction can ever see the data in an intermediate state; a bank manager can see the transferred funds on one account or the other, but never on both—even if she ran her query while the transfer was still being processed. More formally, isolation means the transaction history (or schedule) is serializable. For performance reasons, this ability is the most often relaxed constraint.
• Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won’t need to abort the transaction. Typically, all transactions are written into a log that can be played back to recreate the system to its state right before the failure. A transaction can only be deemed committed after it is safely in the log.