Managing and Using MySQL: Second Edition

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

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

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

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

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

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

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

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

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

Comments

MySQL

[filth grinder]

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

*shrug, but what do I know.

Re:MySQL

[sugrshack]

um... can't you use another app for that, such as Perl or PHP?

Re:MySQL

[the Man in Black]

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

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

Re:MySQL

[smagoun]

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

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

Re:MySQL

[noda132]

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

Re:MySQL

[Anonymous Cowrad]

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

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

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

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

Re:MySQL

[cpfeifer]

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

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

Re:MySQL

[sql*kitten]

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

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

Re:MySQL

[Lachrymite]

That's what they have PostgreSQL for. MySQL doesn't even stand up to the ACID test unless you replace the default table handler.

The only reason I can see for using MySQL over PostgreSQL is purely for speed. If you need a more robust feature set, stick to PostgreSQL.

Re:MySQL

[Anonymous Coward]

MySQL doesn't do views? Oh man, I new it was simple, but thats amazing. No views! Heh...

Re:MySQL

[noda132]

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

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

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

Re:MySQL

[sugrshack]

true, you have a good point. I haven't had to use it for such large projects (we use Sybase where I work), but what part of "free" don't you understand?

I find it amusing how people will bash stuff for which they paid nothing.

Re:MySQL

[cygnusx]

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

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

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

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

Re:MySQL

[zaphod110676]

I really wish it had subselects. I use these all the time with other database packages. They are easy enough to work without when you are querying from a programming languange but when you just want to look at a couple of rows to double check something they make life a lot easier.

Re:MySQL

[Jacer]

ALOT...."*shrug, but what do I know." obviously not a lot...

Re:MySQL

[mbrod]

Why does someone always ask this when talking about MySQL?

Any programmer worth 2 cents can simply code all the functionality they need a lot easier and with more flexibility than in a stored procedure.

Coding it you can be more carefull with database locking, rollback conditions, system load, anything!, etc.

Re:MySQL

[digitalgiblet]

quote: I find it amusing how people will bash stuff for which they paid nothing.

I don't believe he was bashing anything, just offering constructive criticism.

Since when does saying "I think this system would be better if it had X" constitute bashing?

Also, free isn't a bargain if the tool doesn't meet the needs of the project. MySQL is great for some projects, but not for others.

A screwdriver makes a lousy hammer! That doesn't mean I'm bashing screwdrivers (unless I'm trying to use them as hammers...).

Just my $0.0002 (originally two cents, adjusted for inflation, deflation, recalibration, etc.)

Re:MySQL

[Anonymous Coward]

Most DBAs don't speak PL/SQL, or Transact, or Progress, or, or, or...

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

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

Re:MySQL

[Anonymous Coward]

Does MySQL support sound under enligtenment? I can't get it to work.

Re:MySQL

[Anonymous Coward]

Your comment would be A LOT better if you didn't write like an eighth grader who failed English.

Re:MySQL

[highcaffeine]

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

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

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

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

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

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

Now, choose one of these two options:

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

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

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

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

Re:MySQL

[ipjohnson]

Or you could just have a server in between the GUI and the db that does your "number crunching" that way your db is seperate from your buiseness logic ;-)

Re:MySQL

[Bake]

Actually, no.

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

Re:MySQL

[micromoog]

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

Re:MySQL

[afidel]

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

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

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

Re:MySQL

[Camulus]

I happen to like MySQL because it is simple. I use it to write moderate sized web based applications. It is screaming fast because it does not have all the extra "features" slowing it down. If you are writng some gigantic application that uses 450 tables to the fifth degree of normilization, then yes, you want something that is not mySQL. However, if you need some thing to handle several small transactions extremely fast, then MySQL smokes. The correct tools for the job my friend.

Re:MySQL

[Anonymous Coward]

I find it amusing how people will bash stuff for which they paid nothing.

You get what you pay for.

Re:MySQL

[mark_lybarger]

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

Re:MySQL

[Anonymous Coward]

I guess I still don't get it completely. Sucking 9.9M of useless data sounds like a poorly formed SQL query.

Re:MySQL

[rjamestaylor]

(albeit there are some "cleaver" work arounds)

These would also be known as hacks, right?

Re:MySQL

[highcaffeine]

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

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

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

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

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

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

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

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

Re:MySQL

[Osty]

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

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

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

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

Re:MySQL

[highcaffeine]

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

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

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

Re:MySQL

[Ioldanach]

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

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

Re:MySQL

[mark_lybarger]

hacks or bandages, whatever. there are pros and cons to each dbms, but the reasons for now choosing MySQL are changing. similar to the IE/Mozilla story, but I really hope Mozilla fares better. Mozilla was architected from the ground up with standards and flexability in mind. IE was designed to be good/quick, feature rich and whatever else M$ decided to give. now we're seeing how quickly Moz can close the on speed while still maintaining standard compliance and as an added bonus adding features that users want! tabbed browsing rocks! no-pop up messages rock. it's all about providing ease of use for the user. as a user of a database (application developer) i want something that's easy to use and adheres to the standards.

Re:MySQL

[rjamestaylor]

Holy Oblivion, BatMan! That was a pun! A joke! A ruse! A play on a slight spelling error that had ironic (or symbonic) undertones!

Take a step back from the debate and think context...

Re:MySQL

[plumby]

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

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

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

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

Re:MySQL

[EastCoastSurfer]

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

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

Re:MySQL

[cduffy]

Unless the business logic layer is run serverside (and only the presentation layer runs directly on the client), that fails to address the issues he's talking about (as it's still necessary to redeploy the app when the business logic layer changes).

If that's so, though, you're adding Yet Another Layer of complexity to your system (running a separate application server whereas previously such was unnecessary), and thus yet another thing that can go wrong, simply to emulate something that could be done inside the database itself. (I'm not saying the tri-layer system is bad, I'm saying that unnecessarily adding another piece of running software that can fail and break the system is bad, as opposed to keeping multiple layers running inside the same process).

