8 Reasons Not To Use MySQL (And 5 To Adopt It)

Esther Schindler writes "Database decisions are never easy, even — or maybe especially — when one choice is extremely popular. To highlight the advantages and disadvantages of the open-source MySQL DBMS, CIO.com asked two open-source experts to enumerate the reasons to choose MySQL and to pick something else. Tina Gasperson takes the 5 reasons to use MySQL side, and Brent Toderash discusses 8 reasons not to. Note that this isn't an 'open source vs proprietary databases' comparison; it's about MySQL's suitability in enterprise situations."

Re:oooo, goody

[tomhudson]

The guy is arguing with himself - the first 2 reasons:

• MySQL Uses the GPL
• MySQL Doesn't Use the GPL

Oh, boo-hoo, dual-license bad!

The rest of the article is equally stupid. For example, "If you already own proprietary licenses ..." has NOTHING to do with whether Mysql is a good fit or not. Next it will be "I hae Pepsi in the fridge; I really want a glass of free cold water or a bottle of Coke right now, but I'll buy some more Pepsi instead seeing as I've already standardized on it".

The 8 reasons not to use mysql

[mgkimsal2]

1. MySQL Uses the GPL
2. MySQL Doesn't Use the GPL
3. Integration With an Existing Environment
4. Product Maturity
5. Feature Set Maturity
6. Availability of Certification
7. Corporate Considerations
8. Perception of Scalability

They all have *some* merit, but all are very dependent on your situation. 1 and 2 seem to cancel each other out, as in if #1 is an issue for you, #2 probably wouldn't be. #3 is sort of weak, arguing that if you already have many other databases, adding yet another different system is detrimental. That's not an argument against MySQL, but against disparate systems altogether. The rest of the issues are matters of degree. "While MySQL does have a certification training program, its training availability is not nearly as widespread as for, say, Oracle or MS-SQL Server." True, but if you're comfortable with the level of quality of certified MySQL people, then go forward. It'll contribute to the general upward spiral of adoption, hiring, certification and so on. MySQL is going to keep growing, it's just a matter of how quickly and in what directions.

P.S. Printable version here -> http://www.cio.com/article/print/113111 [cio.com]

Reasons not to use MySQL? These are stupid reasons

[Tairan]

Someone need's to slap this author with large trout. There are many reasons NOT to use MySQL, of which this article touches on only one. For example:
--Innodb scaling across multiple processors (MySQL bug ID 15815, still not completely fixed)
--Limit of 1024 current transactions ( MySQL bug 26590)
--Terrible performace when running MySQL Cluster
--Single threaded mysqldump exporting and importing (recently fixed in 5.1)
--Single threaded replication (making many changes? Don't count on it if you're running replication)
--Poor handling of subselects
--ineffecient ORDER by and GROUP BY
--Poor quality filesort algortythm (want to see your $20,000 dollar database server die?)
--better performance in 4.1.x

Let's also mention that 5.1 has been out in beta for years now. When is it ever going to ship? MySQL now is proclaiming fixes in 5.2, and 5.1 isn't even on the board to ship yet.

With all that, and more, I'm surprised this author could only come up with "it isn't made by Oracle" and "product mateurity."

*disclosure -- yes, I play with MySQL databases all day long in large high use production environments. MySQL is great for small systems, but there -are- some problems when running on large enterprise grade systems. It'll get there

Re:Warning: mysql_connect(): Too many connections

[nuzak]

No, that's part of the "8,573 reasons to not use PHP" article.

Re:The 8 reasons not to use mysql

[EsbenMoseHansen]

Are those supposed to e reasons? How about

• Command line completion in mysql client sucks
• Transactional support is a bit sub-par compared to postgres.
• Unless carefully configured, not nulls, group bys and quoting are broken.
• Sequence support is also a bit sub-par.
• Some entity name's case sensitivity is dependent on host filesystem
• Subselect support slow and memory hungry (I've only tested this a little)
• Blobs cannot be accessed as streams

Only 7, but all of those are real real complaints :)

Re:Additional reason

[Shados]

The more advanced RDBMS engines have OLAP/Datawarehousing support built in. For example, in SQL Server, thats SSAS, and it works wonderfully well, and is as integrated as it can be. Oh, one can argue that its technically a "separate" database, but from an installation, licensing and development point of view, they're one and the same. (Analysis Services also happens to be the market leader in that department...).

Thats just an example, and while thats Microsoft, I'm sure there are plenty of non-Microsoft equivalents for relational databases and olap cubes being integrated as one (and a half?) product.

