Scott Miller

As I posted before, I have been tasked with converting a large block of Oracle stored procedures to MySQL.

As you might expect, MySQL won out over Oracle in the "bean counter test" (accounting and administration procurement project approval) because it cost $5K vs. over $100K for Oracle. So we were basically told to use it. Opportunity cost of the extra time to learn it, code it, and the possible risks were voiced but not considered in the decision. The vast difference in price won the day. But maybe my experience will help you if you face the same challenges in the future.

I have been using MySQL full-time for 6 weeks now. The stored procedure functionality was not added to MySQL until November 2005, and it is still in its first version (v. 5), although we are using the beta of v. 5.1.

I am using TOAD for MySQL to compile and run my SQL and stored procedures. TOAD doesn't have a debugger in the MySQL freeware version. There are not many options for debugging MySQL stored procedures. We have evaluated MySQL Developer Studio, but there were some issues in the debugger. We are still working with them for support. In the meantime, we purchased Illatis StepIn, but I haven't used it extensively yet. It is bare bones debugging. I can't change code, and I can't recompile. Overall this whole experience is like stepping back five-ten years. I am used to using TOAD and PL/SQL Developer to develop and debug stored procedures in Oracle, so this is like using stone knives and bearskins (Star Trek - City on the Edge of Forever). Although there are a few books on MySQL, they all cover the database, and most are rudimentary. Only one that I have found covers mySQL stored procedures : O'Reilly's MySQL Stored Procedure Programming. This should tell you alot.

As for the stored procedure features:

• When a MySQL stored procedure does not successfully compile it sends somewhat cryptic error messages. In addition, MySQL only checks syntax of the SQL and control statements. It does not check variable names or variable binding or calls to other procedures. The code compiles fine. Then when I run the procedure it triggers a generic SQLEXCEPTION error.
• There is no SQLERRM or SQLSTATE or Signal variable in the stored procedure language where you can actually see which error was trapped. This has been my number one beef with MySQL stored procedures. It makes debugging very difficult. If I misspell a variable name there is no error. I have wasted literally hours on trying to find problems that should have been very easy.
• No package support. This has been a problem because all of our legacy Oracle PL/SQL Code is arranged in packages. So all of those procedures have to be seperate. Oracle PL/SQL also allows function overloading, which MySQL obviously does not allow, so each of those functions need to be changed/renamed.
• SET is a pain in the butt. To assign a value to a variable:
  SET latitude = -39.5; (MySQL)
  latitude := -39.5; (Oracle)
• Overall, not many books on MySQL stored procedures. There are many books on using MySQL (just the database), but only one O'Reilly book,

Verdict:
MySQL may be fine for basic database use, but the stored procedure language is very immature. If you are like me and like to keep your business logic in stored procedures than in code, then look elsewhere. All of the people I talked to and apps I looked at that use MySQL put all the business logic at the client or in server script code rather than in stored procedures (example: Drupal puts all of the logic in PHP).

And remember the points above, because at some point you may have a department head or manager who looks at the two databases (whichever one you use vs. MySQL) and becomes convinced that MySQL is the way to go based on price.