MySQL - This is not a mature or reliable RDBMS
Written: Feb 22 '05 (Updated Feb 23 '05)
|
Product Rating:
|
|
| Management Ease: |
 |
|
| Technical Support: |
 |
|
| Reliability: |
|
|
| Scalability: |
|
|
| Availability of Third Party Software: |
 |
|
|
Pros: MySQL is free.
Cons: In this case, you get what you pay for.
The Bottom Line: MySQL presents too many data integrity problems to be considered for critical applications. If cost is a factor, consider more robust open source solutions such as PostgreSQL and Firebird.
|
|
|
| jordinfixes's Full Review: MySQL |
I've been reading lot's about open source databases as of late. There's a reason for this and that is one of my client's moved from Microsoft SQL Server 2000 to MySQl 4.1.7. Now let me be clear about the key issue here: my client didn't chose MySQL because it is a better product, rather, they chose MySQL because they had not properly licensed SQL Server. The key issue was of cost, not of functionality. Unfortunately, the business managers failed to recognize that the initial cost of a RDBMS is almost immaterial when compared to the ongoing development and maintenance costs usually associated with running a database system.
I was hesitant to support the switch to MySQL because I'd read many philosophies in the MySQL reference manuals that contradict traditional practices of sound database design. A key issue for the MySQL designers seems to be the tradeoff of performance versus data integrity. In every instance where the database has the opportunity to enforce data integrity, MySQL takes the approach of suggesting (and often forcing) client application logic to perform these critical tasks. The lack of support for stored procedures and proper transactions causes the MySQL designers to suggest that optimally SQL queries, data validation, and referential integrity checking be embedded into client application logic as opposed to being enforced by database. The MySQL ideological theme seems to be that having the database provide these features is an unacceptable performance burden. In the world I come from, MySQL's "ideological theme" is not only ridiculous, it is a key indicator of bad database and bad client/server design. For all the speed gained, a database that allows corrupt data to enter its relations is entirely useless. After reading the MySQL reference documentation, I'm left to believe that MySQL's designers never read Dr. Codd's books on the relational model (Dr. Codd is the inventor of the relational database) and simply don't understand what a relational database management system is supposed to provide.
So let's get down to the nitty-gritty here. My background is in computer science. Apart from having worked with DB2, Sybase, Oracle, and Microsoft SQL Server over the last twenty years, I specialized in relational and object oriented database systems during the completion of my computer science degree. Over the last few months, I've been testing MySQL 4.17, PostgreSQL 8.0, Firebird 1.5, and MaxDB (first release). I'm going to focus on MySQL because that is the topic at hand.
Let me summarize my findings on MySQL as follows: due to data corruption problems, missing features critical to effective client server design (such as stored procedures) and buggy administration tools I cannot recommend MySQL for any mission-critical application. There are so many robust and complete RBDMS products on the market that even considering MySQL seems unproductive. This is a big statement so let's examine why I feel this way in a little more detail.
--- Transaction Management Problems ---
MySQL 4.17, when using its InnoDB storage engine, does provide ACID transaction management. However, the system's insistence on also supporting non-transactional storage engines renders the transaction management features useless. In order to support non-transactional engines, MySQL forces default values (and bogus values when data is submitted in the wrong format) into the database. These default and bogus values are also forced into transactional tables without throwing errors. Let's look at an example of this.
During my testing, I created the following table:
MyTable
(
MyKey varchar(10) NOT NULL,
MyDate date NOT NULL,
MyNumber tinyint NOT NULL
)
To my surprise, MySQL allowed me to execute the following inserts on this table:
1.) INSERT INTO MyTable (MyKey, MyDate, MyNumber) VALUES (NULL, '2005.02.99', 1000)
2.) INSERT INTO MyTable (MyKey, MyDate,) VALUES ('001', '2005.02.31')
3.) INSERT INTO MyTable (MyKey, MyDate, MyNumber) VALUES ('1234567890ABCDEFG', '2005.02.15', 100)
Here's what was actually inserted into the database:
1.) '', 0000-00-00, 127
2.) 001, 2005.02.31, 0
3.) 1234567890, 2005.02.15, 100
In case 1, I insert the disallowed value NULL into a non-null allowable field. I also inserted a bogus date value and a numeric value that overflows the tinyint field. My SQL accepted all of this without error substituting the values shown.
In case 2, I insert a slightly less bogus date (that is none-the-less, still bogus) and forget to include a required field entirely. As you can see, MySQL accepted the "slightly less bogus date" verbatim and substituted zero into the required numeric field I left out. MySQL threw no errors at this insert.
In case 3, I intentionally overflow the varchar field and insert acceptable values for the reaming fields. Once again, MySQL throws no errors and instead truncates the varchar overflow.
The end result of all three inserts is a database that contains completely bogus data. (Gee... I'd sure hate for that numeric overflow to be a $1,000 receivable that is now recorded as only $127.) MySQL, by design, substitutes default values when required values are missing and when data is passed that is in the wrong format for a field. Date fields are particularly problematic because MySQL has no date checking at all. By the end of this, I truly had to ask the obvious question: "What good is the ability to rollback a transaction if the database doesn't report data errors?" The answer is as I've alluded to above. Transaction management is rendered somewhat useless because MySQL accepts and forces bad data into its relations.
Version 5.03 of MySQL (now in beta) ostensibly corrects these problems with options that force the database to fail any insert or update for which required fields or bad data are passed. Unfortunately, the beta 5.03 version truly is beta as it crashed many times on my test server. Thus, I must fault MySQL for its lack of data integrity and transaction management because this is how the software exists presently.
--- Default Value Problems ---
MySQL places implicit default values on all attributes marked as NOT NULL allowable. In essence, this entirely defeats the purpose of required value rules in the database. Once again, this conspires (along with MySQL's automatic "bad data" substitution features) to almost ensure that bad data will end up in the database.
--- Referential Integrity Concerns ---
MySQL 4.17 supports declarative referential integrity and cascading update/delete operations for tables that use its InnoDB storage engine. In my testing, once I declared foreign keys and appropriate supporting indexes, InnoDB seemed to enforce proper referential integrity checking. I tried inserting bogus foreign key values and alienating child records by deleting a parent. Both operations were correctly stopped by the database. (Note, I didn't test cascade operations.) I have two concerns however. Firstly, foreign key support is a new feature in MySQL and the documentation suggests that its implementation places a high performance burden on the database. The MySQL documentation actually still recommends that you implement referential integrity checking in your application logic (which is something you should never do when data integrity is paramount). Secondly, if you use programs based on MySQL (such as open source admin tools, shopping carts, web storefronts, etc.) these programs usually do not support MySQL's declarative referential integrity and thus you must depend upon error-free client application coding. Few client applications are bug free and as a result this leaves you the chore of writing scripts to periodically check the referential and data integrity of the database. These tasks are unnecessary when using a more robust RDBMS system that includes mature referential integrity and data range checking.
--- Lack of Stored Procedures and Triggers ---
The lack of stored procedures and triggers may seem like a small omission at first until you consider that this means all business rules and database logic must be coded in the client application. This causes a number of problems some of which are:
1.) Development teams working on different applications but accessing the same database will undoubtedly code conflicting and inconsistent rules.
2.) Processing load cannot be effectively distributed between client and server because client programs must handle business logic that would otherwise be centralized in the database server.
3.) Developing client application programs becomes more complex, consumes greater time, and becomes more bug prone because all system logic must be coded in the client (as opposed to abstracting portions in the database).
4.) Issuing dynamic SQL from the client creates performance and security problems. When stored procedures and triggers are created in the database, the database precompiles the SQL code and controls access (both to the code and to the underlying tables).
5.) Common SQL routines cannot be easily shared amongst different application programs (as they would be if stored in the database).
--- Buggy Administration and Query Browser Tools ---
Ugh. I got very tired of using MySQL's Administrator and Query Browser tools. The problem with these tools is that they are full of bugs. The Admin tool is slow and frequently sends syntactically incorrect SQL to the database. The Query Browser isn't much better. When editing records, the query browser reported duplicate keys when I tried to update a key to a new unique value. (This left me to write SQL update statements to do the task manually myself). The most significant failing of the Query Browser is that it doesn't provide a mode that allows editing of normal SQL scripts. In quick editing mode, the Query Browser let's you enter a single query at a time. (Note, you cannot save this query once you've entered it. In fact, if you mistakenly close the Query Browser without first copying and pasting your query into a file, the Query Browser throws your query away.) In script mode, the Query Browser allows you to enter multiple SQL statements, but none of these statements can return rows. (If you attempt to return a result set in script mode, you get a critical API sequencing error). In short, I couldn't use the GUI tools to perform even the most basic database administration tasks without resorting to typing commands myself using the command line (CLI) tool. The CLI tool requires special mention because it is the only tool in the MySQL toolset that actually seems to work. Half way through my testing, I ended up managing my database by running SQL scripts in the CLI tool. The CLI tool allows you to run SQL, DML, and any combination of commands you need. So what's the problem with this you ask? Oh... well... only this... the CLI tool is reminiscent of how I used to maintain database products... oh let's say, about fifteen years ago. In today's world, every major relational database product has GUI tools that offer vastly increased productivity than having to write scripts for every database task. After working with the MySQL CLI for a while, I really *did* feel like a bit of a dinosaur. I also felt like I'd be wasting my client's money because there are far more effective ways to manage a RDBMS system.
Just to provide a basis of comparison, let's take Microsoft SQL Server and see how it stacks up to MySQL. Here we go.
Validates data ranges and date types: SQL Server (Yes), MySQL (No)
Enforces full referential integrity: SQL Server (Yes), MySQL (No)
Provides transaction management: SQL Server (Yes), MySQL (Partially)
Supports stored procedures: SQL Server (Yes), MySQL (No)
Supports triggers: SQL Server (Yes), MySQL (No)
Provides full support of SQL commands and aggregate functions: SQL Server (Yes), MySQL (No)
Includes standardized procedural query language: SQL Server (Yes), MySQL (No)
Provides custom data constraint rules: SQL Server (Yes), MySQL (No)
Has productive GUI tools: SQL Server (Yes), MySQL (No)
Provides tools for visualizing and drawing foreign keys, constraints, and data rules: SQL Server (Yes), MySQL (No)
Provides complete documentation out of the box: SQL Server (Yes), MySQL (No)
Scales to large numbers of users: SQL Server (Yes), MySQL (No)
Provides full range of RDBMS features
while simultaneously offering superior benchmark performance: SQL Server (Yes), MySQL (No)
What's interesting is that these are just the few things that came to mind during my brief testing with MySQL. My expectation is that the gap between these two products would grow considerably under greater scrutiny. I actually surprised myself during this process because despite initial reservations, my open source friends had me convinced that MySQL would stack up well. To this I can only say the following: those that are convinced MySQL stacks up to an enterprise class RDBMS have either not used an enterprise class RDBMS or are smoking crack (or both). Once you start testing, there is no question that MySQL isn't even remotely in the same league as systems like Oracle, SQL Server, and others.
So... under what conditions would I recommend MySQL? Gee... that's a tough question because I can really only think of one answer: when the project is very small and cost is a key issue. Even in these circumstances my experiments with PostgreSQL and Firebird show that while open source databases haven't reached the level of completeness of commercial products, there are much better alternatives to MySQL. Just as an example, PostgreSQL 8.0 provides almost all the features of SQL Server that I outlined above in my SQL Server versus MySQL comparison. Now this is not to say that PostgreSQL is as complete or as robust as SQL Server, but it does have most of the features that are required in a RDBMS and that are missing in MySQL. Truly, I could only recommend MySQL for databases of a few tables and even then only when data integrity isn't important. As the database grows in size (both in terms of number of tables and number of rows in those table) data integrity problems allowed by MySQL would seem to entirely outweigh its free price tag.
In summary, after testing MySQL 4.17 my suggestion is to avoid this product (at least until it grows up a bit). Some of the other open source databases (PostgreSQL 8.0 for example) seem to deserve further investigation and attention. As a database analyst, MySQL seems lacking in too many critical areas for me to recommend it for any serious project. There are many other free and cheaply priced alternatives to MySQL that seemingly do a much better job in allowing you to protect data and distribute application logic.
Recommended:
No
|
|
|
|
Epinions.com ID:
jordinfixes
|
|
Reviews written: 7
Trusted by: 2 members
|
|
|
- Add to My Yahoo!: 
- Add to Google Homepage: 
© 1999-2009 Shopping.com, Inc.