Slashdot Mirror

← Back to Stories (view on slashdot.org)

MySQL 5.0 Now Available for Production Use

Posted by Hemos on from the moving-into-the-big-time dept.

chicagoan writes "MySQL AB today announced the general availability of MySQL 5.0, the most significant product upgrade in the company's ten-year history. The major new version delivers advanced SQL standard-compliant features such as stored procedures, triggers, views & new pluggable storage engines. Over 30 enterprise platform and tool vendors have also expressed enthusiastic support for the new release of the world's most popular open source database."

30 of 359 comments (clear)

  1. stored procs and triggers, finally by cerelib · · Score: 5, Insightful

    I have always been amazed thy MySQL has been able to gain the popularity it has without features like stored procs and triggers.

    1. Re:stored procs and triggers, finally by SpaceLifeForm · · Score: 4, Insightful
      And if the database is not properly protected with constraints, you can screw up the database. And if the schema has to change, we have to hunt down your code and make changes there.

      The best way to manage a database is to only allow applications to modify the database via stored procedures. You'll have far fewer problems that way.

      --
      You are being MICROattacked, from various angles, in a SOFT manner.
    2. Re:stored procs and triggers, finally by User+956 · · Score: 5, Informative

      Almost every database out there impliments an ISO or similar SQL standard as it's base (SQL-92 in most cases). They then build on top of that by adding their own features, while still supporting the common SQL syntax. It's not about being a barebones implimentation of a standard, it's about supporting the standard as your base.

      PostgreSQL supports SQL-92, while adding it's own extra features (which describes most other databases like Oracle and MS SQL too), including the support of the "LIMIT" statement. MySQL doesn't support any standard base, instead existing as an arbitrary mish mash of standard and propritary SQL. It wasn't until the current version, 4, that MySQL even bothered to add support for UNION.

      With every other database you can start working safe in the knowledge that while having it's own extensions, you're working with a normal "SQL" database. MySQL, while posing as SQL, has little if anything in common (in particular see threads about optimization - getting fast code in MySQL means learning an entirely new system filled with quirks and vomit inducing workarounds to solve language faults)

      --
      The theory of relativity doesn't work right in Arkansas.
    3. Re:stored procs and triggers, finally by temojen · · Score: 5, Informative
      And if its in your code, the query executes using the resources of the machine running your code, as opposed to the resources of that (usually) bigged database server.

      This is so wrong it made my head explode. All queries are executed in the server. Stored procedures are compiled and optimized once (per connection, and most sites use connection pooling).

    4. Re:stored procs and triggers, finally by JoeD · · Score: 4, Informative

      With hardcoded SQL, you run the risk of SQL injection exploits, unless you're very careful to escape each and every user-written field that makes its way into a SQL statement.

    5. Re:stored procs and triggers, finally by commanderfoxtrot · · Score: 4, Informative

      You should always use bound placeholders in SQL. Then you don't get SQL injection exploits.

      i.e. define a parameter then execute: SELECT x FROM y WHERE p = '?'

      It is also faster as the DB can use an already prepared query plan.

      This is the ONLY way to write decent SQL applications?

      --
      http://blog.grcm.net/
    6. Re:stored procs and triggers, finally by msuzio · · Score: 4, Informative

      That would be what prepared statements are for. No need to use stored procs just for that.

      --
      It's a strange world -- let's keep it that way
    7. Re:stored procs and triggers, finally by xelah · · Score: 5, Insightful

      That's an excellent argument for having a layer between applications and the data. Stored procedures are certainly a way to achieve this, but they aren't the only way to achieve it. Is a bunch of, say, Java stored procedures all that different to, say, a Java server which exposes application domain methods via CORBA or J2EE (or whatever), is the only way for the rest of your system to get at the database and contains all of the queries all that different? Not really - and the second method has some advantages (like allowing you to run many copies across many computers). IMHO you really do have to think about your system architecture and it's requirements before making a decision like 'everything goes through SPs'.

    8. Re:stored procs and triggers, finally by Anonymous Coward · · Score: 4, Insightful

      C'mon, how can you say that ?

      One of the challenges of MySQL 5 was precisely to get closer to the SQL:2003 standard. And it did.
      Consider the MySQL stored procedures for example : their syntax is probably one of the most respectful of the norm today. And that effort was also made for all the other new funcionality of MySQL 5.

      Now since you're talking about the past flaws of MySQL, you shouldn't confuse the absence of a functionality with the proprietary implementation of that functionality.
      It's true that until 2 years ago or so MySQL didn't support UNION but when it did it was in a standard-compliant way. But as far as I know MySQL has never had such a proprietary approach as the one Oracle had to outer join syntax for years for instance.

      Concerning the LIMIT statement, it is proprietary syntax because there is no equivalent for it in the SQL standard ! By the way you won't find two RDBMS that implement it the same way...

      So don't tell us MySQL is one of the less standard-respectful databases because it's just not true. It might not be the most SQL standard-compliant because it lacks standard functionality but what is implemented is fairly normative.
      And don't come arguing that MySQL should implement "all of the standard or none of it" because you know pretty well it is not possible for a young RDBMS like this...

  2. Well done MySQL AB by iBod · · Score: 4, Insightful

    It's not the fanciest, or the fastest, but it's ubiquitous and free!

    I for one have found it invaluable on many projects where a full-featured, high-capacity RDBMS would have been more trouble and expense than it was worth.

    Props to MySQL!

  3. Well this is neat by lewp · · Score: 5, Insightful

    No matter if you're a MySQL supporter or someone who thinks that everyone should use a "real" RDBMS, having all these new features available to MySQL developers is a good thing. There's quite a few apps, I'm sure, that don't use these features in databases where they're available simply because they're aiming for the lowest common denominator that was MySQL's feature set.

    Anyway, not trying to start an argument about the relative merits of any particular RDBMS, but this is a good thing all the way around. I look forward to taking it for a spin.

    --
    Game... blouses.
  4. Gotchas by Ed+Avis · · Score: 4, Interesting

    It would be cool if someone knowledgeable could check the old MySQL Gotchas list and see how many have been fixed in 5.0. My hope is, nearly all of them.

    --
    -- Ed Avis ed@membled.com
  5. Re:Innovation by choas · · Score: 5, Funny
    So there I go, looking up stored procedures on Wikipedia and it gives me this:


    Wikipedia has a problem

    Sorry! This site is experiencing technical difficulties.

    Try waiting a few minutes and reloading.

    (Can't contact the database server: Lost connection to MySQL server during query (10.0.0.101))


    Well at least I now know you're not a troll and it DOES gave something to do with MySQL ;)

    --
    I will work to elevate you, just enough to bring you down
  6. Re:what the? by b0r1s · · Score: 4, Informative

    'General' implies usability in production systems. What you really want to read it as is this is the first non-beta release.

    We tested many of our sites (including my personal favorite, vobbo, a site for video blogs) and found some very significant speed improvements, especially in some of the math functions (SIN, COS, etc).

    --
    Mooniacs for iOS and Android
  7. Re:Almost caught up to MSSQL! by Anonymous Coward · · Score: 5, Interesting

    With all due respect, SQL2K has been one of the most stable databases I've ever worked with. Sybase was a close second, Oracle was fine once you got it installed. Say what you will about their consumer products, but MS can make some damn fine products *when it wants to*.

  8. Woohoo! MySQL is finally ready! by philovivero · · Score: 4, Interesting

    I've been waiting for years for stored procedures, triggers, and... ah. Wait a minute. No, actually, I've been running multi-terabyte millions-of-transactions-per-hour database clusters with MySQL for about two years now.

    Well. Anyway. Now all the little shops that have been making excuses about why not to use MySQL can now start using it.

    (In fairness, actually, yes, the MySQL gotcha's page scares me, too)

    --
    fifth sigma, inc.
  9. Re:Generic Web-Frontends for MySQL by FireFury03 · · Score: 5, Informative

    This is slightly off-topic, but I was wondering if anyone is aware of any generic web-frontends for MySQL?

    How about http://www.phpmyadmin.net/?

    --
    http://blog.nexusuk.org
  10. phpMyAdmin by smelroy · · Score: 4, Informative

    I'm not sure about reporting specifically, but phpMyAdmin is the way to go for a generic MySQL front end.

    --
    Switching to Linux can be an adventure!
  11. Re:Innovation by b0r1s · · Score: 4, Informative

    Postgres was free ('as in beer') and free ('as in a real license'), and gave away these features long ago.

    Besides, for 'freedom', the BSD license used by Postgres beats the GPL hands-down.

    --
    Mooniacs for iOS and Android
  12. does that mean they fixed the gotchas? by RelliK · · Score: 4, Interesting

    Any word on when they are planning to fix this? With this careless disregard for data integrity, it's hard for me to take MySQL seriously.

    --
    ___
    If you think big enough, you'll never have to do it.
  13. Why MySQL is popular by einhverfr · · Score: 5, Interesting

    I stopped using MySQL as my primary RDBMS in 2000 (I still use it when apps require it, but I almost never program for it.

    When I started using PostgreSQL 6.5, I noticed that it was *far* harder to use than MySQL. It had a *huge* learning curve and was missing obvious functionality such as alter table drop column. But it provided better data integrity checking than MySQL. So for the next two years, I would prototype databases in MySQL before moving them over to PostgreSQL.

    MySQL was good enough for simple CMS type tasks and extremely user friendly at a critical time in the market. PostgreSQL, designed for enterprise apps from the beginning, placed technological soundness ahead of ease of use. However, over the last five years, PostgreSQL has actually become the simpler RDBMS to use and program for. No questions of "I misspelled InnoDB and now it created a MyISAM table instead" or such.

    Unfortunately, it seems that by the time PostgreSQL became easy to use, MySQL already had cornered the low-end market. However, I would say that aside from light-weight CMS tasks, PostgreSQL is still far and away the better application for a number of reasons:

    1) ACID compliance is pervasive throughout the engine. Creating operations outside a transaction, while possible, requires an untrusted programming language (like C, PL/PerlU, PL/PythonU, etc).

    2) Date's Central Rule is designed into the RDBMS and cannot be circumvented by the application (which is not the case in MySQL 5.0 as strict mode can be disabled by an application).

    3) PostgreSQL, while not perfectly standards-compliant, is far more standards-compliant than MySQL. This allows for much more portable code to be written for PostgreSQL than MySQL.

    4) PostgreSQL is much more extensible than MySQL. You can add language handlers to allow you to create stored procs in whatever languages you want. PostgreSQL currnetly ships with PL/PGSQL, PL/Perl, PL/Python, PL/TCL. Other languages, such as PL/PHP, PL/Java (or PL/J), PL/SH, and PL/R are available as addons. I believe there is an attempt to make Mono available for stored procedures. Also you can add new data types without too much difficulty.

    5) PostgreSQL has better Business Intelligence capabilities than MySQL. Capabilities include table partitioning and more. Parallel queries (across nodes) are under development in a spinoff project called Bizgres.

    --

    LedgerSMB: Open source Accounting/ERP
  14. Re:Almost caught up to MSSQL! by TheKubrix · · Score: 5, Informative

    I hope someone mods the parent down, because thats just stupid/ignorant.

    I've been running MS SQL 2000 for about 4 years now and it has NEVER crashed. Nor has it corrupted any data or any other such destruction.

    I notice that its people that either have _NO_ database experience tend to bash MSSQL, and they don't even know why. Your comment is a case in point.

  15. Quite a news day by dtfinch · · Score: 4, Funny

    NASA drops the whole "shuttle" idea. Andy releases a new version of Minix. DrDOS steals from FreeDOS. And MySQL becomes a real database server.

  16. Now with SAP... by MosesJones · · Score: 4, Interesting


    The biggest thing here isn't the stored procs et al... its that SAP, you know the worlds biggest enterprise software vendor... will CERTIFY its application on MySQL (when using the old SAPdb stuff). This means that organisations that spend MILLIONS on SAP systems can get support if they run it on OSS.

    That is the big deal, not functionality its about the support. MySQL might be the poor relation to Postgres in terms of functionality, but MySQL has a MUCH big best friend who can open doors where functionality doesn't count.

    This is a real moment IMO, as a well known OSS database has a massive seal of approval from one of the most famous for reliability vendors in the market.

    Next time your boss says that OSS can't do a DB, tell him that SAP disagrees.

    --
    An Eye for an Eye will make the whole world blind - Gandhi
  17. Unofficial slashdot MySQL thread checklist by jonfelder · · Score: 5, Funny

    1. I love MySQL!
    2. Who cares? Postgres is and always has been better.
    3. I used to use MySQL, but now I don't.
    4. I used to not use MySQL, but now I do.
    5. If you use MySQL you are stupid.
    6. If you do not use MySQL you are stupid.
    7. Only Nazis and CowboyNeal use MySQL.
    8. Did anyone say goatse.cx?

  18. Some thoughts by ngunton · · Score: 4, Interesting

    I've been using MySQL for about six years now, and it's been working very well for me. I utilize it on my crazyguyonabike.com, a bicycle tour journals website. It has about 750 journals on there, with over 60,000 pictures. I use replication to back up the database remotely, and all in all it works very well. I honestly can't understand the level of hatred towards the tool that emanates from many of the posts here.

    I have to say that I cringe every time I see a MySQL story on slashdot these days, because it just seems like there is a legion of PostgreSQL zealots just waiting for any chance to denigrate MySQL. It's the same littany every time - PostgreSQL is so much better, have they fixed the "Gotchas" yet, etc etc. Even when MySQL AB adds a feature or does fix some perceived failing, then the detractors simply ignore this and move on to some other apparent showstopper. For example, it's not enough that MySQL has transactional capabilities - no, now they simply moan that it's not the default (MyISAM still is).

    We seem to have people who have what can only be described as a religious mindset when it comes to these issues. "Religious" in the sense that their minds are closed, and no matter what new facts come to light, they will simple twist everything around to match with their existing worldview. So, in these people's minds, MySQL AB adding features is not a positive thing, it's rather a sign of how wrong Monty was in the past to suggest that most people really don't need transactions for everything. Well, at what point exactly do we have "proof" that I don't really need transactions for my website? Is six years of 24/7 use enough? If not, then how long exactly?

    Yes, I've had problems, of course I have. You will with any tool, PostgreSQL included. No matter the fact that PG has had transactions from day 1, people still got corrupted tables occasionally. But at the end of the day, the results are the same - do you still have your data? Is it intact and internally consistent? I can answer yes to that. I don't mind having some logic in my application to delete some records when some other records get deleted. It works really well, and while in theory it could cause data inconsistency, in practice this has never happened. Even if it did, a quick perl script would be sufficient to clean things up - I'm doing that kind of thing all the time anyway, as the database evolves and I need to shift stuff around or change table structures. It's no big deal, really! Some will say No, this is a Horrible Solution and you should put business logic into stored procedures... I say, get a life. That's *your* solution, it's not everybody's. You're simply moving your complexity around, you'll never really get rid of it. Some people are more comfortable with their complexity in stored procedures, I'm perfectly comfortable with it in my Perl application. So what, does it work for you? If so, then who cares.

    There *are* some things in MySQL that disturb me, but I don't know if they are common to other DBMS solutions out there. One of the big ones for me currently is that the query optimizer only uses one index in queries. I know you can have multi-column indexes, but I still see this being a problem for some of my more complex queries. Does PostgreSQL do this better? Informed opinions please, rather than fanboy noise.

    Also, speed. I hear lots of anecdotal tales about how much faster PostgreSQL is these days, especially under load from multiple connections. I'd like to hear from anybody who has actually made a transition from MySQL to PostgreSQL for a high-load Web application. Can PostgreSQL really replace MySQL now? Or is this another case of wishful thinking?

    Thanks,

    -Neil

  19. Re:Innovation by Jamesday · · Score: 4, Informative

    10.0.0.101 is Adler. Its uptime is currently 2017391 seconds (23 days). Adler's uptime is that short because it had a hardware repair. It was probably overload - several DB servers are dead right now and Monday is the busiest day for the site. So far the site is consistently filling to capacity all the hardware which is ordered and that shows no sign of stopping. It's now at 4500 pages per second, 400 megabits/s. For scale, the biggest Slashdotting the site saw was about 650 pages per second.

    Averages over 23 days for this one server: 1620 selects per second, 10 inserts and 3 replaces per second. That is: 140 million selects per day average. Peak rates are about double average rates, typically in the 3000-5000 qps range.

    I'm one of the roots at Wikipedia. Figures from SHOW STATUS just before typing this reply.

  20. Q: using older JDBC connector (LGPL)? by MarkWatson · · Score: 4, Interesting

    I have a question: if I use the older JDBC connector (from June 2002) before the connector project was absorbed by MySQL and became GPLed, is it OK to use MySQL on a leased server with a Java web application that is not GPLed?

    That is, if my web application links with the old LGPLed connector which uses a socket connection to the GPLed MySQL server, then that is fine license-wise, right?

    This is a question for all the 'Slashdot lawyers' :-)

    Seriously, from reading the licenses, I believe that the scenario that I mentioned using the older LGPLed JDBC connector is OK, while using the newer GPLed JVBC connector(s) is not.

    Also: I believe that this is not an issue with Ruby since the client MySQL connector is not GPLed.

  21. Some more thoughts by Just+Some+Guy · · Score: 4, Interesting
    It's the same littany every time - PostgreSQL is so much better, have they fixed the "Gotchas" yet, etc etc.

    I also cringe whenever a MySQL story comes out because it seems like the conversation devolves into two opposing opinions:

    1. Database administrators who understand DB theory, have managed terrabyte servers, and know what a real database looks like. This group hates MySQL.
    2. People who used MySQL to implement a tiny pet project successfully. This group loves MySQL.

    People in the latter group don't understand why anyone would dislike it - after all, their home-written blog software renders DB-backed pages in less than five seconds.

    People in the former group can't imagine why anyone would put up with its many, many shortcomings when other faster, more capable, more Free databases are widely available. They don't understand why some people wouldn't want to use the best tool for the job when there's no legitimate reason in the world not to.

    One of the big ones for me currently is that the query optimizer only uses one index in queries. I know you can have multi-column indexes, but I still see this being a problem for some of my more complex queries. Does PostgreSQL do this better?

    I'm migrating my companies data from an old FoxPro setup to PostgreSQL. I don't have the option of normalizing the data (it would break too much legacy code, although I might look into making backward-compatible views sometime down the road), but selective indexing on columns (and functions on columns!) made 20-table joins work astoundingly well. Only one index per query? That would be completely and utterly unusable here. Yeah, PostgreSQL does that better.

    --
    Dewey, what part of this looks like authorities should be involved?
  22. Re:Almost caught up to MSSQL! by orabidoo · · Score: 4, Interesting

    well, I did do a large project with MSSQL, and while it didn't crash or fail spectacularly the way other Microsoft products tend to do, it did have a few issues with locking.

    specifically, it had an overly complicated strategy of automatically escalating types of locks (row-level, page-level, table-level, etc), the end result of which was that you never quite knew what was going to happen. I did have a rather fun bunch of hours tracking down transaction deadlocks that should not really have ocurred with a better engine.

    the result of it all was that it made me realize how much better MVCC databases (which are able to hold more than one version of a record at a time, and show each client the appropriate version of the universe) are than the ones based on simple locking and exclusive access. on a non-MVCC database, an open transaction which has modified a row will freeze any other client that attempts to read it! imagine how happy your users are when all their front-ends stop working just because one user's computer crashed at the wrong moment.

    AFAIK, all the major open source transactional db engines are MVCC: PostgreSQL, MySQL+InnoDB and Firebird are (dunno about SapDB, Ingres and the various Java engines).

    in the proprietary world, Oracle does MVCC, but Sybase and DB2 don't. apparently the next version of MSSQL will have some sort of MVCC support too.

    btw, all this talk of database independence ("it's all SQL dialects anyway") is an oversimplification in the real world. MVCC or not is actually a big deal in how a database application is engineered. as soon as you want to do anything sightlycomplicated in your transactions, and maintain integrity in the face of multiple clients, you have to think hard about locking, and start using things like "SELECT ... FOR UPDATE". at that point, the code you write will depend heavily on whether your database is MVCC or not.