Managing and Using MySQL: Second Edition

Geekboy(Wizard) writes: "MySQL has been used as a database for many sites and products. The U.S. Census Bureau, Slashdot, Yahoo Finance, and many other high-traffic web sites use MySQL for their database needs. It is a popular choice for databases, as it is GPL'd and thus free from costly licenses. If you need to use it in a proprietary application, you can purchase a non-GPL'd version from MySQL AB." Read on for Geekboy's review of Managing and Using MySQL: Second Edition, which he describes as "the only thing you /need/ to make a usable database." Managing and Using MySQL: Second Edition author George Reese, Randy Jay Yarger and Tim King, with Hugh E. Williams pages 425 publisher O'Reilly & Associates rating 9/10 reviewer Peter Hessler ISBN 0596002114 summary Step by step tutorial to using MySQL, with included tools, and your favorites.

Managing and Using MySQL: 2nd Edition assumes that the reader has a little knowledge of databases, although some of the commands are difficult to get your head around until you have a database to test them on. Sample commands, and output for them are shown in the book, but a sample database isn't shown until chapter 9. MySQL took me through 'MySQL land' with the greatest of ease. Aside from the lack of a test database in the beginning, everything was very well laid out. It started with the basics, and worked up to full implementation and administration.

Part I introduces the reader to MySQL. It begins with a nice history, design elements, features and what you would want to use MySQL with. Nicely detailed instructions for downloading, and installing the current version. Information is provided for Unix, and Windows systems, with examples for FreeBSD 4 and Windows 2000. Basic SQL commands are presented, with sample queries and relevant results.

Part II introduces the reader to tuning, securing and designing the database. Several pitfalls are described, along with instructions for avoiding them. The realm of tuning is divided into application tuning, database tuning, and operating system/hardware tuning. Security is discussed from all aspects, but a database that has security concerns will need extensive testing and evaluation. Database design starts with the design on paper, with both the theoretical, and practical aspects. Once the paper design is drawn out, MySQL assists the reader through the actual making of the database.

Part III describes integrating the database into your favorite programming/scripting language. In the opening paragraph of chapter 12, The C API, the author states: "In this book, we examine several different programming languages: Python, Java, Perl, PHP and C. Among these languages, C is by far the most challenging." The information was presented in a way that readers who are new to C would have small difficulties with, and that intermediate and advanced users would find quite useful. The authors presented enough information, that I was able to write programs that interfaced with MySQL in less than 10 minutes. Perl scripts are presented in the same way. Knowledge of the language you will be integrating MySQL into is necessary, but the book presents and explains the rest of the information.

Part IV is the reference portion of the book. All great books that teach contain a reference section, and this book is no exception. The basic SQL syntax and command set are described, as well as the data types, numeric, string, date, and complex. Operations and functions are explained, as well as their order preference. The PHP API, the C API, and the Python DB-API are also fleshed out in nice detail.

Overall, the authors have an excellent introduction to SQL databases, and MySQL. Full examples are included for each topic, with full explanations. The only things I would change, would be to have a sample database in the beginning of the book, and to have a copy of that database online.

Table of Contents
Part I (Chapters 1 - 4) Introduction
1 MySQL
2 Installation
3 SQL According to MySQL
4 Database Administration
Part II (Chapters 5 - 7) MySQL Administration
5 Performance Turning
6 Security
7 Database Design
Part III (Chapters 8 - 14) MySQL Programming
8 Database Applications
9 Perl
10 Python
11 PHP
12 C API
13 Java
14 Extending MySQL
Part IV (Chapters 15 - 20) MySQL Reference
15 SQL Syntax for MySQL
16 MySQL Data Types
17 Operations and Functions
18 MySQL PHP API Reference
19 C Reference
20 The Python DB-API
Index

You can purchase Managing and Using MySQL: Second Edition from bn.com. Slashdot welcomes readers' book reviews -- to submit yours, read the book review guidelines, then visit the submission page.

Comments

MySQL

[filth grinder]

MySQL would be ALOT better if it supported stored procedures and those sorts of things.

*shrug, but what do I know.

Re:MySQL

[the Man in Black]

I appreciate the mad dash for first post, but at least say something that's appropriate, like "MySQL would be better if it supported rollbacks and those sort of things". You know, something that makes sense.

What you refer to as stored procedures can be emulated with the greatest of ease in MySQL with a bit of Perl scripting and mod_perl. And as far as I know, stored procedures are currently on the to-do list for MySQL.

Re:MySQL

[smagoun]

What you refer to as stored procedures can be emulated with the greatest of ease in MySQL with a bit of Perl scripting and mod_perl.

That's nice, but most DBAs (that I've met, at least) don't speak Perl. They speak various dialects of SQL. Hell, a lot of engineers I know don't speak Perl. MySQuirreL needs real stored procedures, not workarounds. It's not "greatest of ease" if you have to learn a new language (esp. if that language is Perl!!)

Re:MySQL

[noda132]

MySQL 4.0 supports rollbacks. Even the alpha is very stable.

Re:MySQL

[Anonymous Cowrad]

This article isn't about MYSQL, it's about a book about MySQL.

Stored procedures are terribly simple to implement in whatever's driving your database, so there's really no point in bloating MySQL with them.

If you're going to point out the lack of transaction support, have a look at this.

If you're just going for early post E-Z Karma, well done.

Re:MySQL

[cpfeifer]

This is coming from someone who hasn't done big, multi-tier DB app development. Stored procedures, views, triggers and the like are crucial to keeping the persistance related logic in it's proper place - the database.

