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."

80 of 359 comments (clear)

  1. what the? by haxhia · · Score: 2, Insightful

    What's the difference about this release and the "non general" release that was announced a while back?

    1. 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
    2. Re:what the? by b0r1s · · Score: 2, Insightful

      Friend finders, using zip -> lat/long data to find people 'near' you (for some arbitrary radius).

      --
      Mooniacs for iOS and Android
    3. Re:what the? by rk · · Score: 2, Informative

      To use the Pythagorean Theorem, you've got to convert lat and lon to some type of Cartesian Coordinate system to really do it right, although at mid-latitudes, it's not too bad a get a relative proximity as long as you're not interested in a real unit of linear measure (kilometers, miles, etc.).

      The planet is round (a geoid, to be a pedantic geography nazi) and lines of longitude are not parallel, although in local coordinate systems you can generally assume they are and not make huge errors. Longer distances, though, will require you to break out the trig, and the pure form of the Pythagorean Theorem is less useful. I find it easier to work such problems in spherical coordinates.

  2. 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 djwavelength · · Score: 2, Interesting

      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.

    2. 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.
    3. 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.
    4. Re:stored procs and triggers, finally by IANAAC · · Score: 2, Informative

      A lot of times (particularly all these tiny web apps), stored procedures aren't needed. But when you have this behemoth of an app (say, AR/AP/GL... very non-trivial stuff), that does the same thing in several different places, stored procedures are a godsend.

    5. 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).

    6. Re:stored procs and triggers, finally by lpangelrob · · Score: 2, Informative
      Yeah, haven't really needed those feature at this company. I've temporarily inherited a MS SQL Server database, so decision time on the fate of that database is nearing (and I don't even consider myself a DBA).

      At my old company, their reservation system relied on advanced database procedures, so they used... an advanced database, namely Oracle. Imagine that. MySQL not necessarily competing with Oracle. Most blogs (small blogs, and wikis) don't need Oracle.

      --
      -Rob

      Biblical fiscal responsibility

    7. 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.

    8. 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/
    9. 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
    10. Re:stored procs and triggers, finally by jferris · · Score: 2, Insightful
      Excellent point. Additionally, putting SQL inside code is just plain bad. Granted, Web Development doesn't have the rigid constructs of deploying a compiled code base where changes to aforementioned statements would require a complete rebuild. Still, it is good practice to include an abstraction of functionality that is not in the code.

      Yes, a lot of developers know how to write SQL Statements. I was a DBA at one point, before becoming a developer. But, now that I work at a company that has a dedicated DBA, I do not write stored procedures or SQL Statements for code use. It is better left in the hands of someone who has a job with Database in the title. ;-)

      --
      You are in a maze of little twisting passages, all different.
    11. Re:stored procs and triggers, finally by xelah · · Score: 3, Informative
      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).
      The OP is presumably referring to work which stored procedures do which isn't part of a query (like running business logic, chopping text about, or god knows what else). Performance wise it /might/ be better to do this on your database machine if it drastically reduces the amount of data sent across a network. Otherwise, IMHO, it's not such a good idea otherwise as you're pushing work into a part of your system which probably can't be spread across machines so easily. (Though you may have other reasons for using SPs than performance, of course.)

      SPs aren't the only way of compiling and optimizing a query once. For instance, caching Perl DBI statements will, for databases which support it, result in the same thing. With Postgresql, for example, DBI will send 'PREPARE blah AS ' before the first execution and then use 'EXECUTE ' afterwards. Unlike SPs these disappear when you drop your connection (and so remain inside the client code and not in the DB).

    12. Re:stored procs and triggers, finally by GweeDo · · Score: 2, Informative

      That was the GP's point. MySQL has gained all this popularity *with out* those features. It wasn't until today that they officially had them.

      --
      Unstable Apps: Our Android Apps Don't Suck
    13. Re:stored procs and triggers, finally by tzanger · · Score: 2, Interesting

      It's funny you should mention that. I have always had FAR more trouble installing mysql than postgres. Always.

    14. 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'.

    15. Re:stored procs and triggers, finally by GregWebb · · Score: 2, Informative

      Not with MSSQL they're not. ANY parameterised query gets its execution plan cached for exactly the same length of time, according to Books Online. SProc or elsewhere makes no difference.

      --

      Greg

      (Inside a nuclear plant)
      Aaaarrrggh! Run! The canary has mutated!

    16. Re:stored procs and triggers, finally by GregWebb · · Score: 2, Informative

      There's still enough ways of injecting SQL into stored procedures - assuming that your app using stored procedures automatically protects you from SQL injection isn't clever, and you should be filtering your input regardless.

      --

      Greg

      (Inside a nuclear plant)
      Aaaarrrggh! Run! The canary has mutated!

    17. Re:stored procs and triggers, finally by wieck · · Score: 2, Insightful

      MySQL AB needed 10 years to add all those features. Being as popular as MySQL is means (among other things) that a huge horde of self taught users had 10 years to learn how to live without them. Don't expect all of those simple PHP scripters to unlearn and improve over night.

      Congratulations MySQL AB. The 5.0 release is a very significant milestone and I am sure, serious database users appreciate the new features.

      Jan

      --
      It takes a real man to ride a scooter ... what are you compensating for?
    18. 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...

    19. Re:stored procs and triggers, finally by jsebrech · · Score: 2, Insightful

      This is the ONLY way to write decent SQL applications?

      In the vast majority of cases, yes. It's stupefying how little mention is made of variable binding / prepared statements in SQL tutorials. Most of the time it not only is more secure, but easier to read (in code), AND faster.

      On the other hand, if you're doing data mining, with the complex queries that go along with that, you start to get a performance hit from the database not knowing the exact values of the variables you've bound (unless you're using one of the higher end databases, which have support for variable peeking). But at that point you probably are not running your queries from a PHP front-end, since that would make DoS all too easy.

      I suppose a large part of the reason for variable binding getting so little mention is that mysql didn't support it for quite a while, at least not in PHP (maybe it did with another API?).

    20. Re:stored procs and triggers, finally by adolfojp · · Score: 2, Insightful

      Lets say that you didn't create the database. Now you must remake every piece of code to work with the data. Lets hope that you didn't make any mistake or miss any important thing that had to be done when processing the data. Lets hope that it is not a banking solution processing my money. Lets hope that you are not working in a group and someone else screws up.

      Use the procedures in your software when something SHOULD be done. Use stored procedures in the database when something MUST be done.

      Cheers,
      Adolfo

    21. Re:stored procs and triggers, finally by einhverfr · · Score: 3, Interesting

      PostgreSQL has another option as well. Views and rules. When I use stored procs for these, I always wrap them in views in order to keep the interfaces clean and portable. YOu can essentially define custom select/insert/update/delete using these tools in ways that are more flexible than triggers though triggers have an edge in other areas.

      I usually think of app structure this way (this is a flexible guide, not a hard map):

      User Interface
      Application Logic
      Data Access
      Data Presentation (views/rules, can include multi-app business logic)
      Data Maintenance (triggers)
      Data Storage (base tables)

      --

      LedgerSMB: Open source Accounting/ERP
    22. Re:stored procs and triggers, finally by bshensky · · Score: 2, Interesting

      Chr!$t almighty! How many layers of abstraction do we really need to code in the name of portability and "enterprise-worthiness"?

      I've done my share of stored proc programming, shell scripting, OO design, and J2EE implementations, and after 15+ years of it, and while all the theory around this appears sound, I continue to see these systems collapse not on their own weight, but the weight of the surrounding corporate IT infrastructure.

      When was the last time you witnessed a project that, with a little nip here and a little tuck there, went from Oracle and iPlanet on Solaris to DB2 and Jrun on Windows? It's never "a little nip here and tuck there". The enterprise ecosystem is too diverse to make it that simple. So why bother in the first place?!?!?!?

      Oh, I forgot...the "consulting" body shops like to push these "enterprise architectures". Gotta migrate platforms due to the latest corporate buyout/merger? That'll be $5.5M, half up front, thank you very much.

      --
      Makin' money, makin' friends, makin' whoopee and wearin' Depends
    23. Re:stored procs and triggers, finally by Jearil · · Score: 3, Informative

      what if '?' = '1; delete employees;'

      Then it would probably return:

      Empty Set (0.00 sec)

      as I doubt any record in field p will actually equal the string '1; delete employees;'

      Now what you're probably thinkog of is setting that ? in '?' to be something like:

      1'; delete employees;

      attempting to escape out of the select prematurely with a well placed ' after the 1. However, using prepaired statements (which is what I believe the GP was speaking of) runs the statement through a parser to set escape characters into the query strings so something like that can't happen. The example above would turn into something like:

      1\'; delete employees;

      which would still yeild a fun result of Empty Set (0.00 sec). You don't actually need a stored procedure for safe queries with user input, just a language that has prepared statements (or heck write you're own version of it if your language of choice doesn't have it, it's not that difficult of a thing to make).

  3. Innovation by chrysalis · · Score: 3, Funny

    Wow, triggers and stored procedures. MySQL really does innovation.

    --
    {{.sig}}
    1. 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
    2. Re:Innovation by iBod · · Score: 2, Insightful

      Although these features may no longer be 'innovative' they take a lot of work to implement and MySQL is giving you that effort for free (as in beer).

      What have you innovated lately?

    3. 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
    4. 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.

    5. Re:Innovation by Jamesday · · Score: 3, Informative

      Adler is a dual Opteron with 16GB of RAM and 6 15,000 RPM SCSI drives. We have two like that, one with 8GB and 15K SCSI, several with 4GB and slower drives.

      Suda, a dual Opteron 4GB box with 6 10K SCSI drives has 8 day uptime and 580 qps average but it's probably been out of normal load quite a bit of that time for various chores.

      Holbach, a dual Opteron 4GB box with 6 10K SCSI drives has 28 day uptime and 616 qps average.

      Ariel, a dual Opteron 8GB box with 6 15K SCSI drives has 8 day uptime after repairs and 1280qps.

      Samuel, the current master, is another 32GB dual Opteron with 6 15K SCSI drives, has 83 day uptime (that is, no crashes or deliberate MySQL server shutdowns for 83 days). Only 367 qps and I think it was not in service for quite a while before it was made master - think we put it into service earlier than planned because of the hardware problem on Adler. It's typically running nearer 2000-4000qps now it is in service.

      khaldun and bacon, both 4GB dual Opterons, one with 10KK SATA and the other with 7200RPM SATA are both down. All are running Fedora core.

      Add up those query averages and it comes to 385 million per day cross 5 servers. We might pass the billion select per day mark this year; hard to predict.

      Some people will say that MySQL is incapable of doing serious work, even though just 5 main database servers are powering a top 100 site delivering 1 in every 1,000 web pages viewed in Alexa.com's sample. Others will say they use MySQL because it gets the job done. Including me. :)

  4. 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!

  5. 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.
  6. 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
    1. Re:Gotchas by GabboFlabbo · · Score: 2, Interesting
      Well from the release email:

      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

      Sound Goood?
  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. Re:Generic Web-Frontends for MySQL by LDoggg_ · · Score: 2, Informative

    OpenOffice 2.0 comes with a database front end application like Access.
    You can create a database with its small embedded hsqldb or connect to an external database like Mysql or postgres.

    You'll need Sun's jvm for this stuff to work as well

    --

    "If they have both, tell them we use Linux. And if they have that, tell them the computers are down." -Dave Chapelle
  9. 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.
    1. Re:Woohoo! MySQL is finally ready! by iBod · · Score: 2, Interesting

      >> I've been running multi-terabyte millions-of-transactions-per-hour database clusters with MySQL for about two years now.

      Are you serious, or was that just a throwaway remark, or a joke?

      I specialize in VLDBs and I'd be really interested in some details if it's actually true.

      Not that MySQL would even be on my radar for such a job, I think you would write a very interesting case study if you are doing what you claim.

      Care to provide any more info?

  10. 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
  11. 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!
  12. It's good, but there's better... by User+956 · · Score: 3, Informative

    It is still lacking compared to other free databases such as PostgreSQL and Firebird, but version 5 is a real improvement. (as mentioned, now you have things like triggers, stored procedures, views and sub-queries.) If you use strict mode integrity checking will work reasonably.

    What I'm currently miss the most in the new version is that it can't handle domains and the ability add check constraints as you create tables is somewhat lacking. So, even if MySQL have done a tremendous job improving their product I would still go for PostgreSQL, or Firbird any day both for technical and legal reasons. Both Postgresql and Firebird also seam to be better at internationalization.

    The fact that Oracle just bought the company that supplies the default MySQL storage engine doesn't spell good for the future. Even though MySQL could continue to use InnoDB in the future under the GPL licence it is in Oracles power to raise the licence fees for commercial use. That would mean less incomes to MySQL AB and that could hurt their ability to develop the product further. However, afaik Oracle have not said anything about raising the prices other than that the licence deal with MySQL is going to be renegotiated in '06. To me that sounds a bit ominous.

    --
    The theory of relativity doesn't work right in Arkansas.
    1. Re:It's good, but there's better... by Fahrvergnuugen · · Score: 2, Informative

      The fact that Oracle just bought the company that supplies the default MySQL storage engine

      InnoDB is not the default storage engine in MySQL... MyISAM is.

      --
      Kiteboarding Gear Mention slashdot and get 10% off!
    2. Re:It's good, but there's better... by iBod · · Score: 2, Informative

      If you're worried about MySQL truncating/rounding numbers, then you should take a few database design classes and learn how to handle numbers and choose the correct data types in your schema.

      "It's a poor workman that blames his tools" - [somebody 1655].

    3. Re:It's good, but there's better... by MourningBlade · · Score: 2, Funny

      If Oracle was thinking smart, they'd make sure InnoDB is free, or at least really cheap.

      MySQL is about the best argument out there for Oracle.

    4. Re:It's good, but there's better... by einhverfr · · Score: 2, Insightful

      Where does that leave *NIX whose basic commands can do terrible damage silently?

      To be honest, I am not a huge fan of the "silence is golden" attitude that UNIX has developed. After all, we have STDERR for a reason. But it does make sense in that the shell was originally designed to be a lightweight scripting engine for system tasks, and that there are different design criteria for an operating system and an information management system.

      However, this being said, PostgreSQL used to truncate strings silently (this was fixed some time ago). The SQL 92, 99, and 2003 specs are very clear about strings and numbers not being truncated automatically unless you do this with an explicit cast-- strings only, such as CAST ('abcd' AS CHAR(2)) will store 'ab'. MySQL tries too hard to do *something* and fails only after having tried things that it should not try.

      --

      LedgerSMB: Open source Accounting/ERP
  13. Big Concerns with MySQL by baggins2002 · · Score: 2, Interesting

    I initially started using MySQL because it was faster than PostgreSQL.
    But now with the involvement of SCO and Oracle in this little project I am looking to write future applications on PostgreSQL or SQLlite. I cannot see any good coming from Oracle's involvement with Innobase or SCO involvement with MySQL.
    I could understand Oracle becoming more involved with PostgreSQL, because I can see PostgreSQL being more of a stepping stone to Oracle.
    SCO well their just SCO, and I don't see them doing anything but creating mischief within the OS community.

  14. 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.
    1. Re:does that mean they fixed the gotchas? by Entrope · · Score: 2, Insightful

      The meaning of that comment in bold is pretty clear: The gotcha list was verified against previous versions of MySQL. The author does not know which still apply to version 5.0. The comment does NOT say that MySQL 5.0 fixes the gotchas; the question "does that mean they fixed the gotchas?" is entirely appropriate.

    2. Re:does that mean they fixed the gotchas? by GabboFlabbo · · Score: 3, Informative
      Well, straight from the email:

      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

  15. Same question I asked when it went beta: by jbellis · · Score: 3, Informative

    Do you get these features in all table types, or do you have to use the (much slower) InnoDB tables, as with transactions?

    1. Re:Same question I asked when it went beta: by perbu · · Score: 2, Informative

      Triggers and procedures are storage engine independant. BTW: in a lot of cases the InnoDB storeage engine is faster.

  16. Re:no x.0 for me... by sethadam1 · · Score: 3, Insightful

    I never get the .0 release of anything ... And I've been wanting to try out Postgres anyway...

    What's the purpose of posting something like this? I am not trolling, I'm just curious why everytime there's a MySQL thread, someone has to chime in about why they won't use it and why Postgres is better. What is the motivation to visit a thread solely to post something negative. Seriously, what is wrong with internet culture?

    So I'm not completely off topic, I read the feature list, and this thing looks fantastic. Views, triggers, sp's, a new data type, BIT, for storing Booleans, which MSSQL has and is AWESOME. You may not want to try it, but some of us are excited to get our hands in it and have been waiting for the first "blessed" release!

  17. 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
    1. Re:Why MySQL is popular by lukej · · Score: 3, Insightful

      I think I am pretty agnostic about the whole Postgres/Mysql love affair. But I do find amusement in the 'personalities' of those supporting both sides.

      Point #3;I always like the standards = portable argument. Reality check:

      a> if somebody writes a huge DB app, standards compliant or not, their going to stick with their base DB
      b> if it is a small DB app, then it's trivial to rewrite if you do want to migrate DBs

      With all my Postgres and Mysql based stuff, I've never rewritten one for the other. Often times I've upgrade the DB or it's host machine. But, with both Postgres Mysql, performance for my (granted, small) DBs is fine with both, the DBs are free, and installing is simple.

      I just don't think DB migration takes place often, even if you can find two compliant DBs!

    2. Re:Why MySQL is popular by einhverfr · · Score: 2, Informative

      Date's Central Rule (attributed to C. J. Date) states that the data integrity (and by extension business rules) should be enforced by the database schema and client applications should not be able to circumvent it.

      In reality, this usually manifests in the idea that what you put into a database should always be what you get out of it, and that your database schema should enforce such things as arbitrary data constraints (CHECK) and referential integrity (FOREIGN KEY).

      --

      LedgerSMB: Open source Accounting/ERP
  18. 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.

  19. 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.

  20. 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
  21. 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?

  22. 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

  23. Current results of the MySQL Gotchas by Anonymous Coward · · Score: 3, Informative

    Results of tests against MySQL 5.0.16-nightly-20051017-log (I downloaded and installed this latest snapshot today)

    1.1. NULL, or when NULL IS NOT NULL
            The behavior was not changed, but it's of no importance anyway.
    1.2. AUTO_INCREMENT
        The behavior was not changed, and I must admit that all that sounds scary. On the other hand we're using a LOT of mysql where I work and never run into a single problem caused by this particular problem.
    1.3. ENUM
            Behavior unchanged - This isn't a real problem at all...
    1.4. Case sensitivity in CHAR / VARCHAR fields
            Weird behavior which might degrade performance, or help you - depends on what you are doing. But I don't agree with the author's suggested solution redefining the table string as binary since you can simply force a binary comparison on the select, so who really cares about this?
    1.5. VARCHAR limited to 255 characters
            This restriction was lifted. Current limit is: 2147483647
    1.6. VARCHAR's trailing blank allergy <= fixed ^^
    1.7. DEFAULT NOW()
            This deficit only affected mysql versions below 4.1 - And I can tell you it didn't reappear in 5.0 ;-)
    1.8. INSERT INTO ... SELECT ...
            Like 1.7 this was only true for versions prior to 4.0.13... Nothing to see here
    1.9. Comments beginning with --
            So ok... comments introduced with -- don't work. As a web developer I never came across having to comment sql inline XD
    1.10. UNION and literal values
            This bug was fixed. Although I ran into a character set problem on this one since the table and mysql defaults were set different and unions are supposed to have the same character set - or maybe I'm just too tired to understand what just happened...
    1.11. Division by zero
            This behaviour is still intact 1 divided by 0 results in NULL ... Doesn't really bother me.
    1.12. 'concatenation' || 'or'
            This "fault" results from not running mysql in ansi mode which makes it overload the || operator diminishing its usefulness.
    1.13. What goes in - isn't (always) what comes out
            Holy shit, a variable range overflwos! If anyone really falls for this - go take a beginner's programming lesson...
    1.14. February 31st
            The behaviour has changed. But since date (as is datetime) is basically a string, I don't really like the kind of checking mysql is now performing O_o I must look into that further since you still can insert some "malformed" dates, but only some of them get changed. What's wrong with that?!?
    1.15. Space between function name and parenthesis
            Although the behavior changed, the author won't be happy with what he sees because it still doesn't behave like his dbms of choice... But if we're honest - this is no bug!

    Now, some things got fixed, some things just changed and most of these don't even matter. All in all 5.0 is a nice release and in my opinion MySQL is still very likable and for me as sys admin quite comfortable.
    What bothers me most at the moment is 1.14. - because that might have some effect on real world situations. Maybe someone else wants too look into this further so I can read about it tomorrow?

    1. Re:Current results of the MySQL Gotchas by jadavis · · Score: 2, Interesting

      Can someone please inform me about InnoDB?

      I heard that InnoDB builds up dead tuples with lots of inserts/updates, sort of like PostgreSQL without VACUUM. Is that accurate? Can someone explain? Do InnoDB tables just keep getting bigger? Is it fixed in 5.0?

      --
      Social scientists are inspired by theories; scientists are humbled by facts.
  24. 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.

    1. Re:Q: using older JDBC connector (LGPL)? by shutdown+-p+now · · Score: 2, Informative

      According to MySQL AB and their interpretation of GPL, any sofware talking to the server using their protocol is to be considered a derived work, and thus has to be GPL'd as well (or you must buy a commercial license). Of course, such interpretation is completely brain-dead, not what GPL is about at all, and would most likely not hold in the court; but then again, IANAL. Either way, they don't want you to use it that way, even if they can't enforce it.

  25. 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?
    1. Re:Some more thoughts by ngunton · · Score: 2, Interesting

      You seem certain that PostgreSQL can use more than one index per query. Well, a cursory search on Google comes up with this page. The "Red Hat Database" is basically PostgreSQL (I think!), and a little way down this page you can see this comment:

      "Note that a query or data manipulation commands can only use at most one index per table."

      Here's another link which seems to confirm this.

      I believe I have seen comments somewhere regarding experimental support for multiple indexes in queries in PostgreSQL, but I am interested as to whether this is a mature technology, rather than new and/or experimental, or limited to special cases.

      Thanks,

      -Neil

    2. Re:Some more thoughts by Anonymous Coward · · Score: 2, Insightful

      You seem certain that PostgreSQL can use more than one index per query.
      Yes, and that is correct. It can only use one index per table, though.

      One of the big ones for me currently is that the query optimizer only uses one index in queries.

      Only one index per query? That would be completely and utterly unusable here. Yeah, PostgreSQL does that better.

      "Note that a query or data manipulation commands can only use at most one index per table." (and multi-column indices are _one_ index)

    3. Re:Some more thoughts by rtaylor · · Score: 2, Insightful

      Technically PostgreSQL 8.1 can merge two scans of single column indexes together into a single table scan. This falls somewhere between a bitmap and regular indexing -- it builds a lossy bitmap on the fly to do all of the inter-column tricks.

      With the beta's I've been taking my multi-column indexes and splitting them up to let the bitmap Index Scan deal with them instead.

      --
      Rod Taylor
  26. Standards woes with MySQL by einhverfr · · Score: 2, Informative

    SQL standard says that "table1" should not be equal to Table1 (ie "select column1 from Table1"). MySQL doesn't respect this

    MySQL is further from PostgreSQL here. The standard specifies that identifiers which are not double quoted should be folded to upper case. MySQL provides no case folding which breaks compatibility with the standard pretty clearly. PostgreSQL violates the standard by folding to lower case (as opposed to upper) which is compatibible with the standard in 99%+ of real world applications (though I am a big proponant of providing the option of folding to upper).

    MySQL supports all operators in the core.

    Right... Except that some (like ||) do different things than the spec says unless you change the mode to ANSI mode. This leads to *very* unportable code.

    Here are some areas under active development in PostgreSQL at the moment:

    SQL/PSM standards support.

    SQL/MED standard support

    As for SQL-2003 compliance, you can see the list of supported and unsupported features at http://www.postgresql.org/docs/8.0/interactive/fea tures.html

    Unless you can point to specific SQL-2003 features that MySQL supports properly and PostgreSQL does not, I call FUD.

    --

    LedgerSMB: Open source Accounting/ERP
  27. sort of by einhverfr · · Score: 2, Interesting

    Strict Mode attempts to solve many of them. I understand that there is a new set of gotchas, but we shall see (MySQL is not my primary RDBMS).

    Strict mode is only a partial solution, however, because applications can turn it off(!) and thus circumvent the protection it affords.

    --

    LedgerSMB: Open source Accounting/ERP
    1. Re:sort of by bedessen · · Score: 2, Insightful

      That, and it's not turned on by default.

      As an analogy, consider the case of PHP and its register_globals setting. Originally this defaulted to 'on' but this puts great pressure on the author of the code to take extra care not to introduce serious security bugs, and it was widely recommended that people disable this and not write scripts that depend on it.

      I guess the PHP developers got tired of being blamed for all the shoddy PHP code out there, so a few years ago they changed the stock default to 'off'. Yet there are still lots of (dare I say POORLY WRITTEN) scripts out there that require register_globals=on despite the fact that this is generally a bad idea. So even today you still find entire servers running with register_globals=on just because some dinky script needs it.

      If that is any indication, it means that many/most MySQL sites will continue to run with strict checking disabled, because a) people don't know any better, b) it's the default, and c) there are probably deployed applications that break with it turned on. And even if they eventually make it enabled by default, people will still continue to rely on the old behavior. Though in this case I think the setting can be enabled per-connection, rather than being a server-wide setting -- but I'm not positive.

  28. 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.

  29. Re:GPL ! BSD by einhverfr · · Score: 2, Insightful

    I know I'm following an obvious rabbit trail here, but I feel it's important. The BSD license may be arguably more vendor-friendly, but the GPL does a better job ensuring the sustained freedom of the code. Besides, there isn't anything in the GPL which makes it difficult to marry GPL'ed code with closed-source code, as long as credit is given where due and the source is available for the OSS portions.

    The only thing that determines the sustained viability of a quality open source project is the size of its active developer community. The license is largely irrelevant as long as it provides the basic freedoms that are the prerequisite for open source software.

    PostgreSQL started out at UCB, and the core team feels that they should respect their heritage by continuing its role as a reference implimentation, available for commercial and foss spinoffs alike. Personally I don't see why that is a problem. They have a much larger active developer community in terms of contributions to the core tree than MySQL, and their core developers are highly educated in the theory of RDBMS's. The core team seems to have loyalties as follows:

    1) To relational and database theory
    2) To the SQL standards (minor issues here, but not too bad)

    If you have a sufficiently large community, proprietary spinoffs will never be able to compete. I would point out that when work on the Win32 port was launched, SRA contributed most of their proprietary work in this area (Powergres) back to the project to jump-start it. You can't compete with Free, so you have to proprietize only what is essential to your differentiation strategy. Failure to follow this rule means increased costs, and decreased competitivity.

    --

    LedgerSMB: Open source Accounting/ERP
  30. So close... almost no longer a toy! by Safety+Cap · · Score: 2, Interesting
    C:\>mysql -V
    mysql Ver 14.12 Distrib 5.0.15, for Win32 (ia32)

    [snip]

    mysql> show columns from foo;
    | Field .| Type . . . . . . . .| Null | Key | Default | Extra
    | id . . | bigint(20) unsigned | NO.. | PRI | NULL. . | auto_increment
    | mydate | date. . . . . . . . | NO.. | . . | . . . . |

    2 rows in set (0.02 sec)

    mysql> insert into foo (mydate) values (0);
    Query OK, 1 row affected (0.09 sec)

    mysql> select * from foo;
    | id | mydate
    | 5 | 0000-00-00 |
    1 row in set (0.00 sec)

    mysql>
    WTF is 00/00/0000, 5cr!pt K!dz Day?

    D'ooh!

    --
    Yeah, right.
    1. Re:So close... almost no longer a toy! by imroy · · Score: 2, Interesting

      You must have a strange definition of "integrity". If you want to insert strange dates and other invalid data into your toy database, go ahead. Every web programmer knows that you do all your checking in the client code anyway, right? But try to run a business like that, just try. In a year your tables will have lots of nonsense entries that you'll have to fix by hand. And foreign keys are only used by fancy GUI apps to draw diagrams, right? No-one uses them to ensure the integrity of their database, hell no!

  31. Re:NOT READY FOR ENTERPRISE USE by kg4czo · · Score: 2, Informative

    They'll say this because they want you to BUY an Enterprise support license for use in Business. Otherwise, if you don't need the extra support, I see no reason to buy a license.