As for the cost of per-CPU database licenses... with SAP DB, Firebird and PostgreSQL available, why would one care (unless the support contracts are also per-CPU-structured)?

Re:MySQL

[neuroticia]

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

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

-Sara

Re:MySQL

[global_diffusion]

Ha ha ha ha ha. I'd mod you up if I had any points.

Re:MySQL

[rjamestaylor]

But, you have a good point, even though the joke was lost on you...

Re:MySQL

[Anonymous Coward]

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

Right there is enough reason to dismiss anything further you say. But then you go and claim that anyone who wants to store any kind of information in an organized manner should have 3 gigabytes of RAM.

So I won't try and refute your claim that just because something that was hacked on 10 or 15 years ago to a product that was originally designed 20 or more years ago has no cruft. The only real argument for building stored procedures into the database is to reduce cpu load, but of course, with 3 gigs of RAM, who not throw another couple CPUs at every select * from employees.

Re:MySQL

[Anonymous Coward]

The real problem is people who haven't caught up to multi-tier architecture and thing there is nothing in the world but a GUI and a DB. Here is the way a real architecture works:

database --> data integrity logic --> business logic --> application logic --> presentation logic --> display --> user interaction

there are at least three layers there. The only thing that is not so easily separated from the database is triggers. You can still run your data integrity (and even business logic) on the same machine as the database. In fact, using temporary tables for rollback is less taxing, and much easier. Complex transactions want saving anyway.

Re:MySQL

[MattRog]

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

Re:MySQL

[dk.r*nger]

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.

Let there be no misunderstanding. Stored procedures are good.

But your scenario belongs to the Dilbert universe in absurdity and bad bad bad software design. I can think of the first few ways of making such updates work without having to update the client manually.

Fx. let the application check for updates everytime it is started, download it, install it and restart. Tada. This happends while the user fetches a cup of coffee = zero user downtime.

or have the application running on a (few) central server(s)... heck, it might even by webbased. Updates are transparent to the users..

We, who are about to salute you, die.

Re:MySQL

[consumer]

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

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

Re:MySQL

[sheldon]

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

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

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

Re:MySQL

[King of the World]

Often the results of a previous SQL query are used to form the current SQL query. In such a case stored procedures are lovely.

Re:MySQL

[Anonymous Coward]

Have you ever run complex financial reports (i.e. the kind suitable for quarterly filings for publicly traded companies) that span several business units for a company with thousands of employees? Depending on the types of businesses involved, that can be a tremendous amount of data to go through to build those reports. Think retail where you can be as detailed as each of the thousands of daily transactions at each store location; or at best daily logs from each store including IO from the bank accounts for reconciliation, receivings and orders with your numerous suppliers, the list goes on. Many large companies deal with amounts of data in as much of a real time fashion as their IT departments can provide -- to a degree that would have been practically inconceivable just 10 years ago.

When you leave the little league and you're dealing with hundreds of millions of dollars worth of business each month -- not everything can be JOIN'ed. Accounting is a very tricky subject, and you can't always make a single clean query to your database to get the data out right -- no matter how beautiful the design is. Accounting laws change, business practices change, your company enters into completely new lines of business with very different regulations; a whole host of things make providing the data to the accountants a very "fun" job.