I like MySQL, but without a good object relational mapping tool (like Castora decent sized schema turns into an icky mess of code. On my last project I wasn't allowed to use Castor and ended up writing 3000 lines of JDBC code to support basic CRUD operations on business object on an 11 table schema. I couldn't use EJB's container managed persistance because the relationships were too complex and the schedule was too short.

Re:MySQL

[sql*kitten]

What you refer to as stored procedures can be emulated with the greatest of ease in MySQL with a bit of Perl scripting and mod_perl. And as far as I know, stored procedures are currently on the to-do list for MySQL.

Oh, and subqueries can be emulated with the greatest of ease in MySQL with a bit of Perl scripting too, but that's not the point. MySQL can execute very simple queries very quickly, but for complex database tasks - which need things like a proper SQL parser, stored procedures and triggers, check constraints, etc. Does MySQL even do foreign keys yet? Even if it does, they were bolted on as an afterthought, whereas most databases have them from day 1.

Re:MySQL

[noda132]

Does MySQL even do foreign keys yet? Even if it does, they were bolted on as an afterthought, whereas most databases have them from day 1.

This is rather... er... not researched. InnoDB, the new MySQL table handler, supports foreign keys and they were built into it from the ground up.

I won't pretend MySQL has all the features other RDBMS's have, but I will stick up for it when people bash it without even researching it. In certain places (websites come to mind) it is the #1 choice, even if money is no object.

Re:MySQL

[cygnusx]

InnoDB, the new MySQL table handler, supports foreign keys and they were built into it from the ground up.

You are right, InnoDB does support it, but there's no automatic indexing, and you have to convert old tables to InnoDB before you can use foreign keys on those.

Anyway, this MySQL doesn't have x thing is foolish I guess -- it's called _My_SQL for a reason, I'm not sure I want it to turn into BigDataCenterSQL :)

That said, for lots of apps, Oracle is massive overkill, and MySQL (or pgsql) is perfectly adequate. Right tools for the job, etc.

Re:MySQL

[highcaffeine]

Your statement shows you don't understand the real benefits of stored procedures. Say you have a very common task that, unfortunately, needs to work on a large set of data, but consistently results in only a few rows of data when it has finished going through all the data. Now, which of the following two options is better:

1. The original large set of data (say, 10MB) is transferred over the network to the front end machine which then runs through it's motions and trims that dataset down to the final 8KB of data actually needed. Aside from the IO, both machines need to reserve 10MB of memory to store the data set, plus additional memory for intermediary data structures while the code works.

2. The original 10MB of data stays on the database server, never being sent over the network to the front end machine. The stored procedure works on the original data culled from the database, does it's magic, and then transfers the final 8KB worth of data over the network to the front end machine. While the database server still needs to allocate 10MB for the dataset plus memory for intermediary data structures, the front end machines only has to allocate 8KB of memory for the final results -- plus, 10MB of data never needed to be transferred over the network.

If you answered #2, you've just given one example of why stored procedures can be a far better way to handle certain problems. They're no panacea, but "emulating" stored procedures in your front end application is a horrible way to justify not providing stored procedures in a database server.

If that example wasn't enough for you, consider the following.

You have an enterprise department consisting of a few hundred employees all running a GUI application which connects to a database to allow access to financial/customer/product/whatever data to all the employees. Various inquires in to the data need to compute values, collate data, or perform other complex operations on tens of thousands of ledger entries/customer accounts/products/whatevers.

Now, choose one of these two options:

1. You embed all, and I mean all (since you don't have stored procedures), logic relating to these inquiries in to the GUI application. You then update each workstation with the new version of the client to support the changes. This involves rolling out the new version to hundreds of machines, causing worker downtime for each machine (or a few very late nights and expensive overtime for your IT staff). Then you find out a few days later that your QA staff didn't catch a very problematic bug that affects half the staff. It turns out the bug was very easy to fix, but you now have to redeploy the updated application to the hundreds of machines -- again.

2. Your GUI application does not contain any of the logic relating to munging/collating/etc. the data in question. These are contained as stored procedures inside the database server. Your client application, installed on hundreds of machines, simply call this procedure on the database. You update the stored procedure and instantly all clients are now using the new version. A couple days later, you find out QA didn't find a problematic bug. Turns out, it was easy to fix, and in minutes all of the hundreds of machines now use the fixed version of the procedure.

As my last example, how exactly do you propose to be able to create effective triggers without some form of a stored procedure? Triggers are a wonderful feature (also lacking in MySQL) that go hand in hand with stored procedures. But, I've already been long winded enough in this post, so I'll wrap up.

Are you starting to get the picture? If stored procedures weren't so useful, nobody would want them. The real problem is that people who don't understand databases or database application design *think* that stored procedures can always be effectively "emulated" in the client layer, and so they *think* that stored procedures aren't useful.

Re:MySQL

[Bake]

Actually, no.

When you use a stored procedure it's nearly always treated as an atomic action with regards to locking and rollback conditions, even though that very same stored procedure inserts and updates many tables/rows at once.
You also get the added bonus of an optimized action when using a stored procedure.
Now, I don't know what kind of a guru witchcraft programmer you are, but a stored procedure will most definately play nicer with DB locking, rollback conditions, system load etc. than if the same activity were programmed into the DB application.

Re:MySQL

[micromoog]

Beautiful answer. Now if we can just get these cretins to understand why views, subqueries, and transactions (not this "atomic operations" BS) are important too, we'll be getting somewhere.

Re:MySQL

[afidel]

1.)No, you do a proper three tier design with a presentation layer, a business logic layer and a database layer. This way if everything is properly designed and implemented then any piece changing should not effect the other 2 pieces. With the cost of liscenses per CPU for DB2, Oracle, and MS-SQL being so expensive why would you do any more processing on the database side then you have to to get acceptable system performance? Networking equipment is cheap, you can buy a hell of a switch for what a single cpu liscense for Oracle costs, and so are middle tier boxes, use linux or small sparc boxes in some kind of load sharing arangement.

Are you starting to get the picture? If stored procedures weren't so useful, nobody would want them. The real problem is that people who don't understand databases or database application design *think* that stored procedures can always be effectively "emulated" in the client layer, and so they *think* that stored procedures aren't useful.

Actually I think people that overuse stored procedures are almost as bad =)

Re:MySQL

[mark_lybarger]

PostgreSQL has really tightened the speed gap to where it is almost non-existant. The feature gap that is still there (albeit there are some "cleaver" work arounds) are a major reason to stick with PostgreSQL. Somehow the former speed issue did cause many many tools to be developed around and for MySQL, so in that respect it might be a favor. It's also available more easily on the win32 platforms.

Re:MySQL

[rjamestaylor]

(albeit there are some "cleaver" work arounds)

These would also be known as hacks, right?

Re:MySQL

[highcaffeine]

That would alleviate some of the problems, but it does not solve all of them, and it even introduces new ones.

What it does solve is the deployment problem of updating hundreds of client applications. You would only need to update this intermediary server that sits in between the clients and the database.

What it does not solve is the fact that you will still have to transfer all data from the database server to another machine before you do any work on the data. And you still need to allocate effectively twice as much memory as you would if you just kept the data on the database server and used stored procedures when appropriate.

However, here are just a few of the new problems your solution introduces:

Your clients no longer just connect to a database server. They're connecting to a piece of middleware which will broker all connections. This can be an advantage for some types of applications, but it is more difficult to implement and could very well increase your code base significantly. Not to mention, if your programmers don't have the experience implementing solid middleware solutions, your setup could be very inefficient.

What about all the database queries that don't need the stored procedures? Your solution gives two options. a) Each client maintains one connection to the database server directly and one connection to the middleware server. b) All requests go through the middleware. The first option introduces new complexities in to the code, and puts more of the responsibility for deciding when to query the database and when to use a stored procedure on the software developers, not the database designers. In general, that is not a good thing, unless your programmers are also DBAs (or equivalent/better knowledge).

The second option is terribly inefficient. You now have to transfer all the data from the database server to the middleware, then from the middleware to the client. At least twice as much traffic as you should be generating for cases where the middleware didn't actually need to do anything to the data itself.

I'm not saying that middleware is inherently bad. It is a very appropriate (if not exclusive) solution for many problems. But, to say that you don't need stored procedures because "we can stick another server in between the clients and the database" is a very poor solution.

Re:MySQL

[Osty]

They might know one of them. It's better to use something there are standards for. It's better to separate data storage from data manipulation. Just because Java isn't a very good language for handling data, or perl may not have the prettiest syntax doesn't mean you should try some archaic proprietary obscure weak scripting language that is compiled into the database. Someday there will be a decent API for data manipulation, and it's going to be through mysql if it ever comes.

Sure, you'll believe that up until the point where MySQL finally hacks SQL-based sprocs into their little database, and then you'll love it. You might want to sync up with the MySQL dev team, so that you'll know when they're ready to implement SQL-based sprocs so you can change your argument.

Transactions shouldn't be built into the database either. There should be a transparent API (that maybe uses temporary tables for temporary storage), but transactions as they exist in Oracle or a huge waste of memory, and suprize! -- one of the biggest failure points for data corruption.

