MySQL Stored Procedure Programming

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.

Comments

MySQL aren't trustworthy

[ShieldW0lf]

You know what it is?

It's lack of trust.

MySQL started off making their fast little datastore that uses a little SQL syntax, and they told everyone under the sun that if MySQL didn't do it, you didn't need it, and ought to design around it.

Referential integrity, ACID compliance, enforcement of rules... every step along the way, they tell you you don't need it and you ought to push the missing logic to the next tier, until they get it, if they get it, then they're so great.

Couple that with the Project Mayo/DivX corporate structure, then place Postgresql next to the whole shebang as a superior alternative, and the arguments for giving MySQL any of your attention become increasingly small.

MySQL owes its success to the fact that it was always so simple to choke it off when you were giving it to people on $5/month hosting plans, so it became popular among the very cheap.

Period.

Re:MySQL aren't trustworthy

[bahwi]

"Postgresql next to the whole shebang as a superior alternative"

I call shenanigans. Only recently has it become superior. Documentation isn't just a feature, it's a necessity, even with the open source world, and Postgresql's documentation is finally getting to a good point. I tried numerous times to install and use this "superior alternative" with bad documentation(it was all there, but in backwards, confusing steps, how to access your database was before creating it, etc..) it was just disorganized. Privileges were a bitch with very confusing instructions. Yeah, if you knew it, superior alternative, if you didn't, RTFM, oh wait, that didn't actually help. Now the MySQL can compete better on features(not completely mind you, PostgreSQL still has some important features missing from MySQL) the documentation is starting to improve a lot on the postgresql front.

Re:MySQL aren't trustworthy

[Overly Critical Guy]

I've never had a problem with documentation. Are you saying the only reason you discounted PostgreSQL as a superior database was the documentation, despite the fact that for years, MySQL would happily drop data without telling you?

Re:MySQL aren't trustworthy

[XorNand]

