Posted by
Hemos
on from the lock-yer-rows-gentlemen dept.
egerlach writes "All you DB admins out there might be interested to know that MySQL 4.0 has finally been released! It's only 4.0.0 alpha, but you can download it here. You can also check out a full list of changes."
Any support for Foreign keys yet?
by
Andreas(R)
·
· Score: 2, Informative
Does this new release of MySQL support the proper use of foreign keys internally? (found no info in release notes). Or has MySQL a different way of implementing a "One-Many"-relation?
My university (NTNU.no) is using an old version of MySQL (from 1998), which does nothing when you set a foreign key.
Re:Any support for Foreign keys yet?
by
skroz
·
· Score: 2, Informative
According to the article linked by this slashdot story, foreign key support should show up in 4.1 along with subselects and a few other goodies. Would be nice to have these BASIC features...
-- --
Minds are like parachutes... they work best when open.
"The new version is intended as a platform for building mission critical, heavy load database solutions"
Still no proper transactions, no subselects, no
foreign keys or views. How can this be a "mission critical" SQL database? I still don't get why people
use it. Sure, for some situations you can get
it to work, but why bother?
One database is not better than another because it has a bigger checklist of features. If that were true, then we'd all be using Oracle (which is actually a very good RDBMS). MySQL has advantages and disadvantages over PostgreSQL. The same is true for almost any database.
Let's look at your complaints of MySQL lacking features one by one:
This is a nice feature, but *not* necessary. Many times a proper JOIN can be used instead. Alternately you just use multiple SQLs. However, this is the one missing feature of MySQL that I want the most.
no foreign keys
You don't need foreign keys to maintain referential integrity. A proper GUI, among many other things, can enforce this anyway. It is a nice feature, but definitely not needed in a well designed system. Further they slow down performance and I have seen projects where they are not used because of this.
views
These can be nice too, but I personally never use them. They are simply not required in any project I've ever seen. Actually I think views are confusing because they mask the real tables. I think this is a style issue more than anything else, YMMV.
How can this be a "mission critical" SQL database?
Don't get me wrong. I like PostgreSQL too. They are both great and both are very useful in production environments. Why does there only have to be one open source database? The competition is truly healthy for both products and ultimately for us developers.
Re:But why?
by
chris.bitmead
·
· Score: 3, Informative
What a troll. They do a performance test of
postgresql without running the vacuum analyzer
to update statistics. Then they "wonder" (as
if they didn't know) why postgresql is slow.
Duh!
My previous project (a commercial software product) evaluated the mjaor free DBs (postgreSQL, mySQL...) and we had to select mySQL because it was the only free DBMS with a large user base (for support), active development (again, for support), and a Windows port.
Our DBA wanted to use postgreSQL for some of the reasons mentioned in these fine posts. However, we had a requirement to be able to run in a pure Windows or Solaris 8.x environment. MySQL is the only major, free DBMS that fits that bill.
I know there's a million bitty-little open source/free ware dbms projets out there, but we didn't have time to fix any bugs/issues that we might've found. We had enough code of our own to write, we didn't have time to fix anyone else's product. I know this goes against the whole open source dogma, but sometimes it's the truth in industry.
-- it's not going to stop until you wise up, no it's not going to stop. so just give up.
Re:But why?
by
killmenow
·
· Score: 4, Informative
we had a requirement to be able to run in a pure Windows or Solaris 8.x environment. MySQL is the only major, free DBMS that fits that bill.
Umm, how about Interbase (or Firebird for that matter)? Definitely qualifies as "major" IMO, is being actively developed, and runs on Windows, Linux, Solaris, and others...
We had enough code of our own to write, we didn't have time to fix anyone else's product
So basically, you were just looking to get a DB you didn't have to pay for, didn't have to support, and didn't have to contribute anything back to. In other words: trying to get something for nothing...you're right, that is often the "truth in the industry."
Re:But why?
by
micromoog
·
· Score: 5, Informative
SubSelects: You don't need to do these, it can be worked around pretty easily.
Views: Good ridance. A maintenance nightmare.
You clearly have no knowledge whatsoever of databases. Maybe you don't use subselects in CS102: Intro to Databases, but there are many things that can ONLY be accomplished with subselects. The simplest subselects would be worked around using temporary tables (which unnecessarily complicates the application), and the more complex requirements cannot be worked around at all (go look up "nested subquery").
Views are one of the greatest things ever to happen to a real-world database system. Various applications like their data organized in different ways, and the creation of views allows a single dataset to be used for all. The workaround is to create and populate separate tables (now that's a "maintenance nightmare").
The bottom line: a "mission-critical" database needs to support these things, and the others listed above. People would much rather use a different system that supports these features than rewrite their existing applications around the limitations of MySQL.
Let me preface my reply by saying I do in fact admin a fairly large MySQL installation, and it performs better than one would expect with its limitations. However, that is no reason to gloss over its deficiencies.
>>no proper transactions
>Yes it does! If you use certain table types
All of which are fairly new and account for only a small portion of the installations, meaning that they are no where near as well tested as the default table type (MyISAM).
Not to mention that none of them seem to have reliable benchmarks available. And to make matters worse, InnoDB has a big banner on their front page comparing themselves to a "leading database" but if you click on the link and read through the text, they state:
"Note that the tests were not run in exactly
the same way for the other database: the
comparison does not satisfy strict standards."
Publicizing the results of an admittedly flawed benchmark is unprofessional and, in my opinion, highly unethical.
>>no subselects
>This is a nice feature, but *not* necessary.
>Many times a proper JOIN can be used instead.
>Alternately you just use multiple SQLs.
>However, this is the one missing feature of
>MySQL that I want the most.
Agreed - it is possible to work around this issue. Though it does increase client code complexity.
>>no foreign keys
>You don't need foreign keys to maintain
>referential integrity. A proper GUI, among many
>other things, can enforce this anyway. It is a
>nice feature, but definitely not needed in a
>well designed system. Further they slow down
>performance and I have seen projects where they
>are not used because of this.
Yes it is possible to do integrity checks programmatically. However, this does nothing for manual administration, and requires implementation for every piece of code that might modify the database.
In most intstances I would consider not using foreign keys to be a poor decision, particularly after dealing with the mess created by a database where they decided to use programmatic checks for integrity.
As a side note, there is partial implementation of foreign keys in the InnoDB table handler, though it has some fundamental flaws to it. It drops constraints on an ALTER table, it allows you to drop referenced tables, and it lacks features such as CASCADE ON DELETE.
>>views
>These can be nice too, but I personally never
>use them. They are simply not required in any
>project I've ever seen. Actually I think views
>are confusing because they mask the real tables.
>I think this is a style issue more than anything
>else, YMMV.
Views make it possible for you to modify the schema of a database without having to touch your client code.
Views are also a wonderful way to present a simplified view to your programmers, rather than expecting them to know, e.g. how to do a full outer join on three or four tables with a sub-select thrown in just to make it a little more confusing.:)
>>How can this be a "mission critical" SQL
>>database?
>How about better performance [mysql.com].
As I have pointed out before, the benchmarks on mysql.com are for a single thread of access only. Which does not mimic the real world environment of the vast majority of database installations. Unless that is going to be your method, those benchmarks are essentially useless.
To their credit, representatives from MySQL AB have promised a more robust test in the future. But until that's out, I cannot put any stock in their published benchmarks.
Re:In other news
by
chris.bitmead
·
· Score: 4, Informative
Actually, it's not GPLed, its BSDed. i.e.
nearly, almost public domain.
Transactions, foreign keys
by
blackcat++
·
· Score: 5, Informative
I hate to disappoint you but transactions, foreign keys and row-level locking are available and seem to work quite well.
Re:Transactions, foreign keys
by
mauryisland
·
· Score: 2, Informative
Nested queries and stored procedures are due in a 4.1 release scheduled for this December, according to this article.
Re:Transactions, foreign keys
by
jamie
·
· Score: 3, Informative
"I haven't looked at the slashdot code recently, but there were a dozen and a half different methods behind the database functions that could have been optimized to better utilize MySQL."
We welcome specific suggestions and criticisms. Please
submit a Slash bug report
and let us know which methods you're talking about. Thanks!
Re:Transactions, foreign keys
by
krow
·
· Score: 3, Informative
To date we had one bug in Innodb that I have found (and we found one other in replication, but no one would have noticed that other then the fellow who has to keep this stuff running...). We have had some growing pains that we have went to. In our first week of operation we had a number of hardware problems bring us down (we have since moved hardware) which was only related to the DB's because MySQL was running on that Hardware. This http://www.tangent.org/~brian/talks/dbsummit_scali ng/ take you to some slides that I did for a talk I gave on Slashdot's DB.
-- You can't grep a dead tree.
Re:Windows Frontend?
by
robwills
·
· Score: 3, Informative
SciBit make Mascon, which is excellent.
I use their Free Mascon product with my intranet servers and it is robust, quick, many features and a solid interface.
If I paid for the full version, I could design/alter tables & indexes. Because I don't do that often, I will do it by hand using the cli client or use phpMyAdmin which is pretty cool too.
Yes it does, if you install and use the latest InnoDB table handler (see www.innodb.com )
Re:In other news
by
dhogaza
·
· Score: 4, Informative
Actually the InnoBase table type removes the biggest reason why MySQL traditionally has shit its pants under heavy load, as it provides row-level locking and non-blocking writes rather than the old table-level locking required with MySQL's original table type.
So it should be much better in this regard.
I don't know from personal experience, though. I use PostgreSQL instead because it's got important features like referential integrity checking and even better, a development team who understand why such features are important.
Re:Windows Frontend?
by
thornist
·
· Score: 2, Informative
Well i guess I'm walking into your trap, but... have you tried MySQLfront? I'm guessing you have as it's not obscure, but what didn't you like about it?
For me it does almost everything I need out of a frontend (and certainly stomps mightily on the piece of shit that is MySQLGUI). And yes I went through all that evaluating too, and MySQLfront came up way on top - edit data, table design, server management etc etc
Lets see if they get it right this time
by
SnapperHead
·
· Score: 5, Informative
MySQL has been missing some very important key parts, which makes life very difficault working cross database.
timestamp field doesn't follow SQL standards
Sub-selects
Triggers would be nice
select disinct on (field_name)... would also be nice
To sum it all up, I hope they plan on following SQL standards this time around.
-- until (succeed) try { again(); }
Re:How about a competition? (was Re:Proof, please)
by
alessio
·
· Score: 2, Informative
> But what if you
wanted to use PostgreSQL with the Slashcode; would this port be doable in anything like a reasonable amount of time?
Yes, I am precisely doing that. The major blocks are only the style of the code and the use of some useless SQL extensions. I cannot say that the systems gain something from the port excluding a more reliable (IMHO) database engine: it could have been useful to use VIEWs and subselects in the Slash code, but the application has been written with MySql in mind - that's the main point.
If you are interested in Slashcode on PostgreSQL, please raise your voice at Slashcode.
-- "It is more complicated than you think"
(The Eighth Networking Truth from RFC 1925)
Re:The real reason for this release
by
gregwbrooks
·
· Score: 2, Informative
The over-simplified version:
transactional (i.e., all inserts/deletes have to be completed within a transaction series or none of them are completed)capabilities; and
I think, row-level locking.
--
"It was a summer's tale: Just a boy, his Linux, and a head full of dreams..."
The first national bank in Spain doesn't use them
by
icoloma
·
· Score: 2, Informative
>> This is a terrible suggestion. Unless you're developing a tiny application that only you will be using, anybody who doesn't use foreign keys is completely incompetent
I used to work for a company in Spain that developed a pair of applications for an important bank here (supposed to be #1 according to some studies). They had the policy of NOT TO USE FK NEVER (of course, we are not talking about mainframes, but some Oracle databases). My personal impression is that they did that because it simplified development.
How can you call incompetent to someone that just doesn't use FK? (disclaimer: I try use them everytime I can) "You've got a lot to learn"? Let me see. So, you have read one book and think that everyone that doesn't agree to that concrete book doesn't have a clue?
Sorry, but I don't agree either. I prefer to use FK for my own reasons. But I also have programs working without primary keys. And the world didn't fall out for this.
The Gemeni table handler is produced by NuSphere, which has become demonized for not (properly) GPLing the table type as is required by the MySQL license. There is a FAQ from the MySQL folks about the dispute.
Do away with set-theoretic background of SQL?
by
Jayson
·
· Score: 2, Informative
Clustered indexes. These basically physically sort the table based on particular columns. This allows you to ORDER BY username ASC without using anything special since the rows are already sorted on username (if you have a users table and cluster the username col).
It seems like most of your problems would be easily solved if we ditched the original set-theoretic background of SQL (where results are in an unordered set as opposed to an ordered sequence). If the DBA was heavily aware of the ordering of tables, then constructing efficient queries that made linear table/index scans would be easier. It seems like this would be amazingly cache and read-ahead friendly.
Slashdot Mirror
Does this new release of MySQL support the proper use of foreign keys internally? (found no info in release notes). Or has MySQL a different way of implementing a "One-Many"-relation?
My university (NTNU.no) is using an old version of MySQL (from 1998), which does nothing when you set a foreign key.
Still no proper transactions, no subselects, no foreign keys or views. How can this be a "mission critical" SQL database? I still don't get why people use it. Sure, for some situations you can get it to work, but why bother?
Actually, it's not GPLed, its BSDed. i.e.
nearly, almost public domain.
I hate to disappoint you but transactions, foreign keys and row-level locking are available and seem to work quite well.
SciBit make Mascon, which is excellent.
I use their Free Mascon product with my intranet servers and it is robust, quick, many features and a solid interface.
If I paid for the full version, I could design/alter tables & indexes. Because I don't do that often, I will do it by hand using the cli client or use phpMyAdmin which is pretty cool too.
Yes it does, if you install and use the latest InnoDB table handler (see www.innodb.com )
Actually the InnoBase table type removes the biggest reason why MySQL traditionally has shit its pants under heavy load, as it provides row-level locking and non-blocking writes rather than the old table-level locking required with MySQL's original table type.
So it should be much better in this regard.
I don't know from personal experience, though. I use PostgreSQL instead because it's got important features like referential integrity checking and even better, a development team who understand why such features are important.
Well i guess I'm walking into your trap, but... have you tried MySQLfront? I'm guessing you have as it's not obscure, but what didn't you like about it?
For me it does almost everything I need out of a frontend (and certainly stomps mightily on the piece of shit that is MySQLGUI). And yes I went through all that evaluating too, and MySQLfront came up way on top - edit data, table design, server management etc etc
until (succeed) try { again(); }
Yes, I am precisely doing that. The major blocks are only the style of the code and the use of some useless SQL extensions. I cannot say that the systems gain something from the port excluding a more reliable (IMHO) database engine: it could have been useful to use VIEWs and subselects in the Slash code, but the application has been written with MySql in mind - that's the main point.
If you are interested in Slashcode on PostgreSQL, please raise your voice at Slashcode.
"It is more complicated than you think" (The Eighth Networking Truth from RFC 1925)
"It was a summer's tale: Just a boy, his Linux, and a head full of dreams..."
>> This is a terrible suggestion. Unless you're developing a tiny application that only you will be using, anybody who doesn't use foreign keys is completely incompetent
I used to work for a company in Spain that developed a pair of applications for an important bank here (supposed to be #1 according to some studies). They had the policy of NOT TO USE FK NEVER (of course, we are not talking about mainframes, but some Oracle databases). My personal impression is that they did that because it simplified development.
How can you call incompetent to someone that just doesn't use FK? (disclaimer: I try use them everytime I can) "You've got a lot to learn"? Let me see. So, you have read one book and think that everyone that doesn't agree to that concrete book doesn't have a clue?
Sorry, but I don't agree either. I prefer to use FK for my own reasons. But I also have programs working without primary keys. And the world didn't fall out for this.
The Gemeni table handler is produced by NuSphere, which has become demonized for not (properly) GPLing the table type as is required by the MySQL license. There is a FAQ from the MySQL folks about the dispute.
Josh Woodward
It seems like most of your problems would be easily solved if we ditched the original set-theoretic background of SQL (where results are in an unordered set as opposed to an ordered sequence). If the DBA was heavily aware of the ordering of tables, then constructing efficient queries that made linear table/index scans would be easier. It seems like this would be amazingly cache and read-ahead friendly.
-j