What MySQL Lacks

only for RuBoard - do not distribute or recompile

The following section is not short, nor are some of the missing things trivial, such as a stored procedure language, triggers, and enforced referential integrity. MySQL is GPL; it is open source. The same hacker community that built the Internet is working on and improving MySQL; any image it has to protect is one based on competence, not marketing. Personally, I would rather hear the downside up front than find out when I am hip-deep in code that you can t do that.

Therefore, the following sections present some of the shortcomings of MySQL. Table 1.2 summarizes what MySQL lacks, and the following sections provide pertinent information about each feature that was available at the time this book was written.

Table 1.2. Quick Guide to Functionality Missing from MySQL

Functionality	Notes
GUI	In progress when this book was written.
Stored procedures	Scheduled for version 4.1; Zend scripting engine. Possibly Python and others, too.
Referential integrity	Reported to be in alpha testing.
Transactions	New table-type BDB supports transactions, but MyISAM is still considered the default table type.
Cursors	Reported to be implemented in 4.1.
Triggers	Scheduled for version 4.1; Zend scripting engine.
Record-level locking	Scheduled for version 4.
Subselects	Scheduled for version 4.1.
Named views	Single-table views should be in 4.0 or 4.1. Multi-table views should be in 4.1.

GUI: Houston, We Have Command Line Only

Get used to it: MySQL is designed to be used from the command line. Owing to Linux s UNIX heritage, that should come as no surprise. It s just an observation, but my experience is that the power db admins use and prefer command-line functionality over GUI. Why? Well, the learning curve is steeper, but after that s conquered, productivity goes up. ( Specifically , being able to write complex shell scripts to perform advanced functionality can be an extremely powerful tool.) Think of the difference between vi and Notepad (if you are familiar with vi). Yes, it is harder to learn, but in the end, the more difficult tool is more productive.

Actually, as this was being written, a GUI was in beta. Figure 1.1 shows the main form.

Figure 1.1. Beta stages of the MySQL client GUI interface.

As you can see, not all the functionality was available, but like everything else in the Open Source universe, that will continue to change rapidly . If this is of interest to you, by the time this book is in print, a fully developed product might be worth investigating.

Transactions

When you need to conform to business rules and maintain data integrity, you can use a transaction to ensure that multiple operations against a database are all complete or that they are all incomplete and the data is in its original state. The most common example of this is when a bank transfers money between two accounts. The bank doesn t want to credit one account and then find that the debit from the originating account failed. That would leave the money in both accounts. The idea of transactions is that before any changes are committed to the database, all actions have been completed successfully. If not, all pending changes must be rolled back, and the data is reset to original values.

Actually, as this book was being written, MySQL posted a press release claiming that the program supports transactions (of sorts). In a press release dated May 9, 2000, MySQL announced experimental support in version 3.23.15 for Berkely DB from SleepyCat Software ( www.sleepycat.com ) , a transaction-safe database handler. Version 3.23.25 fully implemented this, but it is not enabled by default. The press release is at http://www.mysql.com/news/index.html. From there you can follow the link to see if this will suit your needs.

It appears that MySQL is introducing a new table type ( BDB ), with which you can use a Begin/Commit/Rollback syntax and mechanism. (I did not have a chance to play with this before finishing this book.) By the time you have this book, there should be available a MySQL binary with BDB support. With BDB tables, it appears you can do all the standard stuff you expect from transactions: BEGIN, COMMIT, ROLLBACK, and also working in AUTOCOMMIT mode. As always, check the MySQL Web site for the latest, and subscribe to the announcements mailing list.

You can partially emulate transactions (in other table types) by locking the tables in question, but you must supply your own rollback mechanism. See http://www.mysql.com for a discussion of COMMIT/ROLLBACK, paying particular attention to the section How to Cope Without Commit/Rollback.

Stored Procedures and Cursors

A stored procedure is a series of statements grouped into a single program that can be executed in the database. The advantage of stored procedures is that they can be optimized by the database for maximum performance. Also, the database can have a stored procedure language that normally resembles a hybrid of SQL and a programming language. This pseudo-language allows you to manipulate data in ways that are not standard to SQL ”specifically, iterating row-by-row through the data in order to manipulate individual fields or maintain variables . Such row-by-row iterations are called cursors.

Because MySQL lacks a stored procedure mechanism, specialized language, and cursors, you must supply your own mechanism.

A stored procedure mechanism is on the MySQL developers to do list; it s reported that it will be included in version 4.

How to Emulate Stored Procedures and Cursors

Now that you know stored procedures and cursors are not part of MySQL, here are a few hints on how to emulate them.

For standard SQL-compliant statements, you can create a text file that contains the SQL statements, including select statements. You separate SQL statements with semicolons. The text file can then be fed to mysql to perform the queries, as follows :

 % mysql test_db < day_check.sql

mysql is the program name of the MySQL client, and test_db is the database to which it should connect. day_check.sql is the text file that contains the SQL statements. Multiple SQL statements are separated by semicolons. Note that this is the short syntax using the defaults; your installation might require something along these lines:

 %mysql --host=localhost --user=yourname --password=your_pword test_db <  day_check.sql