Sure, the situation in the post you replied to may not be "common" for your line of work, but that just means you have the luxury of not doing my job (or the guy's who posted it -- I can feel his/her pain). ;) And sometimes, yeah, the database was not designed well, and even though it's not your fault (trust me, work for a large multinational behemoth and you will almost certainly have to support some really brain dead software at some point) you don't have the power to change it -- just the paychecks telling you to deal with it.

Re:MySQL

[Anonymous Coward]

Another benefit to stored procedures. Where they're called 100's of times in a short time frame, they're compiled only once and cached to memory for re-use, as opposed to recompilling every time. Efficient use of resources, in other words.

Re:MySQL

[Anonymous Coward]

A very good reason to code SP's is to remove the task from the programmer. Leaving control & performance of the database to the DBA. Sound like a good idea?

Re:MySQL

[Jacer]

That's right, it was good stuff!

I'd buy this book

[i_am_pi]

but I use webmin

Let's get this out of the way right now

[Anonymous Coward]

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

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

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

[Anonymous Coward]

I run a top-5 US bank with MySQL on a P166MMX and it runs great why do you need Oracle?

WTF? I run a Fortune 100 company's entire enterprise infrastructure off of a 386 with 4 megs of RAM with Linux and MySQL, what kind of crack are you smoking?

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

[jayhawk88]

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

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

[Anonymous Coward]

What about Access?! You forgot Access! And flat textfiles! Jerk!

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

[techwolf]

No wonder KMart went out of business... next time, try text files. They don't get corrupted as easily.

-techwolf

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

[DEBEDb]

I run a Fortune 100 company's entire enterprise infrastructure off of a 386 with 4 megs of RAM with Linux and MySQL,

Hey, we still use tin cans and strings
for our network's physical layer.

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

[Anonymous Coward]

We use rusty steel cans and twine! And we run NCP for our protocol layer!

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

[Anonymous Coward]

Hey, we still use tin cans and strings for our network's physical layer.

Bah! We use barbed wire, and X.25 for our physical layer.

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

[Anonymous Coward]

ahh. an Enron employee.

really?

[Lxy]

Read on for Geekboy's review..., which he describes as "the only thing you / need/ to make a usable database."

Yeah, until the third edition....

Re:really?

[Geekboy(Wizard)]

(+1, Funny)

Re:really?

[Lxy]

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

application side security

[numatrix]

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?

Re:application side security

[sheldon]

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

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

Quality Web Systems: Performance, Security, and Usability

Re:application side security

[mrkitty]

www.cgisecurity.com

O'Reilly

[Beatbyte]

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

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

Awesome stuff.

Re:O'Reilly

[lobos]

I recently purchased both of the books (if anyone else interested is reading this, they're available together for a discount on Amazon.com.) I have no experience with MySQL, PHP or anything similar to them. Which book would you recommend reading first for someone who doesn't know anything about what's inside either of the books yet?

Re:O'Reilly

[Geekboy(Wizard)]

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

Re:O'Reilly

[Beatbyte]

Start with the MySQL book. Hopefully you have SOME grasp of general programming (C and such). Then read the combo book. That seems to be what they're designed for. Thats how I'm approaching it and its all working great.

Hope this helped..

Re:O'Reilly

[JabberWokky]

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

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

--
Evan

Re:O'Reilly

[smack.addict]

No, it does not cover replication.

no C++?

[tps12]

I was disappointed to read that the C++ API to MySQL is not discussed. While the C API could no doubt be utilized, most professional developers aren't going to consider a database in the enterprise if it won't gracefully interface with existing object-oriented code.

Other than that, this sounds like a great book. I am very interested in MySQL, which must be a good database system, since it's what slashdot uses. I've always associated slashdot with stability and relyability.

Re:no C++?

[therealmoose]

I've always associated slashdot with stability and relyability.

And good spelling!

Re:no C++?

[smack.addict]

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

guh.

[siliconwafer]

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

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

Re:guh.

[Anonymous Coward]

whoops...
http://www.commandprompt.com/ppbook/
e xcellent book.

Re:guh.

[elmegil]

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

Re:guh.

[questionlp]

O'Reilly has a book on PostgreSQL called Pratical PostgreSQL, which was published at the beginning of this year. A bunch of other PostgreSQL books can be found ; Prime Time Freeware has published printed versions of the online documentation (which I did a brief review on the "Use and Administration" portion here.

Re:guh.

[questionlp]

Oops... that should have been:

O'Reilly has a book on PostgreSQL called Pratical PostgreSQL, which was published at the beginning of this year. A bunch of other PostgreSQL books can be found here; Prime Time Freeware has published printed versions of the online documentation (which I did a brief review on the "Use and Administration" portion here.

Re:guh.

[Kamel Jockey]

O'Reilly has a book on PostgreSQL...

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

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

Re:guh.

[questionlp]

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 :)

The book isn't too bad, but a lot of it is stuff from the on-line documentation, mixed around, added a little spice, and published. For me, it's good enough and small enough to carry around with me along with other books to read casually or when working away from the comforts of a cube (well, compared to a server room or a colo cage).

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.

I definitely agree with the last sentence... the same goes for any similar items: Linux and FreeBSD, Sendmail, Postfix, Exim and qmail, Perl and Python, etc. Choose the best tool for the job.

if only....

[smagoun]

If only MySQueeL supported stored procedures, subselects, etc

Yeah, I know there's support for subselects in the 4.1 dev tree, and I know there are workarounds for both SPs and subselects. I don't want workarounds, I want the damn feature!

Oh well, I guess I should be happy that they finally support transactions, sort of.

Re:if only....

[vegetablespork]

If you need all that, you can get it here and pay for it. Sheesh.

Re:if only....

[Lachrymite]

That's what they have PostgreSQL for. The only reason I can see for using MySQL over PostgreSQL is speed when durability and serious features aren't an issue. If you need a more robust feature set, stick to PostgreSQL.

Re:if only....

[mustangdavis]

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



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

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

To everyone that has too much money: Go buy Oracle

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

Re:if only....

[Anonymous Coward]

do you know anything about databases? no? that's what I thought. SPs have nothing to do with slowing down the DB. Now, transactions are another story. SPs are actually faster in most cases, since they're pre-parsed.

now go back under your rock where you belong.

Re:if only....

[lewp]

Or you could just use PostgreSQL and get all of it and not pay. Sure lots of people have need for Oracle, but I really doubt those people are considering MySQL at the same time.

Re:if only....

[philj]

Oracle is free for non-commercial use.....

Re:if only....

[vegetablespork]

Absoulutely. I'm just tired of hearing people bash open source, free products when alternatives, both open (as you pointed out) and closed are available.

Re:if only....

[Anonymous Coward]

Ya' know, he didn't say anywhere that SPs and speed were mutually exclusive; he just name what appear to be the strength of each DB.

Re:if only....

[Craig Davison]

Ever hear of query overhead, jackass? Single queries and stored procedures really do speed things up.

Re:if only....

[mustangdavis]

Yes, I have heard of query overhead! What do you think stored procedures and nested queries generate??? The answer: QUERY OVERHEAD! MySQL is designed to query a simple database QUICKLY (really just basic selects, updates and deletes). ANYTHING that you add after that will begin to generate SERIOUS overhead .... JACKASS!

Go back to school and learn what you are talking about before flaming people!

Best Win32 Front End I've Seen

[WellHungYungWun]

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

Re:Best Win32 Front End I've Seen

[noda132]

A great cross-platform GUI is MyCC, available at www.mysql.com. It's pretty stable and has friendly stuff like inline editing. Not all that mature, but worth a look. It's what I use :).

Re:Best Win32 Front End I've Seen

[SirSlud]

Indeed, MySQLFront is a real time saver.

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

Re:Best Win32 Front End I've Seen

[Anonymous Coward]

by inline editing, do you mean a grid that will updated, insert and delete automatically, similar to TOAD for oracle?

Re:Best Win32 Front End I've Seen

[Anonymous Coward]

Best out there, but could use better GUI design and bug-squashing.

Re:Best Win32 Front End I've Seen

[Anonymous Coward]

Yep. It greatly helped in our transition from Access (ugh) to SQL. Minimal retraining was necessary.

Now if I can just convince the Powers that Be to scrap all other Microsoft products...

Re:Best Win32 Front End I've Seen

[plumby]

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

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

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

Web application security

[ppetru]

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

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

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

Re:Web application security

[King of the World]

