Slashdot Mirror
MySQL 3.23 Declared Stable
redcoat writes "After two long years, MySQL 3.23 has been declared stable. Improvements over 3.22 and .21 include rudimentary transaction support using the BerkeleyDB lib, full-on replication (master/slave configuration) and lots of other goodies. It's been a long wait, but a worthwhile one, no doubt."
Well those are neat, but they are minor features to most people. Replication and load-balance between slaves is neat, but what's the point to load balance between slaves when MySQL doesn't even cache queries. With web sites running always the same queries again and again, caching queries would gives incredible speed boost and isn't a very hard feature to implement (quite easier than foreign key or transaction IMHO).
Sure things improves in MySQL, but huge missing features are ignored in favor of minor improvements here and there. I'm sorry but full-featured transaction, foreign keys, caching, constraints, nested queries, etc. are what people expect their database to do, and they are very MISSION CRITITAL in many situations. Reworking the table file format yet again is not what I'd call a massive improvement.
Well, compared to what? It doesn't even have a can-opener!
Do yourself a favor and get yourselves a Victorinox Swiss Army Knife with builtin digital altimeter and thermometer, so you can play with a real knife!
The illegal we do immediately. The unconstitutional takes a little longer.
--Henry Kissinger
What do you want? A stable database? Or lots of
new features with bugs in them?
It's a whole company, but do you know how much people they are? Don't always think of giants like Mircosoft or Intel when thinking of companies, MySQL is developed by just a few people (I counted 12 or 13 coders), and they are keeping the whole stuff platform independent, the server and the tools run an Windows, and many flavours of unixes.
Ever built a transaction feature yourself? Or a rudimentary database? I don't think half an year is to long for the things they implemented in 3.23.
Michael
Michael Bergbauer (michael.bergbauer@gmx.net)
I/O Error G-17: Aborting Installation
Seems like a Relational database system would have relations and a Transactional database system would have transactions.
With Transactions the code is to write first then check. If there are problems, you can always rollback.
With MySQL (other than with transactions on BDB), the update is atomic. It either succeeds or fails. It's a different paradigm and in many cases a better paradigm. You do need to do better analysis and coding, though.
I found an interesting article
by a fellow who has worked on some
major open source oriented sites.
He does a quick comparison of
postgres and mysql here.
You obviously don't understand MySQL versioning, what it means is that the latest release MySQL 3.23 "is the latest stable release"
Alex
In 3.22.34, it would crash just from a run o' the mill ORDER BY. Hope at least that much is fixed...
www.Jackassery.com
It allows NULL in indexed columns!
Are you saying this is a good thing or a bad thing? Certainly at least for non-unique indexes you want to be able to have an index on the rows that do have data. Queries which use the index should should still be speedy.
For unique indexes it's another matter. Oracle allows unique indexes on nullable columns which is pretty bogus since it doesn't ensure uniqueness.
-Bruce
But the Microsoft solution does get you transaction support in ASP, never mind the database, a replication architecture that has been around a couple of versions (I've never actually used it, but I know that at least SQL6.5 had it), and an army of button-pushers who know how to use it.
Maybe you don't need all the database and transactional features for your application, but you aren't comparing like for like here.
"don't fall into the fallacy of believing that Perl can solve social problems. Maybe Perl 6 can, but that's a ways off"
So Do Nusphere.
I ran tests for a client using both Nusphere & AbriaSQL distributions of php, mysql,apache & perl (nupshere only) on Red hat & win 2k. The Nupshere was easyer to install on both platforms.
Also unless you pay for support, AbriaSQL use elder versions of all the programs. whixh is bad.
Yes, Postgres 7.1 supports LEFT, RIGHT, and FULL outer joins. It's still in beta but it's been running Sourceforge for a month ...
Well, check out sapdb at www.sapdb.org.
It will be GPLed soon.
Wow. No foreign keys. No transactions. No stored procedures. An Excel Spreadsheet has the EXACT same functionality as MySQL.
That might be an issue if performance were a problem. Most web sites built on MySQL get results plenty fast without having to cache things.
I don't think anybody is suggesting using MySQL to run your Fortune 500 company database. MySQL's niche is web sites. The web is inherently session-based. If you've got somebody entering data in a form, and they bail halfway through, you have no way to tell. How many web sites allow you to "unsubmit" a transaction? So transaction rollback isn't a big deal for a lot of applications. Likewise with foreign keys. If you're coding your site in such a way that foreign key violations are what tip you off to a data integrity problem, you need to do some more work.
MySQL is not Oracle. It's not PostgreSQL either. In many ways, that's a good thing. That's the great thing about competition: You can pick the tool that does what you need.
Let's see ...
...
...
:)
1 & 2: PG 7.1 (now in beta3) allows infinite length tuples, so you might want to reconsider that restriction, given that you're still in development and that PG 7.1 will be released in a very short time.
3: "order by" has to sort the output rows regardless, so sorting the indices alone doesn't help as much as you might think. This is particularly true if you're doing a "join" first. And merge joins result in an ordered set of output tuples which in some cases, at least, will match the "order by" sort requirements, meaning it can be left out altogether
4: PG 7.1 does
5: True
6: Not quite sure what you want, here, but PG does support a fast "COPY" command to import data quickly.
7: True
8: PG 7.1 does
9: True
10: Sourceforge recently migrated from MySQL to PG 7.1
I'm of the opinion that the open source databases are all so substantially behind the capabilities of the proprietary market leader Oracle that I don't consider them viable replacements for enterprise grade data solutions. I work on a system that has 800 concurrent users at any given moment, and I don't see anything remotely close to worthy of consideration in the open source arena.
Worse, I see Oracle as pulling away. In the last two years, the open source databases have struggled to add features that I view as expected and required while Oracle has added 10X more. Transactions & row-level locking, nested selects, foreign keys, etc... are not optional features. In order to compete, open source needs database projects with forces comparable to the linux kernel, apache, gnome, and KDE projects. I just don't think Larry Ellison is out there worrying about GPL'd competition.
I don't think Oracle gets the proper attention in the open source community. Oracle has a greater "lock in" effect and has mostly got a free ride from the open source community so far. Oracle is a big gorilla that is rapidly becoming the most secure proprietary software vendor around.
GRANT FILE ON *.* TO replicate@ip_address IDENTIFIED BY 'password';
Although, you are correct, as this is a potential problem to be aware of, as listed in the MySQL documentation:
"Don't give the file privilege to all users. Any user that has this privilege can write a file anywhere in the file system with the privileges of the mysqld daemon! To make this a bit safer, all files generated with SELECT ... INTO OUTFILE are readable to everyone, and you can't overwrite existing files. The file privilege may also be used to read any file accessible to the Unix user that the server runs as. This could be abused, for example, by using LOAD DATA to load `/etc/passwd' into a table, which can then be read with SELECT."
--It's Pimptastic!--
On the developer's mailing list of Akopia's Interchange open source e-commerce package, http://developer.akopia.com/archive/interchange- users/2001/msg00535.html , Monty Widenius said himself last week the following:
Our development has during the last year grown from 2 1/2 to 8 people and we have also got a much bigger market penetration during this time.
Hey, thanks, it works!
Any ideas on how efficient it is for large tables? Does it have to internally randomly order the whole table before returning just one row?
3. AFAIK, if you are sorting a table, which is very common in web pages, indexes reduces a lot the complexity of the sort. According to the PG docs, it's even makes use of temporary files for sorting data in case the output it's too big for doing it in memory.
6. It means that the file is available in the client or server side. It's important if you want to "download" a file from the client or import the contents from a server side file. It affects access privileges as well.
Regarding the remaining issues... you are right too.
--ricardo
sgis ddo ekil t'nod i
Michael Widenius gave himself some responses, which might be interesting for those following this discussion.
Search http://developer.akopia.com/archive/interchange-us ers for "monty@mysql.com" or read the thread:"[ic] postgres versus mysql"
Please don't post comments to that list if they are not related to "Interchange". Thanks.
At a recent open source database conference-thingy down here in the SF bay area (sponsored by abriasoft), the mysql guys said that row level locking was a ways off. I, personally, was rather sad to hear that.
Say you have an e-commerce app... one table with orders, one with customer account.
Now say you allow orders to be passed with or WITHOUT an account (don't want to force people on creating an account first).
you have in your orders table a "customer_account" field, can be NULL or can a foreign key to the customer table.
Now you can do this in MySQL :
SELECT * FROM orders LEFT JOIN customers ON orders.custumer_account=customers.account_name WHERE (blablabla)
And then you have in your results either only orders info (if custumer_account is NULL) or orders AND customers info at the same time. No need for two separated requests...
So LEFT JOINS are quite usefull in any table that has a foreign key that can be NULL.
yeah! is there an efficient way of doing that with PostgreSQL? I'm currently selecting count(*) from the table, then picking a random number from that group and selecting the row. Probably not the best method. Thanks!
"On weekends, to let off steam, I participate in full-contact origami."
I think MySQL developpement is slloooowwwwwwww. You would think a whole company dedicated to MySQL would allow for lots of new features coming out every week.
Well, they have been working on the 3.23 for about half a year and all they managed to do is implement a basic transaction feature. And they didn't even did it really by themselves, they had to take some code from the Berkeley DB and use a different table file format.
I guess by 2017 we can expect to have nested queries, 2045 for row-level-locking and 2078 for true foreign keys... at least Postgresql, with all the its flaws, is improving at a much faster pace and we can expect a whole lot of migration from MySQL to Postgresql sometimes in 2001/2002.
In the open source world, I find two RDBMS especially good. PostgreSQL and Interbase. They are both fine products and probably near commercial grade RDBMS standard.
:)
Now, would that be Interbase with or without a compiled in superuser backdoor account?
Of course, for pure SELECT power, nothing beats MySQL. So if you're not inserting data too often, and you don't mind running ISAMCHK every now and then, and data integrity isn't entirely critical (which does work for a surprising number of people), then yay MySQL!
I use it.
Don't you just love the way MySQL gives reasons not to use SQL features they don't support? And not just foreign keys, either, but transactions as well, among other things. Yeah, yeah, yeah, there's now rudimentary transaction support via BDB. Not that I'd trust any important data to something that is considered "rudimentary".
Then again, some people just have to reinvent the wheel several times over. One could wish that MySQL would just go away, in favor of more mature products that are also "Free". It won't happen anytime soon, but we can dream.
It allows NULL in indexed columns!
That's hardly fair. MySQL is in no way "equivalent" to MS SQL Server, which is in the class above.
That said, you could just as easily use Sybase ASE on linux, which is effectively free, and is a proper RDBMS.
If you have local users, it's in your best interests to upgrade, since
Went to the postgres site, downloads are only for linux, didn't see a source download. What gives? is it only supported on linux?
I have run it on Linux and NT for the past 6 months. I built a real estate lease entry and tracking system using Interbase, PHP, and Apache. It does support full transactions. It does support full FKs. I don't know how it compares to PostgreSQL, but PG was not an option as the client only has NT for the server.
"You're gonna need a bigger boat." - Chief Brody
With PostgreSQL so far I've had to use two queries. If there's a way around that someone please tell me. :-)
are you referring to the except clause in select? it uses a subquery, but i think it's still considered one query.
use LaTeX? want an online reference manager that
-- john
Once they find out that they have something stable in their distribution, they will have to change it as a matter of "Company Policy." :^)
---------
I think you can make the argument that Interbase lost 'the battle' because they have a lousy marketing department. Until Interbase went Open Source (tm), I had never heard of it even though I have been building Oracle and Sybase applications for the past 5 years.
Sounds more like a marketing problem than a functionality problem.Interbase has cool features that other 'commercial' RDBMS do not have (like events).
"You're gonna need a bigger boat." - Chief Brody
SELECT * FROM tablename ORDER BY random() LIMIT 1;
PostgreSQL runs on Windows NT just fine.
Find enclosed some of the conclusions:
- PG does treat BLOBs as part of a tuple, MySQL does.
- There are serious problems with PHP and PG transactions for reading and writing BLOBs in the database. PG forces to use transactions, MySQL doesn't.
- PG doesn't use any index to speed up sorts (ORDER BY clauses), MySQL does. This is a problem for web pages were most of the result are ordered according to articles IDs or modification dates.
- PG, as in V7, doesn't accept tuples of more the 8KB, 32 KB support can be selected at compilation time but speed is seriously affected. There is not this kind of restrictions in MySQL.
- PG does not accept "full-text" indexing and queries.
- In MySQL you can choose if a file to be imported is being read by the backend or the frontend.
- You have to run VACCUM ANALYZE in PG from time to time in order to get your queries optimised.
- PG doesn't allow to do BLOB content search, MySQL does.
- PG accept RULES for executing SQL sentences on table insert or updates, MySQL doesn't.
- NASA has migrated one of their webs to MySQL
;-)
We found that the previous reasons justify the migration to MySQL.--ricardo
sgis ddo ekil t'nod i
The PostgreSQL HOWTO at the LDP was written by a moron and is in no relation to the PostgreSQL project. Just a couple of days ago the LDP webmaster finally yanked it.
So, I am suck running MySQL as root becuase that get it taken care or ... ARGGGGGGGG!
until (succeed) try { again(); }
until (succeed) try { again(); }
Which databases support replication and transactions?
--
--
You sure got a purty mouth...
Comment removed based on user account deletion
MySQL doesn't recognize or enforce foreign keys. But you can still make fields that hold values that are keys to other tables, which, according to my relational DB course, is what you call a "foreign key" :-)
I think the MySQL syntax even allows for the keyword "FOREIGN KEY" in table definitions (and they even plan to sometimes save this information somewhere, maybe there's hope !).
Well done guys and girls.
:) The equivilent freeware solution came to exactly zero dollars (duh). (hardware costs not included, btw).
I'm of the opinion that along with several other open source applications, MySQL is what's helping Linux make large inroads to the enterprise market.
A while back I priced a WinNT solution for a basic e-commerce site, with MS-SQL 7, ASP, and all the rest of the NT fluff needed. It came to over $5,000 Australian dollars (about $7.33 USD
MySQL was a large part of the equation. I was and still am very impressed with it.
Again, well done to the team.
Alas gallinaceas de urbe bovis volo
The biggest "feature" of 3.23, of course, is the fact that it's GPLed. 3.22 is not. As for switching to Postgresql, I need enum, I need mediumint(5) unsigned zerofill. To some extent it's just programmer laziness, but it's nice to have your code not need to do so much data type checking and formatting. User-defined data types and/or stored procedures would be preferrable, then I wouldn't have to use tinytext for urls, for instance, but for now the only two real choices are MySQL and Postgresql.
ok then your [sic] infringing on my copyright! Could you as [sic] me next time before STEALING my comments for your own?
I was really hoping that proper foreign key support would be added by know but was dissappointed to see it missing from the list. I decided to see if it was in the TODO list and was surprised to see full foreign key support in the Things that have to be done sometime section instead of the Things that should be in 4.0 or the Things that must done in the real near future sections. So it looks like full foreign key support won't be around till 5.0 or later. *sigh*
Lack of full foreign key support (and maybe transactions) is the only reason I don't consider mySQL a proper relational database management system as it claims sinceit doesn't enforce relationships via referential integrity checks. In many large complex applications, having referential checks built into the DB is very useful and it would be a great boon if mySQL had this functionality.
Grabel's Law
Sure, it might be a _little_ bit quicker then PostgreSQL. But, I still belive PostgreSQL is the better choice. But, its good to hear its now a stable version. Maybe they can start adding things like sub selects and what not.
until (succeed) try { again(); }
until (succeed) try { again(); }
AbriaSoft has a free download for "AbriaSQL," a distribution of MySQL, PHP, and some other stuff with a wizard-driven interface that takes just a few minutes. It fixes the MySQL install problem in RH7, also. Check'em out...
Someone fill me in. I don't get the joke.
Or is it just a really bad typo?
-
-Be a man. Insult me without using an AC.
- I don't care if they globalize against free speech. All my best free thoughts are done in my head.
There's applications where you simply don't need transactions (anything without interdependencies between different acions). They may not be sophisticated, but they're quite numerous. Thus, a DBMS without transactions is much more useful than a knife without a handle. If it were different, MySQL would not be so widespread.
The illegal we do immediately. The unconstitutional takes a little longer.
--Henry Kissinger
==
Postgres is trying for speed and MySQL for correctness.
==
What are you talking about? I read your post five times and still can't figure out exactly what you are saying. With an opening statement of "Postgres is a mostly complete DB, with some things that need work. MySQL is a fast hack with little overhead.", one is left scratching one's head from the start. What makes Postgres "more complete" than mysql? How does one determine how much "overhead" mysql has? It looks like you might be trying to imply that postgres's SQL implementation is more complete than MySQL's but the statement is too vague to know for certain. Finishing up with "Postgres is trying for speed and MySQL for correctness", the post closes with the reader trying to figure out what "correctness" would mean as it would supposedly apply to an RBDMS. Could you be any more vague in your assessment and comparison of the two systems? All this in a +5 post... argh!
badtz-maru
==
MySQL have for many years been been considered as a very unstable product and that it have been (still are probably) lacking a lot of really useful features.
==
I have never heard of any complaint of stability with mysql whereas postgres versions prior to 7 were well known for eating databases. As for useful features, how about the blob support in postgres? Oh yeah, it's nonexistent. I can argue this factually because I prefer postgres but let's not turn a blind eye to its shortcomings. As for interbase, with the backdoor discovery being so recent, it would be hard to consider that platform seriously until a complete security audit was performed on the code.
badtz-maru
Not every aplication is going to be "Mission Critical" we have been using MySQL in my shop for a TON of applications. (Search Engine, Account Data, and for a LARGE scale File Sharing DB) While MySQL is not oracle, it's cost/performance ratio is pretty darn good.
I am become Troll, destroyer of threads
The people who are reporting MySQL crashes should submit bug reports. The MySQL people claim they haven't lost data in years, and if that's not true, it needs to be publicized.
The replication feature has been changed a little since the tutorial was written, so you may want to check out the documentation at the MySQL site. Here's a copy of my own /etc/my.cnf file from both machines, which are setup for 2-way replication, for reference (perhaps I'll get around to posting a tutorial of my own later today on The Linux Pimp
Main server /etc/my.cnf file
[mysqld]
log-bin
master-host=ip_address_of_backup
master-user=replicate
master-password=your_password
server-id=1
Backup server /etc/my.cnf file
[mysqld]
log-bin
master-host=ip_address_of_main_server
master-user=replicate
master-password=your_password
server-id=2
--It's Pimptastic!--
That's because it actually cost more then SQL server. If the dolts over there at borland marketing dropped the prices and actually advertised it once in a while it might have stayed competitive. Of course their loss is your gain.
You can go download a multiplatform, stable, easy to manage and maintain database that has been in production forever and is rock solid and has great documentation.
War is necrophilia.
I second this statement. I would say it's been stable for at least 2 years as well, that's how long I've been using it. We are using MySQL to run a fairly large db (128+MB) (one that I would _not_ relish rebuilding) with over 8 queries/s (that's average over the last month, which is exactly how long ago I restarted mysql for an upgrade). There's nothing wrong with mysql stability-wise, definitely nothing wrong with its performance (if you turn off table locking), and if you can get by without transactions (for which there is support now with db3). I have to say I wouldn't use it for anything really important or involving money, but that's only because it's not a 'trusted name', so it's largely a perception issue.
"Hot lesbian witches! It's fucking genius!"
Everybody always says it's my-ess-queue-ell, but I don't believe them. Try this:
:)
mysql> select soundex("mysql");
mysql> select soundex("mysequel");
You see, even the MySQL developers think it's pronounced "sequel"!
mysql-data-dir/my.cnf is used to set server-specific options (RedHat defaults to
~/.my.cnf to set user-specific options.
My original post suggestion of /etc/my.cnf works fine, but it opens you up to the potential security issues discussed above, as isil so wisely suggested. I believe the proper thing to do is to put your my.cnf file in /var/lib/mysql. Thanks for the correction.
--It's Pimptastic!--
This is what I love about the MySQL/PostgreSQL silliness. Postgres is a mostly complete DB, with some things that need work. MySQL is a fast hack with little overhead. For real solutions where you can apply heavy engineering, MySQL's speed advantages can be mitigated away. However, it serves a niche.
If you have a little web site (or even a big one) where integrity is not key and ease of setup is important, MySQL serves that niche. If the improvments don't sacrifice their advantages, go MySQL. However, if they move towards completeness at the expense of speed... well, how silly. We already have Postgres...
Postgres is trying for speed and MySQL for correctness. Yeah... we get two similar systems. I would rather they focus on their advantages so we maintain two useful system.
As a lover of DB theory, I hate MySQL. As a lover of low barriers to entry, I love it. I'd hate to see MySQL lose it's niche by becoming too general.
Alex
Interbase IS a commercial grade database. It has been for years and many companies paid big bucks for licenses until it became open source. It's really really solid.
War is necrophilia.
"Now, would that be Interbase with or without a compiled in superuser backdoor account?"
It took open sourcing to ferret that one out.
Makes you wonder what other back doors exist in all other products too.
War is necrophilia.
Well, it was possible to do something similar in 3.22 with a little hack. Like:
SELECT id, name, ((id*0)+rand()) AS random FROM table ORDER BY random;
Each row will have its own random value in the on-the-fly-generated field "random". And as usual, you are able to perform an ORDER on each field in the result.
- Peter Brodersen; professional nerd
You could already do that before with some trickery :
SELECT something, (RAND()*10) as random FROM table ORDER by random LIMIT 1
the *10 is necessary or MySQL would optimize the request and compute RAND() one time for all rows...
If find the big support for MySQL somewhat surprising. When I'm running a RDBMS, I want stability, even if it happen to be a commercial or a open source one.
MySQL have for many years been been considered as a very unstable product and that it have been (still are probably) lacking a lot of really useful features.
In the open source world, I find two RDBMS especially good. PostgreSQL and Interbase. They are both fine products and probably near commercial grade RDBMS standard.
So even if MySQL is now considered stable. So what? We have other choices, which have been developed with great care for many years and have been added features from their stability, not the opposite.
So in my humble opinion, if you want to run anything (semi)serious, definately do not run MySQL, there are MUCH better options out there.