MySQL makes sense... sometimes

[R3d Jack]

Many applications need a small to medium size database that contains important but non mission-critical data. MySQL is perfect for those applications. No licensing hassles, no funds requests, no major administrative overhead. I don't have enough experience with MySQL to recommend it for a really critical database, but we do have plenty of need for smaller databases that we can set up quickly. I certainly don't see spending a lot of money on SQL Server (which we are doing) or any other big commercial server to run a bunch of small databases.

Re:Reasons not to use MySQL? These are stupid reas

[drinkypoo]

Don't forget there's other options. For example, Sybase and MS SQL Server (based originally on Sybase 10) are both faster than oracle and more compliant than MySQL.

Re:The 8 reasons not to use mysql

[larry bagina]

#8: "February 31, 2007"

#2 is not an issue with enterprise applications

[anomalous cohort]

If you want to distribute the license for the database along with your own project, your project must either be licensed under a similar compliant license, or you must pay for a commercial license.

This article comes from CIO website so I think that it if fair to say that they are most interested in applications for the enterprise. Enterprise applications don't bundle the database. They may require a certain database vendor product/version or a small set of database vendors but they don't bundle the database with the application. This is true whether or not you use SQL Server, DB2, Oracle, MySQL, or PostgreSQL.

IANAL, but IMHO there is no legal restriction to selling a commercial, closed source application that requires MySQL as long as you don't include the MySQL application with it. This isn't a problem for enterprise applications because businesses that need such applications already have sufficient IT experience to run the vendor specific database that they are going to accept. If a company doesn't have sufficient IT experience to do this then they are going to have to go the SaaS route. Their are not going to be able to manage the application even if it did come bundled with a vendor specific database product.

Re:The 8 reasons not to use mysql

[EsbenMoseHansen]

#8: "February 31, 2007"

I almost included that one, but they have actually fixed that 5.02. You can still specify ALLOW_INVALID_DATES as an sqlmode for that nostalgic feeling, though.

Re:lame "bias" argument

[dedazo]

Last time I checked, DB2 was more scalable than Oracle

That depends entirely on the platforms you happen to run them. DB2 on NT (what used to be called "UDB") is a joke; DB2 on OS/390 is pretty much what defines a "big-iron" database. Oracle on NT is nowhere near as good as it is on Solaris. But Sybase on NT is actually quite good - almost as good as SQL Server on the same hardware. Sybase 12.x on HP-UX is also quite good.

Re:Reasons not to use MySQL? These are stupid reas

[Anonymous Coward]

Bzzt! Thanks for playing!

SQL Server was based not originally based on Sybase System 10. It was much earlier than that. Thats why there is no CT-lib client interface for SQL Server, it didn't appear till System 10.

Whether either are faster then the big O depends on a number of factors. Traditionally, Sybase has been strong in the Wall St. type businesses where there is a very high OLTP workload.

Scaling, robustness, etcetera.

[seebs]

I recently started looking into databases, and I asked a bunch of friends. All the experienced ones gave roughly the same advice: If you don't have time to read directions, just throw something together with MySQL. It'll be okay. PostgreSQL would be better.

So I took the extra ten minutes, and I'm pretty happy.

Every large site I know of that uses MySQL has had scaling problems of one sort or another under load, usually to do with trying to handle multiple writes to the database. At least a few people have simply swapped in PostgreSQL and seen problems disappear instantly. One friend did performance testing, where what he found was that MySQL was faster for small sets of clients, but that it slowed down faster, and that for largish N, he couldn't get it complete the test on the available hardware, but PostgreSQL just ran.

Having set up both a few times now, and having debugged problems with both, there is simply no way I'd use MySQL given any choice at all. It runs, but it feels accreted rather than designed. I know, Cathedral and Bazaar and all that... But there are times when you really do want the feeling that someone considered something up front.

Re:I tried to pay attention. I really did.

[Anonymous Coward]

I think there's a difference between "powered by" and "used by". Google didn't spend 7 man-years creating BigTable for nothing...

hmmm, very incorrect

[kpharmer]

> Not all database types are fit for all purposes. Relational databases for instance are bad for data mining/warehousing
> due to poor query performance but good for data entry due to high transactional performance

Your information is incorrect:
1. relational databases have been used for warehousing and reporting (data marts) for 15 years - and are used for more than any other solution for this purpose. Ok, sure you've got a lot of OLAP out there, but there's probably almost as much Relational OLAP (ROLAP) via Microstrategy, etc as there is true OLAP.