Geez, is that site a piss-poor ripoff of Ximian.com or wot?

Personal experience

[Marque_Off]

Managing and Using MySQL: 2nd Edition assumes that the reader through the actual making 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, but a sample database in the beginning, everything was very well laid out. It started with the greatest of ease. Aside from the mysql side, but typically security flaws in 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).
Aside 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 from the lack of a test database in the book, but a database to test them on.

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

Part II introduces the reader has a little knowledge of databases, through the actual making of the language you will be integrating MySQL into is necessary, but the book presents and explains the rest of the bookm although some of the book, and to have a database to test them on.

All great books that teach contain a reference section, and this book and its awesome. Personally I'm using it for web applications and if anyone of you are doing the same, Complimenting each other in the beginning of the commands are difficult to get your head around until you have a database to test them on (of the computer nerdish-tech know how sort).

Front End ?

[MrChris007]

MySQL is great, I love it. I recently taught myself how to use it along with Apache and PHP. One thing that I haven't found yet for MySQL that there should be is some software that would allow a less advanced user to still get the power and functionality of a database system. Basically something like the Microsoft Access front-end (because I have to admit that the microsoft access interface is simple enough that it allows people who don't know much about databases to utilitize them), but using MySQL as a database backend.

Re:Front End ?

[nomadlogic]

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

Re:Front End ?

[reaper20]

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

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

Re:Front End ?

[Anonymous Coward]

Do you always ingore your customers project requirements? I understand that Access is not a good idea for a business system, but to actively ignore a customer requirement without explaining what and why you are doing it seems unethical.

Re:if only.... QWZX

[Anonymous Coward]

Just out of curiosity, why are you bitching about this when there is a far superior alternative?

MySQL needs to just go away.

sqlsecurity links

[Anonymous Coward]

www.cgisecurity.com/lib
www.sqlsecurity.com

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

[Wee]

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

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

-B

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

[MattRog]

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

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

[PapaZit]

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

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

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

[Captain Large Face]

Simplified,

SELECT complaint FROM whining_bastards WHERE subject = 'MySQL'

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

[zulux]

Hear hear!

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

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

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

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

[Reality Master 101]

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

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

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

[gillbates]

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

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

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

[Anonymous Coward]

basically you would be willing to as long as you dont have to pay for it.

you make it sound like it is the developers duty to serve your needs.

they may not have the driving goal of a datacenter database. get over it

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

[Wee]

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

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

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

-B

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

[Christopher Thomas]

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

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

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

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

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

[carlos_benj]

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.

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

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

[ManxStef]

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.

Have you heard of Interbase? It's an industrial-strength DBMS written by Borland, very much along the lines of Oracle/MS-SQL et al. Did you know they open-sourced it a few years ago? The result:

Firebird

