Why not MySQL?

[KLin]

http://www.youtube.com/watch?v=1PoFIohBSM4

Interesting video showing the differences between MySQL and PostgreSQL.

 

[Markbnj]

Interesting. We tend to go with MySql by default for open source projects in Django, and at least I wasn't aware it allowed some of these constraints to be violated. For most websites building from the ground up it's probably more about instinctive revulsion than practical problems, but I can see where there could be some real risks if you're migrating a lot of data.

 

[velvetpants]

Mysql is an abomination. I've always said so.

 

[beginner99]

Mysql is an abomination. I've always said so.

Agree. Never had to deal with it until recently and didn't know it's peculiarities. I'm actually tending to not call it a relational database...

Only reason I would choose it is for simple stuff with little value that must be cheaply hosted. Else go PostgreSQL.

I think I remember that integers default to 0. So if you don't supply a value it will be 0 instead of NULL.

EDIT:

He doesn't state the storage engine used. IMHO another weird thing of MySQL because it can completely change it' behavior. The default engine MyISAM AFAIK does not have transactions!!! You need InnoDB for that which has other downsides. Then there are companies that have custom storage engines for MySQL which are better than the default ones and also might not have the weird behavior as shown in the video.
And you can actually set the engine on a per table basis so will need to now the engine to know how the table will behave or just to sanity checks before anything or use a real RDBMS.

 

[ringtail]

``

 

[ringtail]

In my experience, over the last several years the MySQL default table handler engine is InnoDB, which has transactions, fk's. It was way back years ago that the default engine was MyISAM.

The thing is, MySQL has the dominant share, far bigger support community, saturation. Maybe beta was better but the world adopted vhs. Maybe Linux is better but the world adopted Windows. Similar deal here. Postgre is a tiny niche player, with no hope of ascending to the status of MySQL.

I watched the video, and think it's presenting information years out of date re: MySQL, and is therefore invalid.

 

[Fallen Kell]

To be honest with you, I think the things shown in that video are simply design decisions. When you have tons of custom applications all reading/writing to your database, would you rather: throw exception codes which the other application would then need to test for, trap, and display, or would you rather simply attempt to do what they asked you to do to the best of the ability given the constraints set on the database? Hey, you are trying to put text in which is larger than my buffer, well, you can't do that, but you can fit this many characters.... To be honest, MySQL puts the onus on data verification where it should be, at the client side application which it taking the input in the first place. People who use MySQL know this. People who use databases in general should be doing this anyway. You should always test/verify the data before attempting to load it into a database, this way you don't bog down the database server dealing with bogus data inputs in the first place.

 

[cytg111]

Postgresql all the way, but that is just cause it gets me most of what i have been used to with oracle. But then again that says alot right there.
I wouldnt think twice (ok maybe three), about replacing oracle bases with postgres ones in production for larger coorps. postgresql is up there. Actually the main arguments against keeps being support and revision ..

 

[Markbnj]

To be honest with you, I think the things shown in that video are simply design decisions. When you have tons of custom applications all reading/writing to your database, would you rather: throw exception codes which the other application would then need to test for, trap, and display, or would you rather simply attempt to do what they asked you to do to the best of the ability given the constraints set on the database? Hey, you are trying to put text in which is larger than my buffer, well, you can't do that, but you can fit this many characters.... To be honest, MySQL puts the onus on data verification where it should be, at the client side application which it taking the input in the first place. People who use MySQL know this. People who use databases in general should be doing this anyway. You should always test/verify the data before attempting to load it into a database, this way you don't bog down the database server dealing with bogus data inputs in the first place.

Nah, I don't buy it. Things like defaulting to "today" when you try to insert a null into a not-null date field are just wrong. I would much rather the db engine strictly enforce constraints so I can be notified as early as possible when my app is doing something stupid.

 

[velvetpants]

When you have tons of custom applications all reading/writing to your database, would you rather: throw exception codes which the other application would then need to test for, trap, and display...

uuuh... yes
I don't want the database to assume what it is that I wanted to do. When I set the rules on a table, it should not adjust my data to fit those rules (unless I explicitly pass it through a routine which does so), it should either let the data through if it matches or deny it completely.