I recently decided to move to Postgres from MySQL. There are some pretty cool things about Postgres; flexible authentication options and sequences being my current favorite. I've found that it's docs are actually pretty good (I don't know what they were like before). However, since the topic here are stored procedures, I have to really complain bitterly about Postgres's implementation of stored procedures. Creating a sproc that returns a dataset rather than just a outpur parameter is a convoluted dance. You have to exactly define the output using a "TYPE" and then iterate over the dataset using a cursor-like syntax.

Consider this simple example that I'm currently working on. It's for an internal peer review application where coworkers give one positive and one negative comment about each other:

DROP TYPE answers_type CASCADE;
CREATE TYPE answers_type AS (
positive text
,negative text);

CREATE OR REPLACE FUNCTION answers_get(
_review_id int
) RETURNS setof answers_type AS $$
DECLARE
rec answers_type;
BEGIN

FOR rec IN SELECT
positive
,negative
FROM answers
WHERE review = _review_id
ORDER BY id DESC
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END; $$ LANGUAGE plpgsql;

This ends up being very unwieldy for larger and more complex stored procedures. I recently compared simple output -parameter only stored procedures between Postgres, MySQL, and MS SQL on my blog. Fortunately it's a lot more straightforward when using that method. I'll stick with Postgres for now and just hope that this is eventually improved.

Re:MySQL aren't trustworthy

[CoughDropAddict]

Amen, brother.

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.

Reasons not to use foreign keys. There are so many problems with FOREIGN KEYs that we don't know where to start.

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:MySQL aren't trustworthy

[LizardKing]

Only recently has it become superior. Documentation isn't just a feature, it's a necessity, even with the open source world, and Postgresql's documentation is finally getting to a good point.

Unless something dramatic has happened to MySQL documentation since I last used it (version 4.1), then PostgreSQL has the better documenation and has done since as long as I've been using it. The PostgreSQL documentation reads like a coherent set of books, while MySQL documentation seems to be a random mess that grew out of some simple README file. As for the software itself, PostgreSQL has always been superior - a simple comparison of features alone bears this out, but if you want to go further take a look at the code as the same distinction is found there.

Re:MySQL aren't trustworthy

[ShieldW0lf]

My point is that the people behind MySQL have no problem telling you that their software is suitable for a purpose even when it isn't.

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:MySQL aren't trustworthy

[swusr]

Since PostgreSQL 8.1:

CREATE OR REPLACE FUNCTION
answers_get(_review_id INT, OUT positive TEXT, OUT negative TEXT)
RETURNS SETOF RECORD AS $$
SELECT POSITIVE, NEGATIVE
FROM ANSWERS
WHERE REVIEW = $1
ORDER BY ID DESC
$$ LANGUAGE SQL;

Re:MySQL aren't trustworthy

[umeboshi]

I suspected the same thing, as I have been using foreign keys for a long time in postgresql now, and I have never come across those problems. I read the section right above this and went pale. I was told that mysql got support for foreign keys a couple of years ago, but I now understand what they consider "support" for foreign keys.

The real kicker for me was this sentence.

The only nice aspect of FOREIGN KEY is that it gives ODBC and some other client programs the ability to see how a table is connected and to use this to show connection diagrams and to help in building applicatons. I always believed that relational integrity was the major "nice aspect" of foreign keys. I guess this really sets the record straight, and gives some of the best insight into the capabilities of the developers in making a good relational database.

What's your opinion

[CastrTroy]

I would like to know what slashdotters think is a good level of stuff to be putting into stored procedures. I've seen organizations where every single select query is put into a stored procedure. I've also seen places that avoid it like the plague. Personally I like to keep as much business logic as possible out of the database, but I realize it can speed up things considerably, so I use it where speed is critical. Is there any hope that stored procedures will become cross platform and work on all databases, at least to the level of SQL, so that there's some kind of standard, or do they just push us towards vendor lock-in?

Re:What's your opinion

[sammy baby]

Oh man, talk about a can of worms.

On the one hand, folks who are trained to think in terms of data relationships and database development are liable to want to put as much as possible right in the database.

On the other hand... well, forget it. There is no other hand, it's more like a shifting mass of appendages, like Shiva on an acid binge. The other extreme are people like David Heinemeier Hansson, who want to use database to store information and otherwise stay the hell out of the way - as I believe he put it once, to be as dumb as possible.

That may be a minority view, but the minority isn't exactly vanishingly small. eBay, for example, not only skips out on transactions (you know, the thing people like to beat up on MySQL for not having years ago), but doesn't even enforce referential integrity in the database. Stored procedures are right out. (warning: reference is a PDF.)

My own personal, under-informed and probably worthless view: I like enforce referential integrity where I can in the DB, and transactions are a good thing, but stored procedures are better left to maintenance and administration functions than anything involving business logic.

Re:What's your opinion

[arivanov]

Nothing.

I am fully aware that I would be flamed to death by ACID zealots, but I will again repeat nothing.

We are in 2007. Using non-OO fortran-like obscenities dated 1993 instead of looking at the calendar and getting a grip with reality gives you exactly that - nothing.

There is a reason why banks, govt, etc has been looking more and more at middleware layers on top of databases. You get the same you get with stored procedures while still having modern data and code representation.

Similarly, the little engine that could should have actually looked harder at making things that matter like isolation levels, foreign keys, etc work with all of their engines and not just one. Which they lost to Oracle anyway and which had no means of online backup (within the same license). They did one right thing at one point by introducing stored Java, C++ and Python procedures. It is a pity they did not expand on that and degraded into the fortran age instead.

Re:What's your opinion

[shatfield]

My opinion? I'm glad you asked :-)

Rule #1 of Software Development in the 21st century:

Don't put any business logic into sprocs.

Why?

It's very simple really, and very logical.

1) Only CRUD operations should be in sprocs. These sprocs are designed to allow someone to create/read/update/delete data in the tables in the database without giving the user access to or knowledge of the underlying table structure. Use application roles (approles) to allow access to the tables. This keeps people from loading up your tables with Excel and walking all over your data. Also, this prevents SQL injection attacks, as your sprocs will choke all over them and nothing will get trampled. Any time that you put SQL statements in a form, you are opening yourself up to SQL injection attacks, unless you have specifically guarding against them, which requires even MORE work. Not giving your users knowledge of the underlying table structure makes it easy for you to change that structure, should you (or the DBA) need to.

2) The second that you place a business rule into a sproc, the users will want the rule changed. This means that you now have to go back to the sproc to update it. Why is this bad? Because sprocs are almost always under the control of the DBA. This sometimes isn't that bad, but most of the time there is a layer of politics involved between DBAs and Software Developers. What usually happens is that the dev will make the change to the business logic in the middle (or even worse, GUI) tier, thereby doing twice as much work. This happens way more often than you think it does.