Firebird is ACID-compliant, includes stored procedures, views, sub-selects, transactions, replication, etc. (ie. all the stuff that MySQL doesn't do/claims to do "soon" in the documentation), and runs on the following platforms:

• 32-bit Windows
• Linux (i386)
• Solaris (Sparc)
• HP-UX (i386)
• MacOS X

Lesser-supported builds:

• FreeBSD
• Solaris (i386)
• Sand AIX

Historically, Firebird (under it's original name) has also been built and run on the following platforms: DG-UX, SGI-IRIX, NCR3000, CRAY, DEC-Ultrix, DEC-VMS, UnixWare, Apollo, OS2, Novell Netware and HP MPE/XL.

So it's definitely NOT a "database built by volunteers". Besides, the coders/contributors to the Firebird source that I've personally spoken to (and that hang around the Delphi-DB list) I'd rate as some of the most knowledgeable dbms experts around. Want to contribute?

Guess you haven't heard of it, otherwise you'd probably be using it already....

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

[nograz]

Personally I prefer PostgreSQL because MySQL uses a bastardized version of SQL. I wouldn't even call MySQL a "database" since it doesn't have triggers, rollbacks, stored procedures or nested selects!

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

[zulux]

The comparison you found is great!

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

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

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

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

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

[Anonymous Coward]

Or...

SELECT * FROM MySQLABPropaganda
WHERE UserID = (SELECT UserID FROM Users WHERE Name LIKE "MySQLZealot")

Oh, wait. I can't do that can I? ;)

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

[Anonymous Coward]

And maby a bit of Lisp for good measure.

And maybe a bit of English.

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

[kcbrown]

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

What are the advantages of using MySQL over PostgreSQL?

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

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

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

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

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

So what other advantages does MySQL have over PostgreSQL?

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

[smack.addict]

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

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

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

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

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

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

[Anonymous Coward]

This is how I feel about Linux applications in general. Granted, the kernel is very stable on the hardware that it DOES support. If the applications were more stable, I would be a more ardent Linux user.

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

[gillbates]

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

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

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

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

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

[Anonymous Coward]

hey jackass. care to drop a column from a table in postgres? didn't think so.

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

[Anonymous Coward]

No you can't. If you knew correct SQL syntax you would know the = should be IN.

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

[kistel]

I remember abandoning Interbase when a "SELECT id,name FROM table" returned like 5 rows, while at the same time a "SELECT name,id FROM table" returned 3 (there were no updates to the db between).

Obligatory PostgreSQL Plug -- Free For Commercial

[ClarkEvans]

One thing to note is that PostgreSQL is free for proprietary applications; while MySQL must be licensed since it is GPLed. Besides, you can
now write your triggers in perl or python with
PostgreSQL; which IMHO, is damn cool. Besides
being very stable, good performace, etc.

[OT] Can anything get a rating besides 9?

[ajakk]

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

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

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

[Geekboy(Wizard)]

I don't know about other books, but this one kicked ass! The only reason it wasn't a 10, is that the test database was hidden. Otherwise, I loved it (Hence the glowing review ;-).

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

[Christopher Thomas]

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

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

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

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

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

[maiden_taiwan]

I used to review CDs for a pretty well-known music magazine. Occasionally I would submit negative reviews, but the Editor always rejected them. His reason: "We receive 100 CDs a week for review. It's our responsibility to call people's attention to the best ones." So, all of the published reviews were positive. No secret affiliate program or kickbacks, just a focus on the good.

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

[mongoks]

Just because something gets a 9 doesn't mean it's going to be good for everyone. People need to read the reviews and figure out if the book addresses the subjects they need. A good reviewer will point out the book's shortcomings and perhaps suggest other publications which could help the reader overcome them.

As far as the referrals to Amazon, if it's really that big of a deal don't do it. Would you rather have all of the profit go to Amazon? A good review is helpful to me and if I can help out the author of the review in return then great.

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

[babbage]

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

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

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

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

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

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

[ryantate]

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

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

Wrong

[Betcour]

MySQL 3.23 supports procedure since the old days it integrated BDB table handlers. Most people would use InnoDB for that nowadays as it seems it's a bit better supported.

non-GPL version

[Enry]

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

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

Re:non-GPL version

[Anonymous Coward]

Except that NuSphere's proprietary license to MySQL *expired*. At the hearing, the judge took a pretty dim view of NuSphere's claims that the license was still valid.

"the only thing you need"

[MattRog]

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

Not so much.

Re:"the only thing you need"

[MattRog]

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

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

Re:"the only thing you need"

[micromoog]

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

Re:"the only thing you need"

[MattRog]

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

Re:"the only thing you need"

[Geekboy(Wizard)]

The only thing you need to get started. You don't need anything else. You can and should check other things if you are doing it professionally, but for Joe User who doesn't know MySQL, and wants to learn it, he can just buy one $40 book, as opposed to 5 $60 books. This one has everything tied in. Of course there are books with more details, but this has more than enought to get you started.

Re:"the only thing you need"

[MattRog]

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

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

Re:"the only thing you need"

[smack.addict]

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

I'll wait for the MySQL Cookbook

[saints-in-hell]

Although they changed the name, the first edition of this book was not worth purchasing. The honourable Mr. Paul Dubois will be putting out his own O'Reilly book in October entitled "MySQL Cookbook".

saints-in-hell

Re:I'll wait for the MySQL Cookbook

[emir]

i agree with you, first edition (mysql & msql) was not that good.

oreilly books usually are much much bettter than mysql & msql book was...

GLP Question...

[zulux]

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

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

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

If I'm wrong, please enlighten me!

Re:GLP Question...

[Anonymous Coward]

nice sig.
Why not "Kill the Poor"

Re:GLP Question...

[Anonymous Coward]

nice sig.
Why not "Kill the Poor"


Xulux here.
Off topic, posting AC

Perhaps you were unaware, but I'm located in the USA. Here, in the USA, if you're poor* - it's because you're lazy or stupid or both. There are no victims of poverty here in the USA, only selfish people who want it all and refuse to work. The poor are enabeled by people who, usually, for good reason (inharitance, fraud, theft), feel guilty about their sucess and are shamed into 'helping.'

Thankfully, we don't need to 'kill' any of them. If we stoped feeding them - most would find gainfull employment quickly. The rest would be the disabeled and would need help.

There are a lot of excuses that people give for the lazy - they all melt away when you realise how many people come to the USA without a penny to their name, and hardly any English skills, and they do just fine.

* except for people with mental or physical disabilities that prevent them from pulling their own weight. They deservie our compasion, and it's a shame that they get lumped in with the lazy and the slovenly.

Re:GLP Question...

[Ashtangi]

My gut reaction is one of agreement. I have thought about using MySQL for my own apps, because I have been using, well, Access (where is my asbestos jacket . . .), as my clients all have Access (sigh . . . back in the MSDos 3.x days I liked MS). But I am curious about this.

Seems like an app that stores it's data in a GPLd database does not then become GPL. Is there a good FAQ that answers GPL questions? I do not speak legaleeze so reading the GPL itself does me little good.

Re:GLP Question...

[mark_lybarger]

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

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

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

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

Re:GLP Question...

[Anonymous Coward]

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.

Currently, for GPLed code it makes _all_ the difference. The GPL doesn't restrict use, only distribution. X11 is not GPL, so it might be different. Plenty of people have 'abused' the GPL by putting it on the server. Just a little example: FICS used to be GPLed and free, but ICC took the code, made a _commercial_ chess-server out of it and refused to reveal the modified sourcecode. How? Since the server application is never _distributed_, they don't have to share the code with anybody.

I think the ICC people should reevaluate their stance, but that's just IMHO.

Re:GLP Question...

[nconway]

I believe the PostgreSQL libs (for accessing via other languages) are LGPL, so you don't have to realease your source for those.

AFAIK the entire PostgreSQL source tree is BSD'd (with the exception of a few optional modules in contrib/, but that is being fixed).

Re:GLP Question...

[nconway]

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?

Not sure about the rest of your question -- but I can tell you that it doesn't apply to PostgreSQL. Since PostgreSQL is released under a BSD-derived license (no advertising clause), you can do what you like with it, pretty much.

Re:GLP Question...

[mark_lybarger]

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


"PostgreSQL is subject to the following COPYRIGHT:

PostgreSQL Data Base Management System

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

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


lameness filter

Re:GLP Question...

[euphline]

The last time I wrote a big app, I built in a layer to talk to multiple databases. It could speak w/o any adaptation to MySQL, Informix, Oracle, etc. (Never released a version that actually talked to MySQL, though, back then no one wanted anything but Oracle or Informix.) My feeling was that because it wasn't _dependent_ on MySQL, I wouldn't have to release it under GPL if I didn't want to. Never became an issue, though.

-jbn

Re:GLP Question...

[Micah]

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

Apparently not. Here is what the licensing policy says:

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

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

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

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

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

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

Re:GLP Question...

[Anonymous Coward]

Thanks for the replies everyone!

Wow, all the way down to here...

[krugdm]

...and still no comments relating to the actual review ...

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

[Anonymous Coward]

Its like using COBOL. Who uses it .. honestly, really anymore?

PostG baby.... come on. IF you don't have to money to use Oracle, use PG. Evolution has to move on past old and slug systems

- anon

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

[eberry]

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

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

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

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

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

[Anonymous Coward]

Quick note here:

SQL Server (.NET) is the fastest Windows database

Oracle is the fastest Solaris database

DB2 is the fastest AIX database

MySQL is the fastest GNU/Linux database

There really isn't a good way to benchmark these databases since each wants to run in a different environment with different hardware. Do games run as fast under WineX as they do under Windows? I think not. So is Linux worthless, or Windows better?

I do agree with you, I'd rather license DB2 over Oracle, and I've used 7, 8 , and 9. But Oracle has the best business support and training options therefore will be going nowhere

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

[Anonymous Coward]

"Even MS SQL Server is better than Oracle."

Sorry dude, this aint April Fool's Day. That's a ridiculous assertion - pull your head out.

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

[eberry]

To: Larry Ellison (a.k.a. Anonymous Coward)

Oracle on SLOWaris is slighty faster then mySQL on a 286 running Slackware 95.

Sorry Larry. Oracle is POS. Time to go back to the drawing board.

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

[Anonymous Coward]

Sorry, you're wrong. The only way that MySQL outperforms Oracle, MS SQL or IBM DB2 is if it is in an unsafe mode and with result set cacheing set on, and even then only in very specific applications where result set cacheing is actually useful. In most real-world scenarios, the commercial databases outperform MySQL. I saw a very recent benchmark about a month ago that once again confirmed this. Sorry, can't find the link now... I'll look for it and post it if I find it though.

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

[eberry]

I saw a very recent benchmark about a month ago that once again confirmed this. Sorry, can't find the link now...

Oh I can't find the link now? How convienent. Did you try looking on oracle.com?

Why don't you define real-world scenerio? How was the research done in this subject? Did they try replacing each instance of Oracle with a MySQL server and test that? I can't wait to see this article that pits every database server against MySQL. I never thought it got that much attention.

If you say that it works better in most real-world scenarios then you obviously don't work in the real world. I have worked extensively with Oracle, MS SQL Server, MySQL, and DB2. And if you think that Oracle is faster than MySQL then you have clearly never used it. Since you clearly have no facts to backup anything you say I have no idea why you post at all. See how I posted an article supporting my claim?

Sorry Larry, but no one's biting.

all you need

[PapaZit]

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

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

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

Re:all you need

[smack.addict]

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

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

Re:all you need

[PapaZit]

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

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

Re:all you need

[smack.addict]

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

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

Something Slashdot should run....

[Lord_Slepnir]

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

Re:Something Slashdot should run....

[Anonymous Coward]

0 rows isn't an error

Re:Something Slashdot should run....

[Anonymous Coward]

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

I know this is nit-picking, but returning 0 rows is by no means an "error" -- it's a perfectly legitimate result set.

Why MySQL sucks

[Frank T. Lofaro Jr.]

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

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

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

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

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

MySQL isn't truly open source.

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

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

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

If you care about your data and need a REAL database which is actually Open Source, use PostgreSQL.

See this article:

Why Not MySQL.

Re:Why MySQL sucks

[Anonymous Coward]

I know I shouldn't repond to a troll, but I can't help myself.

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)

The article he quoted is ancient.

I use MySQL, have never had a database go bad in two years, and do indeed care about my data. And MySQL MAX supports transactions. I've even added the ability myself by sending the SQL update & insert commands to a log file. MySQL is a decent, fast database

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

[A nonymous Coward]

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

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

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

GEORGE REESE

[Anonymous Coward]

Smack j00 sux0r. LOLOLOL!!!!!

Stored Procs

[dcocos]

I'm was working on a project where all of the work flow was handled on the DB side in stored procs instead of in EJBs. This made debugging a pain b/c you have to have a DBA do things out side of your normal build process and testing is harder because you have events happening outside of your control. If people would just learn to use DBs to store information and code to do the logic (sorry PL/SQL isn't gonna cut it) systems would be rolled out faster and with less bugs.