First off, let's just throw away 20+ years of database research (both academic and commercial from places like Oracle, IBM, and Microsoft). Obviously you know better, it seems. However, that aside, of course most transaction implementations "waste memory" (if you're going to be running a serious database, you won't have any problems getting 2 or 3GB of RAM). That memory they "waste" is used basically as a scratch space. All data manipulations are done there, so that when you get that commit tran call it can all be written to disk. Or just as easily thrown away when you have to rollback tran. Yes, that can get nasty when you're doing large updates, or have a long transaction. That's the point where it helps to understand how the database you're using actually works, because then you can work on making your code perform better. As far as transactions causing data corruption ... well, not that I've seen. Maybe with MySQL, because their transaction support is just a hack, and it's still pretty flakey and new. I wouldn't judge transactions in any other real RDBMS by MySQL's transactions.

Re:MySQL

[highcaffeine]

You are definitely correct that proper design is the way to go. I would never argue against that. However, my original point is that there are certain problems that are better solved by stored procedures. If you can avoid sending out a several dozen MB each time a query is run by spending an extra second on the database server to do some additional processing, and if it supports your business goals, I would say that is a situation that could benefit from stored procedures.

And quickly, as for your last comment, I also agree. Overuse of any feature is not a good thing -- even if it's a feature you like. You might be surprised since it may seem that I am advocating the use of stored procedures more than you may like, but I have not used a single one in the past year in any of my applications (which support tens of thousands of users each day and are mission critical for our business). Why? They weren't appropriate solutions to any of the problems.

What I am arguing against is the notion from some people that stored procedures are *never* needed because they can be "emulated" in the client layer which is untrue.

Re:MySQL

[Ioldanach]

MySQL can execute very simple queries very quickly, but for complex database tasks - which need things like a proper SQL parser, stored procedures and triggers, check constraints, etc.

And just to add my $0.02, it also is in desparate need of subqueries. I can't do WHERE x IN ( SELECT y FROM foo WHERE x=z ) for example. Granted, that's a trivial case that can be reduced to a simple SQL statement, but the subselect functionality is of critical importance in some SQL queries, especially since you can't emulate them with an outer join if you're doing a DELETE.

Re:MySQL

[plumby]

Stored procedures are terribly simple to implement in whatever's driving your database

Apart from the fact that it wouldn't really be a stored procedure then.

The point of stored procedures is that the execute inside the database, allowing them to run much more quickly than external procedures calling into the database for every row. They also allow improved security, as all access to things like updates can be removed from calling applications, and then the app can call stored procedures that allow certain, controlled, modifications to the database.

I'm not knocking mySQL for not having them. I think it's a great application, and I have used it for developing a commercial prototype of my company's web site. But don't be blind to its limitations (and there are several). Stored procedures, along with proper security, transactions etc, are vital ingredients for any serious enterprise RDBMS.

Re:MySQL

[EastCoastSurfer]

proper three tier design with a presentation layer, a business logic layer and a database layer.

Who says that each tier must be on a seperate box? When you use stored procedures you are really using a three tier design with the business layer and the database layer on the same box. Sometimes this is the preferable way to implement the 3 tier system because of the performance increase you will get with stored procedures.

Re:MySQL

[neuroticia]

How does this have *anything* to do with a BOOK written about MySQL?

This comment would be ALOT better if it was about the book or about the review. ;)

-Sara

Re:MySQL

[MattRog]

Good answer. Also one niceity is that the query is pre parsed/compiled/planned/etc. so you do not have to waste time re-generating query plans for individual SQL, so there is a performance benefit from that when you have many SQL statements being executed.

Re:MySQL

[consumer]

Say you have a very common task that, unfortunately, needs to work on a large set of data, but consistently results in only a few rows of data when it has finished going through all the data.

You say that as if it were a common thing. I can't think of why you would ever need to do that unless you were doing a join in your application rather than in the database, which would be a mistake. There are rare occasions when a stored procedure can be useful, but this one is so rare it doesn't bear consideration.

Re:MySQL

[sheldon]

We have numerous examples within our company where processes that took an hour to execute in the three-tier disconnected fashion were reduced down to under a minute by the proper application of stored procedures.

If you follow your logic, in many cases you *SHOULD* be using stored procedures, because they will decrease the load on your database.

It all depends, and different solutions should be evaluated for their impact to systems as well as end user performance.

Let's get this out of the way right now

[Anonymous Coward]

Right, MySQL story. To save everyone else the trouble, I'll go first.

MySQL rocks!!
No it doesn't, PostgreSQL rocks!
MySQL is faster.
No it's not.
Yes it is.
Use Oracle if you want a serious database.
I run a top-5 US bank with MySQL on a P166MMX and it runs great why do you need Oracle?
MySQL doesn't have transactions yet!
Yes it does
No it doesn't
PostgreSQL rocks!
No MySQL rocks!

Re:Let's get this out of the way right now

[jayhawk88]

You forgot to flame MS-SQL. I believe the correct position would be right after the Oracle zealot comment.

O'Reilly

[Beatbyte]

I've been reading about 100 pages a night of this book and its awesome. Personally I'm using it for web applications and if anyone of you are doing the same, also pick up Web Database Applications with PHP & MySQL (ISBN: 0-596-00041-3). Complimenting each other in the very best way.

Every time I read an O'Reilly book it reminds me of why they're the only thing in my book case (of the computer nerdish-tech know how sort).

Awesome stuff.

Re:O'Reilly

[Geekboy(Wizard)]

When I started on this book, I knew nothing about MySQL, PHP, or similar. I now have a (somewhat) working MySQL database with a perl script accessing it, and a C prog administering it, in about a month. This book, and the K&R book on C is all I used. The O'Reilly's is friggen awesome!

Re:O'Reilly

[JabberWokky]

Hey, does the book cover replication? I'm looking to set up MySQL in a master-slave replication for a "warm backup" of sorts.

Every time I read an O'Reilly book it reminds me of why they're the only thing in my book case New Rider is also great - O'Reilly and New Rider are just about the only thing on my sysadmin/programming bookshelf (NR's Essential References are great).

--
Evan

Re:O'Reilly

[smack.addict]

No, it does not cover replication.

guh.

[siliconwafer]

There's plenty of documentation out there for MySQL. Seems like the last thing we need is another MySQL book.

How about a PostgreSQL book? I stopped using MySQL awhile ago for my high traffic site, as its lack of features (subqueries, views, triggers) proved insufficient. Postgres rocks!

Re:guh.

[elmegil]

It's not "another" MySQL book. It's a second edition of what was formerly MySQL & mSQL. Significantly revised to focus on what people are really using, and reorganized to make it a lot more clear than the first edition. I have to say I was happy to buy this edition to replace the old one because it's a much better reference.

Re:guh.

[Kamel Jockey]

O'Reilly has a book on PostgreSQL...

I was flipping through this book during my last trip to the local bookstore. I have to say that for the money involved, I really was not too impressed with it. It did not have anything there that the Postgres docs didn't already cover, and many of the examples were trivially easy. Now while the docs have the same problem, they are still free :)

I'm using Postgres extensively for development at work and I still use a little MySQL at home for some unrelated projects. Each one has its place, its not fair to pit the two against each other.

Best Win32 Front End I've Seen

[WellHungYungWun]

http://www.anse.de/mysqlfront/ This is hands down the easiest tool for constructing databases. I use mysql for keeping track of millions of records, and this tool has proven stable. It is an "Enterprise Manager" like gui that could make a pointy haired boss a mysql guru in no time. My .02

Re:Best Win32 Front End I've Seen

[SirSlud]

Indeed, MySQLFront is a real time saver.

Now if only you could use it to whack the whiners ("MySQL doesn't let you use transactional inserts using subselects in stored procedures! Cry!") over the head to knock some sense into 'em.

Re:Best Win32 Front End I've Seen

[plumby]

A lot of the "whiners" are people that are pointing out that, although mySQL is a great application, it doesn't have the features required to be used as a serious database in any large enterprise.

