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
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.
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.
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!--
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.
"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.
If you have local users, it's in your best interests to upgrade, since
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
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
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
Comment removed based on user account deletion
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
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...
That's also the case when you have two tables, say customers and widgets, and customers can have any number of widgets, including zero. Say you want to display a list of all customers and the number of widgets they have. Implementing it with a simple join, customers with no widgets simply won't show up in the result set. With MySQL there's a way to do it right with left joins. With PostgreSQL so far I've had to use two queries. If there's a way around that someone please tell me. :-)
That's the biggest disadvantage of PG I've found, and it's not *that* big a deal.
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.
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!--
Does Postgres even support LEFT JOIN yet?
... END when SELECTing from tables with 30,000+ rows is very fast (another thing Oracle don't support at all)
The LEFT JOIN is something I wish they (Postgres) would put in, but then, Oracle don't support it either...
The reasons I prefer Postgres are, to name a few:
Transactions
Sub SELECT's
Using CASE WHEN
Postgres used (pre 6.5.3 i think) to have some memory leaks when using transactions, but this seems to have been fixed in later versions.
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!--
The next version of PostgreSQL (in beta testing right now) is supposed to support outer joins, larger row size (8-32K limit currently) and write ahead logging among other improvements.
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
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.