Re:Stored Procs

[Anonymous Coward]

well then you are the typical dumb ass. SP are the way to go when developing an application. Let's say all mighty EJB developer there, that you die today in a car accident and your company decides to scap Java and rewrite the app in PHP or CF. You're telling me now I have to decypher your logic. Sorry pal, do as much processing, error checking, and logic as you can in your SP. It will no only cut the poring time down to nothing, but all you have to do as a coder is qualify your variables and do SOME logic and let the database do the rest.

Do yourself a favor and go take some database classes so you can learn why you should be using SP, trigger, check constraints, and transactions in your database design. Then you can come back and post something a little more intelligent.

Re:Stored Procs

[dcocos]

Obviously you've never written commercial software that had to be supported across different platforms and databases. If you are writing an app that is for specific client for once specific project I agree you can (and should) do a lot of the heavy lifing on the DB side. But if you are writing an application for distribution, seperating your DB and code is essential, I've seen too many

if (Oracle) {
} else if (MSSQL){
} else if (Oracle7) {
} else if (Sybase) {
}

As a matter of fact I've taken several database classes and have done work with Database repositories and data movement.

PHP + MySQL = use ADOdb

[Kozz]

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

Plenty of PostgreSQL docs

[Jerky McNaughty]

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

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

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

MySQL in a corporate environment

[mlrtime]

I am new at creating databases and maintaining them. Using MySQL with online tutorials was very easy. I implemented a PHP-MYSQL web page to parse server logs, and it works great in my company.

However, the administration teams quickly found out about the PHP-MYSQL solution and instantly turned it down saying that the tools are not 'standard'. They want something on a commercial DB (Oracle, Sybase) and a 'supported' language (perl/java).