You may be thinking "Well, duh, it wasn't designed for that. Stop whinging", but having seen several posts where people have claimed that transactions, stored procedures, etc are not important for a "serious" database, I think it does need to be pointed out.

Like I say, I'm not knocking mySQL for not having those features. It's a free tool, that does what it does very well, but a lot of people seem to believe that it's capable of fulfilling a role that it isn't.

Web application security

[ppetru]

Does anyone know of good references that cover the security of web applications from the ground up?

Yes, try the Open Web Application Security Project. They released a very informative paper on building secure webapps, and it's free.

(I'm not affiliated with them in any way)

Re:Front End ?

[nomadlogic]

check out the openoffice front end. there was a posting a little while ago about that. basicly it's a DB frontend built into openoffice. i've already started using that to convert ASP/Access junkies ;-)

Re:Front End ?

[reaper20]

Use an ODBC connection to connect to MySQL, then you can use whatever frontend you want.

I have a client who insisted on using Access for their db. Set up the client boxes with MyODBC, set up the data sources in windows, they connect, blamo, as far as they're concerned, they're using access.

Great, now we're going to have a war...

[Wee]

I can hear it now: "MySQL sucks", "It doesn't have triggers|rollbacks|stored procedures|nested selects", "It isn't PostgreSQL|Sybase|Oracle", "It's not a real RDBMS", "It uses a bastardized version of SQL", "It stole my woman", "It owes me money", "It called the Pope bad names", blah blah blah. Guess what? It doesn't have those "features" for a reason! MySQL was meant to be fast and small, that's it. It was meant to fill in the gaps left by mSQL and to drive web sites (quickly). It's a feature-rich, glorified flat file, ok? And it fills its intended role quite well.

You can't even mention MySQL without the know-it-alls coming out of the woodwork. You'd think the mere mention of MySQL offends their sense of personal or national pride or something. If MySQL isn't your taste, doesn't meet your needs, isn't robust enough, whatever, then don't use it. Use PostreSQL or another RDBMS. Why people continue to begrudge other's use of MySQL is beyond me...

-B

Re:Great, now we're going to have a war...

[MattRog]

I would agree to that if it wasn't the de-facto open source DBMS, and if the MySQL marketing-machine didn't *scream* otherwise.

Re:Great, now we're going to have a war...

[PapaZit]

It's a feature-rich, glorified flat file, ok? And it fills its intended role quite well.

Unfortunately, a flat file fills the "flat file" role even better. ;)

Re:Great, now we're going to have a war...

[Captain Large Face]

Simplified,

SELECT complaint FROM whining_bastards WHERE subject = 'MySQL'

Re:Great, now we're going to have a war...

[zulux]

Hear hear!

If MySQL goes the way of PostgreSQL, there won't be any point to MySQL.

I hope MySQL continues to play to it's strengths - simple, fast and easy to use.

If someone bereates MySQL because of it's lack of ACID features, they are obviously a one tick pony that can only grasp one idea at a time. A good programmer,db-admin or carpenter is able to choose the best tool for the job - and doesen't force a tool to do somthing it wasen't designed for.

Re:Great, now we're going to have a war...

[Reality Master 101]

Exactly right. It wouldn't offend me at all if we didn't have people coming on here bragging how they wrote the package to handle financial transactions for their company's web site using MySQL. "Well, it works, so who needs transactions?? Hyuck yuck!"

It's worse than some Microsoft DBA bragging that "who needs C++ when we have Visual Basic?"

Re:Great, now we're going to have a war...

[gillbates]

Ok, granted. Problem is, business systems need a good database on the scale of DB/2 or Oracle. A toy database built by volunteers just won't cut it. Granted, it might be good enough for a web backend, but it's not enterprise class software.

I believe my company would be open to replacing their aging mainframe with a few Linux boxes. But the problem is that there are no free software databases that work as well as Adabas or DB2 do on the mainframe. If the free software folks would get serious about database development, then perhaps Linux could make real inroads into corporate America, and move from the server room to the enterprise datacenter.

Re:Great, now we're going to have a war...

[Wee]

If MySQL goes the way of PostgreSQL, there won't be any point to MySQL.

I know this might seem shocking, but I agree with you completely. :-) Very well said. I use MySQL at home for small stuff: calendars, MP3 data, that sort of thing. My hosting provider, like many others, has it installed as well, so that's a handy "feature" for me. I write an app for home use which can move to the outside world if I want it to. I also use it at work since it's ubiquitous there as well. Again, using it at home makes writing apps which can move around much easier. (Although I use DBI and ADOdb, so portability isn't much of an issue except for my own spinal macros.) I've also used PostgreSQL as well. We needed its features, so that's what we used. But for lightweight stuff, MySQL works fine. Anyway, like you say, use the right tool for the job.

BTW, I found a good comparision of open source databases that some people here might be interested in.

-B

Re:Great, now we're going to have a war...

[Christopher Thomas]

Guess what? It doesn't have those "features" for a reason! MySQL was meant to be fast and small, that's it.

The problem is that without row-level locking, the "fast" part kind of goes away when you start getting substantial traffic.

I'm told that's in development. It still seems like a bit of a kludge, as the original architecture was built without intent to support things like this.

I don't see why sites don't switch to Postgres when they run into the locking problem. Machines are powerful enough now that processing power isn't the problem it was when mySQL was invented. Why not use tools in the domains where they're best suited?

Re:Great, now we're going to have a war...

[zulux]

The comparison you found is great!

I know I'm preaching to the choir but..

The whole debate has analogies to other aspects of life - I love French food, but the world would be a sorry place if French food completly displaced Jamacian food or Thai food. Grizzly bears are beautifull animals, but I would want them to dsplace the common house cat.

When people ask me if they should learn C++ or Java, I tell them both. And maby a bit of Lisp for good measure.

I'm very happy to have MySQL in my toolbox, it fist nicly between Perl driven text files and PostgreSQL.

Re:Great, now we're going to have a war...

[kcbrown]

I don't want to start a flamewar. I really don't -- this is a perfectly serious question:

What are the advantages of using MySQL over PostgreSQL?

Seriously, what are they? I really want to know. It's important, because the more use a database gets, the greater the amount of support it gets and the more useful it becomes. So if there are some truly significant advantages to using MySQL over PostgreSQL, then it would be useful to know them so that PostgreSQL can be improved in those areas. So what are those advantages?

Speed? Perhaps. But what I've read indicates that PostgreSQL is faster under a heavy load. Under what additional situations would the additional speed be the deciding factor?

What features does MySQL have that PostgreSQL does not? I'm especially interested in those features that would make the difference in the decision to use MySQL over PostgreSQL.

My experience with both PostgreSQL and MySQL is that MySQL has the following advantages:

1. The administrative interface is nicer (I especially like that the access control is done entirely with tables within the database), and just seems like less of a kludge. But that may simply be because I used MySQL before PostgreSQL.
2. There are more ways to manipulate the database schema than in PostgreSQL (you can drop table columns easily, for instance). This advantage is mitigated somewhat by the fact that administrative functions in PostgreSQL can be performed within a transaction.

So what other advantages does MySQL have over PostgreSQL?

Re:Great, now we're going to have a war...

[smack.addict]

Personally I prefer PostgreSQL because MySQL uses a bastardized version of SQL.

No, it does not. It uses ANSI SQL 2, entry level. It actually goes further than entry level. It, like PostgreSQL and every other database engine in existence supports proprietary add-ons. These are useful to DBA's, but terrible for programmers to use.

I wouldn't even call MySQL a "database" since it doesn't have triggers, rollbacks, stored procedures or nested selects!

You may not call it one for those reasons. However, you would be speaking your own little private language. First of all, being a database has nothing to do with those factors. Being a database is only about storing data. NTFS, your filing cabinet, a card catalog, and OpenLDAP are all databases.

Furthermore, MySQL does have rollbacks and will soon have support for triggers, stored procedures, and nested selects.

Re:Great, now we're going to have a war...

[gillbates]

So what's the problem with your company using commercial databases on Linux? Nobody said (well, not nobody) that a free OS must run a free DB.

True, but that would put us back in the situation we're in now, where we are at the mercies of the vendor every time we need to upgrade. Basically, the problem is that unless we own the software, we are at the mercy of the vendor who may either:

1. Discontinue the software in the future.
2. Force us to upgrade (Yes, this happened to us).
3. Increase/Restructure the licensing fees so that we pay a per-processor-cycle or per-month charge to lease the software. Incidentally, they tend to do things like this after a company has been using their product for a while and built up a large codebase. They'll charge just marginally less than it would cost to convert to another vendor's database.
4. Charge a fee for every line of code the programmers hired by the company write. Yes, there are licensing agreements which effectively do this; not only do we pay for the database, we have to pay the vendor royalties for every line of code we pay programmers to write.

As you can see, we've had some big problems with proprietary software - problems that can only be addressed by using free software.

[OT] Can anything get a rating besides 9?

[ajakk]

I thought it was just me at first, but do any books that get a rating that is not 9 get posted? I went to the book section and checked out the most recent reviews. Of the last nine books reviewed, eight of them got a nine, and the other got five stars (out of five I assume). The two book reviews before that (both in the Java meets XP subject) got 7's.

I would like to see someone review a book that isn't very good, and tell us why. It almost seems that these great book reviews are being pushed so someone can make some bucks through an affiliate program.

Re:[OT] Can anything get a rating besides 9?

[Christopher Thomas]

I thought it was just me at first, but do any books that get a rating that is not 9 get posted? I went to the book section and checked out the most recent reviews. Of the last nine books reviewed, eight of them got a nine, and the other got five stars (out of five I assume). The two book reviews before that (both in the Java meets XP subject) got 7's.

Evidently people only feel the need to write a review for books they found very good :).

