





Managing and Using MySQL: Second Edition 261
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.
MySQL (Score:2, Offtopic)
*shrug, but what do I know.
Re:MySQL (Score:1)
Re:MySQL (Score:2)
I like MySQL, but without a good object relational mapping tool (like Castor [exolab.org]a 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 (Score:2)
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 (Score:2)
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 (Score:2, Informative)
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.
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 (Score:2, Informative)
Re:MySQL (Score:2)
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 (Score:2, Informative)
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 (Score:2, Offtopic)
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 (Score:2)
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 (Score:5, Informative)
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 (Score:2)
Re:MySQL (Score:2)
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 (Score:2)
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.
The real reason SPs suck (Score:2)
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:MySQL (Score:2)
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 (Score:2)
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.
Re:MySQL (Score:2)
Re:MySQL (Score:2)
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.
Discussion summary (Score:2)
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.
Re:MySQL (Score:2)
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 (Score:2, Insightful)
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 [mysql.com].
If you're just going for early post E-Z Karma, well done.
Re:MySQL (Score:2)
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 (Score:2, Funny)
This comment would be ALOT better if it was about the book or about the review.
-Sara
Re:MySQL (Score:2)
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 (Score:2)
Re:MySQL (Score:2, Funny)
These would also be known as hacks, right?
Let's get this out of the way right now (Score:5, Funny)
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 (Score:4, Funny)
application side security (Score:1)
Web application security (Score:4, Informative)
Yes, try the Open Web Application Security Project [owasp.org]. They released a very informative paper on building secure webapps, and it's free.
(I'm not affiliated with them in any way)
Re:application side security (Score:2)
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 [awprofessional.com]
O'Reilly (Score:4, Insightful)
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 (Score:2)
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 (Score:2)
Re:O'Reilly (Score:2)
guh. (Score:2, Interesting)
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. (Score:2, Informative)
Plenty of PostgreSQL docs (Score:2)
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.
PostgreSQL Book (was: guh.) (Score:2)
These books are:
If you're interested in a larger listing of (dead tree) books that are available, you can find a list here [postgresql.org].
Re:guh. (Score:2)
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 (Score:3, Informative)
Re:Best Win32 Front End I've Seen (Score:2)
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 (Score:2)
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.
Great, now we're going to have a war... (Score:5, Insightful)
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... (Score:3, Insightful)
Re:Great, now we're going to have a war... (Score:2)
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... (Score:2, Offtopic)
Unfortunately, a flat file fills the "flat file" role even better.
Re:Great, now we're going to have a war... (Score:2, Offtopic)
Simplified,
SELECT complaint FROM whining_bastards WHERE subject = 'MySQL'
Re:Great, now we're going to have a war... (Score:2)
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... (Score:2)
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 [geocities.com] that some people here might be interested in.
-B
Re:Great, now we're going to have a war... (Score:2)
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.
Exactly. Precisely. But only used to be... (Score:3, Interesting)
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.
Re:Great, now we're going to have a war... (Score:2)
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... (Score:2)
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:
Re:Great, now we're going to have a war... (Score:2)
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... (Score:2)
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:
So what other advantages does MySQL have over PostgreSQL?
Other features (Score:2)
Wel, I like your two (especially, the one about the monitor being nicer -- I hate PostgreSQL's CLI). There are more advantages:
Anyway, there's a whole list of pros as well as cons [mysql.com] 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 (Score:2)
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.
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.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.
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.
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.
Yeah, I know what you mean. :-)
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 [weblogs.com]) 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.
PostreSQL, It's a DBMS *and* a Dessert Topping! (Score:2)
< waits for laughter > ummm... its a joke
< more silence > ummm... you see... he spelled PostgreSQL wrong... and postre means dessert in Spanish
< embarrased look > and Saturday Night Live did a thing about a floor wax that was also a dessert topping... a long time ago
< sigh > I guess I was reaching... never mind
< shuffles away >
Re:Great, now we're going to have a war... (Score:2)
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.
[OT] Can anything get a rating besides 9? (Score:4, Insightful)
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? (Score:2)
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? (Score:2)
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 [newriders.com] book, titled simply MySQL [slashdot.org], 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? (Score:2)
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.
non-GPL version (Score:4, Interesting)
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" (Score:2)
Not so much.
Re:"the only thing you need" (Score:2)
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" (Score:2)
Re:"the only thing you need" (Score:2)
Re:"the only thing you need" (Score:2)
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" (Score:2)
GLP Question... (Score:2)
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... (Score:2)
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... (Score:2)
"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... (Score:2)
Apparently not. Here is what the licensing policy [mysql.com] says:
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.
I think you're wrong (Score:2)
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.
all you need (Score:2)
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 (Score:2)
A database is just a collection of data. There are a billion different kinds of databases, some ACID, some not.
Re:all you need (Score:2)
Search on Google for "database acid test" for plenty of other references.
Re:all you need (Score:2)
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.... (Score:5, Funny)
PHP + MySQL = use ADOdb (Score:3, Informative)
I'm not sure which niche MySQL is supposed to fill (Score:3, Insightful)
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.
Re:I'm not sure which niche MySQL is supposed to f (Score:2)
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 (Score:2)
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.
Re:I'm not sure which niche MySQL is supposed to f (Score:2)
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.
Re:I'm not sure which niche MySQL is supposed to f (Score:2)
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.
Re:Front End ? (Score:3, Informative)
Re:Front End ? (Score:2)
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.
Re:if only.... (Score:2, Insightful)
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.
Re:MySQL? Didn't my dad use that in the 60s? (Score:2, Funny)
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.
Re:really? (Score:2)
Good Grief.. Get your facts straight... (Score:2, Informative)
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...
Got a url where we can read this?
Now that's actually something I agree with. Although there are ways to work around it.
Download the mysql-max build and BAM! there it is...
Those are some harsh words. Care to back that up with some evidence?
So.... The GPL isn't open source?
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"?
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].
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?
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... (Score:2)
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 (Score:2)
Re:MySQL in a corporate environment (Score:2)
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:An alternative.. (Score:2)
Second that. C.J. Date's book is an utter classic.
Re:no C++? (Score:2)