2. Take db2 for example, it:
        - support three different forms of range partitioning (union-all views, multi-dimensional clustering, and range partitioning)
        - supports hash-partitioning of the data across many servers - think "beowulf cluster"
        - given the two above you can spread your data across 100 4-way servers, each with fibre access to a heavily-cashed SAN.
        - Now when you issue a query db2 will spin up all 100 servers - each hitting its own local piece of the data (not 100 copies of the whole data, but each server with 1% of the whole).
        - Because it also supports range partitioning each server is probably only going to scan 10% of the total data in a typical query.
        - Because it support query parallelism it'll split each query on each node into four separate pieces (getting near-linear performance speedups) - now you've got 400 cpus working.
        - Because its optimizer is about the best one on the market - it isn't going to auger itself into the ground on your 100 line sql query.
        - That should allow you to crunch down a billion rows to your 24 row output in couple of seconds at most.
        - Of course, it's also smart enough to rewrite your query to automatically hit any summary table that could speed the query up. So, it may only have to scan 2400 rows - and may return the results in 0.001 seconds.

3. The point is that warehousing, reporting and analytics work very well in a relational environment. But you need to pick your products well. If you want to handle terabytes of data you can put it in MySQL, SQLite, MS Access, Foxpro, etc - if you really had to. But, life will be *far* easier if you put it into a product that can handle the volumes much better.

Why NOT to use MySQL

[buss_error]

I find that Brent Toderash's comments tend toward a faint aroma of FUD, while Tina Gasperson didn't exactly hit a hole in one with two of her five comments. Brent's comments seem to be more of "It's open source, it's not open source", certification of training issues, and in general only one half assed comment about the technology itself, which even he doesn't pretend to offer supporting evidence for.

Tina is dead bang on with the TCO commnet; We pay more for Oracle support than we pay two Certified DBA's.

Brent's comment about "ignorance" of workers not knowing a company has a site license for propertiery systems is not a technology fault, but a management fault. That cannot be properly consider a fault of the technology.

Seems to me to be a send up. A trial ballon to support a future brochure slick about why paying $$$ for an RDBMS makes sense and why something "Free" isn't. We all know that using open source isn't free unless you have unlimited staff time and don't count system administration costs against a particular project. Open Source CAN cost a lot more than a closed source system, but it's not something I've seen. I'm sure there are examples, I just don't know of any.

There are also times when open source doesn't make sense. Like in situations of unlimited libility in case of failure. Take a nuke reactor. Say you use open source products to control that reactor, and it melts down because a pump failed to start, a valve was incorrectly closed, and humans didn't follow proceedures. Automatically, it's the fault of the open source product, obviously, because you were too cheap to go buy "good" software.

Until the human race as a whole can value a gift freely given by a stranger, it won't grow much past it's current point.

MySQL Gotchas

[sneakerfish]

This page has a list of MySQL gotchas

http://sql-info.de/mysql/gotchas.html [sql-info.de]

Some of my favorite are things where MyQL accepts values it shouldn't and it doesn't throw an error. For example you can insert a 0 into a date field, 30000000000 into an in column (it will just ignore the higher order bits.

MySQL is OK for quick and dirty, but it will always be dirty. If you want MySQL to be decent:

1) Set it up with InnoDB and make that the default table type. MyISAM should only be used for data warehouse tpye applications where you are doing a lot of IO and its OK for the DB to be down for hours while you recover your corrupted MyISAM tables.
2) Set the strict sql mode in the my.cnf. I don't remember exactly what the parameter name is, but you want MyQL to throw an error if you throw stupid values at it. Otherwise it will accept wacky values and you'll end up debugging it later.
3) Set the default character set to UTF-8 if you can. This can be a bear but its worth it to be able to handle foreign characters.
4) Avoid the fancy "features" if you can. The old features still have unresolved bugs and it isn't going to get any better with more and more storage engines going in.
5) Monitor the performance constantly and be prepared to partition your data. Scale out isn't always as easy as it sounds.

Re:The 8 reasons not to use mysql

[Snover]

You can set SQL modes, such as STRICT_ALL_TABLES, that will cause MySQL to reject invalid data instead of truncating it. There is documentation [mysql.com] about the various SQL modes.

Re:The 8 reasons not to use mysql

[spyowl]

It's this kind of thing that makes me still suspicious of MySQL. I hope that for the next

release - 6.0 or whatever it is - they can make a clean break with historical stupidity, and release a DBMS that gives safe, ANSI-compliant behaviour out of the box.