So, I have to rewrite everything using the commercial products. I agree that you cannot have many different products to support (its a headache). But, when an open source solution is implemented and turned down because its not 'commercial' is a problem often faced.

-mlr

Re:MySQL in a corporate environment

[hether]

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

Re:MySQL in a corporate environment

[sheldon]

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

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

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

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

Re:MySQL in a corporate environment

[Anonymous Coward]

Yeah, AT/T is that way too

Good Grief.. Get your facts straight...

[gabe]

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

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

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

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

Got a url where we can read this?

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

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

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

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

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

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

MySQL isn't truly open source.

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

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

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

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

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

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

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

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

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

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

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

[Anonymous Coward]

"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 misinforma"

Too bad MySQL development proceeds much slower than a snails pace. zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz........

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

[mesocyclone]

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

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

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

Does MySQL?

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

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

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

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

DB2 on the Mainframe Works Well???

[Black-Man]

As just having gone thru an upgrade to version 7... I can safely say DB2 on the mainframe blows! Unicode support... they're working on that... Concurrency issues out the wang...

I'll take Oracle 9i on UNIX any day.

Amazon has it for $27.97, 30% off

[peterdaly]

At Amazon: Managing and Using MySQL (2nd Edition), $27.97.

Currently only one review, which rated it a 2 out of 5 stars.

Free shipping on orders over $49.

-Pete
(above link is an affiliate link...)

Re:Amazon has it for $27.97, 30% off

[carlos_benj]

Free shipping on orders over $49

Addall shows it at several places for the same price - including shipping (well, to AZ anyway). One place in the list has it for a couple bucks less.

Answer: inflation

[Anonymous Coward]

I thought it was just me at first, but do any books that get a rating that is not 9 get posted?

Yes, it is because of inflation. It used to be that all books got a rating of 8.5.

Well, duh?!

[DFossmeister]

Why don't you run Adabas, DB2 or Oracle on the linux boxes then? Doesn't seem that hard to me.

DFossmeister

An alternative..

[Digital_Fiend]

If you want to learn about databases in general, rather than learning about a specific database, you might try C. J. Date's book, An Introduction to Database Systems (get it used - and don't worry about those stupid whiners who gave it low ratings, they just don't want to think carefully and precisely about data storage). I would suggest reading this (it's a bit challenging, but people on this website are generally pretty bright and up to a challenge, right?) and then merely perusing a specific database manual.

It seems much more consistent with the way computer science is taught in college: learn the timeless knowledge FIRST, and then the stuff that goes obsolete becomes trivial to learn.

Just a suggestion.

Re:An alternative..

[jonabbey]

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

learn to spell

[Anonymous Coward]

It's A LOT not ALOT.

Re:learn to spell

[Anonymous Coward]

Really? Wow, thanks alot!

LOL, call us back when you have a job kid!

[Anonymous Coward]

I mean, thats a good one. With comedy like that, you'll have no trouble finding a three session deal with FOX.

What, you wern't joking? Oh, well then would you like to try your hand at re-writting the two enterprise Telecoms applications that I work on, so that they don't use stored procedures? Our Oracle developers are awfully stressed at the moment, and could do with some light entertainmant...

Allright, let's solve this once and for all

[marieLeon]

The awnser to this debate is as simple as ever the best SQL Server is this one sorry but someone had to do it

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

[cartman]

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

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

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

Don't forget...

[alien_tracking_devic]

Dude, MySQL is dying.

PPCPP

[Anonymous Coward]

Point to Point Carrier pidgeon protocol.

PostgreSQL Book (was: guh.)

[Christopher Cashell]

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

These books are:

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

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

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

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

[smack.addict]

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

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

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

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

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

[cartman]

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

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

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

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

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

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

Nice troll.

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

does MySQL still lose data?

[Kunta Kinte]

Not intending to be a troll or anything. But I have heard in the past that MySQL will lose a very small fraction of it's data due to it's underlying technology ( ISAM?? or something ).

Is that still true? or has that been fixed?

I think you're wrong

[Tim Ward]

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

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

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

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

[cartman]

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

Actually, I amend this. ATOMicity is not required if there is only one user who ever updates the data, and the database is backed up beforehand.

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

[alacqua]

...Use PostreSQL or another RDBMS...

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

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

[Anonymous Coward]

Please leave slashdot. Forever.

--/. anti-lamer squad

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

[mahmut_kursun]

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!

MySQL is Nr. 1 within the "el cheapo" niche.

Its "everything goes" software if you do not have any budget.

Even years ago, lots of Webproviders offered Webpages with PHP+MySQL for some pocket money. You couldnt find that with PostgreSQL or MS SQL Server or similar systems. Today it is more or less the same.

I mean Data is not safe and is never safe and it wont be any saver if you have no knowledge. But getting into PHP+MySQL is easy, so you can get MySQL knowledge easy and can use it well. Not everybody can invest in experienced Database admins + Sysadmins + Programmers + Maintainers + OS/Software/Hardware.

See it this way, if you make polls on Webpages and a search engine and a Forum and stuff like that, the data has no value at all. Would you care about that data? In case you would lost some, you could install new or replay an old backup set!

Here are 3 big advantages for Beginners:
PhpMyAdmin - Idiot proof Admin-Interface to MySQL, you upload it and you run it, no SQL knowledge needed to create/delete/move a database. Good part, it became so popular that PostgreSQL benefits from PhpPgAdmin now.

New Riders, Title: MySQL, Editor: Paul Du Bois.
It makes a perfect MySQL Handbook. If I would run that MySQL company, I would license it and add it into every sold copy of MySQL.

Foxserv - WAMP for idiots, unzip and go.

I mean what more can one expect? You will need anything "save stuff" only and only if you are going to do something that you will switch on today and you want it running reliable for a long time.

