Posted by
michael
on from the some-data-loss-inevitable dept.
ulrikp writes "Swedish MySQL AB, makers of the MySQL database, have released an Alpha-version of their flagship, dubbed MySQL 5.0.0. The changesinclude basic support for SQL-99 stored procedures. Please note: Despite the version number, this is an Alpha release, and not for general consumption."
I've been wanting sub-queries and stored procedures for a couple of years now.
This is sweet, professional level db programming for free might have an incredible impact on the web
Don't forget that most of your favorite sites probably use MySQL.
-- Will code a sig generator for food
I have to confess..
by
wackybrit
·
· Score: 3, Interesting
I've been using MySQL 4.1 on a live server (with customers) for two or three months now. I basically wanted subqueries at the time, and so I just upgraded the box with disregard for everyone else;-) Luckily everything worked great! There's a little quirk with Unicode if you play with the character type settings on certain fields (as long as you stay away from Unicode you'll be more than okay). I also occasionally have the server die when given certain lengthy queries, but it comes straight back, and gets on with things.
So, no, it's hardly Oracle, but even MySQL's alpha stuff seems to be reasonably usable, as long as you aren't doing anything too serious. And, as any database expert will tell you, you probably aren't going to be using MySQL for anything that serious anyway. Nice work MySQL.. I'll spare my users from an immediate upgrade to MySQL 5 however.. for now!!
i am not much of a database expert, but i have used transactions, rollbacks (and commits:) as well as subselects with MySQL 4.1, so please enlighten me: what makes these feature not "proper" in MySQL?
MySql vs. Postgres
by
Alien_Phreak
·
· Score: 4, Interesting
Hi all,
I keep hearing about all the great god-like features of Postgres....but what exactly can Postgres do that mysql can't? i'm going towards setting up a central database (MySQL) linux server at work which will be accessible via Ms. Access using an ODBC driver from the clients. (ie. client running Msft Access changes data on a mysql database on a linux server, easy enough to use gui and a strong enough backend)
So far, i'm not doing anything out of the ordinary. nothing too complicated database wise. What exactly would be the advantage of using Postgres.
What does it do that mysql can't?
Things broken with MySQL
by
jesterzog
·
· Score: 4, Interesting
One of the biggest problems with MySQL that so many people complain about is that the entire design philosophy is not one that supports data integrity. Take a look at the list of MySQL Gotchas for starters.
The reason that it's satisfactory for slashdot is the same reason that it became popular in the first place. It was one of the first freely available database servers, and it had particularly fast benchmark results for situations that required lots of selects and not many updates. (I might be slightly wrong about the details, so someone correct me if necessary.) The fast-select advantage made it a popular option for websites a few years ago, because they involve just that. Lots of page-views (selects) and not many updates.
On the other hand, the integrity aspect of MySQL makes it a pain to code for. The MySQL credo seems to be that if input doesn't make sense, then try to guess what was meant and don't report it. (eg. Inserting a null instead of reporting an error.) Furthermore, the MySQL parser recognises certain parts of SQL syntax that are ignored, and (again) not reported. Instead, it just doesn't do anything and pretends that it all worked.
So instead of being able to rely on constraints set in the database, as can be done with nearly any other well-used database, it's necessary to put large amounts of integrity-checking code for things as simple as making sure that dates are the correct format. In some ways it's more like a half-built SQL interface to a regular file system than a database, with features for data integrity hacked on here and there if you know how to use them and always use them correctly.
In slashdot's case, don't be surprised if a random comment goes missing every now and again, because the integrity support just isn't there without complicated overhead work that induces possible mistakes. Slashdot can get away with losing bits and pieces of data, but that's not usually the case. If I say that I want to put some data in the database and that it must meet specific rules, it should either put it in or result in an error. MySQL doesn't do this reliably.
MySQL now seems to be living only on it's fast reputation that it had in the past, driven by people who've heard that it's good and it's fast, but don't know the details. By now, other free databases (postgresql in particular) have caught up a lot, and should be preferable in most cases if you're starting from scratch.
What I've heard is MySQL's main point in favor (at least on the MyISAM table) is the performance, especially on a command sequence that largely consists of SELECTs, with comparatively few write ops. It is probably reasonable to suspect that at least some of that performance advantage over (as near as I can tell) every other SQL (or near-SQL) implementation comes from not implementing the extraneous stuff.
Thus, if you have an application where the queries are going to mostly be SELECTs (and the application effectively does its own constraint checking and approximation of foreign keys), MySQL with MyISAM is almost certainly the best choice, as you don't need to use the extra features that others provide, and the performance increase is useful.
Does this mean that MySQL/MyISAM is ideal for any other type of application? No. Is it even suitable for any other application? Absolutely not, and anyone who says otherwise is probably insane.
But there's a large application space where the constraints and query sets are well-suited for MySQL/MyISAM (web CMS type things being but one example of this... basically anything that just needs a datastore that's reasonably relational where an SQL-like interface is useful).
Re:Discussion of MySQL should include MySQL gotcha
by
Sxooter
·
· Score: 2, Interesting
Cool, I get your point on most of what you say, but, this:
It would be a reasonable complaint if MySQL didn't have an ANSI-compliant mode, which it does.
doesn't fix all the problems. It fixes quite a few, but the ones that are left are still pretty nasty, imnsho.
Further, about Postgresql, you said:
2. The documentation is crap. Actually it's worse than crap, but going into that in detail would be off-topic. (Whereas the MySQL documentation is mostly very, very good.)
I call shenanigans. While the Postgresql docs are not aimed at the beginner, as someone who already understands database theory, I found them quite good. I didn't need someone to point out what a foreign key was or what it could do, only how Postgresql implemented it. While the MySQL docs are great for beginners, they are actually pretty damned cumbersome for people with experience. Also, the Postgresql docs have had a lot of input in the last year or so, so you might want to look at them again.
--
--- It is not the things we do which we regret the most, but the things which we don't do.
Slashdot Mirror
This means that whenever it is stable and ready for production, it is going to have a random version number. Not the smartest practice.
.0 and not .rnd()
It would help if the major stable releases were
-Charles
Learning HOW to think is more important than learning WHAT to think.
I've been wanting sub-queries and stored procedures for a couple of years now.
This is sweet, professional level db programming for free might have an incredible impact on the web
Don't forget that most of your favorite sites probably use MySQL.
Will code a sig generator for food
I've been using MySQL 4.1 on a live server (with customers) for two or three months now. I basically wanted subqueries at the time, and so I just upgraded the box with disregard for everyone else ;-) Luckily everything worked great! There's a little quirk with Unicode if you play with the character type settings on certain fields (as long as you stay away from Unicode you'll be more than okay). I also occasionally have the server die when given certain lengthy queries, but it comes straight back, and gets on with things.
So, no, it's hardly Oracle, but even MySQL's alpha stuff seems to be reasonably usable, as long as you aren't doing anything too serious. And, as any database expert will tell you, you probably aren't going to be using MySQL for anything that serious anyway. Nice work MySQL.. I'll spare my users from an immediate upgrade to MySQL 5 however.. for now!!
mogorific carpentry experiments
i am not much of a database expert, but i have used transactions, rollbacks (and commits :) as well as subselects with MySQL 4.1, so please enlighten me: what makes these feature not "proper" in MySQL?
Hi all,
I keep hearing about all the great god-like features of Postgres....but what exactly can Postgres do that mysql can't? i'm going towards setting up a central database (MySQL) linux server at work which will be accessible via Ms. Access using an ODBC driver from the clients. (ie. client running Msft Access changes data on a mysql database on a linux server, easy enough to use gui and a strong enough backend)
So far, i'm not doing anything out of the ordinary. nothing too complicated database wise. What exactly would be the advantage of using Postgres.
What does it do that mysql can't?
One of the biggest problems with MySQL that so many people complain about is that the entire design philosophy is not one that supports data integrity. Take a look at the list of MySQL Gotchas for starters.
The reason that it's satisfactory for slashdot is the same reason that it became popular in the first place. It was one of the first freely available database servers, and it had particularly fast benchmark results for situations that required lots of selects and not many updates. (I might be slightly wrong about the details, so someone correct me if necessary.) The fast-select advantage made it a popular option for websites a few years ago, because they involve just that. Lots of page-views (selects) and not many updates.
On the other hand, the integrity aspect of MySQL makes it a pain to code for. The MySQL credo seems to be that if input doesn't make sense, then try to guess what was meant and don't report it. (eg. Inserting a null instead of reporting an error.) Furthermore, the MySQL parser recognises certain parts of SQL syntax that are ignored, and (again) not reported. Instead, it just doesn't do anything and pretends that it all worked.
So instead of being able to rely on constraints set in the database, as can be done with nearly any other well-used database, it's necessary to put large amounts of integrity-checking code for things as simple as making sure that dates are the correct format. In some ways it's more like a half-built SQL interface to a regular file system than a database, with features for data integrity hacked on here and there if you know how to use them and always use them correctly.
In slashdot's case, don't be surprised if a random comment goes missing every now and again, because the integrity support just isn't there without complicated overhead work that induces possible mistakes. Slashdot can get away with losing bits and pieces of data, but that's not usually the case. If I say that I want to put some data in the database and that it must meet specific rules, it should either put it in or result in an error. MySQL doesn't do this reliably.
MySQL now seems to be living only on it's fast reputation that it had in the past, driven by people who've heard that it's good and it's fast, but don't know the details. By now, other free databases (postgresql in particular) have caught up a lot, and should be preferable in most cases if you're starting from scratch.
What I've heard is MySQL's main point in favor (at least on the MyISAM table) is the performance, especially on a command sequence that largely consists of SELECTs, with comparatively few write ops. It is probably reasonable to suspect that at least some of that performance advantage over (as near as I can tell) every other SQL (or near-SQL) implementation comes from not implementing the extraneous stuff.
Thus, if you have an application where the queries are going to mostly be SELECTs (and the application effectively does its own constraint checking and approximation of foreign keys), MySQL with MyISAM is almost certainly the best choice, as you don't need to use the extra features that others provide, and the performance increase is useful.
Does this mean that MySQL/MyISAM is ideal for any other type of application? No. Is it even suitable for any other application? Absolutely not, and anyone who says otherwise is probably insane.
But there's a large application space where the constraints and query sets are well-suited for MySQL/MyISAM (web CMS type things being but one example of this... basically anything that just needs a datastore that's reasonably relational where an SQL-like interface is useful).
Cool, I get your point on most of what you say, but, this:
It would be a reasonable complaint if MySQL didn't have an ANSI-compliant mode, which it does.
doesn't fix all the problems. It fixes quite a few, but the ones that are left are still pretty nasty, imnsho.
Further, about Postgresql, you said:
2. The documentation is crap. Actually it's worse than crap, but going into that in detail would be off-topic. (Whereas the MySQL documentation is mostly very, very good.)
I call shenanigans. While the Postgresql docs are not aimed at the beginner, as someone who already understands database theory, I found them quite good. I didn't need someone to point out what a foreign key was or what it could do, only how Postgresql implemented it. While the MySQL docs are great for beginners, they are actually pretty damned cumbersome for people with experience. Also, the Postgresql docs have had a lot of input in the last year or so, so you might want to look at them again.
--- It is not the things we do which we regret the most, but the things which we don't do.