This is probably for the best. A book being bad or mediocre isn't surprising - it's the good books that are hard to find.

The only exception I can think of would be a book that you'd otherwise expect to be good (e.g. the O'Reilly on MP3s, which is an outdated overview of MP3 playing and ripping programs, as opposed to the detailed analysis of the guts of an MP3 codec that I'd expected).

Re:[OT] Can anything get a rating besides 9?

[babbage]

I heartily agree -- book ratings, particularly for anything with an animal on the cover, are *way* overrated on Slashdot. This book in particular could have been an interesting case study in whether the situation is getting any better, because the book in question is an update to the previously titled MySQL & mSQL -- and it was absolutely awful. Don't let the review of that book fool you -- it was without question the weakest O'Reilly book I have come across to date. And yet, if all you knew about it was the Slashdot review, you'd have thought it was yet another winner from the fine team at ORA. Wrong.

At the time I read it, I didn't know much about MySQL, but I had finished my battery of database design classes and I knew a little Perl & Python, and it was obvious that the book was riddled with errors, from poor explanations of normalization in early chapters to Perl scripts that, had the reader been so industrious as to type them in verbatim, wouldn't even compile, nevermind produce the intended result if the syntax bugs were ironed out.

To be fair to the generally excellent staff at O'Reilly, I'm sure there were later editions that ironed out many of those flaws, but the fact that not one but two Slashdot reviewers gave the book high marks says a lot more about the quality of the reviewers Slashdot is able to produce than it does about the rare splot on O'Reilly's otherwise fine record.

A year or so ago, the authors of the current rewrite of the book were soliciting peer feedback on the Perl section in particular, because of the bad reputation the original version got . I'm sure they worked very hard to make a better book this time around, but did they succeed? Who knows? This reviewer makes no mention of the original edition, and apparently doesn't realize how awful it was.

I'm interested in the update, but unfortunately will not trust this review in its assessment. I wasted thirty bucks on the last version, regretted it, later found the New Riders book, titled simply MySQL, and was for the most part happy. The Perl sections there are a little odd -- this author's code doesn't feel very idiomatically "native" to me, more like things a long time C hacker would prefer -- but as a reference & manual it is far better than the first edition of the O'Reilly book. I hope that with this edition they're catching up, but as far as I'm concerned the definitive reference manual for MySQL is already out, and like it or not there are no animals on the cover. Even if you wouldn't realize that from the reviews you see on Slashdot....

Re:[OT] Can anything get a rating besides 9?

[ryantate]

True, except for when there's a new CD coming out that many people are going to consider buying because it's from an established artist. In that case, it would still make sense to print a reviewing trashing the CD, to save people time and money. This is why Greil Marcus took the time to write in Rolling Stone, of Dylan's Street Legal, "he's never sounded so utterly fake." ('Changing of the Guard' wasn't too bad though.)

Likewise, it would make sense for Slashdot to tackle books it knows readers will be curious about, like new editions of the Camel and so forth, even if they do not warrant glowing recommenations.

Re:if only....

[mustangdavis]

Seriosly, stored procedures would be nice, but they would take away MySQL's edge on Postgres .... and that is SPEED!!!!!! I hope they NEVER put in stored procedures!!! It would bloat things too much and make MySQL = Postgres 9or worse) in every regard! MySQL could use nested selects and such (for those times when I'm feeling lazy and want the DB to do all te work for me), but stored procedures would kill MySQL!!!



To everyone concered about stored procedures: GO USE POSTGRES!!!!

To everyone that needs a FAST database:Use MySQL!!

To everyone that has too much money: Go buy Oracle

I hope this ends this silly string of people whining about stored procedures.

non-GPL version

[Enry]

you can purchase a non-GPL'd version from MySQL AB

Yea, until MySQL AB decides they don't want to do business with you anymore. Then your commercial version becomes GPL'd and they take you to court. NuSphere licensed MySQL from MySQL AB and look where it got them.

"the only thing you need"

[MattRog]

Sound? Correct? Well-thought-out? Standardized? Scaleable?

Not so much.

Re:"the only thing you need"

[MattRog]

As a follow-up, people who think they can gain all the knowledge they need from a single book are fooling themselves.

If you're looking for GOOD RDBMS DEVELOPMENT documentation (books, manifestos, etc.), take a look by anything by Codd or Date. You should also check out www.dbdebunk.com

Re:"the only thing you need"

[micromoog]

Codd or Date would be appalled that you're using MySQL in the first place :)

Re:"the only thing you need"

[MattRog]

LOL how true that is. Although at DBDebunk.com they have lots of critisims (well earned, of course) for pretty much every RDBMS out there, although I bet they wouldn't even give the time of day to MySQL. :D

Re:"the only thing you need"

[MattRog]

"the only thing you /need/ to make a usable database." is what you said. I argue that by some quirk of fate or due to something insanely simple it *may* be useable, but without good relational study and analysis you will NOT design a good database.

What the heck does Joe User need to know about performance tuning (which first and formost needs to be tackled in database and SQL design), security, extending MySQL, 6 different APIs, etc. if he doesn't need to know about designing a good database structure in the first place?

Re:"the only thing you need"

[smack.addict]

The book covers good database design and database application design. It covers normalization, denormalization, object/relational modeling, and distributed application architecture.