3) Adding Database servers to a cluster isn't cheap. If you have a lot of users, and you have had to cluster your servers to accommodate more users, you'll have to add more database servers as you add more users. This is expensive, especially if you are using SQL Server or Oracle. Adding more middle-tier servers is much cheaper, being that you only need the underlying OS. Not to mention that there is a diminishing point of returns when adding database servers.

I hope that this makes sense to you, and helps you build arguments in the future for NOT allowing devs to put business logic into the sprocs!

-Steven

Re:What's your opinion

[PRMan]

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.

1. 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".

2. 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.

3. 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:What's your opinion

[Anonymous Coward]

I am fully aware that I would be flamed to death by ACID zealots, but I will again repeat nothing.

You mean, people who actually know something about data modeling?

We are in 2007. Using non-OO fortran-like obscenities dated 1993 instead of looking at the calendar and getting a grip with reality gives you exactly that - nothing.

Well, sonny, this might be before your time, but do you know how they wrote apps in the 60's and 70's? That's right. Everything was in the apps. Everybody was creating their own little toy DBMS layer and calling it something different. Data integrity was nowhere to be found. Application independence was a joke. There was no relational model to tie together and generalize the network and hierarchical models.

Now in 2007, we see developers doing things the same way. Of course, instead of "network DBs" we have "object DBs", and instead of "hierarchical" we have "XML". The previously unnamed pseudo-DBMS layer is now called "ORM layer" or "persistence layer" or "middleware". Instead of general queries, we get XML-RPC interfaces and navigational, element-at-a-time retrieval. People think that pulling out objects in a "for loop" is somehow superior to asking the DBMS for what you need in a single query, letting it optimize the query, and retrieving it all at once.

As far as geniuses like you are concerned, the last 3-4 decades of data management research just didn't happen. Except maybe the functions are all lowercase now, so you can make fun of old-fashioned all-uppercase syntax.

Congrats. You've come a long way, baby.

Re:What's your opinion

[nuzak]

> Transactions are really bad for performance.

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.

> ... Why do you want to hold up the entire database when someone accidentally pulls the network cable on your PC?

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:What's your opinion

[turbidostato]

"Don't put any business logic into sprocs."

Do put critical enforceable bussiness logic (and I mean *bussiness* logic, not application logic) as near as possible to the data (and that usually means SP).

"The second that you place a business rule into a sproc, the users will want the rule changed"

That's true. And that's an advantage of stored procedures: they won't be able to change them (that's why I talked about Critical-Enforceable-Bussiness-Logic).

"Why is this bad? Because sprocs are almost always under the control of the DBA"

Why is this good? Because sprocs are almost always under the control of the most data management knowledgeable techie round there.

"most of the time there is a layer of politics involved between DBAs and Software Developers"

And that's good because that refrain them from their need (either because management pressure or pure ignorance) to do the thing the Real Stupid Way just because it's the fastest/the only way they know.

"What usually happens is that the dev will make the change to the business logic in the middle (or even worse, GUI) tier, thereby doing twice as much work"

So you say that such a bussiness logic should go into the middle tier instead of the RBDM because in that case the developer would do a stupid thing -like expressing it in the middle tier? This is both a circular argument and one more reason to put those kind of rules *out* of the reach of such a stupid developer.

"Adding Database servers to a cluster isn't cheap. If you have a lot of users, and you have had to cluster your servers to accommodate more users..."

Then you need to add more database horsepower. If you need to push or pull more data from the place the data is stored... well, you need it. No middleware is going to change that.

"Adding more middle-tier servers is much cheaper"

Yes. And buying a 1GB pen-drive is even cheaper than that, having in common with your "solution" that they both have nothing to do with the problem at hand. You either need to pull/push more data from where it resides or you don't. In the first case you always need more database horsepower; in the second you won't need it (and if cacheing is a good solution to your current problem then no, your problem is not that you need to push/pull more than provided and no, a cacheing solution is not a "middleware").

Resuming:
You will want stored procedures for your critical enforceable bussiness logic because:

