Problem solve Get help with specific problems with your technologies, process and projects.

MySQL, your SQL

Mike Hillyer answers users' questions about MySQL 5 and upgrading to MySQL from Oracle and Microsoft.

No release date has yet been officially announced for the production-ready MySQL 5, but MySQL expert Mike Hillyer has the inside scoop on its new features. In this tip, Hillyer answers users' questions about MySQL 5 and upgrading to MySQL from Oracle and Microsoft. - Editor

What are some situations in which the new features of MySQL 5 might be valuable?

Hillyer: Sure, let's look at some of the flagship features:

Stored Procedures are procedural code that is stored within the MySQL server and which can be executed entirely on the server-side. MySQL uses standard SQL:2003 stored procedure language, also known as PL/PSM. Here is an example of a stored procedure:

CREATE PROCEDURE procedure1                /* name */
(IN parameter1 INTEGER)                    /* parameters */

The preceding procedure takes a single integer parameter and inserts either "birds" or "beasts" into a table depending on whether or not the parameter equals 17.

While stored procedures are not for everyone, one compelling argument for using stored procedures is their portability: You can write business logic once in a stored procedure and then re-use it in a PHP front-end, a desktop application, and in a portable application by calling the stored procedure on the MySQL server. You can called the same stored procedure from C, PHP, Java, VB, or any language that can connect to MySQL. This can save a lot of effort because you do not have to re-implement the same logic over and over again.

You can learn more about stored procedures in an article here.

Triggers use the same procedural language as stored procedures, but rather than waiting for a user to call the code, it is attached to an event, such as an INSERT into a table. Events are typically INSERTs, UPDATEs and DELETEs. Here is an example of a trigger:

IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

In this case the trigger checks the data being inserted into a table to see if the leftmost character being inserted into the s2 field is not 'A' and if so sets the value of the s1 column to 0. Triggers can be used for logging activity (say you want an audit table that shows each action taken on another table as a log), validating data (check whether inserted values meet a complicated criteria), and maintaining relationships in a table handler that does not enforce foreign keys.

You can learn more about triggers in an article here.

A view is "a named, derived table whose definition is a persistent part of the database." Think of a view as a stored query that can be used as a table. Here are a couple of examples of views:


WHERE col1 IN (SELECT MAX(col1) FROM t);

Using views simplifies use of MySQL. If a complicated query is used for business reporting, it is often easier to turn it into a view, then end users can use the view to create reports. Views can also help with security; if a user should only have access to a subset of the rows in a table, create a view and grant access to it instead of the entire table (say for example you wanted to provide access to only a single region's sales data in a multi-region table).

You can learn more about views in an article here.

How you use the new features of MySQL will depend on your application, and your level of expertise. Most users can benefit greatly from views without a lot of experience in procedural programming and would do well to give them a try.

How scalable is MySQL?

Hillyer: That depends on your needs, but let me give you some examples of real-world scalability. At the 2005 MySQL User Conference last month I encountered users running MySQL on 20 terabytes of data for statistical analysis. I attended sessions where Ticketmaster described how MySQL was scaled to handle over 500,000 ticket sales per day through replication setups of dozens of servers in a multi-location replication hierarchy. Wikipedia uses MySQL servers with up to 32gb of RAM to handle high loads and MySQL is capable of handling even more memory and resources. Big names like Yahoo, Google and Slashdot use MySQL to run high-load Web applications.

In the end it all depends on what kind of scaling you need, but you can rest assured that MySQL has scaled very well for others.

My company currently uses tape drives for backing up MySQL. Our system is getting a bit old, and we're looking at other options. Can you suggest some backup systems that work well with MySQL?

Hillyer: Generally a backup solution is a mix of hardware and software, and the hardware is not really tied too tightly to MySQL since you can store your backup on tape, optical disk, or drive arrays. If tape worked before you may just want to get a newer tape solution.

On the software front there are a variety of options: you can use built-in tools such as mysqldump and mysqlhotcopy, or you can use a third-party backup solution from companies such as Veritas and Arkeia. MySQL has published on their Website a list of backup solution providers that may be of use to you.

We run Oracle 8i for everything, but we need to upgrade. We'd like something less expensive, of course, but it's got to be able to handle an active Web sales business with offices on two separate coasts. What are our options with MySQL? Should we be looking at a utility model, or would that transition from client-server be too hard?

Hillyer: Depending on how you currently use Oracle, MySQL may very well fit your needs. You need to take certain factors into account, such as how extensively you use stored procedures. MySQL supports a different standard for stored procedures. Oracle uses PL/SQL and MySQL uses PL/PSM. This could result in a major rewrite if you make use of a lot of Oracle-specific syntax.

As far as being able to handle an active Web sales business, this should not be a concern. Companies such as Ticketmaster and Travelocity make extensive use of MySQL in high-transaction environments and find that MySQL scales just fine for their needs.

As for the choice of model, I can't really answer on what information you gave. I know a lot of organizations first look at moving the back-end database then worry about changing their application down the road, and it often seems that making small changes in progression can be easier than tearing the whole system apart at once.

Slowly, my company (150 users) is moving to Linux. We want to get off of Microsoft SQL, but the migration task seems like a big deal for a 1-person (me) IT shop. If we chose MySQL, would we lose any functionality or data? Am I going to be able to handle this alone?

Hillyer: A loss of data should not occur if the job is done properly. Functionality may be difficult to move, depending on the amount of stored procedures in your database. MySQL 5 has stored procedures, but MySQL follows the PL/PSM standard for stored procedures, and MSSQL uses T-SQL. While the two standards are similar in a lot of ways, you may find yourself having to modify the MSSQL stored procedures to get them working with MySQL.

You can use the MySQL Migration Toolkit to help move your data and schema from MSSQL to MySQL, and I would recommend reading an article I wrote on migrating MSSQL and Microsoft Access databases to MySQL. In addition, I delivered a presentation on migrating MSSQL and Microsoft Access databases to MySQL, and you can view the presentation materials here.

For more information:
  • Get more expert MySQL advice from Mike Hillyer
  • .
  • Read how MySQL 5 is set to open once-closed doors in this interview with MySQL AB CEO Marten Mickos.
  • Dig Deeper on Linux servers

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.