Want to read Slashdot from your mobile device? Point it at m.slashdot.org and keep reading!

 


Forgot your password?
Close
typodupeerror
×
Programming Books Media Book Reviews IT Technology

Managing and Using MySQL: Second Edition 261

Posted by timothy from the everybody's-sql dept.
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.
This discussion has been archived. No new comments can be posted.

Managing and Using MySQL: Second Edition More

Managing and Using MySQL: Second Edition

Comments Filter:

  • MySQL (Score:2, Offtopic)

    by filth grinder ( 577043 )
    MySQL would be ALOT better if it supported stored procedures and those sorts of things.

    *shrug, but what do I know.
    • um... can't you use another app for that, such as Perl or PHP?
      • 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 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.
    • 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.
      • 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 (Score:2, Informative)

        by noda132 ( 531521 )
        MySQL 4.0 supports rollbacks. Even the alpha is very stable.
      • 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 (Score:2, Informative)

          by noda132 ( 531521 )

          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)

            by cygnusx ( 193092 )
            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.
        • 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 (Score:5, Informative)

        by highcaffeine ( 83298 ) on Tuesday July 02, 2002 @11:38AM (#3808058)
        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.
        • 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.
        • 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 =)
          • 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.
            • 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.
          • 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.
          • 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.
        • 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.
        • 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.

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

    • Re:MySQL (Score:2, Insightful)

      by Anonymous Cowrad ( 571322 )
      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 [mysql.com].

      If you're just going for early post E-Z Karma, well done.
      • 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 (Score:2, Funny)

      by neuroticia ( 557805 )
      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

  • Let's get this out of the way right now (Score:5, Funny)

    by Anonymous Coward on Tuesday July 02, 2002 @10:27AM (#3807568)
    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!
  • Does anyone know of good references that cover the security of web applications from the ground up? This is good that they appear to devote some time to security from the mysql side, but typically security flaws in web applications tend to be in the communication between the front-end and the database, or in the front-end itself. What books if any cover the entire process for security?

  • O'Reilly (Score:4, Insightful)

    by Beatbyte ( 163694 ) on Tuesday July 02, 2002 @10:28AM (#3807572) Homepage
    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.
    • 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

  • guh. (Score:2, Interesting)

    by siliconwafer ( 446697 )
    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. (Score:2, Informative)

      by elmegil ( 12001 )
      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.
    • 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.
    • There are actually quite a few PostgreSQL books, including two which have the full text available online, for free.

      These books are:
      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 [postgresql.org].

  • Best Win32 Front End I've Seen (Score:3, Informative)

    by WellHungYungWun ( 580730 ) on Tuesday July 02, 2002 @10:33AM (#3807606) Homepage
    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
    • 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.
      • 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.

  • Great, now we're going to have a war... (Score:5, Insightful)

    by Wee ( 17189 ) on Tuesday July 02, 2002 @10:51AM (#3807724)
    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

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

      • 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?"

    • 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. ;)

    • Simplified,

      SELECT complaint FROM whining_bastards WHERE subject = 'MySQL'

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

      • 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 [geocities.com] that some people here might be interested in.

        -B


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

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

    • 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?
    • 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?

      • 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 [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

        • 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 [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.

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

  • [OT] Can anything get a rating besides 9? (Score:4, Insightful)

    by ajakk ( 29927 ) on Tuesday July 02, 2002 @10:54AM (#3807746) Homepage
    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.

    • 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).
    • 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 [slashdot.org] -- 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 [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....

  • non-GPL version (Score:4, Interesting)

    by Enry ( 630 ) <enry@noSpaM.wayga.net> on Tuesday July 02, 2002 @10:58AM (#3807775) Journal
    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.
  • Sound? Correct? Well-thought-out? Standardized? Scaleable?

    Not so much.
    • 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
  • 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!

    • 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.
    • 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 [mysql.com] 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. :-)
    • 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.
  • ...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.

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

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

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

        • 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.... (Score:5, Funny)

    by Lord_Slepnir ( 585350 ) on Tuesday July 02, 2002 @11:12AM (#3807850) Journal
    SELECT * FROM trolls WHERE IQ > 70; Error: 0 rows returned

  • PHP + MySQL = use ADOdb (Score:3, Informative)

    by Kozz ( 7764 ) on Tuesday July 02, 2002 @11:29AM (#3807986)
    For those who use MySQL [mysql.com] or other database systems with PHP [php.net], I highly recommend using the ADOdb Database Library for PHP [weblogs.com]. 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 [weblogs.com]!

  • I'm not sure which niche MySQL is supposed to fill (Score:3, Insightful)

    by cartman ( 18204 ) on Tuesday July 02, 2002 @02:15PM (#3809503)
    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.
    • 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).

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

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

Slashdot Top Deals

To be awake is to be alive. -- Henry David Thoreau, in "Walden"

Close