MySQL announced the general availability of MySQL 4.1. Certified by the company as production-ready for large-scale enterprise deployment, this significant upgrade to the MySQL database server features advanced querying capabilities through subqueries, faster and more secure client-server communication, new installation and configuration tools, and support for international character sets and geographic data.
MySQL Version 4.1 Certified as Production-Ready
About The Author
Eugenia Loli
Ex-programmer, ex-editor in chief at OSNews.com, now a visual artist/filmmaker.
Follow me on Twitter @EugeniaLoli
So does it have transactions and views now?
This is truly great. For what I can tell, PostgreSQL has gained a lot of converts lately, but MySQL is still very well entrenched. So, even if you’re a Postges fan, you may have a hard time not having to use MySQL sometimes too. Especially if someone else hosts your website or if you use one of the many programs that require MySQL (like mambo http://www.mamboserver.com at least for now). It’s hard to justify running multiple DBs, so MySQL being “good enough” counts for a lot. Any advances like subqueries help bring it up to speed are ultimately a good thing for everyone. Way to go!
Yet another debate about MySQL vs Every other database feature wise.
*yawn*
I have been using MySQL for the past five years on web sites with, hundreds, thousands and at times hundreds of thousands of visitors. No problem!
MySQL is a very nice database and performs admirably. It doesn’t have the features of Oracle and it doesn’t need them either. Try running Oracle on a forum. It will be slow and it will cost you an arm and a leg.
On the other hand, if you have millions upon millons of records and transactions a la Amazon, then it may make sense to run Oracle.
The replication in 4.0.x breaks at times with error like too long field length or something like that. This requires constant DBA support. If they fixed this in 4.1.x that would be good. In change logs of almost every release they mention that fixed several bugs related to Replication or bin-logs but its still not stable enough to be deployed in DBA-less environments. I don’t know how stable is replication support in other free db’s.
Also I wish I don’t have to issue ‘CHECK TABLE’ and ‘REPAIR TABLE’ queries too often to fix corrupted tables.
I have been waiting eagerly for 4.1 to be stable. It’s convenient to be able to write subqueries instead of always expressing a query with JOINs or breaking a query into several statements. Plus 4.1 does have many nice things like SHOW WARNINGS (so at least MySQL does not really _silent_ly truncating and converting stuffs behind our backs), GROUP_CONCAT(), some new data types (esp. spatial), etc.
I would still wait for several months though until 4.1 matures. I migrated from 3.23 to an early production-release of 4.0 and was having problems with MyISAM corruption. But now not anymore with recent 4.0.
Is MySQL ACID compliant yet?
Does it have transactions?
“Does it have transactions?”
MySQL has has Transactions for quite a long while now.
Go read it for yourself:
http://dev.mysql.com/doc/mysql/en/Nutshell_4.1_features.html
For everybody that didn’t read the changelog or news release, it’s 4.1.7 so it’s been through a few release cycles already. I’m planning on updating tomorrow on a test server.
IIRC, myISAM tables NO, but with InnoDB, YES. (Please correct me if am wrong).
….at least in my own server and pc…. 😛 hehe
You’re right.
FYI 4.1.7 does not mean it has been through a few release cycles. MySQL’s versoning scheme doesnt set assign the minor version number to 0 on a milestone release.
On the subject is MySQL “ready” it depends what you mean by the term. For Amazon.com, no Oracle or DB2 would be a better choice.
The major problem with MySQL is not necessarily transactions or speed but features and scalibility. MySQL even with recent speed increases still does not scale well on transaction applications nor apps that require stored procedures and the like.
In fact, the posibility for not using transactions is a huge advantage of MySQL when dealing with large amounts of data, i.e. massive load of statistical data, when you can easily delete and reload everything if an error occurs better than make a rollback (which will take quite a long time for sure).
And, as Chewy509 says, you can allways use InnoDB tables if you need transactions.
Wow, someone who actually understands what MySQL’s true weaknesses are (and aren’t). I’m a MySQL user for a fairly large project. It was a good choice, not a compromise or a mistake. But of course no DBMS is perfect for all situations. Where we find MySQL requiring more DBA time is in transactional performance, scalability, and maintenance of complex queries made more complex by a lack of SQL features.
That said, it would take an even larger, more complex app than ours to make MySQL a truly poor fit. And no, I don’t just have a large ecommerce site and think that that is complex…
and then it will be a much better database. I’m ho-hum on MySQL now for everything but basic web apps. Database-enforced foreign key constraints are something I don’t see as optional.
I’m using MySQL 5.0.0-alpha right now with InnoDB tables and it supports foreign key constraints. According to the documentation, MySQL has supported them since verion 3.23.44. The key here is that you NEED to make your tables InnoDB tables. You do this by appending type=InnoDB at the end of your create statements on the tables.
Then you need to create the indices, and then you can add the foreign keys with an alter table.
I transferred our entire database off of our Informix database server (9.2) into MySQL (for dev purposes) and did some speed tests. Now, this isn’t a proper comparison, but a query with 2 joins and a subquery took 13 seconds to perform on the informix server and only 800ms on the MySQL server.
Of course, that may just be because we’re using Sun hardware (slow) and an older version of Informix. Can’t wait until our DBA upgrades!
re: You do this by appending type=InnoDB at the end of your create statements on the tables.
Or you can configure your my.cnf/my.ini file to make the default table type InnoDB.
In fact, the posibility for not using transactions is a huge advantage of MySQL when dealing with large amounts of data, i.e. massive load of statistical data, when you can easily delete and reload everything if an error occurs better than make a rollback (which will take quite a long time for sure).
Why would this be the case? With a good MVCC-based transaction system, you’d just have to to a seqscan of the table and its indices to flag dead rows. Certainly this would take less time than reloading the entire database.
This version is only for GPL applications, otherwise you need a commercial license?
A teacher at my school explained that MySQL is fast because of the lack of features. Should it have all features like Oracle DB2 or SQL Server, it would loose it’s edge. Is that true?
Probably true; there’s always a tradeoff between complex and fast.
And it shouldn’t have all the features. If you need Oracle features, why not use Oracle?
MySQL’s niche is in the low to mid section of DBs.
MySQL have had transactions for quite some time now.
However transactions are not a all you need to keep your database consistent. Things like check constraints (have no idea if its supported in MySQL), triggers and stored procedures to build such triggers is very useful to keep your data in shape.
Even though you can compensate for the lack of stored procedures in your applications, it is nice to be able to have your business rules implemented close to the data. It makes the risk of breaking them much less when you modify your applications, net to mention that the SQL like languages used in many database products is far more effective and compact than most programinglanguages used outside of the database.
…when MySQL has stored procedures.
Planned for 5.0
http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html
Is anybody considering using the CA Ingres database ? It has been opensourced but I don’t see a lot of people talking about it (everybody seems to be using MySQL or Postgresql). Has anyone tried it ?
Here are my reasons for using MySQL:
1.) Easy to use and administer
2.) Plenty of MySQL literature (i.e. Books)
3.) It works well enough
4.) It’s relatively fast
5.) Seems to have a simple design (i.e. makes it easier to understand)
I know alot of people push PostgreSQL touting that it’s a better database. I’m sure it is a better database, but migrating from MySQL to PostgreSQL is that much more difficult when there are few recently published books on PostgreSQL. Having a book there to guide me through installation, administration, and usage would be of great help. There are plenty of MySQL books, but few PostgreSQL books.
Honestly, I would love to use PostgreSQL for the license alone. I’m a proponent of the BSD license. I guess for now, I’ll keep using MySQL and when I’m ready, I’ll migrate. Besides, SQL concepts are virtually the same not matter what database you use. It’s only the administration part that’s different, which is what I’ll need to learn to fully migrate.
I guess now I have to see what I need to do in order to upgrade from 4.0 to 4.1. Also, should I be using MyISAM tables or InnoDB tables? Can I migrate from MyISAM table to InnoDB tables easily?
I have not used it yet but I really like the new setup on Windows. Good jobs guys.