Slashdot Mirror

I was really hoping that proper foreign key support would be added by know but was dissappointed to see it missing from the list. I decided to see if it was in the TODO list and was surprised to see full foreign key support in the Things that have to be done sometime section instead of the Things that should be in 4.0 or the Things that must done in the real near future sections. So it looks like full foreign key support won't be around till 5.0 or later. *sigh*

Lack of full foreign key support (and maybe transactions) is the only reason I don't consider mySQL a proper relational database management system as it claims sinceit doesn't enforce relationships via referential integrity checks. In many large complex applications, having referential checks built into the DB is very useful and it would be a great boon if mySQL had this functionality.

Grabel's Law

I was really hoping that proper foreign key support would be added by know but was dissappointed to see it missing from the list. I decided to see if it was in the TODO list and was surprised to see full foreign key support in the Things that have to be done sometime section instead of the Things that should be in 4.0 or the Things that must done in the real near future sections. So it looks like full foreign key support won't be around till 5.0 or later. *sigh*

Lack of full foreign key support (and maybe transactions) is the only reason I don't consider mySQL a proper relational database management system as it claims sinceit doesn't enforce relationships via referential integrity checks. In many large complex applications, having referential checks built into the DB is very useful and it would be a great boon if mySQL had this functionality.

Grabel's Law

Lack of integrity constraints (like FKs) is a good example why both mSQL and MySQL blow chunks, compared to Interbase. The MySQL folks give their reasons not support FKs, but IMHO they are a load of crap.

In my 6 years of developing database applications (Oracle, Sybase, Interbase), lack of FKs is the #1 reason why databases contain unclean data. The MySQL approach of 'let the developer' worry about it is assinine - especially if you have multiple interfaces to insert / update data in your database (e.g., a web interface and a traditional client-server interface).