That just creates a whole new world of problems that can be extremely hard to track down, like
"why the hell are there so many empty strings in the name column, client applications converts empty strings to null and the column is not null... wtf"... then after hours of debugging (unless you're already familiar with the dumbness of mysql), you find out that mysql is doing this for "your convenience".

This shouldn't happen. I shouldn't have to babysit the database like it's a person making independent decisions, it should just follow the rules I set for it.
It should simply tell me "You're trying to put null into a not-null column, you can't do that. Fix your code, dummy."
That way I see what the problem is right away and I can fix it or handle it.

He doesn't state the storage engine used. IMHO another weird thing of MySQL because it can completely change it' behavior. The default engine MyISAM AFAIK does not have transactions!!! You need InnoDB for that which has other downsides. Then there are companies that have custom storage engines for MySQL which are better than the default ones and also might not have the weird behavior as shown in the video.
And you can actually set the engine on a per table basis so will need to now the engine to know how the table will behave or just to sanity checks before anything or use a real RDBMS.

This is another point. MYISAM is an even bigger wtf, as a relational storage engine it doesn't even enforce or verify the integrity of foreign keys, quite an essential feature of an rdbms I would say.
I used innodb briefly and it's somewhat better, but luckily I got some sense and ran away from mysql fast as I could.

 

[BoberFett]

To be honest with you, I think the things shown in that video are simply design decisions. When you have tons of custom applications all reading/writing to your database, would you rather: throw exception codes which the other application would then need to test for, trap, and display, or would you rather simply attempt to do what they asked you to do to the best of the ability given the constraints set on the database? Hey, you are trying to put text in which is larger than my buffer, well, you can't do that, but you can fit this many characters.... To be honest, MySQL puts the onus on data verification where it should be, at the client side application which it taking the input in the first place. People who use MySQL know this. People who use databases in general should be doing this anyway. You should always test/verify the data before attempting to load it into a database, this way you don't bog down the database server dealing with bogus data inputs in the first place.

That's twisted logic.

You're right, the onus is on the application to verify data. And to do that, the database should throw an error saying there was a data overflow and let the application decide how to deal with it. Silently accepting bad data is terrible.

 

[beginner99]

That's twisted logic.

You're right, the onus is on the application to verify data. And to do that, the database should throw an error saying there was a data overflow and let the application decide how to deal with it. Silently accepting bad data is terrible.

completely agree.

The worst part is actually that it is changing data, eg 5000 to 99. if you run in such behavior in a half-complicated system you will could weeks finding the issue..and normally you would suspect a logical error in code and not the database. Imean even if you go look in the database and see 99 instead of 5000, if you don't know this behavior this will drive you nuts.

 

[JavaMomma]

I'm in the middle of a project converting a MySQL database (not created by me) to Postgresql. I've been using Postgres solid for the last 1.5 years and it is very capable, missing a few features (compared to SQL Server / Oracle) but still very good. Having used MySQL (5.1 /w InnoDB) a bit in the past, I had to setup replication and few other things, it is a database that feels like it hack built on top of hack.

The other big problem with MySQL are its "users" - reminds me of VB developers (not trying to be insulting, I know there are some VB developers that are talented). Anyway, I crack open this MySQL DB and see what are supposed to be boolean columns as text, with the literal strings "true" and "false" littered through the table complete with spelling mistakes "fasle" - makes my data conversion script so much fun.

It's because many MySQL users don't really understand databases and all they're really looking for is some sort of object persistence - they use MySQL because there are a lot of tools written for it. Recently I've been seeing some of these developers moving towards alternatives like MongoDB.

 

[BoberFett]

I just have to come back and say after thinking about it, how scary it is that there are probably people out there developing real applications that think like Fallen Kell.

Any database which does something "to the best of it's ability" should not be considered ACID compliant and should not be used for any real application.

"Well, you know I tried to commit the whole transaction, but creating the new record failed so we'll just reduce the balance. They'll figure it out the next time they look at their bank statement. Hey, I gave it my best shot."

 

[ForumMaster]

To be honest with you, I think the things shown in that video are simply design decisions. When you have tons of custom applications all reading/writing to your database, would you rather: throw exception codes which the other application would then need to test for, trap, and display, or would you rather simply attempt to do what they asked you to do to the best of the ability given the constraints set on the database? Hey, you are trying to put text in which is larger than my buffer, well, you can't do that, but you can fit this many characters.... To be honest, MySQL puts the onus on data verification where it should be, at the client side application which it taking the input in the first place. People who use MySQL know this. People who use databases in general should be doing this anyway. You should always test/verify the data before attempting to load it into a database, this way you don't bog down the database server dealing with bogus data inputs in the first place.

in a way, yes. these are design decisions. MySQL was designed by programmers. not dbas and it shows. frankly I don't think that a "design decision" to alter the data rather then throw and error is valid. it just shows that MySQL will never go beyond small applications.
a real rdbms such as oracle which is responsible for the data has to validate incoming data. as an oracle dba, what I hate most is seeing databases with no constraints "for the programmers convinience ".

 

[Markbnj]

what I hate most is seeing databases with no constraints "for the programmers convinience "

It's not always easy to tell whether some bit of behavior was intentional or a bug, but if it's bad behavior then it's not better if it's intentional.

Programmers who think behavior like this is "convenient" are bad programmers.

 

[Cerb]

Agree. Never had to deal with it until recently and didn't know it's peculiarities. I'm actually tending to not call it a relational database...

That's like saying the sky is blue. No SQL DBMS is relational. You have to do the work to make each DB itself relational. MySQL make it harder than others.

in a way, yes. these are design decisions. MySQL was designed by code monkeys.

FTFY. I'm a programmer. But, I actually passed my math classes, and understand how sets work. How any non-BASIC programmer can not intuitively understand working with sets is mind-boggling. It's kind of scary to think that such people do OOP.

what I hate most is seeing databases with no constraints "for the programmers convinience ".

Which is doubly bad, because constraints are useful for programmers. Unless, of course, they always fully understand everything about a large complex system worked on by many people, and never wrote a bad line of code in their lives...

 

[alkemyst]

mySQL is more than adequate for the sites that should be using it. A coder with a private website, small online store, etc. It's free and many programmers understand it.

For my programming life it's been mostly mySQL or MS SQL. I took Oracle DBA classes in college, but haven't had to touch it more than passing pretty much the same SQL I'd have passed to mySQL or MS SQL.