The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

What DBMS for a website? MySQL?

I need to build a website that accesses a database. I do not expect a large volume or a heavy load.

I'm considering finding a webhost with Linux, Apache and PHP (because that's what I've used in the past).
Usually MySQL is offered, but I've heard the worst about MySQL (unreliable, loses data, no transactions, etc). If I must choose, I definitely prefer reliability over performance.

Are these rumors true? If yes, what other better options are there?

Not many webhosts seem to have alternatives such as PostgreSQL and the ones I've found are more expensive. Is it worth the price?

Thanks
Parisian developer available for cute geek girls
Wednesday, January 04, 2006
 
 
I've been using MySQL for years, and NEVER experienced anything such as "data loss".  I really don't think that MySQL would still be around if such a thing were true.

Most hosts provide phpMyAdmin with MySQL which makes it REALLY easy to manage your database directly, backup and export from a web page.
Ben Mc Send private email
Wednesday, January 04, 2006
 
 
Have a look at why some people wont recommend MySQL : http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/26/428527.aspx

PostGreSQL is way better. And is free and open source.

If you want something (with license and support options and professional community support) better go for MSSQL 2005 Express and the ones that fit in the same category - Oracle on Linux,  DB2 Personal edition etc etc.

It all depends on the data model that your application requires.
Having stored procedures to access the DB and limiting SQL injection attacks rules out MySQL from my list of hghly secure application back-ends.
Vineet Reynolds Send private email
Thursday, January 05, 2006
 
 
"It all depends on the data model that your application requires.
Having stored procedures to access the DB and limiting SQL injection attacks rules out MySQL from my list of hghly secure application back-ends."

And why would that rule out MySQL.
Andrew Gilfrin
Thursday, January 05, 2006
 
 
Hmm...
So the big fuss is about some minor features (constraints, etc) being ony partially implemented. I think I can live without that... I'm not saying they're not nice to have, but I don't consider them as crucial.

I was worried that a MySQL database would actually get corrupted under a heavy load of simultaneous read/write access. Apparently, no danger there.

The main reason for me to use MySQL is widely available in cheap web hosting plans (not the case for PostgreSQL and others). I'll pass on some fancy features if needed, but all I ask is for my data to be safe....

Thanks for the links.
Parisian developer available for cute geek girls
Thursday, January 05, 2006
 
 
"So the big fuss is about some minor features (constraints, etc) being ony partially implemented. I think I can live without that... I'm not saying they're not nice to have, but I don't consider them as crucial.

I was worried that a MySQL database would actually get corrupted under a heavy load of simultaneous read/write access. Apparently, no danger there."

I must warn you. Constraints = Data Integrity. it's an insurance.
If the fuss is over MySQL not able to implement contraints properly, then we have a big problem with data integrity even with a low amount of database access.

The best article I've come across is here: http://www.bitweaver.org/wiki/index.php?page=DatabaseComparison
Vineet Reynolds Send private email
Thursday, January 05, 2006
 
 
For what you want MySQL is almost certainly fine. I wouldn't have thought that MySQL/PostgreSQL/Firebird would make much real difference to sort of app you describe. For that matter on a low load/load volume site you could probably just run SQLite.

All the back and forth about whether MySQL is any good ignores the fact that most of people pontificating about it are looking from different sides. The people who don't like it are database admins and people with critical data. The people who like it are lightweight data users and solo programmers. Different needs.

Would I worry about my bank to run MySQL with it's slight dodgy reputation? Yes, I don't want an invalid transaction or lost data on my account.

Do I worry about a discussion board is running MySQL? No, if a thread is lost that's just an irritation to the users in question. (Over course the whole thing melting down is more annoying but 1. I doubt any problems MySQL have are that globally bad, 2. that's what back ups are for).

MySQL might still be a "Toy" RDBMS to that blogger. He's probably right. But it's a scaleable and fast toy and that's good enough for an awful lot of sites out there (slashdot most famously).

That said personally I choose PostgreSQL on Linux if I need to this type of thing but that's for historical reasons. (And the other database's I've used Oracle, MSSQL and Sybase aren't really relevant in this context).