The only Good Thing (tm) everyone on /. says about MySQL is that it is 'fast'. Interbase seems just as fast a MySQL on my Linux box. (I have never timed their query response times and I would appreciate any published comparisions to dissuade me.

"Fat, drunk, and stupid is no way to go through life."

SQL stands for Structured Query Language. Its basically a standard way of querying databases. MySQL is a database that supports a large set of the SQL commands. For more information, take a look at their webpage

Actually, you are wrong about the open source bit. MySQL has been GPLed since June 28, 2000. Also, there is some new support for transactions thanks to NuSphere (they are contributing code that allows row locking) Read the story here.

Just a note: I only refer to MySQL since I've never dealt with PostreSQL.

One of MySQL's solutions to replication (before it added replication features) was an option to keep update logs which contained all INSERT commands it ran on a DB. To update a slave database you would simply feed the mysql client the updatelog.

A practical solution could be cycling the update log every hour, then having the slave databases fetch the hour update, etc...

Again, I have no idea if PostreSQL supports this, but just for reference here's the MySQL manual links:

21.1 Database replication with update log
21.3 The update log

Just a note: I only refer to MySQL since I've never dealt with PostreSQL.

One of MySQL's solutions to replication (before it added replication features) was an option to keep update logs which contained all INSERT commands it ran on a DB. To update a slave database you would simply feed the mysql client the updatelog.

A practical solution could be cycling the update log every hour, then having the slave databases fetch the hour update, etc...

Again, I have no idea if PostreSQL supports this, but just for reference here's the MySQL manual links:

21.1 Database replication with update log
21.3 The update log

actually, in the mysql fulltext search docs the mysql guys do a decent job of detailing the _two_ options for querying the fulltext index. putting the "match (a,b) against ('words')" in the "select" list returns the relevance rank against all rows in the table, including those with zero relevance. if you put the match clause as part of the "where" clause then mysql returns only those rows with non-zero relevance, ordered by the relevance score (but the relevance score is not one of the return columns).

- mark

I could not use PostGreSQL because it does not run on NT (client only has NT server and does not want a Linux box for this system). I don't know much about PG, so I don't know how good it is.

I did not use MySQL even though I have run it on Linux and NT for these reasons:

• No transactions (could not wait for MaxSQL before implementation).
• No integrity constraints. Unfortunately, data entry systems require integrity constraints and the attitude of the MySQL developers is IMHO assinine. I have worked on databases with and without integrity constraints and in my experience, the data is always worse in databases where the philosophy is to 'let the database developers' deal with it.
• Unlike MySQL, PG, and most databases, in Interbase, writers never block readers. Check the product overview for details.

Interbase may not have industrial strength for massive number of concurrent users, but this particular implementation is a data entry, low volume system. I cannot personally attest to the viability of Interbase in a large number of users.

Concerning the 'opennes' of Interbase, the source is released, there are no restrictions and this database is not going anywhere soon. The main problem is that Inprise's release of the source left a bad taste in the mouths of free software advocates. This issues have been remedied and they have delivered on their promise to release the source.

Check Interbase out, you'll be surprized.

"Fat, drunk, and stupid is no way to go through life."

Why not use Postgres? It's a real database, real locking etc., commits/rollbacks (called "Transactions" to you msql/mysql boys) and it doesn't have a communist licence.

OK, I concede it doesn't have "real locking", but check out the MySQL site and you shall see that "commits/rollbacks (called 'Transactions' to us msql/mysql boys) are there, and the communist license is actually the GPL.

.iMMersE

It's not just for GUI, you know :)

For a web-server you can have either:

• mod_dtcl in your Apache
• install NeoWebScript, which is an Apache with TCL and tons of TCL extensions
• use AOLServer -- don't let the name scare you, it is a highly-optimized multi-threaded web server. Its main features include database connection-pooling and a powerful Tcl API for application development
• install tclhttpd -- a web-server written entirely in TCL (with SSL support available), which works quite well for me

Your database may be MySQL or PostgreSQL (my preference).

PostgreSQL can be built with TCL support (a client library loadable into a TCL interpreter) and a server-side extension allowing you to write server-side procedures in TCL (not anemic at all, IMHO). Postgres can be built to support SSL connections and comes with pgaccess -- a fairly powerfull database browsing and management GUI-tool (written in TCL/TK).

For MySQL there are also at least two TCL-extensions that provide for TCL access to its client API:

• mysqltcl
• TCL-mySQL

For distributed objects, etc. you could use TCL-DP. Don't let the word beta scare you -- it does wonders. The remote ends can even talk over e-mail!

And there is nothing to beat TCL/TK for a cross-platform front-end application! That's a given...

-mi

P.S. It sucks that paragraph-tags can not have attributes in /. comments. IMHO <p align="justify"> is perfectly valid and quite desirable for a paragraph with over 120 characters...

Not hardly enough to account for the benchmark results.

The MySQL online manual is very thorough. Print it, and it would be better than the books that are out there.

It was nice of you to give a URL to documentation, and then suggest that we print it; however, you give the URL of the "by chapter" HTML documentation, which would take a hell of a lot of clicking to print! On that subject, I've been searching high & low on MySQL's site for some time now, looking for a PDF version of the manual that I can download and/or print; there are hints several places on their site that such a document exists, but I can't find it. Can anyone help?

Thanks,

The "last line of defense"? Hardly. See the Functionality missing from MySQL section.

Summary:

• No sub-selects (these "might be" available in 3.24)
• No SELECT INTO.
• No transactions. (Unstable development software does not count. If you have someone who wants to commit their data to an unstable version of a database, get a new DBA.)
• Stored procedures and triggers. ("We want to add this in the future")
• Foreign Keys (there are given "reasons NOT to use FK's" which scare me considerably if the developers of a database actually believe this!)
• Views (again, "in the future")
• "--" for comments (because they happen not to like this! Well, screw the standard, who needs one, right?)

MySQL has no referential integrity. (Under some definitions, this excludes it from even being considered a RDBMS.) It certainly lacks PostgreSQL's object-oriented capabilities, which are highly useful, such as table inheritence.

PostgreSQL has all of the above features, and has had them for some time (with the exception of FK's, which are new to 7.0). It is stable and well-tested. MySQL developers are still working on these features. I'd say MySQL has quite a way to go before it reaches PgSQL's "last line of defense".

I'm surprised that in neither of the MySQL vs Postgres SQL stories has anyone commented on the near release of MaxSQL.

MaxSQL, made by the mySQL folks, will have transaction support, long the last line of defense in postgresSQL's "we're better because" flamewar.

Frankly, the next year is going to be very interesting for the open source SQL market, and not so good for the big players. (I hear Informix had to lay off hundreds on the peninsula last week).

Kevin Fox

That article looks like it was written by a 5-year-old.

Or indeed someone whose first language is not English. Someone from Finland for example.

This man clearly has taken the time to learn English - could you have written that article in Finish? Or indeed any other language other than English?

Following the links from the page with the article, I find MySQL's site with their own benchmarks of MySQL vs. Postgres: http://www.mysql.com/information/benchmarks.html .

The same page contains benchmarks against other popular databases as well.

NOTE: Pay attention to the numbers, as well as the bar diagrams. The bars are all clipped at the right side of the chart, which is misleading for a few rows.

Check out this article at mysql.com.

http://www.mysql.com/information/b enchmarks.html

>Most people who know much about MySQL know it
>doesn't have transactions and is best used as a
>read mostly write some database. It's fine for
>that. It is not fine for big systems.

Errrr, I know it hasn't been in long, but MySQL now has transactions. It uses the BerkleyDB stuff behind MySQL's SQL engine. The transaction capable tables are slower than the MyIASM tables, but you do now have the choice to run (slower) transactions or not on a table by table basis.

A quick look on the mysql site reveals they were announced as "experimental" on the 9th of May, see http://www.mysql.com/news/article-18.html for the announcement...

I've been running 3.23.22beta1 (with transactions) for about 4 weeks now and its been working great, its not currently under any load tho...

cheers

Iain

>Most people who know much about MySQL know it
>doesn't have transactions and is best used as a
>read mostly write some database. It's fine for
>that. It is not fine for big systems.

Errrr, I know it hasn't been in long, but MySQL now has transactions. It uses the BerkleyDB stuff behind MySQL's SQL engine. The transaction capable tables are slower than the MyIASM tables, but you do now have the choice to run (slower) transactions or not on a table by table basis.

A quick look on the mysql site reveals they were announced as "experimental" on the 9th of May, see http://www.mysql.com/news/article-18.html for the announcement...

I've been running 3.23.22beta1 (with transactions) for about 4 weeks now and its been working great, its not currently under any load tho...

cheers

Iain

It was added a while back.

No it wasn't. MySQL per se does NOT do real transactions. However, MySQL-the-company have partnered to produce MaxSQL, which apparently does.

In fact, I got the O'Reilly book on MySQL(don't get this one, get the New Riders book if you need to have a book.) and I found myself referring to the MySQL help file for most things. I certainly trust it more than the book for correct information.

Comparing a bleeding edge product (postgres 7.0) vs. an older version of mysql (3.22) and saying it doesn't support transactions? Experimental Transaction support has been added see here for details. Notice how they didn't interview anyone besides the guy from Great Bridge. Why can't we all just get along?

It's difficult to find good evidence on both sides of the argument for Open Source. For the record, I'm definitely in favor of open source (especially if your company has no competency in software). In any case, I've included some pieces of evidence, not necessarily in favor of open source per se, but perhaps a bit more generally about dealing with an open community with regards to development.

• Luc Barthlet's "The Simulation Sandbox"
  Actually an overview of "The Sims" before it came out, but the end of the lecture discusses how they used immediate Internet feedback to improve the design of the game.
• Linux GNOME FAQ
  Hidden in the depths of the FAQ in the section "What CORBA implementation is GNOME using?", is a comment about how Xerox's implementation of CORBA was not used. If this had been open sourced, Xerox's name would be associated with an important part of one of the largest software projects around.
• OpenSource.org
  I apologize if I've being repetitive, but I didn't see this URL earlier. Anyway, this site has some very good arguments in favor of open source. You may wish to take what they have and modify it slightly for your specific purpose.
• Successful OpenSource sites
  Conferences like O'Reilly's Open Source Software Convention, LinuxWorld Expo, open source projects like Apache and MySQL, and sites like SourceForge and IBM DeveloperWorks are showing that Open Source Software (in some cases) is becoming quite mainstream. If you have even one really cool project, that's advertising that no budget could pay for.

Well, I hope this helps.

• Built-In Firewall
  
• Great Documentation for the Responsible Administrator, (as contrasted by The Micro$oft Knowledgebase
  
• Timely Security Updates from our Vendors and our Enemies to help us patch things quickly
  
• Source Code; ...that's 2 different links, people...
  

You don't need foreign keys to join 2 tables.

The only thing MySQL doesn't do is CHECK to make sure that the keys you use really exist in the table(s) you're referencing and it doesn't automatically delete rows from table with a foreign key definition. If you use your keys like normal, it'll work just fine!

You don't need foreign keys to join 2 tables.

The only thing MySQL doesn't do is CHECK to make sure that the keys you use really exist in the table(s) you're referencing and it doesn't automatically delete rows from table with a foreign key definition. If you use your keys like normal, it'll work just fine!

You don't need foreign keys to join 2 tables.

The only thing MySQL doesn't do is CHECK to make sure that the keys you use really exist in the table(s) you're referencing and it doesn't automatically delete rows from table with a foreign key definition. If you use your keys like normal, it'll work just fine!

From the MySQL site:

" It only means that you can be sure that while each specific update is running no other user can interfere with it and that there will never be an automatic rollback (which can happen on transaction based systems if you are not very careful). MySQL also guarantees that there will not be any dirty reads."

What it really comes down to, is MySQL is a much lower level database than, say, Sybase. Sybase gives you a lot of high level tools for doing in-database programming. MySQL sacrifices this for speed, and thus you actually have to understand what you're doing in order to program for it correctly. If you do know what you're doing, there really isn't any end-result that you cannot achieve (including arbitrary levels of data integrity).

The question is very simillar to: do you want to code in Java or C? Java provides all sorts of safety mechanisms so that stupid programmers don't write elevator control programs that turn the brakes off and drop the cable. C is fast enough to write large systems in and still have them respond in faster-than-geologic time. Same goes for high level databases and MySQL. MySQL gives you plenty of really nice rope with which nearly any knot can be tied, including a hangman's noose. C compilers also tend to be a hell of a lot easier to install and maintain than a Java VM/RTE, which makes the comparison even more accurate.

Here's an article that explains why mySQL is not a real RDBMS since it's support for transactions are lacking.

This is just wrong. Not all RDBMS' are ACID, thus MySQL is an RDBMS, but it is not ACID. Of course, if you have a look at the MySQL site, there's some good commentary on why transactions are not required in the general case, and specifically are not included in MySQL. What it comes down to, is the ACID definition makes an assumption: atomicity is required in RDBMS' (I don't agree, but am willing to concede that it's required for most RDBMS applications), and transactions are the correct path to atomicity.

This latter assumption is argued against by MySQL. MySQL supports atomicity without transactions. This flies in the face of traditional RDBMS dogma, which certainly does piss a lot of people off. But, in the final analysis, what did you need transactions for? Sure, I can come up with the pathalogical case, and that's when you use Sybase or Oracle or PostgreSQL or any other transactional RDBMS. On the other hand, for 99.9% of the cases, atomicity is sufficient and for about half of those, more that sufficient.

Please, don't try to get people to believe that transactions are required in order to a) be an RDBMS or b) meet the needs of business. It's really very confusing to most people and totally unrequired. What's more, I find that the following things are required in most circumstances, and very few products besides MySQL supply them:

• Ease of installation (under Red Hat Linux, the MySQL install consists of downloading 3 files and typing "rpm -Uvh *.rpm" Compare this to the ultra-frustrating pseudo-user-interface of the Oracle install. Even PostgreSQL was a major pain to install (though nothing comes close to Oracle).
• Ease of administration. MySQL keeps each table as several on-disk files. This means that if you want to move the indexes for a particular table to another disk, you type:
  
  • mv tablename.MYI /another/disk/somewhere
    ln -s /another/disk/somewhere/tablename.MYI .
• Good tools. MySQL comes with a simple command-line SQL program, and just to point out the value of MySQL by a single example, this tool has command-line history and editing (through the readline library) as well as tablename completion. Why does Oracle's sqlplus (or sqlminus, as I prefer to call it) still not have this?
• Speed. This is really the killer. MySQL is the fastest database out there. Except in some very pathalogical cases, MySQL is general 2-10 times faster than other databases for given operations.

Have you read this article on www.mysql.com? MySQL no longer comes under the restrictive license it used to be.

>One other limitation may bug a lot of PHP users - Postgres has no equivalent to MySQL's mysql_insertid() function call.

That's because Postgres doesn't support auto_increment, like mySQL does (it uses a sequence instead).

>MySQL begins collapsing at about 40-50 concurrent connections

What does "collapsing" mean?

>Research reveals that MySQL locks the entire table when an insert occurs

True, but did you read this? It gives tips on how to tune apps that have problems with table locking!

I think this article is crap - All they've done is imported identical data into the 2 databases and compared the results! - In the Real World, you'd performance tune the databases before putting them into production, therefore this isn't a valid comparison.

As an aside, what the hell is wrong with mysql.org? It's mashed!

Now that MySQL supports transactions, I would probably go with it over PostgresSQL.

Even though Sybase hasn't open-sourced their Sybase-SQL server and Adaptive Server Enterprise (and they probably never will), I prefer using Sybase over both PostgresSQL and MySQL. Sybase SQL server 11.0.3 on Linux is free for commercial use.

and no, I'm not affiliated, etc...I just like 'em
---
Interested in the Colorado Lottery?

• Why not release it as open source, but as shareware, so that if people want to use it they can and it is up to their conscience to register and send you the $50 or whatever you feel is appropriate. Remember, though, the more you ask for, the less registrations you'll get.
• You could offer rebates for people who have registered and then send in bugfixes.
• Alternatively, release as open source, but only charge for commercial use (like MySQL - http://www.mysql.com), so again, it is up to the company's conscience to send you $100 or whatever you think a company will think it is worth. Non-commercial users don't have to pay, but will quite likely contribute bug fixes and improvements.
• You could have a stable, bug-free version, and a development version that the contributors will help iron the bugs out of.
• Combining these approaches further, you could charge for the stable version (maybe release as binary only?), but allow free use of the development version to encourage bugfixes.

J

MySQL, for example, is open-sourced under *nix, but is shareware under Windows.

While I agree that a lot of companies are certainly jumping on the "open-source bandwagon" and are only doing it for publicity and stock spikes, I don't think that this is one of them.

--

<spam> KLICK HERE FOR TRANSACTIONS </spam>
--

Can anyone provide references to show that MySQL's implementation of transactions IS inadequate/incomplete?

(that's not just rhetoric; I really would like to know why!)

Being unfortunate enough not to be the server admin, the server I use is indeed running Windows. I managed to get MySQL on it and I am grateful that a Windows version does exist.

According to the MySQL press release, "One of the biggest news is that the Windows version of MySQL is now also distributed under the GPL license." So can I get those 200 Euros reimbursed? :-)

http://www.mysql.com/sup port/arrangements/example/isp.html

"If you charge for MySQL installation and administrative support as part of your customer service, then you need a license because you are selling a service that includes MySQL."

I sure hope this is just something which mysql forgot to take out of the website, because this is certainly not a valid interpretation of the GPL.

• You link a part of the of [sic] MySQL that has a GPL Copyright to a program which is not free software (embedded usage of the MySQL server). In this case your application would also become GPL through the clause in the GPL license that acts as a virus. By licensing MySQL from us under a commercial license you will avoid this problem.
• You have a commercial application that ONLY works with MySQL and ships the application with the MySQL server. This is because we view this a linking even if it is done over the network.
• You have a distribute MySQL and you don't provide the source code for your copy of the MySQL server, as defined in the GPL license.

• if they intend to licence MySQL for commercial use, does this mean that they won't be accepting contributions which don't sign over the copyright?
• is the second term likely to hold up in court?
• if they don't intend to accept contributions, and their second term is held up, their licence really hasn't changed, has it?

Here is the documentation about how to use it.

This new license frees me up. I've been working on a book on building intranets with PHP and I wanted the code to be cross platform, but PostgreSQL doesn't come in binary form for Windows, Access/MSSQL weren't available for UNIX, MySQL had a restrictive license for Windows. Now I can use MySQL with no problems. Woohoo.

LetterJ

web.mysql.com/news/article-22.html

One of the biggest news is that the Windows version of MySQL is now also distributed under the GPL license.

Finally, the Win32 version of MySQL is completely free. This makes it the only free (as in both) industrial-strength database on the Windoze platform AFAIK.

So I now have even more ammo to get my boss to ban Microsoft Access and SQL Server from our office!

Does anyone know how to use this from Active Server Pages (ASP)? ODBC driver I guess, right?

--

MySQL goes GPL. Full article is here.

Really cool news...but offtopic.. ;)

Oh, save us, Bob. Here comes another flamewar. :)

There are historical reasons for why MySQL has acheived its current level of popularity. First off, older versions of MySQL have been GPLed, and all are available at no cost (unless you're going to repackage & sell it). That puts it in a very limited category of databases.

Until very recently, MySQL's only competition within this segment has been Postgres. Many people perceive Postgres as being more difficult to use than MySQL. And frankly, it is, especially if you're using only the command-line tools that come with the distribution. Furthermore, MySQL actually does offer some flexibility that Postgres doesn't, especially when it comes to modifying table properties later on.

Frankly, I'm not at all surprised that there are so many people using MySQL. It's great for jobs where transactions aren't of blinding importance, like, say, discussion boards just like this one.

You may find MySQL inadequate for your needs, but I'm willing to bet that most of the users of the software don't use it to handle $50K+ orders.

From a 15 minute reading of the license, it looks like it's similar to the GPL, excepting that it's a bit more restrictive about providing source code (must come with every copy; pointing isn't good enough, though if they're in the same archive (say, an ftp site) but different files/packages, that's fine), and there can be no charge for the program alone (it may be charged for in a collection, as with the GPL). Oh, and this license only applies when not on a Microsoft O/S (no joke, read the license); that has a seperate shareware license.

So yes, MySQL is open. Sort of.
---

>the licesnse that MySQL is under does not come anywhere close to meeting the open source definition

Exactly. So why not move to a product that has it, like Oracle/Informix/...., or, if you are going to spend the $, why not invest the $-time in PostgreSQL, a database that IS opensource?

Is there any reason beyond: MySQL is what we have been using, so now we will continue to use it?

MySQL has said:
On Roll-Back
"MySQL has made a conscious decision to support another paradigm for data integrity, "
Ok, fine, that is a design choice. If they wanted it(rollback), they would have designed it in.

PostgreSQL has rollback, and just needs database replication, and they would LOVE to see that feature.

So, why work with MySQL, other than "it is what we have always done" or "We didn't think of another option"? Are you hoping to have them change the licence?

Older versions of MySQL are open under the GPL: http://web.mysql .com/Downloads/MySQL-GPL/MySQL_GPL-3.20.32a.tar.gz . The latest version has a source RPM available at http://web.mysql. com/Downloads/MySQL-3.23/MySQL-3.23.16-1.src.rpm. One way or another they are open enough to be qualitatively better than MS SQL Server, even though they may not be a free as we'd like.

Older versions of MySQL are open under the GPL: http://web.mysql .com/Downloads/MySQL-GPL/MySQL_GPL-3.20.32a.tar.gz . The latest version has a source RPM available at http://web.mysql. com/Downloads/MySQL-3.23/MySQL-3.23.16-1.src.rpm. One way or another they are open enough to be qualitatively better than MS SQL Server, even though they may not be a free as we'd like.