GLP Question...

[zulux]

If you need to use it in a proprietary application, you can purchase a non-GPL'd version from MySQL AB.

If I have a propriatary app that uses MySQL or PostgreSQL - is the database server part of my app as far as the GPL is concerned?

My gut reaction is no, due to the fact that you're communicating via SQL over a port and not via function calls. Also, the database is not statically linked - this seems to have been the litmus test for other GPL questions. In addition, the database server can, of course, be on a saparate computer.

If I'm wrong, please enlighten me!

Re:GLP Question...

[mark_lybarger]

ultimately, it depends on how tight your app is with the database. if you're app doesn't function properly without that database, then you've gotta release the source, under the GPL.

typically, you've gotta have some functions in your app that are calling the database. those functions are what communicate to the database. weather those are staticly linked, or dynamically allocated at run time makes no difference. I believe the PostgreSQL libs (for accessing via other languages) are LGPL, so you don't have to realease your source for those.

alternatively, you could use an abstraction layer, ( i'm familiar with ADOdb for PHP) which gives your application database access which can be configured through config files. plug in any database and it should work. under that kind of relationship, you're under the hold still of the libs you're using and maybe you can find one that doesn't force you to release your code.

weather or not the server is on a separate machine, communicating via a port makes no difference. x11 communicates over ports over separate machines, but if my application uses x11 libs, i've got to abide by their licensing scheme.

Re:GLP Question...

[mark_lybarger]

i stand corrected, they're fairly liberal with their code use:


"PostgreSQL is subject to the following COPYRIGHT:

PostgreSQL Data Base Management System

Portions copyright (c) 1996-2002, PostgreSQL Global Development Group Portions Copyright (c) 1994-6 Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
...


lameness filter

Re:GLP Question...

[Micah]

If I have a propriatary app that uses MySQL or PostgreSQL - is the database server part of my app as far as the GPL is concerned?

Apparently not. Here is what the licensing policy says:

[You need a commercial license if...] You have a commercial application that ONLY works with MySQL and ships the application with the MySQL server. This is because we view this as linking even if it is done over the network.

So if it works with MySQL or Pg, OR if you ship the app seperately from the MySQL server, you're apparently exempt.

However, I believe MySQL AB has an incorrect interpretation of the GPL here. "Linking" has never been defined as being over the network in any other GPL context, and you can clearly ship GPL and non-GPL software in the same "distribution" (Linux distro or any package of software) without violating anyone's license as long as they are not linked in the same executable.

Also, how can the view it as linking if it ONLY works with MySQL but not if it ALSO works with PG? That's weird, and I see no basis in the GPL for that.

MySQL is free to license as they please, but they shouldn't say it's GPL and then add restrictions that aren't in the GPL.

So I don't know what would happen if I wrote a non-Free MySQL only app and shipped it with the server. Probably not a good idea to try it and find out though. :-)

all you need

[PapaZit]

...the only thing you need to make a useable database

With that criteria, wouldn't cat, awk, and grep be all you'd need?

Unless you're worrying about the ACID properties that most people use as the minimum criteria for a database. Oh, wait. SQL doesn't have any of those.

Re:all you need

[smack.addict]

Can you reference that as an accepted definition of a database?

A database is just a collection of data. There are a billion different kinds of databases, some ACID, some not.

Re:all you need

[PapaZit]

I don't have it in front of me, but I believe that it was used in Date's book as the minimal criteria for a RDBMS.

Search on Google for "database acid test" for plenty of other references.

Re:all you need

[smack.addict]

First of all, you changed the topic from database to relational database.

Second of all, most RDBMS's out there are not fully ACID; at least, not as deployed in most environments. Thus, by your arguments (which you have made none since you have not really offered up and citations showing this definition), there are no RDBMS's.

Something Slashdot should run....

[Lord_Slepnir]

SELECT * FROM trolls WHERE IQ > 70; Error: 0 rows returned

Re:MySQL? Didn't my dad use that in the 60s?

[eberry]

Huh, let's see. Could it be NASA !

The better question is...who uses Oracle? MySQL out performs Oracle. PL/SQL should be renamed to POS/SQL. Oracle is the worst database system money can buy.

Even MS SQL Server is better than Oracle. It sounds like your dad was a pretty smart man. To bad it doesn't run in the family.

I know it's a Troll but I had to respond anyway.

Exactly. Precisely. But only used to be...

[A nonymous Coward]

When MySQL made no bones about it, before they tacked on a lot of bandaids to make it LOOK as if it supported transactions, before they decided to pretend they weren't just a simple DB with a hacked up SQL front end -- they were a great, simple, lightweight, read-mostly DB.

But then they got defensive, tried to claim that transactions weren't really necessary or even useful, tacked on transactions anyway, added other gorp to make it look like they could compete with PostgreSQL (the ACID free source DB), and that's when I lost all respect for them.

It was bad enough they made all their own little extensions to SQL (timestamps in a row automatically update even if not part of the update statement, etc). At least they had the lightweight speed king crown. Now I won't touch it, because they are going to slow it down with all this tacked on overhead. I simply don't believe that stuff can be added on afterwards while retaining the speed it was famous for. Those fancy features have to be designed in from the start, not riveted on afterwards. They are going to lose the niche they have all to themselves, by trying to compete in the ACID world, with established polished competitors. They will lose what they have by trying to grab what they can't win.

PHP + MySQL = use ADOdb

[Kozz]

For those who use MySQL or other database systems with PHP, I highly recommend using the ADOdb Database Library for PHP. It's feature-rich, robust, and has a smaller and smaller footprint with nearly every successive version. I use it all over the place, and it's very very handy. Sure, there are other abstraction libraries out there, but this one takes the cake, IMO. Check out all the supported database drivers!

Plenty of PostgreSQL docs

[Jerky McNaughty]

The PostgreSQL team themselves have excellent docs available in a variety of online and printable formats.

Dead tree stuff, Addison-Wesley publishes a great book, O'Reilly has a decent book, and Sam's has one that I haven't read yet.

With the Addison-Wesley book (by Bruce Momjian, a regular on the PostgreSQL mailing lists) and the PostgreSQL team's docs, I went from little SQL and PostgreSQL knowledge, to being somewhat proficient in a few months.

Re:really?

[Lxy]

I hit the karma cap, so I post crap like this.

Good Grief.. Get your facts straight...

[gabe]

It didn't have transactions, and the only way to get them now is to use InnoDB (this is NOT used by default), which isn't completely integrated. InnoDB and BDB are taken from other projects, and would be better on their own.

If you'll be so kind as to just read through innodb.com, you'll note that InnoDB was Heikki Tuuri's project at the University of Helsinki, and he has since founded a company to develop InnoDB specifically as a part of MySQL. So, why would InnoDB be better on its own?

Also, did you forget that PostgreSQL was initially a project at Berkeley? Is there something wrong with using code from other projects? If you think there is, perhaps you don't understand what open source and free software are...

MySQL developers have made claims that transactions and rollbacks are a bad thing! I kid you not.

Got a url where we can read this?

They claimed one could have atomicity without rollback. Okay, so what do you do if a SQL statement which is part of a (user emulated) "transaction" fails. You are stuck halfway through, or else you might have to do a SQL statement that undoes what you did (good luck). And if that SQL statement fails, you are hosed.

Now that's actually something I agree with. Although there are ways to work around it.

MySQL does not (by default) support Atomicity, Consistancy, Isolation and Durability (ACID).

Download the mysql-max build and BAM! there it is...

Their developers appear to not know the meaning of these terms.

Those are some harsh words. Care to back that up with some evidence?

MySQL isn't truly open source.

So.... The GPL isn't open source?

MySQL isn't a real database, it is a SQL interface to a file system. MySQL isn't much better than using flat files, and due to the complexity, is often worse.

What is and what is not a "real database" is quite a subjective and personal opinion. Considering that every single database out there messes up the SQL "standard" in some way or another, are any of them a "real database"?

PostgreSQL has none of these problems. And the performance is much better than the old versions.

When PostgreSQL has a replication system as nice as MySQL's, and can then scale easily, I'll check it out. And, no, pgreplicator is not good enough [yet].

Only use MySQL if you don't care about your data. Yeah, it might be fine for a web counter where if it gets hosed, big deal.

Or read some documentation and learn how to use it properly just like thousands of other people do. This site that you have posted your opinions on relies heavily on MySQL, are you going to now demand that Slashdot switch over to PostgreSQL to make you feel better about your views on open source databases?

If you care about your data and need a REAL database which is actually Open Source, use PostgreSQL.
See this article:
Why Not MySQL.

Straight from the article: "NOTE: This Document was written in May 2000. Thus, it is outdated and does not represent the latest data concerning MySQL. I will attempt to find time to rewrite this with more current information soon (August 10th, 2001)"

It's quite useless to point people to an article that is entirely obsolete. Perhaps you also need to update your view of MySQL. Take another look at it. It really can't hurt. Yes, MySQL is missing a few things that would make it more convenient to use for "hardcore" database users (who like PostgreSQL), but they are working on it. Version 4 adds transactions/rollback, etc. Instead of keeping your perceptions fixed in 1998, open your eyes, visit mysql.com, download the damn thing and check it out. Then stop spreading misinformation.

Re:Good Grief.. Get your facts straight...

[mesocyclone]

This reply is an RFI to the previous poster...

We use Postgresql and like it. We chose it because at the time MySQL did not have atomicity.

But... Postgresql doesn't have (or didn't last time I looked which admittedly has been a while) point in time recovery. You can only recover to the last backup - not to the last completed transaction.