(For what it's worth I pay about $20 a month for virtual server webhosting which means I could run whatever database I wanted. Doesn't seem that expensive, though there's the slight admin overhead)
Ian G Send private email
Thursday, January 05, 2006
 
 
"It all depends on the data model that your application requires.  Having stored procedures to access the DB and limiting SQL injection attacks rules out MySQL from my list of hghly secure application back-ends."

FUD FUD FUD

SQL injection happens due to poor developers NOT due to the database.  If you allow bad SQL to reach your database, you're already screwed.

Stored procedure syntaxes are often db-specific so many people have stayed away from them for good reason but Mysql has had support for them since the 5.x series... which is only about 2 months.

Postgres hasn't had the success of mysql for two simple reasons.  First, until the latest versions, you couldn't run it on windows.  If you wanted to develop locally and host elsewhere, mysql was the best option.  Second, tools like phpMyAdmin are fantastic and work predominantly for mysql.
KC Send private email
Thursday, January 05, 2006
 
 
It's the MyISAM corruption issue that seems scary.
The constraint thing is nothing in comparison.

Then again, if there's a hardware failure you're screwed anyway, so.... I suppose frequent backups are the solution.
Parisian developer available for cute geek girls
Thursday, January 05, 2006
 
 
Yep, you better look at studying a bit more about Inno-DB format and ensure that offsite backups exist.
Atmost you could lose a day or a week's information. Anything more and you'll have to look at a better engineered solution using MySQL (requires research, and you'll end up like me).
Vineet Reynolds Send private email
Thursday, January 05, 2006
 
 
"It's the MyISAM corruption issue that seems scary."

I don't think MySQL corrupts databases any more than any other DBMS.  MySQL is one the most used databases in the world.
Almost H. Anonymous Send private email
Thursday, January 05, 2006
 
 
Oh and one more thing. MySQL seems to use row-level locking I think which is resposible for most of the data trashing that happens at high loads. So consider a DBMS that uses a better locking mechanism (eg:PostGreSQL). I remember a good friend of mine who was a Sybase loyalist; switched to MS SQL(dev) / Oracle(live) just because of this row-level locking problem.
Vineet Reynolds Send private email
Thursday, January 05, 2006
 
 
Oracle uses row-level locking too.

"SQL injection happens due to poor developers NOT due to the database.  If you allow bad SQL to reach your database, you're already screwed."

Prepared (pre-compiled) statements are better at preventing SQL injection than early data validation, because the bound variables are treated as plain text by the DBMS.  It's hard to offer protection as good in the application logic.
Matt Brown
Thursday, January 05, 2006
 
 
Of course, MySQL supports prepared statements depending on which interface you use (it's not really a function of DBMS itself, but rather the interface you choose to use).  I use prepared statements for all database inserts and updates in MySQL.
Almost H. Anonymous Send private email
Thursday, January 05, 2006
 
 
I've had a few table corruptions that resulted in minor data loss from MySQL.

In an application of mine that handles about a million database operations per day and that has been operating for the last year and a half, I've had about a dozen or so table corruptions. Most of the time, the corruptions are easily repairable without data loss.

But on two occasions, the tables were not completely recoverable, and on both occasions I lost a row of data from my users table (the entire database contains tens of millions of rows, just to give you an idea for the complete db size).

It was no great tragedy, though, because I was able to recover the data from backups.

Those table corruptions were back when I was using the 4.0.x versions of MySQL, which are now three or four years old. Since migrating to the 4.1.x releases (and now to the 5.0.x releases), I haven't seen a table corruption in more than a year.

My application exclusively uses the MyISAM storage engine.
BenjiSmith Send private email
Thursday, January 05, 2006
 
 
"Prepared (pre-compiled) statements are better at preventing SQL injection than early data validation, because the bound variables are treated as plain text by the DBMS.  It's hard to offer protection as good in the application logic."

So you're saying that you should just relay on the underlying database engine and not do validation/scrubbing yourself?

Here's the thing... once you write/find a solid validation/scrubbing library, you should add it to your standard library of tools.  Therefore, you do it once and you're done.
KC Send private email
Friday, January 06, 2006
 
 
I don't think that anybody said that. It is well known that you should using input validation AND use prepared SQL statements.

Defense in depth, yadda, yadda, yadda...
Turtle Rustler
Friday, January 06, 2006
 
 
"So you're saying that you should just rely on the underlying database engine and not do validation/scrubbing yourself?"

Of course not.  I'm saying know your system and use it in the way that makes the most sense. 

The application's primary concern is whether the input is valid in the context of the application.  The app should correct or reject input which is not valid for its purposes, which will likely have the side effect of stopping a portion of attacks.  Changing anything else, however, has a high risk of causing unwanted behaviors, with minimal payoff.  What if a partial SQL statement is valid input (especially in the case of a user writing an article about SQL injection)?  What will your input processor do to it?  An application which rejects valid input in the name of security is broken.

A prepared-statements- or stored-procedures-capable DBMS is more capable of dealing with threats than an application could be -- so I do rely on the DBMS in part.  The application would have to know every possible method of attack, whereas the DBMS simply doesn't parse the input.  That's not to say that nobody will figure out how to get around it, but it seems to be the best defense available, especially when combined with other defenses (careful privilege regulation, firewalling, isolation from the web server, etc.).

Obviously, any project where the customer demands the tightest security possible (as with national defense) will place user-friendliness as a lower priority.  Most small businesses selling a web service can't afford to do the same.
Matt Brown
Friday, January 06, 2006
 
 
"What if a partial SQL statement is valid input (especially in the case of a user writing an article about SQL injection)?  What will your input processor do to it?"

Well you run mysql_escape_string() over all the input and then you don't have to reject anything.  That's the logical way to do it.

"The application would have to know every possible method of attack, whereas the DBMS simply doesn't parse the input."

Well I have a database access layer that handles all the querying (no concatinating strings together) and it mysql_escape_string()'s everything that needs to be.  There is no way to have a SQL injection attack.

This is not a terribly difficult problem to solve.
Almost H. Anonymous Send private email
Friday, January 06, 2006
 
 
MySQL is a poor DB compared to others. It's very easy to insert data you should not be able to in there and what not... See http://sql-info.de/mysql/gotchas.html for some infos. It's vastly inferior to other DBs in terms of "enterprise-suitability" - not only it doesn't have data integrity (hec, not even ACID compliant) and is a pain to port from, but it's lacking everywhere else: least ANSI-SQL compliant DB of them all, replication, perf monitoring, partitionning, clustering, fault tolerance, log shipping, query parallelism, mature optimizers, overall (memory) tuning, etc etc.

And for small startups or businesses and such, MySQL is NOT free, unlike PostgreSQL, which is also a FAR better DB all-around.

Also, MySQL is in hot water right now since Oracle now owns the InnoDB engine (since MySQL relies on it for all of it's more "advanced" features) - I wonder what will happen to it now. MySQL has to negociate a contract this year...

There is almost always a better DB choice than using MySQL (PortgreSQL, SQLite, Firebird, SQL Express, etc. even Oracle has a free DB now)

Mind you I'm not saying it doesn't work OK for a hobby website or a blog (it's a OK simple DB), but for enterprise apps it's best avoided.
bleh!
Sunday, January 08, 2006
 
 
Holy FUDtastic batman!

"not only it doesn't have data integrity (hec, not even ACID compliant)"

Bzzt.  Wrong.

"least ANSI-SQL compliant DB of them all"

You can start it in full ANSI-SQL compliant mode or be compatible (as much as possible) with pretty much all other database servers.

"replication"

MySQL has excellent replication and the whole server is highly tunable.  The main reason I didn't go with Postgres for my latest project is that I needed solid (and free) replication and Postgres doesn't have it.

"Also, MySQL is in hot water right now since Oracle now owns the InnoDB engine"

InnoDB is GPL.
Almost H. Anonymous Send private email
Sunday, January 08, 2006
 
 
Some questions are better left unanswered. Like "Should I learn C++ or Java?" , "What DBMS for a website? MySQL?", "Which is the best software company in the world? MSFT or Google?".
Vineet Reynolds Send private email
Monday, January 09, 2006
 
 
Vineet,
the number of characters on the subject line is limited, thus  forcing one to summarize the question into a one liner that might indeed look stupid.
As indicated in the real question (in the subject body), the intent was not to have an absolute answer but some clues regarding issues with MySQL and possible alternatives.

(Sorry.... I just couldn't not react when you compare my question to "what's the best company in the world")...
Parisian developer available for cute geek girls
Tuesday, January 10, 2006
 
 
Holy FUDtastic batman!

Referring to your own posts now? ;)

Bzzt.  Wrong.

How so? It is not (in both cases).

You can start it in full ANSI-SQL compliant mode or be compatible (as much as possible) with pretty much all other database servers.

No, it is not full ANSI-SQL compliant. no DB fully implements this fully, but MySQL is the worst of them all when it comes to this. So obvious I don't know how you can even try to contradict this...

MySQL has excellent replication

How so? Perhaps you don't know what "excellent replication" means... Because it doesn't have it. Care to explain what you mean? Other DBs are FAR better for this.

and the whole server is highly tunable.

Not anywhere near other offerings that I've tried. Maybe you haven't sen a highly tunable DB before? ...

InnoDB is GPL.

And MySQL is dual-licensed product, they DO have to negociate a license with Oracle - that's a fact. Perhaps you could look into it...

Holy FUDtastic batman indeed! I had a good chuckle out of it.
meh!
Sunday, January 15, 2006
 
 
I was running a MySql database on a windows server that would properly become corrupt about once a week, often trying to recover it would result in data loss.  This was using MySql3.23, and I've run other installation on Linux and never had a problem.
Ever since I have avoided MySql, slowly migrarting everything to PostgreSql.
John Joske
Monday, January 16, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz