MySQL 5.0 Candidate Released

Brian "Krow" Aker (Former Slashdot Coder now MySQL Employee) writes "I am pleased to announce the release candidate for MySQL 5.0. This version has been in development now for three years. We have worked to add update-able views, ansi stored procedures, and triggers. In addition we have added a number of fun features that we are experimenting with and resolved issues with bad data inserts (which personally annoyed the hell out of me when we rewrote Slashdot a couple of years back so I am happy to see this issue go away). We look forward to feedback on the candidate and will show some love for bug reports."

Comments

Time for new comparisons to be made.

[CyricZ]

It's time for somebody to do a new, impartial study regarding the performance and feature benefits of this new release of MySQL, PostgreSQL, Firebird, SQLite, and perhaps other open-sourced relational databases.

Re:Time for new comparisons to be made.

[KiloByte]

And of course you can't include Oracle in any impartial study.
Hmm... do I smell... fear?

Re:Time for new comparisons to be made.

[RealityMogul]

Call up Microsoft, I'm sure they'd love to sponsor the study.

Re:Time for new comparisons to be made.

[schon]

Correct. If you're measuring performance, then you *can't* include Oracle.

Re:Time for new comparisons to be made.

[tdemark]

And of course you can't include Oracle in any impartial study.

You could, except for the fact that the license you just paid thousands per CPU for doesn't allow you to publish the results.

Re:Time for new comparisons to be made.

[ceeam]

But puhhhlease, don't put MySQL performance based on MyISAM and feature set based on InnoDB! Stick to either one. BTW - last I checked for myself, Firebird was beating _even_ MyISAM for raw speed on simple queries for local engine. Something I did not expect. Oh, yeah! When comparing performance please include results for local engine AND over-the-LAN performance. Different beasts have different protocol issues that show up when using "real" LAN and may be masked when using "local" engine mode. For example, MySQL has very simple and LAN-friendly protocol. Unlike Firebird, but it's still ok. And MSSQL, EG, has very, very serious issues when used for lots of "simple" queries.

Re:Time for new comparisons to be made.

[MemoryDragon]

And please do not use postgres in its default settings but in sane ones...

Re:Time for new comparisons to be made.

[ajs]

From the linked article,

"The standard Oracle license agreement normally prevents users from publishing benchmark results." -http://www.orafaq.com/faqora.htm#SPEED

Clear enough?

Re:Time for new comparisons to be made.

[awol]

Easy just report;
        Performance Prettiness Completeness
1. MySQL Firebird Postgress
2. Postgress MySQL
3. Postgress Firebird
4. Firebird MySQL
5. ... ... ...

Then oracle has no problems with the report since you are _not_ reporting anything about them.

Re:Time for new comparisons to be made.

[Britz]

c't (famous German computer mag) is doing exactly what you are describing. They call for a contest in which everyone can implement their favourite database in a setting with a dvd store and then mail their description on how they do it to the mag.

Rules and everything else is here:
http://www.heise.de/ct/05/20/156/
(but only in German)

Re:Time for new comparisons to be made.

[Matt Perry]

The solution to that is simple. Include Oracle and put it dead last in every category. Explain that the license doesn't allow testing so therefore it didn't even get out of the gate in the tests.

Re:Time for new comparisons to be made.

[zufar]

Thanks, I'm not interested in MySQL performance anymore.I have made transition from MySQL to PostgreSQL since the beginning of MySQL SCO collaboration.

Re:Time for new comparisons to be made.

[ckaminski]

Pls explain?

Re:Time for new comparisons to be made.

[killjoe]

Please include SAPdb and ingres. Ingres seems to have lots of great features I would love to read an objective article about it.

Re:Time for new comparisons to be made.

[pAnkRat]

Your prayers have been heard: Heise Database Contest

The (widely known) german Ct magazine has isued just this contest to its readers.
It uses the "Dell DVD Organizer" wich has "many" database backends.
The software is then (stress)tested with 10 MB and 1GB database sizes.

The contestants are urged to write their own backend, or optimize an existing backend,
or optimize the databse of choise, or do all of the obove :-)

The contest is open until the 13. november 2005 and results will be released some time later.

I know TFA is in german, but I know you can use "the fish".

Re:Time for new comparisons to be made.

[MemoryDragon]

Sure.. the default settings are set towards mem preservation not speed optimization.

Is it a "real" database yet?

[digidave]

Every time any database is mentioned on Slashdot we get a load of comments about how MySQL is not a "real" database because it doesn't support {insert random feature here}.

Is it a real database yet?

Re:Is it a "real" database yet?

[mmkkbb]

Actually, most of those features are in MySQL 4. The two big problems are: Lots of people still run MySQL 3.x, and these features only work for certain table types.

Re:Is it a "real" database yet?

[codesurfer]

I'd say so...despite the detractors, I've been able to use MySQL for many highly trafficed sites, and heavily used applications. Does it have the full robustness of Oracle? Of course not, but it also does not come with the accompanying price tag. Choice of db software is just that...a choice. I can envisage situations in which I may prefer having Oracle, but to be honest, MySQL addresses most day to day situations more than adequately. It's a real db, IMO.

Re:Is it a "real" database yet?

[NineNine]

We have worked to add update-able views, ansi stored procedures, and triggers.

It's much closer. As to whether it has ALL of the features yet, I personally don't know because I don't use it. Updateable Views, Stored Procedures and Triggers are all a big step in the right direction. Now, for anybody who's serious about their data (like I am), there will be a waiting period of a few years while the kinks are worked out, before it's really ready to be compared to other, more mature databases that have had these features for 10 or 20 years.

Re:Is it a "real" database yet?

[LLuthor]

MySQL selects do not use indexes when there are sub-selects most of the time. This makes them practically useless unless you can re-factor the query to use a convoluted join instead.

Sub-selects suck in mysql compared to postgresql or oracle. I havent used other DBs much so I don't know how well they perform.

Re:Is it a "real" database yet?

[Anonymous Coward]

Ah yes, it's very important that you be serious about your databases of free porn. I'm sure your customers are very concerned (:

Re:Is it a "real" database yet?

[Anonymous Coward]

I cannot trust MySQL to store data reliably until the MySQL give a clear explanation as to why they made decisions that no competent database developer would ever make.

For example, you have a constraint limiting a column to a maximum of 99. You attempt to enter 999 as a value, and it got silently altered to 99. Not only is it against SQL standards, it's also completely and utterly the opposite of common sense.

Just once, I'd like to hear a MySQL developer say "yeah, I don't know what we were thinking, that's a really fucked up thing to do". It doesn't matter whether this intentional bug got fixed, what matters is that there are MySQL developers that thought that this was a reasonable thing to do.

That's not an isolated incident, either. There are reams and reams of utter idiocy along exactly those lines. So really, what would it matter if they've fixed those particular instances? How do I know they haven't introduced a dozen more breakages along those lines? So far, they haven't shown any indication that they even realise how fucked up that is - so why on earth would I trust them with data that matters?

Re:Is it a "real" database yet?

[EntropyEngine]

Well I haven't been using a proper database for years, then. How bad is that?

Re:Is it a "real" database yet?

[kpharmer]

Agreed - it is a real database, that can do real work and provide real value.

However, it has a history of inexcusible quality problems with silent errors, silent data truncations, etc, etc. The leadership of mysql also has famously stated that almost nobody really needs transactions. So, there's quite a lot of skepticism about the company and the product.

MySQL might turn that around. Hopefully they will.

Back to price - the difference isn't always so huge: I know that db2 is around $700 for a license limited to a two-way SMP, MySQL is around $500. I think Oracle is also under $1000 for a similar machine. Now, db2 & oracle can go *way* up from there (to support 16-way partitioning, etc). But mysql doesn't even compete there. In the space it does compete, oracle and db2 are in the same ballpark.

Re:Is it a "real" database yet?

[ThaFooz]

It is absolutley a real database now.

IMHO, what prevented MySQL from being a 'real' db wasn't missing transaction feature X, but data truncation. For anyone who hasn't run into this MySQL has a really nasty habit of truncating data if it won't fit into a field. Occasionaly this is desired for logging and non-critical information, but it can cause major problems that are incredibly difficult to track down. The solution up until now was to over-estimate column size and hope for the best (I'm aware that some connectors after 4.1 could convert warnings into exceptions/errors, a bit sub-optimal if you ask me).

5.0 corrects this issue by introducing several server modes controlling just how picky the db is about the input. I couldn't be happier - just being able to use conservative guesses about column lengths (and fixing them on the fly if I encounter larger values) means that my tables and keys can be significantly smaller - a HUGE space and performance boost. I'll be able to sleep better at night too.

At the moment I'm working on a MySQL data warehouse, and having real table partitions and the federated storage engine (virtual tables, so you can do cross-db joins) could solve a lot of problems for me, and views could be convenient (syntatic sugar if you ask me).

I'm sure someone out there will respond 'well if that bothered you, you should just use database x', but as far as I'm concerend one only has a few choices for databases:

• Oracle
• MSSQL
• Postgres
• MySQL
• SQLite

Oracle has everything one could possibly desire and then some, but its expensive to license and expensive to maintain. MSSQL is nice feature and administration wise, but its still fairly expensive and you're locked into using Windows Server (ewww). As far as I'm concerned, Postgress and MySQL are very close. Historicaly it has been about features vs. performance, but the two are converging rapidly, and now its a matter of chosing between a half dozen advanced transactional features and an easy multi-user environment & larger community/language support.

Re:Is it a "real" database yet?

[Anonymous Coward]

You obviously missed my point, so I'll try and be a little clearer.

The problem is not that they wrote software with bugs. And obviously I'm not complaining about fixes, as you seem to be implying.

The problem is the nature of the bugs that they introduced. No developer who is competent to work on a database would ever, in a million years, think that silently altering and discarding data without the application's knowledge is an acceptable thing for a database to do.

So basically, if we know for a fact that they were incompetent before, and they've showed no signs of understanding just how incompetent they were, why would we believe that they are competent now? And if we don't take it on faith that they've magically become competent without anybody noticing, then why would we trust a database that is developed and maintained by incompetent developers?

Re:Is it a "real" database yet?

[jonadab]

According to SQL in a Nutshell, the usual test for this is Codd's Rules. At the time the book was written, MySQL was the only one of the four covered implementations (the others being PostgreSQL, Oracle, and MS SQL Server) that didn't qualify, since it didn't support views or atomic transactions. The copyright date on this says 2001.

Whether MySQL has since gained views and atomic transactions, I don't know. They're both features I haven't yet personally run into a need for, so I haven't checked. That said, I do want to experiment with PostgreSQL at some point; I've heard nice things about it and would like to work with it and get some experience with it. (Oracle, too. That would look nice on my resume.)

And I'm glad to see MySQL getting stored procedures. That's a feature that a lot of ISVs use in their database-using products, and a key reason sometimes cited why their product relies on a particular RDBMS, e.g., Polaris Library Systems has stated that stored procedures are a key reason why their software only works with MS SQL Server. (The other reason they cite is price. Yes, really; I assume they're counting Oracle as the competition when they say that one. And the _real_ reason is because they're pretty much a Microsoft-only shop, same reason they require IIS, too, citing ISAPI, as if Apache doesn't have an excellent extension mechanism. Nevertheless, stored procedures are a valid excuse, and taking away such valid excuses is a good thing.)

Re:Is it a "real" database yet?

[Decibel]

If you're serious about your data, then try this out:

CREATE TABLE t(t tinyint);
INSERT INTO t VALUES(300);
SELECT * FROM t;

See also http://sql-info.de/mysql/gotchas.html

Re:Is it a "real" database yet?

[Anonymous Coward]

I looked up "graceful degradation" and it didn't have anything at all to do with the subject at hand. Allowing malformed or errnoenous data to be inserted into a table which has been expressly defined to disallow data in that size, shape or form is totally broken behavior. For a database to accept such data is neither graceful nor is it degredation.

I suppose you'd also argue that it's a bad idea for the Diesel and Petrol gas pump nozzles to be different sizes, because it should be possible to accidently pump diesel into your petrol-burning car. After all, isn't it better to get /something/ into the tank, even if it's the wrong fuel?

No, rather, I'd expect you might consider the different sized nozzles to be a wise safeguard against the unavoidable human errors which would otherwise occur. Similarly, being able to enforce data integrity at the most logical layer -- the database -- is also a wise and necessary component of any database system which expects to be taken seriously.

Re:Is it a "real" database yet?

[ajs318]

Allowing malformed or errnoenous data to be inserted into a table which has been expressly defined to disallow data in that size, shape or form is totally broken behavior. For a database to accept such data is neither graceful nor is it degredation.

Perhaps you were looking in the wrong places. It's degradation whenever a piece of software doesn't do what you expected it to do {i.e. store something you'd already said you didn't want it to store}. Under those circumstances there are two possible behaviours:

1. Die horribly with an exception
2. Alter the data to fit the pre-existing constraints

Either one is acceptable, as long as you make it quite clear in your documentation which one you chose and are absolutely consistent about it. {There is sometimes a third option, which is to modify the constraints to make the presented data valid. That option, however, often is simply not available and one of the other two must be chosen.}

I suppose you'd also argue that it's a bad idea for the Diesel and Petrol gas pump nozzles to be different sizes, because it should be possible to accidently pump diesel into your petrol-burning car. After all, isn't it better to get /something/ into the tank, even if it's the wrong fuel?

Well, in some countries, the diesel and petrol nozzles are the same size {but different denomination banknotes are different sizes and colours, go figure}. And the people in those countries don't have a problem with it. They look twice before they pull the trigger. {Although they probably hand over their money at the till without looking too closely at it, and would likely fall prey to any scam involving visually-similar-but-different-value banknotes}.

Similarly, being able to enforce data integrity at the most logical layer -- the database -- is also a wise and necessary component of any database system which expects to be taken seriously.

I disagree that the database is the most logical layer at which to enforce constraints. I think the most logical layer at which to enforce constraints is at the application layer. You threw generality of purpose to the four winds the moment you gave the database a schema and imposed constraints. Your application already has to handle out-of-range values somehow or other; by making a noise, displaying a message or shooting a steel spike up the careless user's backside. So you might as well check that the data is valid before you present it to the server; particularly given as you often already have to do some sanitisation anyway with data supplied by a user, just to make sure it isn't being used for some kind of code injection attack.

Re:Is it a "real" database yet?

[kpharmer]

1. The reason that mysql sometimes silently truncates data isn't because they are being clever - it's because they were being sloppy: it's occurs inconsistently, has been acknowledged by the company (relucantly) to be a bug, and they are now bragging about fixing it.

2. Even if it was a clever strategy it is inconsistent with every other database product out there. This interferes with portability, standardization, and commoditization.

3. The value of constraints in a database has been embraced by every other database product, and many other application and database designers. There is no credible argument against database constraints, so your defense of their sloppiness via this argument is irrelevant. But with this in mind - here's a quick explanation of why you're in the minority in this view: consider that your application layer is changing over time, you may add additional applications pointing to the same data, but the older persisted data is not changing in the same way. You may add validations & edits in place today for new data - but your old data idn't benefit from these. If you only validate at the application layer then you will end up with inconsistent data. For this reason you want constraints at both the persistence and application layers - they complement each other well. It's not one versus the other.

Re:Is it a "real" database yet?

[FatherOfONe]

People have mentioned in a few other posts that DB2 is "only" $700. People then mention that MySQL is $500. I would argue that this isn't the case. I would say that MySQL and PostgreSQL are free and DB2 starts at $700, but that cost is just the beginning. What happens if you run a processor with multiple cores? How much does it cost if you migrate off of X86 to Sparc? How much if you want to run it on one of their mainframes? It is my experience that Oracle and IBM tend to "give" away some of their products, but they do that to hook you in to the upgrades. In short, IBM and Oracle are not stupid companies and they want nothing more than to get their hooks in to your company. Given that the database is generally the cornerstone of the companies data, it is a great place for them to start. Then when your company "needs" a feature that isn't supported in the "standard" package the cost will go WAY WAY WAY up.

This isn't the case with PostgreSQL or MySQL. You never get that "drug pusher" type of attitude.

Re:Is it a "real" database yet?

[Phillup]

Is it a real database yet?

Well... I'm a programmer who does db stuff, not a dba... so, I wouldn't know a "real" database if you hit me up side the head with it's manual.

But...

Does it return an error when it doesn't store the data you asked it to store... or does it just silently truncate it and pretend everything went well?

Especially fun when you throw invalid enumerated values at it...

(Hell, they may have that fixed already... but it doesn't work in the setup I'm currently using.)

Re:Is it a "real" database yet?

[ShieldW0lf]

This isn't the case with PostgreSQL or MySQL. You never get that "drug pusher" type of attitude.

Are you kidding? MySQL is TOTALLY a "get our hooks in drug pusher" kind of company. They're the "Project Mayo" of the DB world. Their development/business model revolves around getting everyone onboard with the open source version and then exploiting the open source development community with their policy that copyright to patches and improvements must be transfered to MySQL so they can then be sold closed-source.

And on top of all that, their product sucks. Good enough for internal content management or a free forum perhaps, but hardly something you'd trust with important data. They obviously agree, or they would have stuck to developing their existing codebase rather than using SAPs.

Why do people spend so much time cheerleading for these guys?

Re:Is it a "real" database yet?

[kpharmer]

> I would say that MySQL and PostgreSQL are free and DB2 starts at $700, but that cost is just the beginning.
postgresql is free
mysql is only free under some circumstances, and if you are distributing client apps that connect to a mysql database you might want to get legal advise before assuming that it is free.

> What happens if you run a processor with multiple cores?
doesn't matter to db2's licensing on intel (not positive about power5, etc)

> How much if you want to run it on one of their mainframes?
costs quite a lot more to run db2 on a mainframe. Then again at least it runs there, mysql doesn't. So, I'd count that as a bonus for db2, not a penalty. Of course, if you're running linux on the mainframe, they can both run there. Not sure about db2 licensing then, but assume that it would still follow its linux licensing model.

> It is my experience that Oracle and IBM tend to "give" away some of their products, but they do that to hook you in to the upgrades.

Not really - five years ago there was no really cheap version of either. The difference is that now there is more competitition on the low-end, so DB2 & Oracle have to be more competitive there in order to keep market share against sql server, postgresql, mysql, etc.

Besides, mysql is really the nasty example of this behavior with their hybrid licensing model.

Re:Is it a "real" database yet?

[jpkunst]

From http://www.mysql.com/news-and-events/news/article_ 959.html

Implementing ANSI SQL standard ways of using existing MySQL features means there will be fewer unpleasant surprises ("gotchas") for those migrating to MySQL from other database systems:
* Strict Mode: MySQL 5.0 adds a mode that complies with standard SQL in a number of areas in which earlier versions did not; we now do strict data type checking and issue errors for all invalid dates, numbers and strings as expected

Can we now finally retire that tiresome "MySQL gotchas" link? Please?

JP

Re:Is it a "real" database yet?

[Donny Smith]

>MySQL is around $500.

https://shop.mysql.com/ - MaxDB for non-SAP apps is US$1,490, MySQL Network Silver (working hours phone support) is US$1,995 and the cheapest one is US$595. And all of this is *per year*, folks.

The free version seems to be the only right-priced product they have.

Re:Is it a "real" database yet?

[fbg111]

It's degradation whenever a piece of software doesn't do what you expected it to do {i.e. store something you'd already said you didn't want it to store}. Under those circumstances there are two possible behaviours:

1. Die horribly with an exception
2. Alter the data to fit the pre-existing constraints

Actually there are three options:

1. Die horribly (whatever that means) with an exception
2. Alter the data to fit the pre-existing constraints
3. Don't die at all, return an exception, wait for future valid requests

It should be obvious what the correct, desired behavior is. Methinks thou dost protest too much...

Add bloat, stay fast

[jurt1235]

I welcome stored procedures triggers very much, as long as they are fast enough to compete with the current "just program it yourself in you chosen language" style. Anyway: Another stored procedure language to learn.

Re:Add bloat, stay fast

[jurt1235]

And yes, I know: SQL:2003 standard. I am used to PL/SQL and SAS. At least one reason less to want to use oracle.

Re:Add bloat, stay fast

[Dan Ost]

In PostgreSQL, you can write your stored procedures in
Python, Perl, and a couple of other languages. It probably
won't be too long before MySQL allows the same thing. With
any luck, you won't need to learn a new language at all.

Re:Add bloat, stay fast

[DrXym]

Unless every single app you connect to the DB contains trigger-like code and you don't expect your users to abuse or circumvent the trigger-like code, I fail to see why you would not want them.

As for stored procedures, I thought the whole point of them is that they are precompiled and live on the server side. Not only does this make them faster but you can change or modify the stored procedure without having to change the client code. Again, what's the point of not wanting them?

Is there any evidence to suggest that "just program it yourself" on the client side would be any more efficient? It seems to me that sending a large chunk of SQL over the wire, parsing / validating it, compiling it, and executing it must be for any sane DB slower than a stored proc.

Re:Add bloat, stay fast

[popeyethesailor]

Is there any evidence to suggest that "just program it yourself" on the client side would be any more efficient? It seems to me that sending a large chunk of SQL over the wire, parsing / validating it, compiling it, and executing it must be for any sane DB slower than a stored proc.

It doesnt necessarily have to be client-side code; it can be server-side modules written in C/C++. Even now, people prefer OCI over Oracle stored procedures for some specialized applications. Stored procedures are usually compiled to byte-code, which is then interpreted at runtime. This translates into mediocre performance for most compute-intensive apps.

However I agree with your basic premise; SPs are the way to go for more database manipulation code.

Re:Add bloat, stay fast

[Anne Thwacks]

stored procedures, I thought the whole point of them is that they are precompiled and live on the server side. Not only does this make them faster but you can change or modify the stored procedure without having to change the client code.

The above is true, the killer benefit is that, with them living on the server side, updates in the procedures are synchronised with updates to the data: if the business rules they enforce change, they change for all users at the same moment. This can be very important in cases where data is worth money. (Think: telecomms billing)

Re:Add bloat, stay fast

[drew]

It seems to me that sending a large chunk of SQL over the wire, parsing / validating it, compiling it, and executing it must be for any sane DB slower than a stored proc.

While I am a fan of stored procedures in many cases (if only to avoid having random sql statements strewn around your code), the above is not necessarily true. All DB's that I've used will cache the query plan for queries that you execute, meaning that if you write your query correctly , you only hit the performance penalty for parsing, compiling, etc. the first time.

Of course, this only works if the database supports the concept of bind variables, because a database can't necessarily tell that the following queries are identical:
insert into t values (1);
insert into t values (2);
insert into t values (3);

but it can if you do something like this with varying values for ':X':
insert into t values (:X)

Re:Add bloat, stay fast

[dcam]

I'm not sure that this is a good thing. There is a debate running about SQL Server 2005 (Yukon) supporting CLR procs, which is basically the same question.

The question is why would you want a procedural language working on Set based data? These kind of operations should be moved to the client. You risk tainting your database with operations that aren't really database related.

Note that this is a risk, and that there are some cases where running procedural code against the database is necessary.

How does the source code quality compare?

[CyricZ]

How does the source code quality of this new release of MySQL compare to that of projects like PostgreSQL or Firebird, which have a far longer history and/or were formerly commercial developments?

Re:How does the source code quality compare?

[loconet]

Are you saying that PostgresSQL, Firebird may have better source quality because of their background? They may have better codebase but far longer history and commercial background does not equal good source quality. Some might say it's actually the opposite.

Re:How does the source code quality compare?

[JohanV]

How do you want it to compare?

Source code quality is not easy to compare. At a first glance, MySQL is doing very good. They have this nice blurb about only having 1 defect in 4000 lines being more then 4 times better then with most commercial software. But if you dig deeper, you notice that PostgreSQL has been tested by the same company and only had 1 defect in every 39000 lines of code. Wow, so PostgreSQL must really be a lot better then MySQL.
But if you dig even deeper, you will find some explanation from a PostgreSQL developer and you remember what your mother told you about lies, damned lies and statistics.

You want to know about source code quality? Go read the source.

Re:How does the source code quality compare?

[Overly Critical Guy]

Are you saying that PostgresSQL, Firebird may have better source quality because of their background?

No, he's asking if they do because of their backgrounds.

Re:How does the source code quality compare?

[CyricZ]

Actually, I was suggesting the opposite. Considering that Firebird was a closed source commercial product for years, I would expect it to have quite a terrible code base. But then again, they have had a number of years to fix it up.

Re:How does the source code quality compare?

[CyricZ]

That's a good place to start.

I was thinking more along the lines of how well functions and classes are named, for instance. The layout of the source tree. The amount of code redundancy. The ease with which new features can be added. Stuff along those lines.

Integrity?

[Anonymous Coward]

Does 5.0 have all the issues with data integrity sorted out? These are VERY important to me.

Working "ROLLBACK" even with millions of rows.
No silent data truncation.
No silent "BEGIN TRANSACTION" on unsupported table types.
No being able to drop a table if it is referenced in a foreign key.
etc etc.

Once these are fixed, I will look at how well it performs compared to real DBMSs.

Re:Integrity?

[ip_fired]

I can confirm that MySQL 5.0 doesn't allow you to drop a table if it is referenced by a foreign key.

Also, the way to go when you set up MySQL is to set the default table type to InnoDB, which supports transactions, then you don't need to worry about point 3.

I haven't experienced the other two problems. But then, I don't have millions of rows, only hundreds of thousands.

Re:Integrity?

[twinchang]

For those who often claim MySQL didn't do proper data validiation.

Please check this for MySQL 5.0+ Server SQL Mode.

Re:Integrity?

[ttfkam]

Does it allow you to drop a MyISAM table if it is referenced by a foreign key, or do you have to remember to use a table type that supports foreign constraints?

Re:Integrity?

[ip_fired]

That I don't know. However, I just set my default table type to InnoDB and then I don't have those problems. MySQL is usually 2-5 times faster than our Informix 9 database on the same hardware with the InnoDB tables (a Sun box with 2 Sparc IIIi processors and 4GB of ram).

I wonder if Informix 10 has any improvements in the area of speed, but I'll never find out because it's just too hard to upgrade (it takes sooooo long to configure the server so that it performs with any speed).

Of course it's a "real" database.

[CyricZ]

Of course it's a real database. People have been successfully using it for years. Maybe at one point it didn't comply with the formal definition of a "relational database" due to missing features, but nevertheless was still a suitable product for many. And that alone is enough for it to be considered a "real" database.

Anyone know of a good free MySQL GUI?

[lion2]

Something that is comparable and as easy to use as SQL Servers Enterprise Manager. The tools available on MySQL's site aren't very good. My main concern is easy importing and exporting to and from Microsoft Access. For example I can just copy records from access and easily paste them into SQL Server. Thanks for the help.

Re:Anyone know of a good free MySQL GUI?

[Chagrin]

Use the MyODBC driver. Link the tables with Access and cut and paste all you want.

Re:Anyone know of a good free MySQL GUI?

[Anonymous Coward]

Aqua Studio is very good (http://www.aquafold.com/) links to most different db types as well as standard ODBC

Re:Anyone know of a good free MySQL GUI?

[ThogScully]

I personally find the phpMyAdmin web interface far more friendly than SQL's Enterprise Manager.
-N

Re:Anyone know of a good free MySQL GUI?

[JohnnyBigodes]

Sorry about the "free" part, but Navicat is the best MySQL interface that I've seen to date, and I've tried quite a few (not all of them, obviously :)

Re:Anyone know of a good free MySQL GUI?

[PeeAitchPee]

phpMyAdmin is great for those of us working with PHP.

Re:Anyone know of a good free MySQL GUI?

[lion2]

Well I do prefer free, but if the price is right and the gui is great then I would be willing to buy it.

Re:Anyone know of a good free MySQL GUI?

[minus_273]

EMS MySQL Manager 3 Lite is by far the best

Re:Anyone know of a good free MySQL GUI?

[FlopEJoe]

Aqua Studio is some good stuff. I haven't used it for MySQL but it's an option when registering a new database. Aqua is available "for personal use only" so not quite "free."

OS: Windows, Linux, OSX, Solaris

RDBMS: Oracle, DB2 UDB, MS SQL Server, Sybase, Informix, Postgresql, MySQL

Re:Anyone know of a good free MySQL GUI?

[pvera]

Please mod up parent. Aqua Data Studio is a very damn good application, and the developer is extremely responsive. It is common to have him hang out in the support newsgroup for hours at a time, walking people thru things even if it is obvious they are too lazy/dumb/etc. to follow the documentation and FAQs. Between Aqua Data Studio (which btw runs on pretty much anything that can run Java) and phpMyAdmin, you should be able to do almost 99% of whatever it is that you need. The other 1%? RTFM and use the CLI.

As for the SQL Server Enterprise Manager, it was a turd in 6.5, less of a turd in 7.0 and then got worse in 2000. The improvements added to Enterprise Manager for the jump from 7 to 20000 were pretty damn good, but they are offset by bullshit mickey mouse Jscript interface errors that have no place in a database management application. This sucks because the SQL Server Query Analyzer only got better and has none of these weird Jscript issues.

For those of you stuck in the Oracle world (and cursing the Oracle provided tools), you may want to check out Benthic (http://www.benthicsoftware.com/), they have been publishing very nice and inexpensive shareware apps that work more or less like the SQL Server Enterprise Manager and the Query Analyzer.

Re:Anyone know of a good free MySQL GUI?

[jdgreen7]

Keep an eye out for OpenOffice.org's 2.0 Base application. It has native ODBC and MySQL connectors that will allow you to Create, Edit, and populate tables in a similar fashion to Microsoft Access. The current Beta has a few bugs left in it, and I wouldn't say it's full-featured yet, but it's definitely matured quickly. I'll be monitoring their progress for the next few months and possibly start using their apps for generating some basic reports and automating the PDF creation. OO.o is definitely going to be a contender soon (and already is in some respects).

Re:Anyone know of a good free MySQL GUI?

[j3tt]

The preview version of Toad for MySql is decent enough. (This assume though that you're using 'doze) http://www.toadsoft.com/toadmysql/toad_mysql.htm/

Re:Anyone know of a good free MySQL GUI?

[ckaminski]

Got worse in 2000? Are you nuts? :-P

About the biggest beef I have with SQL server is that you cannot schedule jobs in SQL Agent to run under different user contexts. That's about it. That, and it's sometimes incoherent inability to grow a database under load.

Re:Anyone know of a good free MySQL GUI?

[Phillup]

I use phpMyAdmin all day long and wouldn't touch PHP for my programming projects.

I'm a Perl programmer... so there!

;-)

Point is... phpMyAdmin rocks... even if you *don't* work with PHP.

(I also use tools written in C, C++, Python, Ruby and who knows what other heathen languages...)

Re:Anyone know of a good free MySQL GUI?

[pvera]

My favorite pet peeve is that half the times dbcc_shrinkfile and dbcc_shrinkdatabase are placebos, they don't do a damn thing.

Re:Anyone know of a good free MySQL GUI?

[exKingZog]

Try using them on data files containing lots of blobs... then try doing that on an upsized Access database storing thousands of OLE pictures (existing system, not mine). Nightmare.

Re:SCO

[jurt1235]

Can we trust HP (linux everything campaign) while they are strategic SCO partner?
Can we trust Oracle (Linux is our main development platform) while they are strategic SC partner?
And many many more.
Maybe we can even find the website of a dictatorship using MySQL, oh.

Examine t he license carefully!!

[scorp1us]

After the 3.x series, the license changed drastically. Of course you won't find this discussed much. The permitted uses of MySQL as GPL are significantly reduced. You can only use MySQL in conjuction with OSS. Previously, you could use MySQL in a non-GPL environment under several permissive conditions. The new license is so restrictive now that a special exception is made for PHP

Specifically:

        * MySQL is free use for those who are 100% GPL. If your application is licensed under GPL or compatible OSI license approved by MySQL AB, you are free to ship any GPL software of MySQL AB with your application ('application' means any type of software application, system, tool or utility). You do not need a separate signed agreement with MySQL AB, because the GPL license is sufficient. We do, however, recommend you contact us as there usually are good opportunities for partnership and co-marketing.

        * Under the Open Source License, you must release the complete source code for the application that is built on MySQL. You do not need to release the source code for components that are generally installed on the operating system on which your application runs, such as system header files or libraries.

        * Free use for those who never copy, modify or distribute. As long as you never distribute the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL license or not.

        * You are allowed to modify MySQL Software source code any way you like as long as the distributed derivative work is licensed under the GPL as well.

        * You are allowed to copy MySQL binaries and source code, but when you do so, the copies will fall under the GPL license.

        * Optional GPL License Exception for PHP. As a special exception, MySQL AB gives permission to distribute derivative works that are formed with GPL-licensed MySQL software and with software licensed under version 3.0 of the PHP license. You must obey the GNU General Public License in all respects for all of the code used other than code licensed under version 3.0 of the PHP license.

        * FLOSS License Exception. We have created a license exception which enables Free/Libre and Open Source software ("FLOSS") to be able to include the GPL-licensed MySQL client libraries despite the fact that not all open source licenses are compatible with the GPL (this includes the PHP license version 3.0). Read more about the FLOSS License Exception.

Considering the new license and still lacking features, there is little reason to use MySQL. Postgres has "all that anda bag of potato chips."

Re:Examine t he license carefully!!

[tomstdenis]

* You are allowed to copy MySQL binaries and source code, but when you do so, the copies will fall under the GPL license.

Loophole. So take their code, release it under GPL. Boom instant open source project.

Tom

Re:Examine t he license carefully!!

[Tony Hoyle]

The license exception is meaningless.

Mysql is GPL.. you link it with an LGPL library & distrubute.. OK the license exception appears to allow that.

Now someone links the LGPL library with a closed source application. The LGPL allows that. However the GPL in MySql does not.

Only two interpretations are then possible:

1. The whole package must be GPL - the license exception is meaningless.
2. The license exception holds.. the GPL in Mysql is meaningless.

Re:Examine t he license carefully!!

[Scarblac]

In short:

• If you don't distribute MySQL, you're under no restriction whatsoever
• If you do want to distribute, MySQL is GPL
• Besides, MySQL goes out of their way to give you even more freedom to distribute it if you want to bundle it with stuff that has one of several incompatible licenses

Sounds good to me!

Re:Examine t he license carefully!!

[MikeBabcock]

You're right and wrong at the same time.

Its not that they've restricted the GPL (this doesn't work), its that they relicensed the libraries from LGPL to GPL so that linking against them would cause your program to be GPL'd.

In the "old days" you could link your proprietary app against the LGPL'd version of the MySQL client and be able to access your database server. Now, since the client is GPL'd, you can't do this.

I had many discussions on the MySQL users mailing list about the legal restrictions on this, including whether code released under PHP,Python,PERL using their respective 3rd party connectors (which each linked to the MySQL client) needed to be GPL'd. I was told yes.

Re:Examine t he license carefully!!

[T-Ranger]

Under the Open Source License, you must release the complete source code for the application that is built on MySQL

Well, I suppose that is a question of semantics. I would say that if you take MySQL had hack it, relese it as BobSQL, then this applies - BobSQL is built with MySQL. If you write a recepie organizer that uses MySQL, then you need to do nothing.

Read more about the FLOSS License Exception.

What is the problem? MySQL has a client library, which is released under the GPL. Generaly, if you want to use and distribute MySQL client libraries, you also need to release whaterver is yours as GPL. If its commercial, you can always buy a license from MySQL AB. For other OSS project this may be unacceptable - and for PHP it was, for example. So provided that these other OSS projects conform to specific guidelines, they are free to distribute the client libs.

You always, always, have the option of using MySQL in a non OSS enviroment. Buy a license.

I cant see how their license plan could be any more friendly, unless they switch to a BSD style license. Want to just use it? Knock yourself out. Want to hack on it and continue to release it under the GPL? Knock yourself out. Want to release it with another, non-GPL, OSS license? Knock yourself out. Want to not release the source? Buy a license.

Re:Examine t he license carefully!!

[OneFix at Work]

The key paragraph here is this one:

* Free use for those who never copy, modify or distribute. As long as you never distribute the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL license or not.

So, unless you actually distribute/modify/copy MySQL, then you are free to do whatever you want with the database.

This is actually why some of the newer Linux distros don't include a version of MySQL with the OS...it's offered as a seperate RPM/DEB/PKG/etc...

Nothing has changed...this doesn't mean that you can't write an app that uses MySQL and release it under a LGPL/BSD/Proprietary license...as long as you aren't modifying the code in MySQL, then you're fine.

Postgres may have a few things going for it over MySQL, but license is not really one of em...

Re:Examine t he license carefully!!

[ajs]

It is worth noting that this "highly restrictive license" that they are using is called the "GPL". What the linked page does is just to set some conditions under which you are free of the GPL's requirements for software that incorporates their code.

Of course, all of the usual provisos stand: the GPL is a license, not an EULA, so you don't have to agree to be bound by its terms to *use* the software (the GPL is quite clear on this).

All of these issues exist for ANY GPL-covered library code, and all MySQL is doing is playing by the rules of the GPL in exactly the way people like RMS have requested (this is why the LGPL is backronymed into meaning "LESSER Gernal Public License").

It should also be pointed out that in a language like, say Perl, where code is distributed as text, not as a pre-compiled representation, the claim of tainting can really only be applied to the AST, and not the text which generated it. Should that AST be used to generate stored code (e.g. compiled machine code or a bytecode representation) then that code might be tainted (that's deep laywer territory, so I'm not qualified to say one way or the other).

Re:Examine t he license carefully!!

[ajs]

"So take their code, release it under GPL"

They already do this. That's the problem with this whole license foolishness is that everyone has somehow gotten the impression that MySQL isn't distributing GPLed code. What they are doing is pointing out the restrictions that the GPL places on users of library code (which the authors of the GPL were so strongly aware of that they created the "Library General Public License" with more permissive terms, but later decided that they didn't like that and re-named it the "Lesser General Public License"). What the FSF now encourages users of the GPL to do is exactly what MySQL is doing: distribute under the GPL.

The only difference is that MySQL adds in some loopholes to get around the GPL where it would hinder open source projects with GPL-incompatible (but still open source) licenses. If those projects use GPL-only (e.g. "downstream") copies of MySQL, then they are fully bound by its terms. If they use the version provided by MySQL, then they are granted these exceptions.

Re:Examine t he license carefully!!

[Britz]

Looks like a pretty good explanation of the GPL to me. Maybe MySQL put it in for people that don't understand the GPL. Though the make a nice exception for PHP. That sounds useful to me. I always thought that MySQL 3.x was also GPL'd.

LOL

To the moderators that modded parent up: Please reread the GPL. Thank You

Re:Examine t he license carefully!!

[jadavis]

If you do want to distribute, MySQL is GPL

The problem is that the client library is GPL, not LGPL as one might expect.

That means that any application that you distribute that can access a MySQL database must be linked against the MySQL library, which is GPL, forcing your application to be GPL.

Most people don't consider adding MySQL support to their application to be "distributing MySQL".

Re:Examine t he license carefully!!

[jadavis]

You contradicted yourself. First you say:

If you write a recepie organizer that uses MySQL, then you need to do nothing.

Then you say:

Generaly, if you want to use and distribute MySQL client libraries, you also need to release whaterver is yours as GPL.

If you write an application that merely uses MySQL, you don't have the ability to distribute that application without the source.

Re:Examine t he license carefully!!

[martenmickos]

Yes, please examine the licence carefully, and you will notice that MySQL is friendly to all major open source licences.
 
Thanks to the GPL and our own FOSS Exception, you can mix and match MySQL with other open source software even when the licence texts would otherwise be legally incompatible.
 
The only time you need to be aware of our licensing is when you blend some closed source code into the FOSS stew. And for those situations we can offer a commercial licence.
 
I think it is fair to say that MySQL AB has listened carefully to the feedback from the community and made adjustments to licensing and other practices with the aim to promote the freedom of software. Would you agree? I am keen to hear your viewpoints.
 
Marten Mickos, CEO, MySQL AB
 
P.S. FOSS = Free and Open Source Software

Re:Examine t he license carefully!!

[electroniceric]

You italicized the right word.

I recall reading a while back in MySQL license FAQ that "distribute" includes distributing within an organization as well as outside of it. And since any database application is by nature designed to be used by multiple users, the "don't distribute" basically means "fool around with".

The bottom line is that MySQL's licensing policy is, by design, GPL/OSS your code or pay us. Which is a perfectly valid stance to take, but that also means that for anyone developing in a commercial context where retaing rights to and control of your code has to be on the table, at least at the outset, MySQL is competing on price and features, and that's a whole different ball of wax. It also means that you have to be careful with any apps built on MySQL, as customizations to those apps may incur a requirement to buy a MySQL license.

Let's just be clear that that there's not all that much freedom: any non-GPL (or similar OSS) use is pay for play. Which is an awful lot like most other DB vendors out there.

Re:Examine t he license carefully!!

[Haeleth]

That means that any application that you distribute that can access a MySQL database must be linked against the MySQL library, which is GPL, forcing your application to be GPL.

No it doesn't. The FLOSS License Exemption means that your application is not forced to be GPL if it uses any of 20 of the most popular free software licenses. The exempt licenses include the LGPL, the MIT and BSD licenses, the Mozilla license, the licenses for Perl, PHP, and Python... and the list goes on. In other words, the vast majority of free or open source software is able to link to the MySQL client library without being forced to change its license to the GPL.

This is a significant relaxation of the regular GPL terms. It even makes explicit allowances for users of the BSD license, the group that traditionally dislikes GPL software the most.

The only people who can complain about the MySQL licensing policy are freeloaders who want to benefit from free software without giving anything back to the developers or the community. You will, I trust, forgive me if I don't weep for such people.

Re:Examine t he license carefully!!

[stienman]

You don't have to use the client library - it's just an easier interface than the port on the mysql server.

I imagine that if this were truly a problem, someone would make an lgpl client library. It may not be trivial, but it's certianly not difficult. Tedious, perhaps.

-Adam

Re:Examine t he license carefully!!

[poot_rootbeer]

I cant see how their license plan could be any more friendly

That there's some debate over what MySQL's licensing actually allows demonstrates that there is room for improvement.

unless they switch to a BSD style license.

Which is what PostgreSQL is released under. Why deal with MySQL and worry about whether you've interpreted the rules correctly, when you can choose PG instead and know you're free and clear from the outset?

Re:Examine t he license carefully!!

[ChrisJones]

The license is stupid, all you need to do is go via something like ODBC and you can ignore what mysql say because at that point the GPL linking clause ceases to apply.
IANAL :)

Re:Examine t he license carefully!!

[Pastis]

If you write a driver, then port it to Linux using the same code, you're not obliged to license that driver to the GPL. Because your driver is not a derived application. Dixit Linus.

I would guess that if you write your application first, then port it to mysql, even if you link to it, you're not obliged to open up all your application. Just your database specific bridge.

Consult your lawyers anyway.

Re:Examine t he license carefully!!

[njyoder]

You act as if they're being generous or something. Most, if not all, of those licenses are already GPL compatible, so they could be combined with any GPL code anyway. So all they're doing is saying "hey, we're making a special 'exception' to allow you to do what you could already do wanyway."

So yes, the grandparent is right, you are forced to release it under a GPL license. All BSD/MIT/LGPL/whatever code can be safely licensed under the GPL regardless of any special exception.

You're also missing the point too, that anthing that is not under an open source license can't use those libraries, which is ridiculous. I don't even know why they bother doing it, since they're forcing people to right libraries under more lax licenses, which only delays the matter.

The only people who can complain about the MySQL licensing policy are freeloaders who want to benefit from free software without giving anything back to the developers or the community. You will, I trust, forgive me if I don't weep for such people.

Congratulations, you just described the vast majority of OSS software users. Most of them don't have the expertise to give anything back and most that do, give little or nothing. I'm betting that you yourself have given nothing back. Save your self-righteous indignation for someone else.

This goes against what the GPL was created for. The GPL was created to allow certain software to be 'free', which is what GPL'ing the MySQL daemon does. GPL'ing the library, however, is just being a control freak. The LGPL was created for the purpose of licensing unoriginal libraries that provide trivial or standard functionality (e.g. libc). If your philosophy were followed, things like libc would be GPL'ed and practically no one would use OSS, because they'd be forced to open source all their software just by the mere act of compiling it for an OSS platform.

Re:Examine t he license carefully!!

[jadavis]

By default under copyright law, you have absolutely no right to distribute the MySQL client library.

MySQL grants you this privilege under the condition that the derivitive work is OSS. Linked executables are generally considered to be "derivitive works", and so is basically anything that is dependent upon the copyrighted work.

Re:Examine t he license carefully!!

[jadavis]

I know it doesn't have to be GPL, but it has to be GPL compatible (which most of those licenses are anyway) or one of the special exceptions (like php).

As for the "freeloaders" comment, let me remind you that you included in that group a commercial software developer who just wants to add MySQL support to their application among (perhaps) other database drivers.

You may consider those people freeloaders, but most developers would be surprised by that and consider it a disadvantage to adding support for MySQL. Consider that most commercial database engines have less restrictive licenses on their client libraries, specifically to allow developers freedom to include support for their database.

It's a bit like saying that anyone who writes a commercial application that can operate on linux is freeloading.

Re:Examine t he license carefully!!

[cortana]

Bollocks. An ODBC connector that used libmysqlclient would be a derivative work of MySQL. Your application, using this connector, would be a derivative work of the connector. Therefore your application must be made available under the GPL as well.

If what you said was correct then there would be no difference between the GPL and the LGPL.

Re:Examine t he license carefully!!

[ChrisJones]

"These requirements apply to the modified work as a whole. If identifiable sections of that work are not derived from the Program, and can be reasonably considered independent and separate works in themselves, then this License, and its terms, do not apply to those sections when you distribute them as separate works."

Perhaps the line isn't where I drew it, but there has to be a line somewhere otherwise every bit of software you run on your PC is deriving from something GPL. What about if I separate mysql from the application via a message passing interface, so there's no actual linking, etc. etc.

Grey areas all, and IANAL so anything I say here is my opinion and you can keep your bollocks :)

Re:Examine t he license carefully!!

[cortana]

Grey areas indeed. :)

Note that there is no mention of 'linking' in the text of the GPL itself--it only speaks about derived works.

At the end of the day, it's up to MySQL's copyright holders to look at your application and decide whether it is a derived work or not. It doesn't matter if you derive from their work by linking to their library, talking to a MySQL server directly, or controlling a robot arm with a magnet on the end that is waved near a network interface...

If you talk to the FSF, they would tell you that practically every program on a GNU/Linux system is a derived work of Glibc. Fortunatly this is allowed because Glibc, and most other library programs, are licensed under the LGPL. The fact that the MySQL client library is licensed under the GPL is the source of this whole discussion. :)

MySQL 3 - MySQL 5

[Scoria]

I certainly have to give them credit for one thing. The MySQL developers have been subjected to some very harsh criticism over the years, but few would accuse them of ignoring it.

It impresses me that they actually seem to be listening.

Re:MySQL 3 - MySQL 5

[Anonymous Coward]

They are only subject to harsh criticism due to their attitute (which may have changed since).

They used to simply shrug off features like transactions and views saying things like "you don't need them", and "you can code around them in your app".

To date, mysql (4.1) does not maintain data integrity anywhere near as completely as real DBMSs do.

Re:MySQL 3 - MySQL 5

[Bulmakau]

Some people are affraid that of others listenning to them ;) MySQL is changing to the better I think. Is it a real database? Real enough for busy ./, isn't it? As someone who used MySQL since 3.*, I can't say I didn't have problems with it, but I did have problems with Oracle as well in the past. MySQL is a good product, whether anyone cares calling it a "real" database or not.

Re:MySQL 3 - MySQL 5

[Overly Critical Guy]

The MySQL developers have been subjected to some very harsh criticism over the years, but few would accuse them of ignoring it.

Then why did they dismiss all the criticism in the MySQL 3 manual, even claiming the features make databases more complex and aren't needed? Some of those features are now in MySQL 4.1 and 5 despite their earlier dismissal by MySQL's developers.

You didn't read my post.

[CyricZ]

Had you read my post, you would have seen that I limited it to open source relational databases. Oracle is not, as of now, an open source piece of software. And I believe their licensing agreement prohibits the disclosure of benchmarking data.

Backups

[ad0le]

The one area that has always annoyed me with postgresql and mysql was the lack of a good backup system. You have to script the entire database to do a backup. Oh vacuuming a database sucks as well!

Re:Backups

[electroniceric]

Having to dump to a script is both a blessing and curse. For any database that dumps to less than the size of a CD, it's actually very portable, and allows you to clean up your database with substantially more ease than a binary format. The curse of course is that you can't just reattach your binary backup and have the database go again. Also, since 8.0, Postgres has had a binary backup format, and has done a thorough job resolving dependencies (a really nuisance in 7.x).

Re:Backups

[Dan Ost]

Oh vacuuming a database sucks as well!

Assuming that you're refering to PostgreSQL, vacuuming
is now a background process that doesn't interfere with
normal database operations. It's not like the old days
when you had to stop using the database to vacuum it.

Re:Backups

[tweek]

pg_autovacuum is optional and CAN have a negative impact on normal performance during large data loads (such as a nightly warehouse loads)

Re:Backups

[jonadab]

> The one area that has always annoyed me with postgresql and mysql was the lack of a
> good backup system.

I've not had any trouble. Here's my system for taking a backup of a MySQL database:

1. Get a read lock on the database. (This allows other processes to continue to _read_
        the database, but if they try to _change_ anything they'll be delayed...)
2. cp -r /var/lib/mysql /backup/mysql
3. Release the read lock. (Steps 1-3 are preformed by a nightly cron job).
4. Arrange for the contents of /backup/mysql to be backed up in the normal way,
        e.g., to tape, across the network, or however.

How long step 2 takes depends, of course, on the size of your database and the performance of your hardware (especially, the drives in question). Use a local drive for /backup to avoid unnecessarily long delays during the read lock; you can then copy it from there over the network in step 4, while the database is not locked. Also, schedule steps 1-3 at a time when database usage, especially writing, is at a minimum. Step 4 can take place whenever.

If you have multiple databases, you can either lock and copy them all at once (easier, better consistency across databases (if that matters, which with a good design it might not)) or individually (shorter lock durations), depending on the needs of your scenario.

Re:Backups

[TheLink]

In most cases the following works:

pg_dump dbname | gzip -c | split -b - 20050927-dbname.gz-

If speed matters more than space, replace gzip with lzop.

Of course there are some dependency probs for some databases (things depending on something else and so can't be created yet), so now seems there's the -Fc switch for pg_dump and pg_restore...

Re:Backups

[shani]

MySQL has a command to do this:

http://dev.mysql.com/doc/mysql/en/mysqldump.html

Quoth the page:

If tables are stored in the InnoDB storage engine, mysqldump provides a way of making an online backup of these (see command below). This backup just needs to acquire a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and lock is released. So if and only if one long updating statement is running when the FLUSH... is issued, the MySQL server may get stalled until that long statement finishes, and then the dump becomes lock-free. So if the MySQL server receives only short (in the sense of "short execution time") updating statements, even if there are plenty of them, the initial lock period should not be noticeable.

shell> mysqldump --all-databases --single-transaction > all_databases.sql

We use this and have never had any problems, but I guess we don't have these long running updates (at least not at 12:01 when our backup runs).

Sure, it's a script. Who cares? We used to backup by locking the database and copying the tables, but when we moved to InnoDB that didn't work any more. I timed it, and the mysqldump is just as fast to save and restore as the binary files when you use the "--tab" option.

Re:Backups

[ctr2sprt]

No you don't. Use a filesystem which supports snapshots, like UFS2, and take a snapshot of the filesystem. Back up the snapshot. You will have a "broken" database backup, but since PostgreSQL does logging and all sorts of other ACID-ish things, it can recover from that just fine. You'll just lose whatever transactions were pending at the time the snapshot was taken. Par for the course: you'd lose them doing pg_dump too.

I think you miss the point

[Fished]

The point is not whether MySQL is a "real" database. Clearly, it is a real database that is capable of doing real work.

The point is that, even in recent versions, MySQL has some serious limitations that other OSS databases (e.g. PostgreSQL) do not suffer from, and no really significant corresponding advantages. MySQL was not designed from the ground up to be many things it is now trying to be--it was not designed to support transactions, it was not designed to support foreign keys, it was not designed to support stored procedures. It was initially conceived as a small, fast database for managing very large datasets in a warehousing sort of role. PostgreSQL, on the other hand, was always conceived of as being a heavier-duty database, and this shows in terms of feature completeness and SQL standard compliance.

Given that the performance differential (which was always overstated) has been overcome, why would you want to go with MySQL only to discover what the latest feature to be missed was? What's the advantage to MySQL?

Re:I think you miss the point

[mytec]

it was not designed to support transactions, it was not designed to support foreign keys

Forgive my ignorance, but if a plug-in design allows for table handlers to be added that support missing feature(s), what is inferior/wrong with that? It would seem MySQL was designed with modularity in mind. InnoDB supports the features you mentioned, and that I quoted, so why is that less desireable than having the table handler builtin?

What's the advantage to MySQL?

For me, the massive community is a big plus. A rich set of tools is another advantage.

Re:I think you miss the point

[should_be_linear]

I can tell you what MySQL advantege over PostgreSQL and Firebird was *for me*:
1) Easy to install in Windows. PostgreSQL is (AFAIK) goof _now_ but was not then. Firebird was/is OK in this area.
2) "Spatial" type/index, Fulltext index. Postgis is available for PostgreSQL but as plugin, not out-of-the-box, fulltext is (AFAIK) beta and not very unicode-reliable. I prefer both things to be integrated as I distribute DB engine with my SW. Firebird miss spatial type (just as MS SQL, for example (!)) so it is useless for me.

One thing where MySQL suck is mingw (gcc) support on windows. If you need to connect from C/C++ in windows, use MS VS, otherwise you wil suffer.

Re:I think you miss the point

[electroniceric]

User community, user community, user community.

MySQL is a great example of how the GPL can motivate the use of a product. Because MySQL was one of the earliest DBMS to go GPL, it got embedded into a huge number of GPL applications, and developed the wide range of users that has made getting answers, documentation, tutorials, discussion, etc much easier than its BSD-licensed counterpart. Same phenomenon as Linux and *BSD. Is Linux "better" than *BSD? Who knows, but it's so much more widely used that it's a lot easier to get into it. This is roughly the same reason Windows people run SQL Server.

I run Postgres here at work, and I think it's a fantastic database. In particular, it's well suited for anything science-like because its whole workings are there to see, from indexing algorithms to writing of aggregates to storage choices. It is reputed to get a bit slow on SELECTS when it has huge amounts of data (MVCC giveth and taketh away), but given how much any database's peformance depends on smart indexing, I'd take individual claims of snailesque performance with a dose of salt.

Overall, MySQL's development is great for OSS in general. If you get started, and it doesn't do what you need, you're likely familiar enough with how OSS works to move to Postgres. It also puts price pressure on everyone, which makes more tools available to more developers (part of the point of OSS in the first place).

Re:I think you miss the point

[pico303]

Dude, what are you doing on Slashdot running Windows? You're going to get yourself killed.

Re:I think you miss the point

[kpharmer]

> InnoDB supports the features you mentioned, and that I quoted, so why is that less desireable than having the table handler builtin?

Because it is core functionality.

Having a separate product responsible for managing io may be one of the reasons that mysql's optimizer is so primitive - and may be a reason why they will struggle to improve it. Note: the optimizer is the component responsible for determination of how your joins will be performed under the covers - mysql is notorious for failing to use indexes it should, using indexes when it shouldn't, never using star-joins, five-way joins that day 10x as long as they would in any other database, etc.

Perhaps it's also why views took forever to implement, and materialized views might take another forever.

It's also perhaps the reason for all the inconsistencies in table creation.

There are also benefits to using innodb - it has undoubtably speed up mysql's development by several years. Still, now it's a boat anchor that should be abandoned.

> For me, the massive community is a big plus. A rich set of tools is another advantage.

yep, although it may be the only advantage that mysql has, it's a huge one.

Re:I think you miss the point

[Cyn]

Your argument has a weak point - there's no reason why you couldn't instead embed a BSD licensed DBMS in a GPL application. It wasn't going GPL that 'empowered' these developers. BSD licensing has no restrictions other than a credit line.

I'm not saying the rest of your points aren't valid - but saying that MySQL is where it is today because it was GPL just doesn't fit. I think it's where it is today because it started out dead simple, and people latched onto that - just like PHP.

Re:I think you miss the point

[Kainaw]

Given that the performance differential (which was always overstated) has been overcome

Do a side-by-side comparison on your own data. For me, the performance differential has not been overcome. In MySQL, my reports take about 10 minutes to run. In Postgres, they take 3-4 days. When I mentioned this to our local Pg-fanboys, they came in, gave Postgres its own dual-processor server, extra ram, changed my column datatypes, made a slew of indexes, and reduced the report time from 3-4 days to 3-4 hours. I agree that optimizing Postgres will speed it up a great deal, but without spending a couple weeks optimizing MySQL, I had an acceptable report generation time.

However, on a separate project, I required features that were not in MySQL. So, I used Postgres. Speed was not an issue. In my opinion, this all has to do with the purpose. In the project I mentioned first, we have a data warehouse - shove junk in and make massive reports on it. In the second, we are working with the data on a daily basis. I am not surprised that one database engine works better for one purpose and another works better for another purpose.

Re:I think you miss the point

[Decibel]

Clearly, it is a real database that is capable of doing real work.

I disagree. A real database wouldn't allow this:

CREATE TABLE t(t tinyint);
INSERT INTO t VALUES(300);
SELECT * FROM t;

You really meant 127 and not 300, right?

Re:I think you miss the point

[Fished]

Just out of curiosity, which table type were you using in MySQL? I.e. MyISAM or InnoDB? If MyISAM, then I think it's fair to observe that you're really comparing apples and oranges.

Re:I think you miss the point

[jadavis]

TSearch2 is a full text search module for PostgreSQL that has been available for a long time. It's stable and not in beta.

What's wrong with modules? They're great. It means that you can upgrade TSearch2 or some other module without waiting for the next release of PostgreSQL. It's not difficult at all to install the modules, just copy some files and run a SQL file.

I think that PostgreSQL making use of it's extensibility by forcing some functionality out into modules was very successful from a technological standpoint, a release process standpoint, and a user's standpoint. However, it certainly was a marketing failure. Now everyone thinks PostgreSQL is missing those features. PostgreSQL has never been known for it's marketing abilities.

Re:I think you miss the point

[Rich0]

I agree that legally I may make my GPL project depend on a BSD-licensed product.

Perhaps suprisingly to you, however, many people would choose not to, given the choice of a GPL competitor. This encourages more widespread use of the GPL. It is the same reason why RMS doesn't use MS Word even though he doesn't have any pressing need to modify its source code.

Some may not care that much about licenses and that is fine. Nobody is putting a gun to anybody's head and forcing them to use mysql when creating a new application.

Now, my one pet-peeve in this area is that there is no linux equivalent of ODBC. If I write a windows app it can be made database-neutral - just use ODBC and it doesn't matter if the datacenter prefers SQL Server, Oracle, or even an FOSS DB. On the other hand, with linux everything is linked to libraries and uses db-specific function calls. Sure, you can modularize your code to make it easier to support multiple DBs, but you're doing all the work of supporting other platforms yourself, and your users are stuck with whatever you're willing to support. Sure SQL itself isn't the most perfect of languages portability-wise with all the vendor-specific extensions, but with ODBC you can generally plug any DB into any app. I really wish that somebody standardized on such a model for linux...

Re:I think you miss the point

[Anonymous Coward]

I think you might want to try learning a little about MySQL 5.0 before you go posting based on outdated knowledge:

Your MySQL connection id is 6 to server version: 5.0.13-rc-standard-log
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> CREATE TABLE t(t tinyint);
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO t VALUES(300);
ERROR 1264 (22003): Out of range value adjusted for column 't' at row 1
mysql> SELECT * FROM t;
Empty set (0.00 sec)

Re:I think you miss the point

[StrawberryFrog]

In MySQL, my reports take about 10 minutes to run. In Postgres, they take 3-4 days

What, the same queries? If not, then how did they differ?

Re:I think you miss the point

[moof1138]

I don't want to start a holy war here, but what is the deal with Postgres performance? I've been sitting here at my freelance gig in front of a 4 way Xeon box running Postgres for about 20 minutes now while it attempts to do a simple select from a single indexed table with only 300 rows. 20 minutes. At home, with MySQL on my Pentium Pro 200 running NT 4, which by all standards should be a lot slower than this rig, the same operation would take about 2 seconds. If that.

In addition, during this select, no other queries are accepted. And everything else on the box has ground to a halt. Even 'top' is straining to keep up.

I won't bore you with the laundry list of other problems that I've encountered while working with Postgres, but suffice it to say there have been many, not the least of which is I've never seen Postgres that has run faster MySQL. MySQL on a 486/66 with 8 megs of ram runs faster than Postgres on a 900 mhz machine at times. From a productivity standpoint, I don't get how people can claim that Postgres is a superior architecture.

Postgres addicts, flame me if you'd like, but I'd rather hear some intelligent reasons why anyone would choose to use Postgres over other faster, cheaper, more stable database servers like MySQL.

Re:I think you miss the point

[cortana]

Bravo sir. :)

Re:I think you miss the point

[robnauta]

Why ? It's a common myth that count(*) causes all records to be read and that count(constant) is faster.

In Oracle a count(1) and count(*) are identical and have identical execution paths. It will use a fast full scan of a unique index to count the number of rows, if it can do this it doesn't read the actual rows.

But then again, maybe it's true for MySQL that it will read all data blocks and ignore indexes if you do a count(*).

Re:I think you miss the point

[cloudmaster]

A real database user that needed to hold a value of 300 would not use a column type that has a range of 0 to 255 or -127 to 127, since they would have read the page on choosing types. I guess *nix isn't a real operating system because it will let you destroy your system by doing things like "rm -rf / path/to/file"? You meant for that space to be there, right? :)

Anyway, from http://dev.mysql.com/doc/mysql/en/numeric-types.ht ml, that will generate a warning if you do an update or a multi-row insert (why not in a single insert, I can't really justify):
When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.
Conversions that occur due to clipping are reported as "warnings" for ALTER TABLE, LOAD DATA INFILE, UPDATE, and multiple-row INSERT statements.

BTW - Postgres isn't a real database, either, in the example of storing 32767+1 in a smallint:
http://www.postgresql.org/docs/7.4/interactive/dat atype.html
Some of the operators and functions (e.g., addition and multiplication) do not perform run-time error-checking in the interests of improving execution speed. On some systems, for example, the numeric operators for some data types may silently cause underflow or overflow.

Re:I think you miss the point

[Matt Perry]

A real database user that needed to hold a value of 300 would not use a column type that has a range of 0 to 255 or -127 to 127, since they would have read the page on choosing types [mysql.com].

You missunderstand his example. It's not that the user needed to hold a value of 300. It's that a value of 300 was attempted to be inserted and was accepted. That value could have been caused by a bug in a program that was submitting the value. In any case, the database should not accept values outside the range of the datatype specified for a column (it should cause an error not a warning).

MySQL 5.0 has a strict mode that will make it operate that way so this argument is going to become a non-issue once MySQL 5 is finalized.

Re:I think you miss the point

[brennz]

I would respond with 2 questions

1. Are you sure your SQL is correct?

2. Are you using a GUI tool to perform queries?

I ask those questions, because various Postgres GUI tools have problems with malformed SQL. Rather than immediately error out, the query will appear to be running, but it won't be. It will continue to run forever basically, until you have an epiphany and realize your malformed SQL caused that initial problem.

These SQL problems are found in various tools, and are not a problem within the database itself.

I'd advise you to check out #postgresql on irc.freenode.net . The Postgres people are great and will help you troubleshoot your problem(s).

Re:I think you miss the point

[Phillup]

A real database user that needed to hold a value of 300 would not use a column type that has a range of 0 to 255 or -127 to 127, since they would have read the page on choosing types.

True.

But, a real programmer may not check the data... expecting instead for the storage system to report an error if it can not be stored.

(whether they should check the data before handing it off for storage is another discussion entirely)

Any storage system should report errors if it can't store your data as requested. Not silently change the data and act like everything worked.

Re:I think you miss the point

[electroniceric]

Of course you can embed a BSD-licensed DB into a GPL project - the BSD license permits that, along with everything else.

The point is this: Postgres has existed much longer than MySQL. Yet as the OSS, and particularly GPL, movement caught on, and a large number of OSS apps were produced, MySQL managed to be included in many of them, and Postgres and Firebird did not. I don't really think all that many people sat down and evaluated Postgres vs. MySQL - they just learned about MySQL, heard that it was GPL (a Good Thing), and built their GPL app on it. Had Postgres been easier to set up in those days, would people have used it? I don't know, and nobody can, but I'd guess that MySQL being GPL helped propel it to prominence when Linux was really taking off, and the rest of its success is largely network effects. Given how many shortcomings it had and has, why else would people have preferred it over a more feature-complete BSD-licensed database?

Re:I think you miss the point

[cloudmaster]

No, I understood the example, and agree that MySQL *really* should generate an error just like the "big boy" databases. My point was that the behavior is documented, and that, therefore, someone developing for either of the popular "free" databases should be keeping that in mind along with the myriad other little subtle differences between all the DBMS options. :) Getting bitten by a well documented behavior does not reflect poorly on the DB, it reflects poorly on the programmer for making assumptions - never mind how nice it'd be if those assumptions were universally true.

Re:I think you miss the point

[eyeye]

A real database user that needed to hold a value of 300 would not use a column type that has a range of 0 to 255 or -127 to 127, since they would have read the

Thank fuck we are all perfect and dont make mistakes. Listen, once your data is dirty you are fucked - you can't fix that data.

Re:I think you miss the point

[Moonwick]

Great. Once MySQL 5.0 is even remotely in a shape where you or I would feel comfortable running it in a production environment, then perhaps your argument will hold water.

Re:I think you miss the point

[ckaminski]

Did you remember to type ; at the end of the statement? :-)

Re:I think you miss the point

[cloudmaster]

Fuck, why the pissing shit didn't I think of that? What about the fucking data? Son of a bitch. And here, the bastard MySQL was behaving just fucking like the damned docs said it would. What a bitch!

Re:I think you miss the point

[PinkPanther]

MySQL 5.0 has a strict mode that will make it operate that way so this argument is going to become a non-issue once MySQL 5 is finalized.

Yes, but if strict mode is not enabled by default, then for (most) intents and purposes it is useless. The vast majority of people using MySQL are using the version installed with their OS or via RPM or whatever, start the server and start building their app. Only when they are working on a subsequent version or when someone else joins the development work do they realize how badly they've written their code.

This is a similar argument made by those who code in "forgiving" languages. They turn to a stricter language and get frustrated that they have to keep going back and fixing all these "stupid errors/warnings"...the ones that indicate their programs are MISBEHAVING, the ones their "smarter" languages would have quietly sequestered for them.

I have a customer migrating from MS-Access to a real database. They don't understand why this "stupid" real database gives errors when their FORWARD-ONLY, READ-ONLY cursor code tries to go back a few rows and update its values. It "works" in that other "product"...

Re:I think you miss the point

[Kent Recal]

Well, I think OP was referring to postgres. count(*) performs really, really bad there.

Re:I think you miss the point

[Cyn]

people chose MySQL for a couple reasons:
1) because PHP started working with it early
2) because it was pretty simple to install and get running, esp. re .debs / .rpms
3) because it is and was a lot looser with what you threw at it, and people were just looking for a dumb database.
4) [no way this list is complete or 100% accurate]

no - this isn't a troll. Read the php-db mailing list sometime, most everyone on it is using MySQL - and every new persons question is something along the lines of "Why does my insert statement fail when I give it this string "This isn't working!" - can you guess the answer? search the archives - hell, browse them, it's the start of every other thread.

    A lot of the quick and dirty database apps that get churned out constantly are written in php and mysql (there are plenty of good well thought out products written for these as well) - because both allow nice fast rapid development, and MySQL returns very few errors. If you toss a null at a column that doesn't allow it, it'll just default it out to 0 or a blank string. In an application that doesn't require loads of careful checking, and had to be done yesterday, it was a blessing.

I'll be interested to see if people don't keep the last MySQL 4.1 around for eternity, because they can do such sloppiness safely. It's for "performance". Yeah.

Re:I think you miss the point

[imroy]

In MySQL, my reports take about 10 minutes to run. In Postgres, they take 3-4 days

Any bet you were doing what most MySQL coders do: making tonnes of little, simple queries. MySQL is just great at that sort of workload. It's what it was originally designed for, being essentially a simple SQL interface to the filesystem. Now, I bet you'd get much better performance out of PostgreSQL if you sat down and spent a day (or however long) to rewrite your client logic and queries so that you're making only a few complex queries that spit out everything at once. That's what indexes and the plan optimizer are for. PostgreSQL gives much better performance when you let it create an optimal plan and use appropriate indexes.

Re:I think you miss the point

[Mr. Fazer]

True. Well MySQL 5 does have the stored procedure covered to save its nose. Is it still inattentive to foreign keys ? A real database would never ignore that !

Please stop the MySQL Bashing...

[Anonymous Coward]

Before all the MySQL bashing starts can we please stop and have an intelligent conversation? The bottom line is that MySQL works great! For all those people who say .. "ya its a kids toy" well look at some of the sites and companies that are using mysql.. "Friendster" , "The Friendfinder network" , "Yahoo!" .. These sites each have millions of active members and are running just fine. Where else can you load up a 50+ database cluster and not have to shell out a fortune on licensing fees? All the developer tools are great!!

Re:Please stop the MySQL Bashing...

[ad0le]

Where else can you load up a 50+ database cluster and not have to shell out a fortune on licensing fees?

PostgreSQL

Re:Please stop the MySQL Bashing...

[ad0le]

Ohhhh... BTW, PostgreSQL is the database behind the .org TLD servers as well.

Re:Please stop the MySQL Bashing...

[LLuthor]

Where else can you load up a 50+ database cluster and not have to shell out a fortune on licensing fees?

PostgreSQL?

Re:Please stop the MySQL Bashing...

[Naikrovek]

Yahoo! does not use MySQL for anything in production, at least they didn't when I was there. Jeremy Z. does work there, so that might have changed, but if you're trying to say that Yahoo! uses MySQL to store user data, or anything associated with a user profile you're dead wrong. UserDataBuckets.

Re:Please stop the MySQL Bashing...

[generalpf]

The MySQL guru at Yahoo! -- Jeremy Zawodny -- wrote an O'Reilly book on the different hacks and scrapes they needed to make MySQL perform properly. It's full of anecdotes of MySQL brainf**kery and whatnot. If you consider Yahoo! using MySQL to be an endorsement, try reading High Performance MySQL.

Re:Please stop the MySQL Bashing...

[Overly Critical Guy]

I'll respond and go so far as to ask, why all the obsessive defense of MySQL from fanboys? It's a indisputable fact that MySQL has less features than alternatives like PostgreSQL, it has data truncation issues, NULL-handling issues, and more.

And yet, there are people who defend MySQL to the bitter end when there are already free, superior alternatives like PostgreSQL. I just don't get it. I think these people are just used to the idea of MySQL because it was on their cheap PHP webhosting service, and it's all they learned.

Re:Please stop the MySQL Bashing...

[anomalous cohort]

I am looking at a plone deployment. The requirements call for a relational back-end. Although the docs claim that plone can play with either, I'll be picking MySql over postgresql because I could never get the postgresql to plone adaptor to work.

Re:Please stop the MySQL Bashing...

[poot_rootbeer]

"Friendster" [...] These sites each have millions of active members and are running just fine.

Then why does it take 20 seconds or more for my Friendster homepage to load? And that's how it is today, nevermind how it was 2 years ago when the site was at its peak of popularity, and running as a poorly-optimized JSP app.

(Sure, not all of it can be blamed on their choice of RDBMS... their web servers for images in particular seems to be ill-prepared for the traffic it gets. But still... that's your best example?)

Still waiting for a programmable GUI

[bogaboga]

I am still waiting for a database system/program that will have a GUI as programmable as that of MS-Access. I have not looked at this latest release, but all the GUIs I have looked at up to now do not cut it! The talk of PHPMyAdmin, Navicat, MySQL Control Center and the like, reaffirms my conviction of un-seriousness on the matter of putting business and common logic into database applications. Putting this kind of logic to applications is much more easier in Access but that belongs to Windows - sadly.

Re:Still waiting for a programmable GUI

[wheelbarrow]

You are still waiting? Why aren't you acting? Write a programmable gui yourself. If is such a great idea then you should be able to make a great case for other programmers to make a free and voluntary choice to help you.

Re:Still waiting for a programmable GUI

[TheRaven64]

Write a specification document. Seriously. I haven't used Access since I was at school, and I honestly have no idea what it does that is any use. I suspect a lot of the people who are capable and willing to program an Access-replacement are in a similar position. Tell the community what you need. Just saying `nothing exists that fits my undisclosed requirements' does not help anyone. Please send me the URL once you have put the requirements online.

Re:Still waiting for a programmable GUI

[xappax]

One of the main concepts behind MySQL and other SQL database systems is that the data storage and retrieval is abstracted from the front end. It's certainly valid to complain about a lack of mySQL client applications (which should be addressed to the developers of SQL client apps) but I don't think it reflects on the quality of the database itself. I think it's safe to say that since the vast majority of people who deal directly with databases are programmers or technical users of one sort or another, stability and functionality is rightly emphasised over user-friendliness.

Re:Still waiting for a programmable GUI

[Pfhreakaz0id]

everyone will jump on you, but it's an easy to use gui that a business person can whip up an app in, and a low level programmer can program the trickier bits in VBA. If you want it on top of a "real" database, it's pretty easy with new Access versions to have the back end be SQL Server instead of access. Theoretically, I think you could use another database with ODBC?

Re:Still waiting for a programmable GUI

[mrtom852]

Like kexi

(Disclaimer: I've never used either so I may be way off)

Re:Still waiting for a programmable GUI

[Craig Ringer]

PHPMyAdmin and MySQL Control Center are database management GUIs. I don't think they're supposed to be Access-like apps, and I'm personally happy about that, since when doing DB admin I don't want a tool that tries to second guess me, hides things from me, etc.

One Access-like app is OpenOffice.org 2.0 Base (Go test it and report issues before the final OpenOffice.org 2.0 release!). It's extremely new and it's rough around the edges, but it looks decent. It's an Access clone to the point where I'm surprised Microsoft aren't suing about stolen icons.

If you're looking for something in the same vein, but not seeking a direct Access rip-off, then Rekall may be an option worth investigating. Both it an OO.o are cross-platform.

Do be aware that tools like Access encourage you to do too much client-side. It's important to consider using in-database stored procedures and (often updateable) views where appropriate for efficiency and clean structure. You can then use an Access-like app to provide a user-interface to that higher level database interface, instead of just poking around in the raw tables.

Re:Still waiting for a programmable GUI

[mgv]

MS-ACCESS is the worst DB app EVER!!!

Actually, MS-EXCEL is probably the worst database.

Michael

*** Yes, I am aware of the difference ***

Re:Still waiting for a programmable GUI

[aug24]

I am a database architect, and I will not employ you based on the above. Closely tied GUI and storage is a bad, bad, bad idea.

Storage should have a *business* API implemented for it, and a separate GUI which generates calls to those APIs. All business logic should reside in the storage layer.

You are doing it wrong. Worse, you're doing it in Access, heh heh. I get paid quite well for getting rid of Access databases and re-implementing them in proper API form. Then the business puts the front end of its choice over it - JSPs, web services, messaging systems, whatever.

Justin.

Re:Still waiting for a programmable GUI

[Billly Gates]

Access is really good at writing quick customizable programs with a great UI. I use it as a gui builder as much as a simple database.

  For example I helped a few guys at a small business who were friends of the family last summer. They owned a river rafting company on the Colorado River and had no inventory management at their wharehouse. I could have used mysql and wrote some sort of UI and hacked some ugly program together but I had to do it in a month and a half. I chose access and they loved the new inventory program. It had a nice gui and it was easy to use and manage for their needs and more importantly I did it within a month (I am not a programmer I may mention). If they needed to upgrade later (doubt it... its small business) they could move it to a MS-SQL server on low end intel hardare.

Its like VB which is fine for simple uses that need to be produced quickly. I agree access replacements would be nice. Perhaps a gui builder with rich database controls and scripting support might be in order. However Access is pretty advanced today.

Re:Still waiting for a programmable GUI

[Pionar]

yep. Access is great for small projects like that. The thing I like most about it is its scriptability with VBA.

Re:Still waiting for a programmable GUI

[wizzardfuzzball]

Try openoffice.org (beta 2 is getting there) or Access itself with a ODBC driver

Re:Still waiting for a programmable GUI

[TheRaven64]

I agree access replacements would be nice. Perhaps a gui builder with rich database controls and scripting support might be in order.

I think you've just described GORM. It allows you to draw graphical components, and graphically connect Objective-C objects together. It is also possible to add SmallTalk scripts directly in GORM to tie the components together. Oh, and for the database access side, you can use GDL2 which provides an Object-relational mapping - a system for mapping Objective-C objects to rows in a relational database in a database- and schema-independent way. If you want something a little easier to use than GDL2, there is a CoreData implementation for GNUstep in course of construction which will include a graphical object-relational modeller. Oh, and the result will build happily on *NIX and Windows, and with a little tweaking on OS X.

Re:Still waiting for a programmable GUI

[Forbman]

Do be aware that tools like Access and php, and ASP and python and java... encourage you to do too much client-side. It's important to consider using in-database stored procedures and (often updateable) views where appropriate for efficiency and clean structure. You can then use an Access-like app to provide a user-interface to that higher level database interface, instead of just poking around in the raw tables.

Wow. This really should get an "application developer knows best" vs "DBA knows best" flamefest.

Re:Still waiting for a programmable GUI

[Forbman]

Still, MS couldn't figure out a way in over 14 years to either add VBA events to table and query objects (i.e., triggers), or make linked tables more aware of backend database events/exceptions so that they cound be handled in the client side if need be (i.e., non-updatable view linked via ODBC is "updated", exception passed back to Access, and developer can then write essentially an INSTEAD OF trigger to put all the data in the right places)?

The best (but least popular) DB app GUI builder for Windows has been Delphi. Its database controls (TDataset) meets or exceeds the database "awareness" of Access, especially when compared to VB. While not exactly a complete MVC environment, TDataModule can let you encapsulate a LOT of business logic in a nice container. Etc.

Re:Still waiting for a programmable GUI

[ArtStone]

Isn't the obvious answer here to use myODBC? (now called MySQL Connector/ODBC).

You get the ability to access your mySQL tables and use the easy "point and click" forms drawing of Microsoft Access.

A few things to know if you head down this path:

The first timestamp field in each table is grabbed to be the authoritative "who updated the record first" indicator for ODBC when update collisions happen. mySQL will overwrite the contents of that field whenever ODBC was used to update the record. Consider creating an ODBC timestamp field at the front of every table just for this purpose.

ODBC timestamps only resolve to the 1 second interval - therefore making ODBC inappropriate for critical or frequently updated tables - unless you want to layer additional collision detection logic into the application (update counters, etc..). Access does not hold row locks while you are browsing (and I'm sure you would not like the outcome if it did)

Be careful with using myODBC to update tables with mySQL replication in effect. I had issues with the slave getting invalid updates and discarding them because the myODBC constructed update statement created a very complex where clause (including the ODBC timestamp field) that resulted in record not found conditions.

The Find Record function is dumbed down by ODBC because of bad choices of MS Access defaults... if you want to find a record in the mySQL database, turn off Search Fields as Formatted, and use Match on Whole Field, not Start of Field. Otherwise, it will do a "one record at a time" ODBC scan to try to find the matching record on the client side, rather than constructing an effective Server Side where clause.

Create the tables in mySQL and import the definition into Access. If you define the tables in Access, and then Export to mySQL, you'll find more problems with data type conflicts and indexing issues.

But in general, it has been a useful solution for me. Your mileage will vary, especially if you hate Microsoft products and want to prove they can't work.

Re:SCO

[MindStalker]

Oh come on. SCO licensed MYSQL to include it in their UNIX distribution. This isn't exactly some sort of secret plot.

What about those [MySQL] gotchas?

[bogaboga]

They have done very little about these MySQL gotchas! They should have eliminated most of them first. You can still read them here: http://sql-info.de/mysql/gotchas.html.

Re:What about those [MySQL] gotchas?

[sql-gotcha]

That list addresses issues in MySQL 4.1 and earlier. Unfortunately I haven't had time to try out any of the MySQL 5.0 pre-release versions, so I'm not in a position to provided any kind of qualified commentary. However my impression is that many of the "gotchas" in the list have been addressed.

Re:What about those [MySQL] gotchas?

[minginqunt]

No true.

Those gotchas all (mostly) go away if you run MySQL 5.0 in strict mode. Compatibility mode is provided for 4.1 and back-asswards behaviour if you need it.

See: http://dev.mysql.com/doc/mysql/en/server-sql-mode. html

Martin

Re:What about those [MySQL] gotchas?

[Furry Ice]

Wow, thanks for the link! It never occurred to me that some of these gotchas were design decisions based on the lack of transactions:

TRADITIONAL

Make MySQL behave like a "traditional" SQL database system. A simple description of this mode is "give an error instead of a warning" when inserting an incorrect value into a column. Note: The INSERT/UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a non-transactional storage engine, because data changes made prior to the error are not be rolled back, resulting in a "partially done" update. (New in MySQL 5.0.2)

Of course, as the MySQL line has always been to manage transactions in code, I don't see why they didn't just raise an error and let the application deal with the manual rollback. Who would ever want invalid data to be inserted?

Re:What about those [MySQL] gotchas?

[robnauta]

Of course, as the MySQL line has always been to manage transactions in code, I don't see why they didn't just raise an error and let the application deal with the manual rollback. Who would ever want invalid data to be inserted?

Manual rollback ?The idea is that if you do the following:
insert into mytable values (100);
insert into mytable values (200);
insert into mytable values (300);
rollback;
rolls back ALL changes. This is in Oracle, in Sybase or SQLServer you would start with a 'begin transaction'.
Imagine these inserts are in an upgrade script for an application. Now the script has to either errorcheck and commit after each row, or have nested if-then's. If the second statement fails, delete 100, if the third one fails delete 100 and 200. And you have to do all that in SQL ? No thanks. Give me a REAL database.

Re:What about those [MySQL] gotchas?

[Matt Perry]

Those gotchas all (mostly) go away if you run MySQL 5.0 in strict mode.

Here's hoping that distros ship MySQL 5 with strict mode enabled.

SlashDot

[Chaotic Spyder]

If it's good enough for Slashdot.....

Server usage not shipping applications...

[Anonymous Coward]

The license you describe doesn't say anything about server usage.
Clearly, the main-use for MySQL lies in server-client architecture. As long as you do not ship your self-created web-application, I see no reason you should be suspicious about MySQL.

Re:Examine the license carefully!!

[Chris_Jefferson]

No, their license is giving you more than the GPL. One of the GPL's biggest problems is it doesn't play friendly with many other OSS licences, so they are putting in a special exception to allow you to link to more things (such as things under the PHP licence). Of course, as you say, you can always have the GPL if you don't like their licence.

Just won an iPod Nano Nano

[patrikG]

From http://dev.mysql.com/mysql_5_contest.html

Weekly Prize (8 winners; 1 per week) This is your opportunity to win: * An Apple iPod nano To be eligible to win the Weekly Prize, you must: * File reproducible bugs at http://bugs.mysql.com/

Reproduc a ble. That's a reproducable bug. iPod Nano with scratchable screen: here I come.

Re:Having worked with oracle 10i for the last year

[kpharmer]

> And Oracle 7 for the two years before -- in a not-so-large database -- I think there is not much to fear...

Actually, no: many databases these days are now supporting various types of logs - in which you've got tables with tens of millions of rows. Oracle and DB2 have the following in place to support massive tables:
1. query parallelism - that provides linear performance improvements up to 4 cpus or so
2. data partitioning - that allows the database to just scan data needed, rather than entire table when indexes aren't appropriate (b-tree indexes only work for around 1-3% of data)
3. materialized views - in which views actually hold data - and this data is kept up to date by the database server. Often used for summary tables.
3. query rewrite - in which your queries are automatically re-written to apply to a summary table (see materialized view) if one exists.
4. clustering - in which data spans multiple servers, and all servers work together on your query.
5. smart optimizer - intelligent score-based optimization responsible for determination of best access paths for your queries.

With the above features db2 or oracle can drive 40x the performance of mysql or postgresql in a reporting application (or transaction one with a few large scan-oriented tables) on identical hardware (say a 4-way SMP). If you didn't see an impact from these features then either you have one of the fairly rare apps that can't benefit, or you should revist the design.

Don't get me wrong - I really like postgresql. But neither it nor mysql is even in the ballpark for performance on db2 or oracle. Nor is the price much different - db2 is only around $700 for 4/5 of the above features on a 2-way smp vs $500 for mysql. Eventually mysql & postgresql will support these features. But it'll probably be five years before they are working well.

And despite the fact that I feel postgres...

[MrBandersnatch]

is often the better database solution, I'll still be using MySQL. Why? Well a quick search on cwjobs shows 188 jobs requiring MySQL experience vs 7 for postgres!! Its a real shame but having used the best tool (C++ Builder) for my last job and then being unemployed for @2 years because people wanted either VC++ or Unix based C++ experience; I *WONT* be making the same mistake again!

Anyways, that said, Ive already played about with 5.011 and apart from the yukky syntax one has to use to support transactions it seems quite stable. Its might have taken a long time for them to finally make it a "real" database product but it seems good enough for small databases.

One of my next jobs is to test it with 10 million + records and see how it performs though so my assessment may be premature...

Re:And despite the fact that I feel postgres...

[Decibel]

Actually, there is quite a demand for people with PostgreSQL experience on their resume, and it's set to really explode with all the commercial companies pushing PostgreSQL. I don't keep exact track of recruiting emails I get, but it's been at least one a month for the past 12-18 months. Heck, I've had at least 2 if not 3 emails this month.

Re:And despite the fact that I feel postgres...

[kbielefe]

Sounds like you might be marketing yourself wrong. In my experience, specific experience with specific apps is a lot less important than understanding the genre as a whole and an ability to adapt. There are still some narrow-minded employers out there, but those are people I generally don't want to work for. Remember the year after Java came out, when job postings were asking for 5 years experience?

I would rather hire someone who, when asked about their MySQL experience in an interview, said something like, "I've worked extensively with postgres, which shares most of the features of MySQL. I've heard good things about MySQL, so I have evaluated it and am sure it would take me no time to be productive. It seems stable, but I've found MySQL's syntax for supporting transactions to be unweildy, so I've chosen postgres whenever possible."

This would show me that you know what you are talking about, can easily adapt, and don't blindly follow a suboptimal decision. Contrast with "Everyone was requiring mysql, so I learned it." In other words, I would rather hire someone who had a good reason for making an unpopular choice, over someone who had no reason for making a popular choice.

Re:And despite the fact that I feel postgres...

[Billly Gates]

Unfortunately HR is the one who filters candidates and they require a cs degree, knowledge of differential equations, and a whole bunch of buzzwords with experience for every platform/language under the sun.

If I were an IT manager I would go around HR and write the ad myself and do the filtering.

Business logic does not belong in Access

[Rufosx]

Access is an interesting app but has no business use whatsoever. I have been involved in multiple projects to rip apart poorly written applications in Access to move the data and the logic to where it belongs : a real multi-user relational database.

Writing a business application in Access is like writing it in Excel. The logic is poorly located and hard to share, the app is single-user, and the interface is crap.

What you want is a framework for quickly building apps on top of a real database. And thats another debate.

Re:Business logic does not belong in Access

[EastCoastSurfer]

I agree that Access isn't an enterprise RDBMS.

Access is an interesting app but has no business use whatsoever.

This part I have issue with. Access and Excel both are great prototyping applications. Business users can click around enough to start to get an idea of the exact problem.

I have been involved in multiple projects to rip apart poorly written applications in Access to move the data and the logic to where it belongs

Exactly. Some business person had a rough idea of something they needed and prototyped it out. If they hadn't used Access up front they probably still wouldn't have an idea of the exact problem they needed to solve (and wasted your time re-implementing an application over and over). To say every application needs to go straight to an RDBMS is naive. Unless you're extremely lucky and always working with perfect, unchanging requirements, Access and Excel are great tools to help figure things out up front.

Re:Business logic does not belong in Access

[adam.skinner]

Add to this the fact that Access is appropriate for small projects, ad-hoc data access, and provides an easy interface for those not familiar with SQL syntax (particularly joins and aggregate expressions).

Having mentioned the upside, to say that a tool like access should be bundled or customized for each database strikes me as naive. Most of the real work happens in a text editor; if you're asking for access, you're asking for a simple interface to make simple queries. And that's not what these ever more robust and feature rich databases are "meant" to do.

Re:Business logic does not belong in Access

[EastCoastSurfer]

Having mentioned the upside, to say that a tool like access should be bundled or customized for each database strikes me as naive.

I wasn't saying that at all. The GPP(I think), asked if there was an OSS tool similar to Access. Then someone flamed Access, and I just wanted to point out that Access (and Excel) had their uses. I don't think mysql needs to have an Access front end, but it might be the place to start when trying to build an OSS Access replacement.

if you're asking for access, you're asking for a simple interface to make simple queries

It's been awhile since I've written an access application, but I remember it being much more than just a query front end. When I used it things like forms (VBish type stuff) and reports were prominent and easy to use. Someone with little programming knowledge could cobble something together and give you an idea of what they were thinking.

Re:Business logic does not belong in Access

[OAB]

NO NO NO NO NO.

Users don't 'prototype' applications in Access (or Excel for that matter), they 'develop' them (for want of a better word). Then five years later after they have left the company some poor sod from IT (that would be me) has to fix a broken by (lack of) design mess. The idea that users get a handle on the problem by playing with Access is like saying I learnt surgery by loosing a leg.

Re:Business logic does not belong in Access

[DrXym]

That isn't correct. I've been involved with two financial trading applications that used an .mdb file via JET / MDAC to store accounts, positions and other data. For what it was in its heyday, it was a great database. It may suck compare to modern servers, but it works well within its limitations.

Nowadays Win32 apps that require a data engine have a choice of two - trusty .mdb or MSDE. The latter ships as a 40Mb download, but it is to all intents and purposes MS SQL Server with some file and usage restrictions that mostly don't matter on the desktop. It's a smart move by Microsoft since it means SQL Server is shoe-in if an app's needs grow.

I'm quite a fan of PostgresSQL and I reckon with a little work it could be packaged like MSDE - a database engine without any of the other junk. The Win32 installer is pretty nice for v8.0. A redistributable would probably weight in at 5mb. It would be quite attractive for the same reasons MSDE is - use the PG engine today, upgrade to PGSQL (or even Oracle) running on Win32 / Linux tomorrow.

Re:Business logic does not belong in Access

[afidel]

Access applications can be used as frontends to any real RDMS which has an ODBC connector available (all of them). Access might not make the world's best code, but it does allow people who are not professional programmers access to the data in their database.

Not to flame

[gregarican]

I know that there have been more than a few bashing threads, so I won't add any fuel to them. Props to the mySQL team for adding significant functionality to their software. Triggers and stored procedures are components that are important in making mySQL a production worthy enterprise level product. The big name companies who have been using mySQL likely were using it for basic heavy select statement stuff where inserting or updating recordsets wasn't part of the equation.

If most of the documented gotchas are addressed in the 5.0 RC then all the more I applaud their efforts. Once serious referential/data integrity is fully delivered I will plan to seriously consider mySQL for my next project. Until then it's PostgreSQL for FOSS and MSSQL Server for other stuff...

I had all these on my wishlist...

[TarrySingh]

Database clustering and load balancing(Like the Oracle RAC), is it already available?. Although I'm glad to see the Stored Proc's, Triggers have been included. But good for the dev team :-)

A database is not a GUI

[msobkow]

The only so-called "database" that emphasizes it's GUI is Access. Every other vendor/product I'm aware of relies of separation of duties and doesn't try to roll user interfacing into what is rightly a back-end service.

Administration tools for commercial and OSS databases may be easy for small sites and novice DBA's that don't know their tools, but large applications rely on database scripts to handle configuration, not GUIs. The reason is simple: you can't put a mouse click into CVS/RCS/SCCS/???.

Re:A database is not a GUI

[humbads]

> so-called "database"

Right. Access is not a database, but only a front-end GUI program. What people call "Access" is actually the Jet Database Engine, which is implemented in a set of DLLs. It is similar to SQLite in that it is a file-oriented, rather than connection-oriented, database. Jet database files have a "mdb" extension.

Access can also act as a front-end to SQL Server databases, and has loads of import/export tools. Access (the GUI) is popular with me because makes it so easy to clean up and move data around between databases, text files, Excel files, etc. The Jet engine is free, and it is not a bad engine for everyday tasks involving a few users. The most severe restriction nowadays is the 4GB database size limitation and the fact that it is no longer supported by Microsoft. It will be replaced by SQL Server Express.

Re:A database is not a GUI

[Pionar]

Yes! Exactly.

I love using Access to create databases for clients/coworkers. It's fast and easy, scriptable with VBA and if you get frustrated with the GUI, it even has a SQL execution function.

It won't work for massive databases, but for things like a client list and (snail) mailing list management, it's very ideal.

Re:A database is not a GUI

[Forbman]

The only so-called "database" that emphasizes it's GUI is Access

That's only if you're using Jet (i.e., access) to store the data. If you're using a real RDBMS, Access makes for a great database-aware front-end development environment. Since Jet also does not really support triggers, stored procs, etc., either, then it leaves it to the developer to try and do data integrity stuff mostly in the UI. Yes, this really does blow boogers.

Administration tools for commercial and OSS databases may be easy for small sites and novice DBA's that don't know their tools, but large applications rely on database scripts to handle configuration, not GUIs. The reason is simple: you can't put a mouse click into CVS/RCS/SCCS/???.

So, you write a document that says "click on this", "click on that", "enter 'XYZZY' for Froboz", etc., and check that in to your SCS. You then follow that script when implementing changes to test , qa or production. It very well could just say, "open a command line and run BlowAwayTheDatabase.sql", but when you have to depend on other people to migrate your changes to production, this is how you have to do it.

Re:SCO

[10Ghz]

Well, SCO also ships PostgreSQL with their product, can we trust the PostgreSQL-folks?

Re:SCO

[freshman_a]

SCO OpenServer ships with:

-MySQL
-PostgreSQL
-Apache
-Tomcat
-Mozilla
-KDE

Should we not trust any of those projects anymore?

For good reason

[Stone316]

Most DBA's are adequate at best and most likely don't have much experience trying to squeeze every ounce of CPU out of a box. So I can understand why they do this... I personally don't take 'user' benchmarks very seriously because most times you have no idea how their environment is configured or if it is even configured properly.

Re:SCO

[Decibel]

Unlike MySQL, PostgreSQL has absolutely zero control over the distribution of their code, other than requiring the copyright notices.

Read the fine print...

[Decibel]

AFAIK the clustering and load balancing 'solution' for MySQL means your entire database has to fit in memory. Not very practical...

ERD tool ?

[mallumax]

Does anyone know of a ERD (Entity Relationship Diagrams) tool which is

• Free
• Runs on Linux
• Generates SQL from ERD diagrams ?

Re:ERD tool ?

[Anonymous Coward]

Use Dia http://www.gnome.org/projects/dia/ to create UML diagrams. Then use tedia2sql http://tedia2sql.tigris.org/ to create sql scripts for PostgreSQL, MySQL, Oracle or other RDBMS. For PostgreSQL you have pg_autodoc http://www.rbt.ca/autodoc/ to create diagrams and HTML documentation directly from database server.

Re:ERD tool ?

[Anonymous Coward]

DBDesigner ...

Re:ERD tool ?

[Anonymous Coward]

Azzuri clay plugin for eclipse makes some funky little database designs and DDL scripts.

Re:Having worked with oracle 10i for the last year

[DrShasta]

You are talking about "massive" tables and a "reporting" application. Of course Oracle and DB/2 are the right choices for this. Has anyone ever thought differently?

I've always used MySQL and it has always been extremely fast for me. But I've always known that it isn't meant for data analysis on massive amounts of data. The only time I've ever heard anyone compare MySQL to Oracle and say that MySQL was just as good as Oracle is when using small tables on a web app, which is exactly what MySQL is used for 99% of the time.

Apache Derby.

[flash777]

I know it's not really apples-apples comparison. But can anyone share their experiences with Apache Derby and possibly compare it with MySQL?

Re:Apache Derby.

[NullPointr]

Unfortunately, only the embedded version of Derby (IBM Cloudscape) experience. It is slow and single-user, but not too bad otherwise. Supports SQL-92 standard.

Easy way to upgrade?

[martin_b1sh0p]

I'm not a huge DB guy, so maybe I'm missing something, but based on the instructions on the mysql website it seems like for those of us still running 3.x and want to upgrade to 5.x we have to go to version 4.x first. Is there an easy way to upgrade directly?

Re:Easy way to upgrade?

[Squeebee]

If you dump the database to text with mysqldump you can upgrade to the latest version without issue. The 3-4-5 upgrade path is for those who want to move live tables, in which case you want to make sure all table formatting gets properly upgraded.

Re:Easy way to upgrade?

[SETIGuy]

If your tables are small, just dump them to disk and reload after the update.

Re:I see a problem

[bwalling]

Hmm... I'm no database person but if you're creating a table that only allows a value of up to 127 and go over that, I would expect it to give you 127. What would a real database do?

Return an error and don't make the insert.

Re:I see a problem

[cortana]

"Hmm... I'm no database person..."

Clearly.

"... What would a real database do?"

It would throw an error, instead of silently mangling the data. Fortunatly, MySQL 5 seems to do this and so the original poster is talking shite.

ODBC

[abulafia]

Now, my one pet-peeve in this area is that there is no linux equivalent of ODBC.

There isn't?

I just finished a project that accesses SQL Server from a Linux/Apache/mod_perl app using ODBC via the FreeTDS drivers. (Don't ask, client requirement.)

Granted, not all of the unixodbc drivers are free. But then, they aren't in MS land either, although you might not notice because you're paying for them via a bundle.

Re:ODBC

[Rich0]

Thanks for the info. I'd never heard of it. (Probably because I've never used an open-source app that advertised its support for it.) Actually, do any popular open-source apps actually support it? It would seem like a logical standard unless it has some drawback.

Re:ODBC

[AngryDill]

...do any popular open-source apps actually support it?

Yes. OpenOffice.org is the first thing that comes to mind. There is extensive support in perl as well. Also, WINE supports unixdbc too, to allow running odbc-dependant Windows apps under Linux and BSD.

-a.d.-

Re:ODBC

[abulafia]

AngryDill answers some of this.

The only real drawbacks to it are that (a), it is a little confusing to set up, because it exposes layers of software that in Windows-land are papered over, (b), well, it is ODBC, which isn't my favorite way to talk to databases (lowest common denominator implementations, the weird {? = exec} crap, etc.) (to be fair, not like JDBC is any better, as you'd expect from the name.), and (c) some drivers are not F/free.

For the record, though, the FreeTDS driver (only one I've used UnixODBC) is very solid - once I wrapped my head around the configuration, everything went swimmingly. I really didn't expect talking to SQL Server to work very well, but it does.

Re:Having worked with oracle 10i for the last year

[kpharmer]

> You are talking about "massive" tables and a "reporting" application. Of course Oracle and DB/2 are the right choices for this. Has anyone ever thought differently?

Yes - everytime a product or technology is overhyped people believe it will do everything. Search for mysql and data warehouse - you'll find plenty of people who think it can handle massive data without really understanding what db2/oracle/informix/etc do that's different.

> The only time I've ever heard anyone compare MySQL to Oracle and say that MySQL was just as good as
> Oracle is when using small tables on a web app, which is exactly what MySQL is used for 99% of the time.

Right - in that context mysql/postgresql are competitive. Of Course, that isn't what MySQL AB is telling people. Little surprise, these are the same people that a few years ago were telling people that they didn't need primary key/foreign key constraints either.

Anyhow, I've got an application right now that for its first two years stayed quite small - just a few thousand rows. Implemented in db2 - since we need a major database anyway for the larger databases with a billion rows, and we can save labor by staying consistent. Anyway, this little database is about to now explode in size due to expanded requirements and new customers - we're expecting one little critical table to go from 3,000 rows to around 500,000. Its history table will go from maybe 10,000 to 10,000,000 rows. Other tables throughout the databae will likewise expand. DB2 was overkill when this database was first created, now it's perfect: it'll support the heavy transactional loads, automated system failover, and very fast scans, reports and other tough queries. This application growth is not at all uncommon - everyone is putting far more data into databases than they were just a few years ago.

So yeah, mysql and postgresql are neat products, but their lack of scalability for massive tables or analytical queries is a major gap. And the requirements for this capability are now commonplace - unlike ten years ago when only a few data warehouses really need it.

Re:Examine the license carefully!!

[cortana]

"So, unless you actually distribute/modify/copy MySQL, then you are free to do whatever you want with the database."

RTFL. Your wording can be simplified to, "So, you are not free to do whatever you want with [MySQL]."

From /usr/share/doc/libmysqlclient12/copyright (from MySQL 4.0.24):

All the MySQL-specific source in the server, the mysqlclient library and the client, as well as the GNU readline library, are covered by the GNU General Public License.

If you use libmysqlclient, then you must comply with the GPL, or negociate your own terms with the MySQL company.

Re:Having worked with oracle 10i for the last year

[Phillup]

OK... I'm not arguing about the *premise*... but... I don't consider your numbers to be justification for a different db.

Anyhow, I've got an application right now that for its first two years stayed quite small - just a few thousand rows. Implemented in db2 - since we need a major database anyway for the larger databases with a billion rows, and we can save labor by staying consistent. Anyway, this little database is about to now explode in size due to expanded requirements and new customers - we're expecting one little critical table to go from 3,000 rows to around 500,000. Its history table will go from maybe 10,000 to 10,000,000 rows. Other tables throughout the databae will likewise expand. DB2 was overkill when this database was first created, now it's perfect: it'll support the heavy transactional loads, automated system failover, and very fast scans, reports and other tough queries. This application growth is not at all uncommon - everyone is putting far more data into databases than they were just a few years ago.

I've got a web app I'm working on... that has been in use for three years.

It uses MySQL.

And... it is currently 1.5 Gig in size. One of the tables has almost 4 million rows. There are 64 tables in total.

I figured in a few years I may need to split the db and store the historical data on a different machine...

But, I'm not thinking it is too big at the moment... or for a few more years.

At least.

If you'd taken some time to dig a little bit...

[JetJaguar]

You would've have found out that MySQL AB isn't paying SCO a dime. In fact, SCO is PAYING MySQL to support MySQL on OpenServer, the cross marketing crap, is well, SCO marketing crap (The following is pasted from a message from one of the MySQL developers to the mysql mailing lists):

Nothing to hide, no conspiracy here ;-)

I think the discussion here has hinged on "the nature of the partnership". Let me assure you that no money has gone towards SCO.

They have provided us with the means to build and support binaries on SCO OpenServer 6. So they're paying us for... developing our software, which is all GPL licensed (yes we do sell non-GPL licenses as well, for the same code).

Knowing this fact (SCO funding GPLed development), most people regard the partnership with a benign smile ;-)

The other issue I spotted was about "commercial binaries". Users with OpenServer 6 get a trial subscription to our MySQL Network subscription service. These are certified binaries, but still GPL licensed. Non-GPL (aka commercial) binaries are an optional (but free) extra under MySQL Network. That option exists mainly to assist companies where using GPL-licensed software runs into policy problems, etc. We do also sell non-GPL licenses separately from MySQL Network, to OEM/embedded customers.

I hope this clarifies the situation to your satisfaction. If you have any further questions, please feel free to ask me.

Regards,
Arjen.
--
Arjen Lentz, Community Relations Manager
MySQL AB, www.mysql.com

Re:If you'd taken some time to dig a little bit...

[dAzED1]

I'm fully aware of this fact. I don't care. They're intentionally lending creditial to SCO. That money is coming from lawsuits and threat tactics against Linux users. It's ill-gotten money, for an underhanded cause (SCO trying to pretend they matter anymore, for the sake of continuing legal battles). MySQL AB knew this, yet they played along anyway.

Note also that the "partnership" press release I linked is on mysql's web site - not SCO's. it's not like SCO is spreading this nonsense about SCO being "a leading provider of UNIX(R) software technology for distributed, embedded and network-based systems" - that text is straight off mysql.com. And yes, I know that SCO wrote it. I don't care about that either.

No, it was a dumb move. *If* MySQL AB came out and publically apologized for sleeping with the enemy, then maybe I'd consider them for things again. Otherwise, they're tainted, and their motivations are extrodinarily suspect.

Re:If you'd taken some time to dig a little bit...

[JetJaguar]

Ok, reality check.

1. SCO has pretty much already completely destroyed it's credibility. Even the main stream press has more or less figured this out, even if they don't quite understand all the technical details. No matter what happens, SCO's days as a functioning corporation are numbered.

2. SCO's money is not coming from lawsuits against linux users. Name one linux related lawsuit that SCO has won. While some linux friendly vendors have had to expend funds for their defense, SCO hasn't received one red cent from any of it.

3. A very small portion of SCO's revenue is coming from some linux users that were worried about getting sued by SCO. Yes, this is deplorable, but the vast majority of companies and users have not fallen for this trickery, and those that did fall for it have been suitably chastised for it. The vast majority of SCO's money has been coming from other sources, and this has been well documented.

In other words, the money that SCO is funneling to MySQL AB probably isn't coming from a "tainted" source in the sense that you mean it. One could argue more strongly that Microsoft is indirectly funding MySQL development, since SCO has gotten more money from Microsoft than from it's shenanigans regarding linux.

Don't get the impression that I am defending MySQL AB here, because I'm not. However, I think you are taking a rather extreme position. MySQL AB has done things that have given me pause, and this is certainly one of those sorts of moves. MySQL AB is definitely walking a very fine line to be sure, however calling them tainted and their motivations "extraordinarily suspect" doesn't quite wash given the nature of what's really going on here.

Re:If you'd taken some time to dig a little bit...

[absinthminded64]

I like what you've said and concur for the most part.

They've sold SCO the MySQL AB burger and fries and they've told EVERYONE about it themselves probably per SCO's request/contractual obligation. For all I know they couldn't legally NOT partner with SCO in this way.

They've still done business with SCO and I could care less about where the money came from. As a result the only thing that remains tainted is my opinion of MySQL AB.

I can't change my own opinion. The last time I tried to manually change my own opinion I wrote to the wrong address and tasted blue for days.

I'll keep an eye on them for now. Unfortunately I only have a scornful eye to spare.

C

Mostly....

[einhverfr]

Enhancements that make it mostly a "real" RDBMS include the triggers, strict mode, updateable views, stored procedures, etc.

Still missing are XA/TPC, SQL/MED, SQL/PSM, Roles, SQL/CLI, user-defined types, any real OLAP functionality, and standards-compliance (ansi and strict modes) by default.

These features are mostly useful for large databases with many hundreds or thousands of users, and business intelligence requirements.

Final Proof the World Is Screwed

[tjstork]

It was bad enough that Apple fans who used to tout the advantages of PowerPC will now fall in lock step with the merits of Intel technology, crazy that Republicans in America now defend deficits and Democrats now demand cuts in government spending. Even me, a lifelong Windows Troll, am now running Linux at home.

But this is the last straw!

MySQL turned a simple, lightweight and fast database into a gigantic, feature rich behemoth. Now all the MySQL fans that used to defend its lack of features on the basis of its performance and executable size will now defends its executable size on the basis of features.

The world has just gone insane.

Re:SCO

[10Ghz]

Well, Postgresql.org contains detailed instructions on how to install their database on OpenServer. So they are actively supporting SCO. Can we trust them?

Re:Having worked with oracle 10i for the last year

[kpharmer]

> OK... I'm not arguing about the *premise*... but... I don't consider your numbers to be justification for a different db.

well, depends on what you do with those rows: mysql or postgresql can certainly handle a database with ten million rows in a single table. But unless you can rely on a b-tree index to select just the data you want - you'll end up doing a table scan - and then it'll take 60 seconds or so to get your query back.

With db2/oracle/informix/etc - you've got other options for this 10 million row table. If some of your queries select too much data (like 100% of the data for one customer, group by host & date, then show changes over time for their hosts) you can implement a range partition by customer. Then when you can't use your b-tree you can at least just scan the data for that customer. That might be just 5-10-15% of the total number of rows. Then in addition, you've got query parallelism. Since this is on a 4-way I expect to get response time 25% of what I'd see with a single threaded query like mysql or postgresql performs. Then you've got materialized views, better optimization, etc, etc, etc.

In the end, this table could be supported by postgresql/mysql. But I'd get 60-second response time on queries against that table. With db2 I'm going to get sub-second response time.

> I figured in a few years I may need to split the db and store the historical data on a different machine...
> But, I'm not thinking it is too big at the moment... or for a few more years.

Sure - especially if you really don't need history very much. Personally though, I like to enable trending for data analysis - even on your typical transactional databases. And so I find that historical data is pretty valuable.

Database wars, episode #5512128

[twodot72]

I strongly suspect the slashdot editors have a bet as to how many times they can post a MySQL story and have the same debate, with more or less the same bashing, arguments, and counter-arguments repeated all over again.

It's must be their secret revenge on the fraction of the readership screaming DUPE! every time they post a story resembling an earlier story. Now, they can read their latest MySQL story, point at almost any comment, and scream "DUPE!" they too!

Re:Database wars, episode #5512128

[PCM2]

Blame MySQL AB. How many times have they announced a pre-release version of MySQL 5 now? It seems like I've been seeing announcements for years...

Re:Having worked with oracle 10i for the last year

[Donny Smith]

You're right.

>So yeah, mysql and postgresql are neat products, but their lack of scalability for massive tables or analytical queries is a major gap.

Perhaps in a year or so these guys will have something for low-end data warehousing on PostgreSQL: http://www.greenplum.com/

Wow, very nicely done.

[Some Random Username]

You just posted what is quite possibly the single stupidest comment in the entire history of slashdot. Sorry, I don't think there is a trophy or anything, but you should still be very proud.

First of all, its not graceful degredation, its data corruption. The entire purpose of constraints is to give you an error when you try to insert invalid data. Changing it to be valid data and not even telling you is completely and totally the wrong thing to do. How about if your data doesn't pass a constraint then mysql does a drop table, is that still good for you? Its just as helpful and makes just as much sense.

Second, databases are supposed to have constraints, they store the data, they have all the rules of what is and is not valid data. Duplicating that in your code is absolutely brain dead, although its exactly what php/mysql developers have always had to do. This warps their minds and makes them think like you, that mysql is right, and everything else is wrong. Sorry, mysql is broken, every other database follows the SQL spec and returns an error when there is an error. Randomly changing data is not the correct response to an error condition, nor is there anything graceful about it.

Re:Having worked with oracle 10i for the last year

[SETIGuy]

> You are talking about "massive" tables and a "reporting" application. Of course Oracle and DB/2 are the right choices for this. Has anyone ever thought differently?

Yes - everytime a product or technology is overhyped people believe it will do everything. Search for mysql and data warehouse - you'll find plenty of people who think it can handle massive data without really understanding what db2/oracle/informix/etc do that's different.

That is especially true for PHBs. Some hear "free" and assume it will do everything that their $XXk/year database does, without spending tens or hundreds of kilobucks per year. I haven't used MySQL 5.0 yet, but if it's like 4.X, it'll be fine and speedy for databases smaller than memory. It'll also be fine so long as you don't mind some scheduled downtime to compact your frequently updated tables. It'll be fine as long as you excise "count(*)" from your SQL vocabulary.

MySQL (at least version 4) has a long long way to go in query processing and index searching. I hope to be pleasantly surprised by MySQL 5, but I'll bet that were still going to be using 5 year old copies of a dead product (informix) for tables with more than a 10 Mrows or so. (Our biggest tables are in the few gigarow range.)

Re:Having worked with oracle 10i for the last year

[SETIGuy]

And... it is currently 1.5 Gig in size. One of the tables has almost 4 million rows. There are 64 tables in total.

Call me ten years ago when this was a medium sized database. Now if it's less than 1TB, it's a small database.

Fun Databases

[KevinColyer]

I'm can't wait to see the new MySQL now with its "fun features". I'm totally intrigued as to what a fun feature might be in a data base? Will it start replying to queries in style of the Swedish Chef? Bork ,Bork, Bork! Still great work from the MySQL team. Where would /. be without them? Probably /.ed!

Re:Fun Databases

[aled]

Where would /. be without them?

Running on PostgreSql and posting less MySql stories...

Re:Fun Databases

[KevinColyer]

Touché!

Re:Fun Databases

[aled]

Sorry, couldn't help it :-)

Re:Having worked with oracle 10i for the last year

[Phillup]

In the end, this table could be supported by postgresql/mysql. But I'd get 60-second response time on queries against that table. With db2 I'm going to get sub-second response time.

My pages have to be generated in less than 0.5 seconds on an unloaded system. (That is application and db processing) In production, the system generates pages in less than one second with 100 to 500 users logged in.

When it starts getting close is when I'll start looking at something else. Right now, I've been able to tweak MySQL into meeting that benchmark.

Right now I get this on my dev box:

This page was generated in 0.05 seconds.
 
This page took 0.01 seconds of database time.
 
This page took 0.3 seconds to load.

:-)

That page made 10 calls to the db with a total processing time of roughly 0.0055 seconds. Some pages currently make upwards of 40 calls... but they'll get refactored when their performance starts to lag under load.

How long the browser takes to load the page is by far the biggest time sink... even more so for users that have to actually connect via the network.

Personally though, I like to enable trending for data analysis - even on your typical transactional databases. And so I find that historical data is pretty valuable.

Oh, it would still be available... I would just connect to a different db to pull it in. My code is already written for that to happen, I just need to change the connection string for the historical db. Right now it is exactly the same as the current db connection... so they both live in the same db.

That way we are only dealing with replication and backups for one database server, ATM.

Re:Having worked with oracle 10i for the last year

[kpharmer]

> That page made 10 calls to the db with a total processing time of roughly 0.0055 seconds.

Right, looks like that's working well for you. With mysql getting that query performance on a 4m row table, it must be using an index, which means that you're selecting less than 3% of the data each time. The scalability issue emerges when your result set is 10% of the total table, you have to sort 100,000 rows, etc.

> Oh, it would still be available... I would just connect to a different db to pull it in. My code is
> already written for that to happen, I just need to change the connection string for the historical db.
> Right now it is exactly the same as the current db connection... so they both live in the same db.

sounds like a good plan

"Former Slashdot Coder now MySQL Employee"

[speck]

This phrase is terrifying, and yet somehow not surprising.

Really?

[lorcha]

Does Postgres finally support replication? I had no idea!

VACUUM

[commanderfoxtrot]

I had a similar problem only this morning. I deleted all the rows from a table (by using DELETE FROM) and SELECT COUNT(*) went from sub-second to two minutes.

That's after going from 400,000 rows to zero.

VACUUM ANALYZE made no difference; however VACUUM FULL sorted it out. You should really have auto vacuum running, but for the dev I am doing I prefer to do it by hand.

From what you're saying, this could be the reason why it takes so long.

For what its worth, I've moved to Postgres because it's just so more solid. It's fantastic software. And I know what I'm talking about- I'm heavily involved in (big) mainframe DB2 at work.

Ok, here goes...

[krow]

a MySQL developer say "yeah, I don't know what we were thinking, that's a really fucked up thing to do" Yep, its a fucked up thing. This is why we implemented strict mode for 5.0. In 4.1 you get warnings, in 5.0 if you are using a transaction table it tosses an error. If this is an issue, upgrade to 5.0. Personally for me it is.

Re:SCO

[Jamesday]

Can probably trust both but only MySQL found a way to have SCO fund the development of GPL'd software it had condemned... :)

db choices

[joshsnow]

You forgot Ingres and Informix.

I'm not sure what the status of informix is these days, but Ingres is free to use.

The background to this (without being verbose) is that ten years ago, the big 4 DBMSs were; Oracle, Ingres, Sybase and Informix - practically in that order. They all pretty much had the same feature set. For various reasons (which are too long and tedious to go into now) Informix has ended up with IBM, Ingres went to CA who abused it and then turned it loose last year, Sybase is entrenched in Financial applications and has managed to survive and Oracle is...well...Oracle.

Point is, Ingres is a top product, probably is still major league and it doesn't have expensive licencing associated with it. You should check it out.

mysql is on its way out....?

[burden123]

well, i think ultimately mysql is on its way out unless they do something with their restrictive license... Recently i've been likening it to evil. Because of the license our software department unceremoniously ordered a switch to PostgreSQL. We are told to even port existing applications where possible. I was a supporter for many years but recently my opinion of PostgreSQL has trumped mySQL. as a final nail in the coffin for mySQL in my mind is the fact its hypersensitive to corruption in cases of a power outage. I've restored backups of databases no less then 3 times this year because the system was abruptly reset... this is side by side of other postgreSQL installations that came out fine. so if you're considering MySQL, you better figure in a good UPS in your Total Cost of Ownership calculations.

In-DB vs In-App

[Craig Ringer]

Perhaps. Really, though, the answer is "somewhere inbetween". That's why I said "consider using ..." ;-) . DB app GUIs make it too easy to overlook, or too hard to use, the facilities the database will have to help you, and this generally limits your design choices. In many cases, that's just fine, but in others it can be a serious issue. It's understandable - the smaller, simpler subset of features these apps limit themselves to using in the database host, the fewer quirks they have to deal with and the fewer things they need to emulate for storage engines that don't support them. I'm just not convinced it results in well-designed databases.

I also argue that the app developer's job extends well into the database, in that for many apps views and stored procedures are as much a part of their application as the "real" code. At the app/DB interface like the interface between their various app modules, they should be considering clean design and separation there too, rather than poking directly into some other module's structures. After all, the DBA needs to be a part of the app development if possible, and they're really maintaining a module just like everybody else.

In this context, "developer" is the user who is building a specific database implementation on top of a GUI database app like Access, rather than the developer who wrote that application.

Hmm ... all we need now is a "do it all in the application" nut and a "do it all in the database" nut and we can get a proper three-way flamefest going. Hmm. I'm waiting, and there's still no smell of sulphur - is it possible that being moderate and standing in the middle ground won't get me flamed to death? We'll see.

Re:I see a problem

[spagetti_code]

Exactly! This question points to a huge misunderstanding with MySQL. So called 'industrial strength' RDBMSs are very vary careful to ensure your data is 100% correct. These RDBMSs manage very large databases with GB of data. If (e.g.) referential integrity is lost, you are sunk. If data corruption sneaks in, its pretty hard to fix it or find it - there's way too much data and complexity.

So these RDBMSs provide tools - triggers, stored procedures, a multitude of locking mechanisms, DRI (Declaritive Referential Integrity) and so on. With a well built database:

• You *can't* insert duplicate data
• You *never* modify data behind a users back (e.g. remove trailing spaces on varchar)
• You *never* break a foreign key, leaving dangling data
• You can perform all maintenance while the DB is live
• and so on...

The SQL standard is very clear and explicit about data handling - in general no surprises and never let in bad data. And all 'industrial strength' RDBMSs do a good job of following the standard... sadly not perfect but very good. MySQL is not one of them - see here for some of the ugliness inside MySQL exposed. For example: MySQL's use of NULL is just bizarre, as is its ability to accept invalid data and change it silently.

'Industrial Strength' RDBMSs are about predicability and reliability. Speed is important, correctness is essential. MySQL aint there yet. Oracle, MSSQL, Postgres, Sybase, to name a few of my favourites, are.

Let the flames fly :-)

Re:Examine the license carefully!!

[OneFix at Work]

No, no, no...that's the whole idea of the library. If you use the functions from the standard library, you aren't required to comply with the GPL, only if you redistribute and/or modify the library. It's like saying that if you build your application on top of Linux that you are required to comply with the GPL. Only if you make modifications/distribute/copy the Linux kernel for your project would this requirement hold.

Re:Examine the license carefully!!

[cortana]

"If you use the functions from the standard library, you aren't required to comply with the GPL, only if you redistribute and/or modify the library."

Can you point to the part of the MySQL license that says this? If you can't then I think I would prefer to take the word of /usr/share/doc/libmysqlclient12/copyright over yours. ;)

If MySQL were licensed under the GNU LGPL then you would be right... but it's not. If you link against libmysqlclient, then the derivitive work you have created must be licensed under the terms of the GPL.

"It's like saying that if you build your application on top of Linux that you are required to comply with the GPL. Only if you make modifications/distribute/copy the Linux kernel for your project would this requirement hold."

Not at all. I refer you to Linus' statement at the top of the Linux COPYING file:

NOTE! This copyright does *not* cover user programs that use kernel services by normal system calls - this is merely considered normal use of the kernel, and does *not* fall under the heading of "derived work".

I see no such similar statement in MySQL's copyright file.

Slashdot uses MySQL

[SageMadHatter]

It gets 50 million page views a day and it's up to a 13+ million post count, with at least 1 million registered users. Is Slashdot not proof that MySQL has scalability?

Re:Slashdot uses MySQL

[kpharmer]

> It gets 50 million page views a day and it's up to a 13+ million post count, with at least 1 million registered users. Is Slashdot not proof that MySQL has scalability?

Great question. But no it isn't.

Scalability isn't about size - it's about the economy of size. That is, if one million pcs running MS Access could also handle that load you wouldn't call MS Access scalable - because the cost to handle the load would be so much higher than other solutions.

So, running Slashdot isn't like getting a good score on the TPC:
1. it doesn't tell us what the hardware investment is
2. it doesn't describe how many of those queries come from cache that doesn't hit the database vs actual database hits
3. it doesn't tell us what compromises were taken - that might make it faster, but occasionally (or not so occasionally) result in data integrity problems
4. and we do know that slashdot provides very little in the way of real-time trending, in depth analysis of the data, etc. Exactly the kinds of queries that mysql *can't* scale to. The type of functionality offered is mostly restricted to view posting list, view details of one posting, insert new posting. This is *very easy* to scale on most databases. What wouldn't be easy to scale to do - is to provide a charts that showed (real-time, or nearly so):
        - how the participation in a discussion ebbs & flows
        - how angry or confrontational a discussion is, and how that changes over time
        - which parts of the discussion are the "hottest"
        - etc, etc, etc

So, yep - mysql can probably handle a good read-intensive *transactional* load. But it can't handle a very heavy mixed load, nor a very heavy load in which genuine data analysis occurs. Which isn't something far fetched at all any more - but constitutes most of what I am delivering these days. With Oracle and DB2...

Re:Examine the license carefully!!

[OneFix at Work]

NOTE! This copyright does *not* cover user programs that use kernel services by normal system calls - this is merely considered normal use of the kernel, and does *not* fall under the heading of "derived work".

But that's what this means...

* Free use for those who never copy, modify or distribute. As long as you never distribute the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL license or not.

...it's just worded differently because it's a database and not an OS.

Re:Examine the license carefully!!

[cortana]

Again, that is not what the license says. The license says that the client libraries are under the GPL. Therefore, if you are distributing an application that uses the client libraries, your choices are: GPL it, use another MySQL-approved F/OSS license, or purchase a commercial license from MySQL.