MySQL Stored Procedure Programming 206
Michael J. Ross writes "MySQL may be the most popular open source relational database management system (RDBMS) in the world, but during the first decade of its existence, it lacked support for stored programs, i.e., store procedures, functions, and triggers. The major commercial RDBMS vendors — including Oracle, IBM, and Microsoft — could point to this deficiency as reason enough to choose their proprietary systems over MySQL or any other open source system, such as PostgreSQL. But with the release of MySQL version 5.0, in October 2005, the "little database engine that could" dramatically improved its position against the competition. The most comprehensive discussion of these new capabilities is in the book MySQL Stored Procedure Programming." Read below for the rest of Michael's review
| MySQL Stored Procedure Programming | |
| author | Guy Harrison and Steven Feuerstein |
| pages | 636 |
| publisher | O'Reilly Media |
| rating | 9 |
| reviewer | Michael J. Ross |
| ISBN | 0596100892 |
| summary | A comprehensive guide to developing MySQL stored procedures, functions, and triggers. |
Written by Guy Harrison and Steven Feuerstein, and published by O'Reilly Media in March 2006 under the ISBNs 0596100892 and 978-0596100896, this book is the first one to offer database programmers a full discussion of the syntax, usage, and optimization of MySQL stored procedures, stored functions, and triggers — which the authors wisely refer to collectively as "stored programs," to simplify the manuscript. Even a year after the introduction of these new capabilities in MySQL, they have received remarkably little coverage by book publishers. Admittedly, there are three such chapters in MySQL Administrator's Guide and Language Reference (2nd Edition), written by some of the developers of MySQL, and published by MySQL Press. Yet this latter book — even though published a month after O'Reilly's — devotes fewer than 50 pages to stored programs, and the material is not in the printed book itself, but in the "MySQL Language Reference" part, on the accompanying CD. That material, in conjunction with the online reference documentation, may be sufficient for the more simple stored program development needs. But for any MySQL developer who wishes to understand in-depth how to make the most of this new functionality in version 5.0, they will likely need a much more substantial treatment — and that's exactly what Harrison and Feuerstein have created.
The authors are generous in both the technical information and development advice that they offer. The book's material spans 636 pages, organized into 23 chapters, grouped into four parts, followed by an index. The first part, "Stored Programming Fundamentals," provides an introduction and then a tutorial, both taking a broad view of MySQL stored programs. The remaining four chapters cover language fundamentals; blocks, conditional statements, and iterative programming; SQL; and error handling. The book's second part, "Stored Program Construction," may be considered the heart of the book, because its five chapters present the details of creating stored programs in general, using transaction management, using MySQL's built-in functions, and creating one's own stored functions, as well as triggers. The third part, "Using MySQL Stored Programs and Applications," explains some of the advantages and disadvantages of stored programs, and then illustrates how to call those stored programs from source code written in any one of five different programming languages: PHP, Java, Perl, Python, and Microsoft.NET. In the fourth and final part, "Optimizing Stored Programs," the authors focus on the security and tuning of stored programs, tuning SQL, optimizing the code, and optimizing the development process itself.
This is a substantial book, encompassing a great deal of technical as well as advisory information. Consequently, no review such as this can hope to describe or critically comment upon every section of every chapter of every part. Yet the overall quality and utility of the manuscript can be discerned simply by choosing just one of the aforesaid Web programming languages, and writing some code in that language to call some MySQL stored procedures and functions, to get results from a test database — and developing all of this code while relying solely upon the book under review. Creating some simple stored procedures, and calling them from some PHP and Perl scripts, demonstrated to me that MySQL Stored Procedure Programming contains more than enough coverage of the topics to be an invaluable guide in developing the most common functionality that a programmer would need to implement.
The book appears to have very few aspects or specific sections in need of improvement. The discussion of variable scoping, in Chapter 4, is too cursory (no database pun intended). In terms of the book's sample code, I found countless cases of inconsistency of formatting — specifically, operators such as "||" and "=" being jammed up against their adjacent elements, without any whitespace to improve readability. These minor flaws could be easily remedied in the next edition. Some programming books make similar mistakes, but throughout their text, which is even worse. Fortunately, most of the code in this book is neatly formatted, and the variable and program names are generally descriptive enough.
Some of the book's material could have been left out without great loss — thereby reducing the book's size, weight, and presumably price. The two chapters on basic and advanced SQL tuning contain techniques and recommendations covered with equal skill in other MySQL books, and were not needed in this one. On the other hand, sloppy developers who churn out lamentable code might argue that the last chapter, which focuses on best programming practices, could also be excised; but those are the very individuals who need those recommendations the most.
Fortunately, the few weaknesses in the book are completely overwhelmed by its positive qualities, of which there are many. The coverage of the topics is quite extensive, but without the repetition often seen in many other technical books of this size. The explanations are written with clarity, and provide enough detail for any experienced database programmer to understand the general concepts, as well as the specific details. The sample code effectively illustrates the ideas presented in the narration. The font, layout, organization, and fold-flat binding of this book, all make it a joy to read — as is characteristic of many of O'Reilly's titles.
Moreover, any programming book that manages to lighten the load of the reader by offering a touch of humor here and there, cannot be all bad. Steven Feuerstein is the author of several well-regarded books on Oracle, and it was nice to see him poke some fun at the database heavyweight, in his choice of sample code to demonstrate the my_replace() function: my_replace( 'We love the Oracle server', 'Oracle', 'MySQL').
The prospective reader who would like to learn more about this book, can consult its Web page on O'Reilly's site. There they will find both short and full descriptions, confirmed and unconfirmed errata, a link for writing a reader review, an online table of contents and index, and a sample chapter (number 6, "Error Handling"), in PDF format. In addition, the visitor can download all of the sample code in the book (562 files) and the sample database, as a mysqldump file.
Overall, MySQL Stored Procedure Programming is adeptly written, neatly organized, and exhaustive in its coverage of the topics. It is and likely will remain the premier printed resource for Web and database developers who want to learn how to create and optimize stored procedures, functions, and triggers within MySQL.
Michael J. Ross is a Web programmer, freelance writer, and the editor of PristinePlanet.com's free newsletter. He can be reached at www.ross.ws, hosted by SiteGround.
You can purchase MySQL Stored Procedure Programming from amazon.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
What's your opinion (Score:4, Interesting)
Not really FUD (Score:3, Interesting)
For a high-traffic fairly leisurely updating database where element veracity isn't CRITICAL, they were right.
What they didn't realise was that people on the forums didn't all work in areas where that was true and would prefer not to have to use two databases...
They're better, but then again, they're losing the edge on speed, too.
Re:The parent comment is a classic example of FUD (Score:5, Interesting)
It does speak volumes about MySQL's history, though. The things he lists are are the reasons why MySQL is still compared with M$ Access. They are valid points, because MySQL as a company has a sordid past, as far as presenting something that resembles modern database theory. I'm not saying I don't appreciate MySQL, I'm just say that I thoroughly understand the frustrations. MySQL has been presented as an off the shelf enterprise contendor for years and that's only begining to resemble fact.
Re:MySQL aren't trustworthy (Score:1, Interesting)
I have been using PostgreSQL for over 6 years and I've always found all the documentation I've needed (mostly on postgresql.org actually).
You call shenanigans...I'm sorry but I have to call incompetence on your part...
Re:Stored procedures BAD... story (Score:3, Interesting)
Demonstrably false. The ANSI standard for stored procedures already exists. MySQL has merely implemented this standard. You can port stored procedures to any other database that supports the standard (which admittedly didn't give you a lot of choice last time I looked). PostgreSQL initially took the "be compatible with Oracle route" with PL/pgSQL, so moving stored procedures between the two would be easy. They've since added the ability to have SPs in alternative languages, so it should be easy to add a PL/ANSI to PostgreSQL, assuming someone hasn't already done so (I haven't checked).
Re:MySQL aren't trustworthy (Score:5, Interesting)
For people not familiar with MySQL's history, I would suggest a little reading from previous versions of MySQL's manual:
How to cope without COMMIT/ROLLBACK: For the moment, we are much more for implementing the SQL server language (something like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance. [utk.edu]
Reasons not to use foreign keys. There are so many problems with FOREIGN KEYs that we don't know where to start. [utk.edu]
I decided long ago that the MySQL guys are clowns. MySQL's lack of features was never as big a problem as the fact that I just couldn't take these guys seriously (and the above is only a small subset of the reasons for that).
Re:Stored procedures BAD... story (Score:3, Interesting)
Re:What's your opinion (Score:2, Interesting)
I'll probably get completely trashed for this advice, but I find the following run counter to "the standards", but to be essential for good database performance.
CRUD procedures are often bad for performance. People will send 100 individual queries to the server to do something that could be done in 1. This is common. Or, the middle tier will send 100 queries to the database to do the work of 1. Whatever. In either case, it's bad.
As long as the Stored Procedures/Middle Tier are dealing with single records instead of what the application requires, it's written wrong. But I have seen many database admins that throw up CRUD procedures and then turn off all access to the database, leaving developers no choice. And requests to add additional Stored Procedures are denied because "there are 400 Stored Procedures already".
Transactions are really bad for performance. Really bad. They should be avoided whenever possible. Any Stored Procedure call or single batch sent to most SQL Servers automatically have an implied transaction put around them anyway. Why do you want to hold up the entire database when someone accidentally pulls the network cable on your PC?
Get in and get out as quickly as possible if you want to scale your application well. Make sure the Stored Procedure or Middle Tier does everything you want and does it using a single DB call, not 100 calls to CRUD procedures.
Dynamic SQL has its place sometimes. And there's no substitute for it when it does. You have to know when it's necessary and it should be less than 1% of your total SQL calls. Don't make excuses to be lazy. But when you need variable numbers of columns based on what the user selected you can achieve massive performance gains over any other method.
Re:Deciding if MySQL is an option (Score:3, Interesting)
Re:MySQL aren't trustworthy (Score:3, Interesting)
They've come a long way, but unless I'm misinformed, they've still got problems with automatic data conversion breaking data integrity rules.
It wasn't that long ago they were pushing the worst possible practices as gospel to people who didn't know any better, all while labouring to catch up with the Jones because they knew they were behind.
They are deceitful.
Put it this way, which is more useful as a means of conveyance, a standard car that you don't know how to drive or a rocket car with one button, no steering and no breaks?
MySQL is a suitable tool for servicing trivial data at very high speeds. I don't knock that. My issue lies in the real fact that the team behind MySQL does not have a history of being forthright about the limitations of what they've built.
I don't think much of the parallels their corporate structure and licensing has with the whole Project Mayo/DivX debacle, either. It's just the sort of thing that leaves a sour taste in your mouth.
Re:Deciding if MySQL is an option (Score:2, Interesting)
I know that there are inconsistencies, I am just afraid to search how many are there.
And that including the FACT that there were NO crashes EVER.
Fortunately NO ONE ever needed 100% consistency on that database, and people will probably "pay" for that.
Re:What's your opinion (Score:3, Interesting)
Unless you're using postgres, in which case your performance goes to shit if you DON'T use one. My results with oracle have been mixed -- I suspect, as with all things oracle, it depends on the configuration.
>
What kind of brain-dead database would implement all transactions with a global lock?
As for Dynamic SQL, it certainly has its place, but when you need variable numbers of columns and you're not working with some crazy-ass 200-column table or you have LOBs or vertical partitions, you're usually better off just selecting * and picking out the columns you want. You are using a cursor, right?
Re:MySQL vs Firebird (Score:3, Interesting)
Similarly, the field of database design is choked with so-called DBAs who learned their trade using one tool to the exclusion of all others. Most of them can be forgiven for this, because that's how many database classes and books approach the problem. You're not taught databases
So back to your point: Sure, anybody who really understood database theory might have such a strong adverse reaction to MySQL that they'd never use it for anything. Then again, if they really know what they're doing that well, then they'll probably understand the application they're trying to build well enough to know if it's likely to grow beyond the capabilities of MySQL. If it's not likely to do so, then they might make the conscious decision to use the more popular tool because of its wide availability, strong community and ready support. To me, an Oracle DBA who doesn't also learn how to use MySQL is just being narrow-minded. (He should also understand the drawbacks and advantages of DB2 and MS-SQL, too, whether or not he ever intends to contract out to work on them.)
Or, to put it another way: When the only tool you have is a hammer, every job starts to look like a nail. And treating them that way might work, superficially, but there are all kinds of nails out there. It's not always about bringing the heaviest hammer you can find.
Re:What's your opinion (Score:2, Interesting)
The major application I maintain has pretty much ALL business logic as PL/SQL stored procedures. Why? Because...
- As has been pointed out, having the logic near to the data provides better performance,
- Production hotfixes can be applied without an outage--in our situation the frontend is Java, which would require a jar rebuild and deployment,
- Triggers can enforce business logic,
- Errors in the business logic can be traced with a simple telnet/ssh session without needing access to the client (Citrix, in our case).
- ROLLBACK and COMMIT can be decided based on business logic within the one stored procedure rather than waiting from a request from the client or middleware (which means more network bandwidth).
That's the tip of the iceberg in ways of pros. I'm sure there are a bunch of cons too, but the points I've just listed are pretty compelling.But yes, it all depends on your situation. If you find that it makes more sense to put the business logic elsewhere within your application, then by all means do so. This is just my opinion based on my experience with this application.
BTW this application is BIG with over 900 tables, 2000 stored procedures and 500 Java forms.
Re:What's your opinion (Score:4, Interesting)
MySQL vs PostgreSQL benchmark (Score:2, Interesting)
Re:Deciding if MySQL is an option (Score:3, Interesting)
Ok, choke on this then: I work for a company that runs exclusively MySQL. We have 1,500 tables across our *primary* DBs (that's user-facing data) containing a little over four billion rows. Most of the queries are simple because they're primary-keyed returning single rows, but our largest tables contain 100-200 million rows and are subject to several thousand queries per second.
And we have fewer problems than we did when we were using Oracle with 1% as much data a few years back. Yes, that's right: We switched from Oracle to MySQL. And it was one of the best decisions we ever made.