If you need more advanced capabilities, such as the ability to iterate by row and make changes, you will probably have to create a program that connects to your MySQL database and that does the processing in another language, such as C, PHP, or Perl. To emulate cursors, you must encapsulate row-by-row processing in your C programs outside the database. You will see many examples of this throughout the book.

Triggers

A trigger is a procedure, usually in the database s stored procedure language (an embedded scripting language such as Zend or Python), that can be set to fire when an event happens, such as when a table is deleted, updated, or added to. For example, if you wanted auditing on a database to know who changed, added, or deleted what data and when, you might set triggers for all three events for all tables. That way, if some data was found to be incorrect, you could follow the audit trail to find out when the data was changed and possibly by whom.

If an audit trail is high on your list, look carefully into the logging capabilities already built into MySQL. The --log option to safe_mysqld produces a detailed log file. If really detailed logging is necessary, consider starting safe_mysqld with the --debug option. If you only want to record what s happening, you should use the --log-bin option.

If you have triggers in your C programs, you must supply your own triggers in your C code, as you will see in examples later in this book. In short, you must know what you want to happen and then program your own mechanism for doing it.

As a final note, this is scheduled to be implemented in version 4 using the Zend scripting engine. Word has it that it will be a general API that will work with any scripting engine, but it will initially work with Zend and Python.

Enforced Foreign Key Referential Integrity

Foreign key referential integrity requires you to have an entry in one table in order to make an entry in another table. For example, suppose you have two tables: an invoice header table and an invoice line item table. It makes no sense to enter rows into the line item table unless you have matching rows in the invoice header table first. If you go to join the two tables on InvoiceNumber , the rows from the line item table that do not have corresponding entries in the header table will be left out of the result set. In this case, referential integrity would require that an entry exist in the invoice header table before one could be created in the invoice line items table.

Although MySQL supports the FOREIGN KEY keywords in the CREATE TABLE statement, that s for portability to other databases. MySQL doesn t enforce referential integrity. Of course, if you can t do this in your application, you have probably made some mistakes already. (That is, if you understand the concept of referential integrity, you can add and delete records correctly without a mechanism to enforce it.) To do this, you must know what you re doing: You must understand your data and your application, and you must program both to support your requirements. (As this book goes to press, this is in alpha testing.)

Record-Level Locking

Normally, you don t want any two persons or applications connected to a database to be able to overwrite the same data in the same record in the same table. Therefore, some databases allow you to lock the data at the row level. This means that the first person to start making changes has the data locked, and when he is finished, the data is unlocked, and the next person can make changes.

MySQL doesn t support record-level locking, although you can use table-level locking. (BDB tables, mentioned previously, do support page-level locking.) When designing your database and application, you might have to take this into consideration, but there are ways to get around it if you anticipate it will be a problem. For example, you could set up an area to handle table additions and have a separate set of tables for updates and deletions. Or you could create a table of pending changes that anyone can append to and then use an automated mechanism to execute the changes on a regular basis, such as every 5 minutes or every night, depending on the requirements.

So, what happens when there is a conflict, such as when two separate processes try to update the same table? In such a case, one of the processes has to wait until the other is finished. Updates are considered to have priority over selects, but that can be changed. For more information and a discussion of how to change priorities, see the section titled Table Locking Issues at http://www.mysql.com/documentation/mysql/bychapter/manual_Performance.html.

Finally, this is reported to be included in the version 4 release.

Subselects

In SQL, you could use the following command to select when the amount is less than the average order amount, even if you don t know the average order amount ahead of time:

 select * from tbl_orders where order_amount < (select avg(order_amount) from  tbl_orders);

The second select statement (the one in parentheses) is called a subselect .

MySQL does not support subselects, but workarounds are often possible. Obviously, one very easy way around using subselects in the previous example is to query first for the average order amount and then work that result into your SQL statement for the main query. Clearly, to do this, you need some mechanism for storing the first result for use; this is straightforward when you use C or other procedural languages.

Another workaround is to store the result in a server-side variable:

 select @a:=avg(order_amount) from tbl_orders);  select * from tbl_orders where order_amount < @a;

Still another way to get around this is through the use of temporary tables. In the preceding example, you could create a table of one row to hold the average order amount, join that table with the orders table (not using any join criteria, thus appending a column with the average order repeated for each row), and then use a calculation to compare the order amount to the average order amount on a row-by-row basis. The advantage of this approach is that it uses all SQL, and with MySQL, the temporary tables are dropped automatically when the connection that created them is closed.

Again, this is reportedly to be included in the version 4 release.

Named Views

These are logical tables or defined views, normally of multiple tables. For example, if you had an Invoice Header table and an Invoice Line Item table (in a one-to-many relationship), you could define an Invoice View that would show all columns for both tables in which the invoice number was the same. Views are normally defined within the database, where the database engine can optimize the view and maintain it.

MySQL does not support views, but it is on the to-do list. You can emulate this by putting the SQL statement defining the view into a text file in your project directory and then feeding the contents of the text file to the MySQL server. (The people at MySQL tell me that single-table views should be in 4.0 or 4.1. Multi-table views should be in 4.1.)

only for RuBoard - do not distribute or recompile