Your suspicions are correct generally. However, your hopes may have to live a little longer because if the next version of MySQL is based on anything that they have out today you may be in for a disappointment. Here are my real reasons not to use MySQL:

• MySQL is not scalable: there is no table partitioning. As your data grows and so does your use of the database, you'll find your options for scalability are very limited to what you can "hack" around on your own. Other RDBMS solutions like Oracle, MSSQL, and yes, even Postgres have anywhere from decent to excellent scalability.
• Advertized "enterprise" features are hacked into the stagnant and very monolithic MySQL codebase and frequently do not deliver as advertized. Here are examples: Sub-selects are not well optimized, if at all; indexes cannot be used more than once in the same query to help with optimization; using a combination of triggers and stored procedures within transactions in a medium to high usage environment results in crashes that even MySQL cannot explain; row-level locking only exists if you only have primary key, and no other indexed columns that you need to update, and you are updating using that primary key; does not truly support MVCC [wikipedia.org] - even with InnoDB your selects may block updates and the other way around; replication forces further limitations in concurrency; the list can go on and on. None of these are a problem to any noticeable extent with any of the other "enterprise" RDBMSes.
• More on replication: even though MySQL has somewhat elegant solution for replication, besides limiting concurrency (as already mentioned) and introducing serialization, this solution poses additional tricky fundamental problems. For example, it is nearly impossible to implement a true multi-master replicated environment.
• No HA solution: Oracle and MSSQL (recently and more limited) offer true HA solutions that can increase your database availability in case of failure, and within the HA environment guarantee the transactions and data the applications were led to believe was successfully manipulated. This cannot be achieved with heartbeat and replication using MySQL, or even Postgres for that matter.
• Critical bugs dealing with data consistency: This is not a statistical analysis, but MySQL has had and still has a lot of critical bugs dealing with critical part of the RDBMS - data. e.g., you cannot rely on RDBMS for storing your data if, when queried under certain circumstances, it returns NULLs when it should return the correct data. It is not fully comprehensible how a product released as stable (such as version 5.0) can still have so many critical data-related bugs.
• Horrible codebase: If you are at least a decent programmer, please have a look at MySQL code: monolithic, one main file with succession of countless if blocks for parsing, optimizing and running queries; features such as triggers, stored procedures, and replication visibly hacked in to the existing "bad" design. There's very little abstraction that can leave data files in inconsistent and unreadable state in the event of the server crash (mostly MyISAM). And then, just for kicks, please have a look at Postgres source code: well-organized, separated into well-designed components you'll get acquainted with certain satisfaction to components that do parsing, planning, optimization, execution, and other functions. Code is well-commented and, as a programmer, it will give you a certain comfort when dealing with the software. This is a very important point and demonstrates why Postgres, for example, having a solid foundation, can implement advanced features (such as transaction savepoints,

Re:MySQL the db for people who don't understand db

[einhverfr]

I will take a crack at this.

With a traditional RDBMS, you define your data essentially using a pseudo-mathematic model, assign data constraints and triggers to ensure that the model is not violated, and add views to provide handy ways or putting the data together. You may optionally add stored procedures as a functional interface to that model.

Until 5.0, MySQL didn't even try to be this sort of database. Even in 5.0, you only get real data constraints, triggers, etc. on some types of tables, and strict mode (which does actual data type checking) can be turned off by the application. MySQL still does not compare well to traditional RDBMS's in their home turf (though PostgreSQL, Ingres II, and to a lesser extent Firebird do compare reasonably well-- Firebird to a lesser extent just becuase of some interesting cases involving NULL's).

In fact, MySQL is almost, though not entirely unlike Codd's idea of an RDBMS. MySQL is not something to consider for your RDBMS. Period. End of story. It is not worth it.

However, if what you want is a simple data storage engine for your one app with an SQL interface and many of the features from real RDBMS's, MySQL is not bad. It is a remarkably flexible software development tool with many very useful. It just is not a substitute for a real RDBMS (where, for example, the server must authoritatively and robustly provide data sanity checks).

Re:The 8 reasons not to use mysql

[LLuthor]

Last I used it, mysql never needed to use a cost-based optimizer simply because it never could do anything but nested-loop joins. Sadly this means that ANY non-trivial query can bring mysql to its knees for ALL users connected to that server instance.

Compared to PostgreSQL's optimizer, which tried very very hard to avoid nested loop joins, and can handle medium sized queries (upto around 16-table joins) with relative ease, its a real burden to port apps to work with mysql.