Such a thing can only be archived with system and brains. I say you need so many stuff to develeop and run a Database reliable with no downtimes that you would not believe it yourself. 1 perfect Database = 1 Million USD per year.

1 MySQL Database = 1060 USD per year. 1000 USD for the amateur contractor and 5 USD/Month for the Webserver.

Notice there is no XML chapter?

[Anonymous Coward]

This is mySQL's whack-ass handicap. How the F do you insert XML in this mofo?

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

[smack.addict]

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

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

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

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

And, by the way, MySQL DOES support transactions.

Other features

[Wee]

So what other advantages does MySQL have over PostgreSQL?

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

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

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

-B

Re:Other features

[kcbrown]

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

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

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

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

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

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

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

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

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

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

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

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

Yeah, I know what you mean. :-)

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

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

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

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

[Anonymous Coward]

Not quite. Even if only one user is updating the database at a time, if they do more than one insert/update/delete that need to be done together or not at all, then you still need transactions.

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

[Anonymous Coward]

Most applications that use databases have at least one context in which more than a single table is updated at a time. You are describing a very niche usage of databases. 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.

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

[smack.addict]

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

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

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

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

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

A good starter, but not for the advanced user

[ramakant]

I bought this book thinking it would be a great resource for an administrator using MySQL in a production environment - My mistake. It is true that MySQL is in use by a lot of large businesses and websites, but this book does not cover issues relevant to those implementations. If you are a programmer (even a very inexperienced one) and want to learn more about using MySQL, buy this book. If you have experience with MySQL and are thinking about using it in a heavy traffic production application, this book is useless to you. Stick to the docs on the MySQL site.

The real reason SPs suck

[GunFodder]

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

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

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

And of course.

[Anonymous Coward]

Dude where's MySQL?

MY - S - Q - L

[Anonymous Coward]

Here are some alternative lyrics to the song YMCA that I came up with:

Verse:

Transactions, no they're not in the spec
Procedures, and let's not forget
Triggers, Monty says we must not let
Foreign keys into the feature set

Chorus:

This lightweight database...

MY - S - Q - L It has the tables locked
MY - S - Q - L
Well it's fast and it's free, but you surely must see
it doesn't pro-vide a-tom-icity

MY - S - Q - L It's not relational
MY - S - Q - L
You can do a SELECT but it's less than perfect
You can't gar-un-tee in-tegrity.

Works cited:
http://openacs.org/philosophy/why-not-mysq l.html
http://www.mysql.com/doc/A/N/ANSI_diff_For eign_Key s.html
http://www.pgro.uk7.net/innodb1.htm

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

[FlyingDragon]

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.

Sheesh... have you even seen the average web application?

Most of them don't even index their search columns or check their return codes. Look around -- see all of the counters, polls, message boards, Slash clones, and more? It's not hurting them much. They don't use the tools they have; do you really believe they'd use data integrity tools if they had them? Of course not.

As long as it works properly in normal circumstances and fails gracefully in exceptional events, that's 'good enough' for 99+% of the tasks out there. No, it's not enough for eTrade, but there are other tools for them.

Which books for newbie LAMP?

[Anonymous Coward]

If you had to pick only one book for each of the following technologies, which would you pick for someone who has taken a Linux 1 class, a Unix 1 class, and has a basic understanding of the command line, DOS, Linux, Unix, etc. (I have several web sites running on Apache currently serving static pages, have been able to edit/configure the Apache config file, and have compiled my own kernel, but haven't been able to get virtual hosting to work, and using an old computer as a diskless terminal is a little beyond my capabilities right now, as examples of my abilities):
These are the technologies I need to purchase one (and only one for now) book.
MySQL
PostgreSQL (downloaded an online book a while ago, any other suggestions)
PHP (I have Sam's 24 hour PHP book)
Perl (I have Programming Perl 2nd edition, a little too technical for me right now)
Apache
Any suggestions would be greatly appreciated. Thanks.

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

[Anonymous Coward]

logical dat corruption dut to lack of transactions

HUGE

Re:FNCP

[Anonymous Coward]

Excellent. We'll show the pathetic CLIT who's boss!

Eat it and eat it hard, CLIT!

Pathetic

[Anonymous Coward]

The CLIT showed their lameness by not getting first post. Don't make it worse for them.

Discussion summary

[Crag]

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

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

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

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

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

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

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

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

Thankyoupleasedrivethrough.

I use MySQL and love it

[wessman]

I have been using MySQL longer than MS SQL Server, and trust me, I'm forced to use MS SQL Server at work; for my own projects, MySQL is my choice. It's just so damn easy, efficient and not filled with filler like MS SQL.

That's my two cents.

MySql and PERL for the Web

[pcs305]

"MySql and PERL for the Web" by Paul DuBois. You can have an Apache/Perl/MySql application up and running with the bare minimum experience. Although that may be dangerous. http://www.kitebird.com/mysql-perl/

Dude.

[Byteme]

What bash (no pun intended)? Seems more like a wish list to me. If you truly do use Sybase and compared MySQL to it you would see where it lacks. We have Apache that does quite well at competing with the commercial offerings. It would be nice to see MySQL do the same instead of remaing a 'lite' alternative. ...and to answer the previous question, can you use another app to take the place of stored procedures? Yes, but the preferred method would be having those other apps execute SPs with parameters for the sake of performance (precomiled code runs faster as it has already gone through the optimization), maintainability (you can change the database stucture or the code without touching the application that executes it - as long as parameters passed are the same), and security (giving users execute permissions rather than update delete as they would need if the application did not use SPs). All that said, rollbacks, triggers views and such are also lacking. I guess this is all offtopic for a book review, but I did not start the thread.

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

[Anonymous Coward]

smack.addict wrote: '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 engine'

Squeaking of Trolling George, shouldn't you let folks know up front when you're posting messages about your own book?

http://slashdot.org/~smack.addict/
http://www.imaginary.com/~george
http://safari.oreilly.com/main.asp?bookname=msql2