Does MySQL?

Also, you mentioned scaling on MySQL... We are looking at potentially large databases (100's of GB) with high transaction loads (say - 500 simple queries per second and 100 simple updates).

Which scales better at that level? (yes, I know Oracle and Informix can do it, but that isn't what we want to pay for).

MySQL has had the reputation of being the little, simple RDBMS for people that didn't have high performance with ACID requirements. I don't know if that is still a valid viewpoint.

Thanks in advance. Maybe this actual information would be useful to others reading all of this too.

Re:MySQL in a corporate environment

[hether]

I just had a similar thing happen to me within my work environment. They system admin refused to support MySQL (and I don't have access to the box its on), then when it wouldn't work correctly told the higher ups that open source software such as this, and the PHP I am using, are unsupported and unreliable. I am now being forced to switch to SQL Server. So far I've been able to hold onto using PHP.

Re:MySQL in a corporate environment

[sheldon]

Maybe you should ask around and see if there isn't an application at your company created with some third party tool that nobody else knows anything about. Most companies have something like this somewhere. Heck, maybe it's just an old Clipper database or something.

Once you've found this abandoned solution, walk right into the CIO's office and volunteer to support it.

After a few months of this(actually it'll extend into years because everybody will now associate your name with it as long as you work there), you'll begin to appreciate why people in IT prefer you use the tools that they already have in house, when they are applicable. I can't tell you the number of times I've had a solution dumped on me by someone leaving the company who assured us "don't worry about the strange technology, i'll support it."

New technology can be nice, but it requires justification on technical merit. (i.e. is there something PHP-MySQL can do for you that Java-Oracle can't?)

Re:application side security

[sheldon]

The main thing is to validate user input so you don't allow your SQL code to be manipulated. Using parameterized SQL is also a good practice.

I don't have a good book to recommend. I just ordered this one, which looked good from the description, but I haven't received it yet.

Quality Web Systems: Performance, Security, and Usability

Re:An alternative..

[jonabbey]

Second that. C.J. Date's book is an utter classic.

I'm not sure which niche MySQL is supposed to fill

[cartman]

I regularly hear the comment the MySQL lacks features on purpose, because it occupies a niche that does not require those features. It is a "lightweight" database and it supposedly fills that role well.

I have never been able to tell which niche MySQL is supposed to occupy. Is there really a niche where it doesn't matter if the database is corrupted? Is there really any niche where it doesn't matter if transactional integrity isn't maintained? MySQL does not provide any recovery functionality!

I can understand the point in lightweight software. But ACID features and transactions are at the very core of what constitutes a database; they are not "bloatware features" like the microsoft paperclip. Having a database without data integrity is like having a word processor which can't save files. It doesn't matter how lightweight it is.

PostgreSQL Book (was: guh.)

[Christopher Cashell]

There are actually quite a few PostgreSQL books, including two which have the full text available online, for free.

These books are:

• PostgreSQL: Introduction and Concepts by Bruce Momjian
• Practical PostgreSQL by John Worsley and Joshua Drake

Both of these books are pretty good. They do have a lot of overlap, but they also present things from different perspectives, and provide some different information. I'd strongly recommend skimming through them both.

If you're interested in a larger listing of (dead tree) books that are available, you can find a list here.

Re:I'm not sure which niche MySQL is supposed to f

[smack.addict]

I have never been able to tell which niche MySQL is supposed to occupy. Is there really a niche where it doesn't matter if the database is corrupted?

Nice troll. Of course there is no such niche. However, not all database applications require full ATOMicity and constraints in order to maintain their integrity. Most web applications are of this nature. For these kinds of applications, MySQL blows away other database engines.

But ACID features and transactions are at the very core of what constitutes a database;

No, they are not. The basic feature of a database is that it structures and stores your data for later retrieval. Transactions are tools that assist in maintaining database integrity; they are not database integrity itself. (And, by the way, MySQL supports transactions).

Re:I'm not sure which niche MySQL is supposed to f

[cartman]

However, not all database applications require full ATOMicity and constraints in order to maintain their integrity. Most web applications are of this nature. For these kinds of applications, MySQL blows away other database engines

What? Most web applications do not require full atomicity and constraints to maintain their integrity? Every application requires full ATOMicity and constraints to maintain its integrity. Integrity cannot be maintained otherwise. If you are updating the data ever, full ATOMicity is required.

The basic feature of a database is that it structures and stores your data for later retrieval.

Something that stores data and retrieves it later, is called a "file." A database has a query language and failure resilience. MySQL only marginally supports SQL and does not support resilience.

Transactions are tools that assist in maintaining database integrity; they are not database integrity itself. (And, by the way, MySQL supports transactions).

Transactions are not "tools to assist" in maintaining database integrity. They are absolutely required to maintain integrity if you ever update the database.

Nice troll.

If you consider a complaint about the lack of ACIDity a "troll," then you know nothing whatsoever about databases.

I think you're wrong

[Tim Ward]

It seems to depend on how the application is presented to the customer. If you ship an application which works against "any database engine", and suggest to the customer that he might like to consider downloading and using MySQL, then you don't need a commercial licence.

If on the other hand you want to present your customer with a single install, and don't necessarily even need him to know that a tiny part of your application is an embedded database engine, then you do need a commercial licence.

There are large segments of various markets where a customer would be ever so slightly pissed off to find out after installing your application that he'd only got part of it and that he needed to fiddle around downloading and installing all sorts of other crap from other places before he had something that would actually run.

PostreSQL, It's a DBMS *and* a Dessert Topping!

[alacqua]

...Use PostreSQL or another RDBMS...

&lt waits for laughter &gt ummm... its a joke
&lt more silence &gt ummm... you see... he spelled PostgreSQL wrong... and postre means dessert in Spanish
&lt embarrased look &gt and Saturday Night Live did a thing about a floor wax that was also a dessert topping... a long time ago
&lt sigh &gt I guess I was reaching... never mind
&lt shuffles away &gt

Re:I'm not sure which niche MySQL is supposed to f

[smack.addict]

If you are updating the data ever, full ATOMicity is required.

So, if your data model only requires an update against a single table at a time, why do you require ACIDity?

Almost no one uses fully ACID transactions. They are simply too costly. Most people use some form of optimistic concurrency to get around the costs of long-lived transactions. If you are issuing a single update/delete/insert against a single table as part of your transaction, then using MySQL without transaction support and optimistic concurrency checks is not distinguishable from doing the same thing in a database with transaction support.

And, yes, there are entire problem domains--namely most dynamic web sites--in which this form of transaction rules. MySQL is faster than any other database for these kinds of operations and makes the most sense.

And, by the way, MySQL DOES support transactions.

Other features

[Wee]

So what other advantages does MySQL have over PostgreSQL?

Wel, I like your two (especially, the one about the monitor being nicer -- I hate PostgreSQL's CLI). There are more advantages:

• Code might be more mature. Early versions of psql suffered a bit. MySQL has been looked at a lot, has a large install base, etc., so it's got more kinks worked out. Probably. YMMV.
• Access control is more fine grained. I'm very used to MySQL's grant tables. You can get nearly the same thing in psql though.
• Windows support. You need Cygwin for psql on Windows. This isn't a biggie for me, but might be important to some...
• I can back up MySQL's DB files really easily, without the DB running if need be (did this once as an emergency backup and it saved my bacon).
• Lots of books, lots of code already written for MySQL. Although I suspect that psql will close that gap. But 8 times out of ten you see something from freshmeat written for MySQL.
• I'm personally more familiar with it... :-)

Anyway, there's a whole list of pros as well as cons over at MySQL's site. However, that list let out the most important thing to consider when choosing any technology: Is it the right tool for the job? Most of the time MySQL has been just fine for my needs.

-B

Re:Other features

[kcbrown]

Cool, I was hoping someone would respond intelligently...

Code might be more mature. Early versions of psql suffered a bit. MySQL has been looked at a lot, has a large install base, etc., so it's got more kinks worked out. Probably. YMMV.

I think the bugs have pretty much been ironed out (except perhaps for some truly obscure ones) in PostgreSQL. So I'm not sure that this advantage is really much of an advantage anymore, though it certainly once was.

Access control is more fine grained. I'm very used to MySQL's grant tables. You can get nearly the same thing in psql though.

True, but the differences are subtle! MySQL's GRANT interface seems to be a little nicer in that it lets you specify wildcards to match all databases, all tables, or all tables.

Windows support. You need Cygwin for psql on Windows. This isn't a biggie for me, but might be important to some...

I have no idea what sort of impact this would have on administration of each, or on performance for that matter. So I'll have to give the nod to MySQL on this one.

I can back up MySQL's DB files really easily, without the DB running if need be (did this once as an emergency backup and it saved my bacon).

You can do the same thing with PostgreSQL, I think, but I haven't actually tested this on a live database. You can lock all the tables in PostgreSQL if need be, and I'd think that as long as the database is running without fsync turned off it would work.

Lots of books, lots of code already written for MySQL. Although I suspect that psql will close that gap. But 8 times out of ten you see something from freshmeat written for MySQL.

This is definitely true! It's why I asked the question, because I'd like to see the same sort of support for PostgreSQL. If a ready-made application exists for MySQL that doesn't for PostgreSQL then that is certainly a compelling reason to use MySQL, provided that you don't also need the capabilities of PostgreSQL.

I'm personally more familiar with it... :-)

Yeah, I know what you mean. :-)

Anyway, there's a whole list of pros as well as cons over at MySQL's site. However, that list let out the most important thing to consider when choosing any technology: Is it the right tool for the job? Most of the time MySQL has been just fine for my needs.

And I completely agree with this, as long as you don't end up outgrowing the capabilities of what you're using. My experience shows, though, that this is a lot easier than you might think, which is why it's important to use a database-independent layer (like ADOdb) whenever possible, and to choose the most capable database engine that meets your needs.

And that's why I asked the question the way I did. I'm interested in knowing what compelling reasons exist for choosing MySQL over PostgreSQL. Sounds like the biggest one is the existence of some application for MySQL that already does what you want, but which doesn't exist for PostgreSQL.

Re:I'm not sure which niche MySQL is supposed to f

[smack.addict]

Most applications that use databases have at least one context in which more than a single table is updated at a time. You are describing a very niche usage of databases.

I am describing most content-oriented web sites (like /.) with dynamically driven content coming from a database. This is indeed a niche, but it is a big ass niche.

Furthermore, if most of your operations are read operations and you have a couple of multi-table writes (and thus require transactions), there are in fact ways to maintain database consistency without transactions.

Besides, even if only one insert is done into a single table, it is still possible to get data corruption without transactions. If your write ends up done over more than one page, then you need transactions. Also, it is possible that a page split will occur during insert or update, thus what you think is a single write ends up being multiple writes. You do not control this.

No, but most MySQL table types protect against this situation.

The real reason SPs suck

[GunFodder]

You are correct that it is better to minimize the data IO needs of an application. And stored procedures will definitely resolve this issue. The problem is that you have to use a stored-procedure language to take advantage of SPs :)

I have mostly suffered with PL/SQL (courtesy of Oracle) and I dread having to work with it. The syntax is awkward. The error messages aren't very good. And since PL/SQL is proprietary I cannot port my code without significant work.

If your app is multitier then you can always stick the business logic server on the same machine as the DB to save network IO. And you get to write your business logic in any language you want.

Re:no C++?

[smack.addict]

We skipped C++ since it is a relatively new MySQL API. You can, of course, interface with MySQL from C++ using both MySQL++ and the C API.

Discussion summary

[Crag]

There have been several intelligent replies to this post already, but no concise summary, and there may still be people out there confused about the issue.

Stored Proceedures are a method of associating functionality with a database in such a way that the database server itself manages the operation of the code. The proceedures may be invoked manually via an SQL statement, or automatically via a trigger.

It is a given that any moderately advanced database application will have some operations which by design need to be close to the data, from the managerial and I/O views.

The salient question is whether a particular design is best served by putting the database server in charge of the proceedures, or by running them in a layer above the database. This upper layer may in fact be on the same machine, and it may be the only object which has direct access to the database. In this case, it would appear to other applications to be the same either way. They make a request, and "stuff happens" that they don't need to worry about.

At this point the answer to the question depends on very specific design requirements of the database and the upper layer. There is no general right answer! In any a "mission critical" enviornment stored proceedures may be prefered because changes to the data structures and code may be tied together more easily. In a "low end" environment, the benefits may be negligable.

I will go out on a limb and propose that if you don't know if you need them, you don't need them. I will also suggest that if you think you "can't" do without them you are probably wrong.

This entire discussion comes down to The Right Tool For The Job, and There's More Than One Way To Do It. It's an important discussion, but no more important than Which Programming Language(s), Which Standard Library, CORBA vs SOAP vs COM, or even UDP vs TCP.

Anyone with a personal stake in the discussion is needlessly burning energy on what often comes down to Academic Wanking.

Thankyoupleasedrivethrough.