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.

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

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 [owasp.org]. They released a very informative paper on building secure webapps, and it's free.

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

Re:MySQL

[noda132]

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

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

PHP + MySQL = use ADOdb

[Kozz]

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

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.

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