*Developers tend to suck regarding data access/management (design, integrity or efficency); DBAs a little less so (it's the work they are focused on, after all).

*No matter how good your bussiness logic implementation on your new shiny app is, tomorrow it will be developed a new app that will access the same dataset (like the example you talk about people pumping data from Excel -presumably instead of through your pretty web app that tried to enforce this or that rule) and will surely break some bussiness logic or at the very least you will need to reimplement them again. If you don't want this to happen, enforce the rules as near to the data as possible so there's no way to overrun them.

*Your managers are crazy. It's much more probable that your new manager (or the old one if it happens to read the last shiny brochure from "PHB's IT") decides to rewrite all your .Net software to Java or the other way around than to move from Oracle to DB2 (if just because -as you already said, privative RBDM licenses and support are *very* expensive to play with them and they are buried more profoundly in the caves of the black teachies so they make for not as good PR as a new front end with a new brighter colorset and round corners). Go defensive and give your bussiness rules a sanctuary against crazyness within your RBDM (I now this seems to be against common wisdom: the most you program as SP, the most locked to the RBDM you are. But while that's true in theory, my experience in the Real World-TM is that more often than not it is the datastore the one that lasts longer than the apps over it).

*Anyway, your mileage is your mileage: think it hard, think it deep and reach your own conclusions.

Re:What's your opinion

[Samah]

I agree wholeheartedly on pretty much everything you've said here.

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

[shutdown -p now]

Transactions are really bad for performance. Really bad.

This was true for those RDBMSes which used locking, but now that more and more are moving to versioning (PostgreSQL, Interbase/Firebird, SQL Server 2005, and I believe Oracle), it changes. You get isolation level that is almost equivalent to ANSI SERIALIZABLE, and it's cheap as well.

Stred pocedures

[karavelov]

"The major commercial RDBMS vendors -- including Oracle, IBM, and Microsoft -- could point to this deficiency as reason enough to choose their proprietary sstems over MySQL or any other open source system, such as PostgreSQL..." They could not point this because PostgreSQL has stored procedures for decades... fully tested, stable, offering e bunch of languages for writing functions/triggers, including Perl, Python, Tcl and their own PlSQL that is very close to Oracle's variant.

The parent comment is a classic example of FUD

[Andy Tai]

Does it tell anything concrete about MySQL 5? No.

Re:The parent comment is a classic example of FUD

[cyphercell]

Does it tell anything concrete about MySQL 5? No.

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.

Deciding if MySQL is an option

[moore.dustin]

if((estimated_rows_in_table($table) < 100000)) {
return true;
}
else {
return false;
}

Re:Deciding if MySQL is an option

[gusx]

How about...

return (estimated_rows_in_table($table) < 100000);

Re:Deciding if MySQL is an option

[coyote-san]

Even that is generous. If you want to use a database as a simple store (and if they've fixed the tendency to hiccup and corrupt the database), MySQL may be acceptable.

If you want a DATABASE it's not even on the table, no pun intended. The whole point of a RDBMS is that it isn't a simple store, it's a mechanism for ensuring that your collection of data is always in a sane state even if you've brought in some clueless interns for the summer or you have a disgruntled employee. Or even if you just have two developers (or one developer with a poor memory).

ACID and referential integrity are two items that are absolute requirements. Calling something without these features a 'relational database' puts you in the same pointy-hair territory as Dilbert's boss saying that he heard the 'mauve' databases were best.

Attribute constraints, triggers and stored procedures aren't as necessary, but they're still extremely powerful ways of ensuring the sanity of your data. Use a trigger to update a 'updated_on' field, don't just trust the developer to always update it. Use an attribute constraint to ensure that you color field is always 'R', 'G' or 'B' (or just use referential integrity to point to a color table).

Finally, to address a question asked elsewhere stored procedures are extremely powerful security tools. By now everyone should know that using string concatenation to prepare queries is a Very Bad Idea. A potentially "don't let the door hit you in the butt on your way out" Bad Idea. Prepared statements are better, but how can you enforce it?

Stored procedures give you an alternative. Drop INSERT and UPDATE rights and force everything to go through a corresponding stored procedure. It's a little more work but it should eliminate any risk of SQL exploits. (It's not a 100% guarantee since you can't eliminate the risk that the database itself can be compromised by carefully selected parameters.)

Re:Deciding if MySQL is an option

[LordLucless]

Actually, if all you want is a big table, MySQL is probably a good choice. It's generally very fast. However, as soon as you start getting a complex database schema, you're probably starting to look at other RDBMSs. MySQL provides a lot of advanced features with InnoDB, but then, InnoDB has just been bought by their biggest competitor. If you're needing any of those features (foreign keys, transactions, etc), then I'd be going with PostGreSQL, because, really, I don't think the InnoDB team is going to be releasing any fixes/upgrades to their MySQL engine any time soon.

Re:Deciding if MySQL is an option

[consumer]

I'm currently looking at a table on a production MySQL db with 176 million rows in it. It has no problems at all, other than being a huge amount of data. Your ideas about MySQL's limitations are not based on fact.

Re:Deciding if MySQL is an option

[kpharmer]

> I'm currently looking at a table on a production MySQL db with 176 million rows in it. It has no problems at all,
> other than being a huge amount of data. Your ideas about MySQL's limitations are not based on fact.

Without partitioning you face the choice of selecting data only via a btree index (only typically works if you need to select less than 3% of the data) or scanning it all. With a more typical alternative (certainly db2, oracle, etc) you can partition the data. This can result in 10:1 differences in performance between mysql & db2/oracle for typical reporting queries.

Without query parallelism you're single-threading all of your queries, and unable to take advance of those extra processors. Since oracle/db2 get near-linear performance benefits from parallelism, you're again suffering a 4:1 performance penalty on a four-way smp.

Without a robust optimizer you will choke on complex queries against even moderate data - so a query that joins a dozen tables together will inevitately go into the ditch with poorly chosen nested-loop joins. Performance penalty? could easily be 100:1 in some situations.

Of course, this doesn't mean that you can't keep 200 million rows in msyql - you certainly can. Just don't plan to get the same kind of performance out of a lot of typically complex queries against it - that you would get from oracle/db2/informix or even sql server. Not unless you spend 20x as much on the hardware anyway.

> Your ideas about MySQL's limitations are not based on fact.
Unfortunately, your ideas about mysql's capabilities are based upon insufficient experience

Re:Deciding if MySQL is an option

[moore.dustin]

All of this is of course based on the scope of the project, but if you are serving up information to a browser, there is certainly a number of queries that will show MySQL's shortcomings. We can argue for ages about what can and cant work for this or that project, but all I was trying to convey was that for any project of significant size or importance, MySQL is not the best answer. It may be an answer, just not the best.

Re:Deciding if MySQL is an option

[Ant P.]

"Without partitioning"? What are you trying to say, that db2/Oracle doesn't have such a basic feature?

Re:Deciding if MySQL is an option

[Ecks]

Although I am a PostgreSQL proponent, I've got to say that mysql will happily handle > 100000 rows in a table. Especially if you properly normalize your tables. I ran a virtual inventory application on MySQL which tracked well over 100000 items (assigned and available IP blocks, atm virtual endpoints etc) for a medium sized ISP. I'm currently building a small decision support application. One of my tables has 8000000 rows in it. It's used twice in a query and that query takes no more then 0.1 seconds to execute under any conditions in MySQL.

-- Ecks

Re:Deciding if MySQL is an option

[JAlexoi]

I am currently looking at a table with only 17 million records.
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:Deciding if MySQL is an option

[Dirtside]

Unfortunately, your ideas about mysql's capabilities are based upon insufficient experience

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.

Re:MySQL vs Firebird

[PCM2]

Is there anyone here that have experiance with both of these databases that can explain in simple terms why one would go with MySQL over Firebird ?

My understanding is that Firebird is by any measure more sophisticated than MySQL, but it lacks the "critical mass" of users that makes it attractive to people who need to be sure they can get ready support for their software. MySQL is available on just about every cheap hosting provider around, too, which means that a lot of ready-built open source Web apps target MySQL as their database of choice.

Put it to you this way: If the features of MySQL are "good enough" for the application you want -- which arguably goes for most of the Web apps out there -- why would you not choose MySQL? There are times when swimming upstream is a noble effort, but generally all it gets you is tired.

If, on the other hand, you have a specific application for which you need a relational data store and you need higher-end RDBMS features, by all means, choose Firebird. Only I think in those cases most people still choose PostgreSQL, for more or less the same reasons as mentioned above.

Re:Stored procedures BAD... story

[dedazo]

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

Rest assured that if you were one of my developers you'd be out the door in no time. The application you're describing here cannot be anything north of trivial if you were able to just switch a connection string and all your messy inline SQL statements continued to run without any changes whatsoever (which I seriously doubt). Not everyone writes trivial applications.

There are reasons other than "fast" to using stored procedures. I've seen enough misuse of SPs, functions and triggers to fill up a book, but when done correctly they are simply superior to the alternative in just about every way.

Re:Stored procedures BAD... story

[Tet]

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

I'm glad I don't work for you, then. Stored procedures are a tool, like any other, that come with their own set of pros and cons. In some situations, the ability to quickly migrate to an alternate database outweighs the benefits that stored procedures may provide. In other situations, it doesn't. The decision to use them or not should be based solely on business requirements, rather than the irrational hatred you seem to be using as a guide.

Re:Stored procedures BAD... story

[DavidpFitz]

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

And you'd get sued shortly thereafter for unfair dismissal ... some manager you are proving yourself to be! A more pragmatic person in a management position would know that you use the right tool for the right job.

There are plenty of good reasons to use stored procedures in a database - they simply wouldn't exist in every serious database if they were not useful. Speed isn't the only reason to use a stored procedure, they can also make some inter-application integration much easier and allow someone in control of the data model to abstract the data collection routines from the underlying data structure - which can be very, very useful. I don't want some Java programmer thinking he knows the best way to get at data in Oracle - using a stored procedure to pass complex data structures back allows tweaking of the select statements, indexes etc... without any testing required of the app layer.

D.

Stored procedures and data integrity

[Tony]

The point of stored procedures is not to make access easier, or anything like that. The point from the RDBMS perspective is to keep your data coherent. Data integrity is THE MOST IMPORTANT role of a database. That's why 3rd normal form is important. That's why stored procedures and triggers and rules are important.

As a database engineer, I would *definitely* fire anyone who didn't use these tools to maintain data integrity.

Re:Stored procedures BAD... story

[drmerope]

The purpose behind stored procedures is not speed. It is about data integrity. Trigger functions are used to maintain additional coherence rules that are not easily expressed by unique, not null, referential integrity, etc. You place these rules in the database so that every consumer application of the database goes through the same logic--receives the benefits of the logic and enforces a coherent logic.

Re:Stored procedures BAD... story

[PCM2]

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

So answer me this: Now that you've made the investment to move to Oracle, can you reasonably foresee moving back to SQL Server? Seems unlikely to me. So if you'd chosen the right tool for the job in the first place, the migration wouldn't have been a problem and you would have been free to use other right tools where appropriate, e.g. stored procedures -- right?

Really, stored procedures have their place. Oracle tends to over-sell them, but to ignore them completely seems like one more step backward in the last n years of best practices and lessons learned.

Re:Stored procedures BAD... story

[RedElf]

Mod parent up!

Stored procedures have added benefits such as additional security, and forcing application developers to implement database functionality properly, not sloppily.

It isn't about speed

[Tony]

Stored procedures should be used to enforce data integrity and data access. The only "business logic" that should be there is the logic that applies to keeping your data whole, complete, and coherent.

Middle-tier application layers are great to help pull data together, present model-specific views of data (say, OO/R mapping, if that is what gives you a chubby), and provide update interfaces. They can even do a first pass at making sure the database will accept the data.

But.

The data logic should be stored firmly in the database logic itself, using stored procedures and triggers and rules. RDBMS engines are *designed* to keep your data in good shape. Use them in that fashion, and you will be plenty happy. Don't, and you risk losing data integrity, and you sacrifice security.

Names matter sometimes

[ClosedSource]

I think part of postgresql's problem is its awful name. Just about anything would have been better (e.g. TurboSQL, AgileSQL, FastSQL, UglySQL, FatDumbAndHappySQL, etc).

Re:Names matter sometimes

[Sloppy]

Ok, I'll fork it and call it OggVorbiSQL.

Not really FUD

[Anonymous Coward]

It IS a little out of date, but the MySQL forums were dotted with little gems from the programming team like "if MySQL doesn't do it, you don't need it".

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:Not really FUD

[elp]

Yep it was really irritating. A lot like the Postgres users who kept claiming the next version of their server was going to be really fast.

In the end the postgres crowd have a right to be upset. They could have been the major player for open databases but their devs were too arrogant to listen to their users and design it to be friendly.

For all its faults mysql is trivial to install and works out of the box for most applications. Last time I looked the default install for postgresql still seemed to be tuned as though it would never have to handle more than a handful of users. Stored procedures and TCP/IP are off by default and the auto vacuum thing needs to be set up manually. Then don't forget that while the postgres query parser does more error checking that mysql its error messages are incredibly cryptic so its MUCH hard to trouble shoot.

Your average newbie takes one looks at it, gives up and moves to mysql. When that newbie finally grows up he has too much time invested in mysql to be bothered learning the intricacies of postgres. Every extra feature that mysql adds is one less reason for anyone to try postgres.

Postgres is a better DB once its setup and tuned properly but considering its usability issues and that mysql users probably out number postgres users a thousand to 1 or more I think mysql is always going to be the number one opensource DB.

Re:Not really FUD

[cortana]

I have to say that you haven't checked out PostgreSQL for far too long. None of your criticisms apply to any vaguely modern version of it.

Re:Not really FUD

[gullevek]

the Postgres speed was true until the release of Version 8. There it might such a huge jump, that was just unbelievable. You should give it a try.

Re:Stored procedures BAD... story

[nuzak]

> As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster."

And your boss would fire you pretty fast. But I doubt you've ever fired anyone over it or even threatened it. Pretty much everyone who pulls out this "I would fire anyone who xxx" gem is full of shit, has never managed anyone, never should, and thankfully never will.

Re:Stored procedures BAD... story

[RedElf]

As a manager now, I would fire anyone who uses stored procedures. Even if it is "faster." As a database engineer I would refuse to work for such an incompetant manager who clearly doesn't understand the technology he is managing, but chooses to micromanage the implementation.

Re:Stored procedures BAD... story

[Anonymous Coward]

And if I were a manager, I'd fire ALL OF YOU for wasting time on a /. DBA pissing match instead of working.

Good one Ploppy

[Tim Browse]

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 long Winter evenings must just fly by.

Re:Stored procedures BAD... story

[Tet]

Now that open applications implement them, the open applications will collaborate and develop a standard. Then there will be a certain level of standards compliant SPs you can use and still not be locked into a vendor.

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 vs Firebird

[LurkerXXX]

Why would you go with MySQL over Firebird?

Easy. The nimrod who wrote the application that you want/need to run didn't make the app database agnostic, so you are stuck with MySQL because "it's more popular". It's a catch 22. Until more folks start writing database agnostic apps, lots of us will get stuck using MySQL in places where we might prefer other databases. And so that will perpetuate MySQL being seen as being more popular... Argh.

Re:Stored procedures BAD... story

[gnuman99]

How about data mining? You need access to the data. Developers are NOT the only people with access to the database. If they are, why the hell use SQL? There are much faster databases.

Re:Stored procedures BAD... story

[Shados]

Don't worrie, I am well aware of corporate software development. I am one of the primary developer for one of the largests non-software companies in the world :) We also have an army of DBAs and database-specific developers, and right now since it IS a stored-procedure-only environment, we have (just on the part I work with) several -douzans of thousands- stored procedure, that do everything you can imagine (including non-database related tasks). I've seen, or at least heard, most everything they can do, no worries :)

Optional parameters and parametrised order bys are the least of my worries here (though having a stored procedure with a dynamic amount of fields, multi-dimentational arrays of parameters, and dynamic order by clauses that allow to sort by a varying amount of fields end up being quite long to make, no matter the magic you apply to them. Easy to write, don't get me wrong, but...). And even with all that, I could just use, for example (on SQL Server), a CLR stored procedure, and even those problems would go away. Thats not the issue at hand.

The issue is that with a well thought out abstraction layer and automated scripts on top of the less critical parts of the applications, you can decouple your model and your database in ways that even a full layer (or two) of views and stored procedures simply can't do.

I mean, great: today my data is coming from 6 datacenters, using several different RDBMS, 2 different ETL technologies, and (thank god) only 1 OLAP system. Thats cute. If tomorrow I decide to change something in there....well, stored procedures aren't the most abstracted thing in the whole wide world. If I upgrade, let say, a SQL Server 2000 to 2005, well, the way to handle something as silly as filtering/paging efficiently changed. Now I have to go through the 4 thousand SPs that used the SQL Server 2k crippled way and optimise them? Hell no, that will probably be simply forgotten and never done. If it ain't broken don't fix it. If I have an SQL abstraction layer, I'll just change 2-3 functions, pass them through the unit and integration tests, and if everything comes up green the douzans of apps in my company will all take advantage of it by the next day.

On top of that, there's the notion that some things are simply easier to think of in an object oriented way, and it just feels real, REAL dumb to have 4 stored procedure for every god damn definition table (thousands!) in there. 90% of those won't be holding money amounts nor credit card numbers, and will be hiding behind locked down web services or remoting APIs -anyway-, so its not like anyone is getting direct access to the tables either. And if thats troublesome anyway (because of the risk of failure of these layers), you still have your views. And if thats still not enough, THEN of course (like in the example you gave), you go the stored procedure way, in which case the SP ends up being just an alternate datasource, the same way my ETLs and OLAP providers are. No biggy: even .NET's silly typed dataset allows you to switch between inline SQL and stored procedures completly transparently (though using inline SQL in a .NET dataset is unmaintainable, so thats probably a bad example :) )

What I'm getting at, is that (again, as your example shows), in corporate environments, doing without stored procedure is virtually impossible. No argument there! You -need- them. However, using ONLY stored procedures is simply a disaster waiting to happen, on top of being a hell of a waste of man hours. Some stuff is simply 10x faster to do using a well made SQL abstraction API. The important part is to always have a good way of using the stored procs that will come in.

Feature comparison checklists are so 1985ish

[ScrewTivo]

I'll have to put on my flame suit for this one only because so many interests are out here defending non-defendable territory.

Why does everyone love CSS for separating content from format .... then turn tail and try to put business logic into the database tier. Try moving a large MS-SQL app that utilized stored procedures to Oracle or MySQL. Tell the wife you'll see her next year!

Presentation - Logic - Storage. Clean and simple. Stored procedures and triggers are nothing more than DB-CRACK. Easy to use in a "pinch" yet keeps you addicted forever.

The concept of moving trigger/stored procedure coding out of the database engine is the scariest thought for Oracle/MS/IBM....yet it works surprisingly well in the freebie MySQL.

This subject is so deep and broad it leaves the mega dollar database companies room for more FUD the HS in NYC in 1905 (check that one out in your database).

SCALE THAT IN YOUR BOARDROOM!

Re:bullshit..

[00lmz]

The 'poorly choosen nested-loop joins' are the decision of the fucktard programmer. And if he is blowing his app out from fucked up loops how does the database lay blame for that?

Do you even know what a 'nested-loop join' is? Hint: It happens inside the RDBMS if the optimizer can't figure out a better way of joining two tables.

Re:The primary purpose of stored procedures

[LurkerXXX]

Bull.

Stored procedures are extremely useful for security, to reduce network traffic to the client or application server, to ensure consistency, to take advantage of database-specific features, and most importantly to ensure data integrity.

Re:MySQL vs Firebird

[PCM2]

Fair enough. But I would answer it this way: Anybody who spends a lot of time fretting over whether to learn Java or C# is missing the point. If you understand programming, it's not hard to learn both. And while you're at it, you should also pick up C and C++, in addition to Python and/or Perl. And whatever else you feel like. Experiment with things like functional languages while you're at it. These are all just tools. Some university-level computer science programs don't teach any programming languages. The language you use to implement your ideas is a secondary concern.

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 ... you're taught SQL. And since a lot of SQL is nonstandard, that means you learn PL/SQL or T-SQL. I'd argue that this is the wrong way to do it. There's a lot of science behind relational databases dating back decades, and DBAs too often ignore a lot of the best practices because they don't really understand relational theory and how databases work.

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.

MySQL vs PostgreSQL benchmark

[spatialguy]

http://tweakers.net/reviews/649/7 Tweakers is a dutch community of online tweakers that was deciding on new hardware. The above link leads you to some stunning performance graphs that show that not only postgresql 8.2 is faster then MySQL 5.0, it also scales MUCH better on heavy loads! This benchmark was discussed earlier on slashdot to some degree, but I think quite a few of the commenters above have not seen this. The tweakers community are longterm MySQL users and were new to PostgreSQL. However they even brought in a MySQL expert to configure it optimally. Still PostgreSQL turned out to be the better dbms

Re:case study

[rapiddescent]

also, if you put this level of detail (see parent) into a stored procedure then it is much easier to unit test the database separately and hand over reponsibility of that unit test to the "database team". It allows the database element to have a version on it's own independant of the source code.

I find that (in big teams, where multi-tier applications are being produced) it is far better to maintain tier separation and avoid having sql code in the app servers or worse, the client - because any minor database change has a ripple effect time and cost impact on other parts of the team.

also; choosing this methdology makes offshore development easier where, perhaps for security reasons the database team is local and the application developers are somewhere else. I've seen this where the dba's and the sql coders where local because the client had risky non-functional requirements and preferred to keep the database people local.

At work I use DB2 on Z and Oracle (including RAC) most of the time. Sybase seems to be getting rarer in the finance industry nowadays. I've never seen mysql or postresql deployed in a very large organisation (yet!). It would be nice to get slashdotters to say if they're using postresql or mysql in large organisations.

rd