Forgot your password?
typodupeerror
This discussion has been archived. No new comments can be posted.

MySQL Readies Release Candidate For 5.1

Comments Filter:
  • Hosting providers (Score:3, Interesting)

    by Aminion (896851) on Wednesday July 16, 2008 @07:38PM (#24222031)
    Whenever I read about a new MySQL version, I think about all of the hosting out there that are still running 4.x. I understand that you can't simply upgrade to the latest version as it would mess up customers' applications, but how about offering customers different versions of MySQL? Is it really that hard to do? A growing collection of well designed web applications require MySQL 5.x and it sucks to miss out om them simply because your hosting provider isn't database flexible enough.
    • by gfxguy (98788)

      Yeah... I dunno... I mean, same server, different port? It's nice to use the default port for your applications. Or different servers?

      Remember back when we had to do http://www.someplace.com:8080 [someplace.com]?

      Pretty annoying. Of course, it's just one variable or setting in a decent app.

      My first concern, when I read this, was about the license. Wasn't there some kerfuffle about Sun buying them?

    • Re:Hosting providers (Score:4, Interesting)

      by Bill, Shooter of Bul (629286) on Wednesday July 16, 2008 @08:24PM (#24222453) Journal
      Some do. Bluehost asked me when I signed up what versions of popular web tools I wanted MySql 4 vs 5 was one of the options.
    • Re: (Score:2, Informative)

      by mattmcm (1143125)

      1and1 allow you to choose both 4 and 5. You choose the version when creating a database on their management page. I don't know about other webhosts, though.

      • by h4rm0ny (722443)

        1and1 had the worst support and worst financial services department I have ever experienced. I had an extremly bad experience with them and I warn people of them at every opportunity. You're okay with them so long as everything goes by the book. Once you pick up the phone it goes to Hell in a big way. I'm with Fasthosts now who offer MySQL 5.0 by default and then of course there's getting your own server if you really need it. It's pretty cheap as a business cost.
    • Re:Hosting providers (Score:4, Interesting)

      by tinkertim (918832) on Wednesday July 16, 2008 @10:00PM (#24223235) Homepage

      Hosting providers that are worth their weight will typically use external MySQL (clusters) and offer various versions. They've learned the painful lesson that running a bunch of over-allocating services that are open to the world on one box only leads to customers canceling due to down time.

      For instance, the $20 I pay a month gets me access to 4.x and 5.x, each version being its own shared cluster.

    • by SamSim (630795)

      I think about all of the hosting out there that are still running 4.x.

      Hah. I recently discovered that my workplace was actually running MySQL 3.x, which is such an old release that it doesn't even support structured queries. "MyQL", I call it.

    • Re: (Score:2, Interesting)

      by pixr99 (560799)

      A growing collection of well designed web applications require MySQL 5.x

      Let me start by saying that I agree with you. It would be great if hosting providers could give us a bit more choice.

      Now I'd like to disagree with just the bit of your statement that I've quoted. By definition, a "well designed" web application can never *require* MySQL 5.x. Well designed web application have abstraction layers and don't care whether you use MySQL, PostgreSQL, SQLite and so on. If web programmers built a proper abstraction layer into their apps, they could support MySQL x.y along with a

      • by lawpoop (604919)

        Now I'd like to disagree with just the bit of your statement that I've quoted. By definition, a "well designed" web application can never *require* MySQL 5.x.

        Well, I think that's kind of silly. The whole reason of using a later version is to take advantage of features that aren't available in earlier versions. That means that you don't have to code those features into the app -- for instance, you don't have to write a complex query into your app; you can just select from a view in MySQL*. But if you're going to allow usage of earlier versions, you will have to write that complex query in code anyway.

        If you're going that far, why not just write a complete, self

        • Lots of things were introduced in 5.x that would require its use over 4.x. As for writing your own storage back-end, there are a lot of those already too. SQLite comes to mind, along with Zope's object storage which I use extensively in some applications and simple BDB file structures as well.

    • by kv9 (697238)

      Whenever I read about a new MySQL version, I think about all of the hosting out there that are still running 4.x.

      most of our customer sites have 3.x/4.x/5.x available. this has never been a problem. maybe your experiences differ from mine.

    • Actually... (Score:3, Informative)

      by encoderer (1060616)

      ...The only thing that really "breaks" from 4 > 5 is database permissions.

      And most (all?) shared hosting are handling permissions at their admin level by necessity.

      The first time I did this upgrade, probably 2005 or so, I was genuinely surprised at how painless it is.

      And the pain-points that are left are SO worth it. MySQL 4 is a toy. It's worse than Access.

      And we're not just talking about the lack of "advanced" features like triggers, sprocs, udf's. We're talking about no support for things like nested

  • XML Functions (Score:4, Interesting)

    by weston (16146) * <westonsd @ c a n n c entral.org> on Wednesday July 16, 2008 @07:41PM (#24222061) Homepage

    First release with native XML functions. If there's indexing behind some of the XPath, this could be a very interesting release indeed.

    I'd definitely be interested to hear what it's also missing that more XML aware databases include, though.

  • nice feature set (Score:5, Insightful)

    by RelliK (4466) on Wednesday July 16, 2008 @08:04PM (#24222255)

    Traditionally, that is to say, up until MySQL 5.1.22, InnoDB handled newly inserted records into an InnoDB table with an AUTO_INCREMENT column by using a global counter which held the last value for the auto-incrementing column. A lock would be placed on this counter for the duration of the SQL statement which did the inserting...
    The new server variable, innodb_autoinc_lock_mode controls how InnoDB treats statements which insert rows into an InnoDB table with an AUTO_INCREMENT column. Depending on your environment â" specifically, whether you are using the binlog for replication or recovery purposes and whether you are executing "batched insert" statementsâ" you can set this variable to 0, 1, or 2. 0 corresponds to the traditional mode, and is not recommended except for very specific scenarios (see the doc link above). 1 represents "consecutive mode" and is the default. In this mode, only statements where InnoDB cannot determine the number of rows to be inserted will use the global auto-increment lock. All other "simple insert" statements (even those inserting multiple records in batch mode) will use a faster, lighter locking mechanism, which results in significant scalability increases. The final setting, 2, represents an "interleaved" mode and has even greater scalability improvements, but cannot be used in scenarios where the binary log is being used for recovery or statement-based replication.

    So now mysql can handle two concurrent inserts? Nice! Except for the fact that this new amazing option is incompatible with replication. MySQL is going to become a real database. Any time now...

    • Re:nice feature set (Score:5, Informative)

      by Bill, Shooter of Bul (629286) on Wednesday July 16, 2008 @08:29PM (#24222503) Journal
      Not exactly. 5.1 introduces row based replication as opposed to the statement based replication that is incompatible with the new behavior. Statement based replication has the slaves execute the exact same statement on the slave. Row based just passes the new values of the modification to the slave.
      • Is row replication still logical replication or is physical replication now an option as well?

        • I must admit, I'm not familiar with that terminology. Row based replication still uses a log file. more info here [mysql.com]
          • I reviewed the link but still can't tell. It sounds like it is logical replication, meaning that row A in the master looks like row A in the slave, although it may be stored in a completely different relative location on disk. Physical replication means the disk locations are the same, the partition tables are the same, etc. Oracle has both options; I was trying to determine if MySQL is chasing some of the replication features of the bigger guys.

            Anyway, thanks for the link.

            • No, its a physically different disk. There is the federation engine which does reference a table on another server. So if it was a normal innodb table on server A and a federated table on server B that was pointed at the table on server A. That's somewhat similar to what you are talking about, but there is increased overhead as the federated table actually queries the server the table lives on. You could are other ways to tell mysql where a table is stored, but having two servers access the same table on th
              • No, I think I wasn't clear. It is not replication on the same disk. It means that there is byte for byte replication and the way the data is stored on master is the same way it is stored on slave. But master and slave could be thousands of miles from each other.

                • Ok, that makes more sense. I would think then that row level replication is physical replication, but its tough to say. The two databases could use completely different storage engines in mysql. The master could be using innodb tables, and the slave could be csv. So the data would be the same, but it wouldn't be stored byte for byte in the same manner.
      • And anyone who likes to bitch about MySQL deserves an Oracle bill. MySQL (et al) might not be perfect but they are open (improve it) and free (yay I can afford to pay for support *and* still pay for hardware and development!).
        • by Tim C (15259) on Thursday July 17, 2008 @05:52AM (#24225853)

          And anyone who likes to bitch about MySQL deserves an Oracle bill.

          Or they could use Postgres...

          • Which has easy to use multi-server replication now?

          • by msimm (580077)
            Or they could bitch about Postgres...

            There, fixed that context for you.

            Flavor trolls. The subject (and my personal current interest) being MySQL I figured you kids would be smart enough to read the et al as an indication that MySQL is one of a number but I guess that wouldn't get you modded informative.
    • Re: (Score:2, Interesting)

      by Anonymous Coward

      How about we fix the obvious too? This bug [mysql.com] makes it impossible to have an Insert trigger and an update trigger both updating a table. Trying to do so triggers database duplicate keys because there isn't a good lock on the auto-inc value.

      A bug, marked as serious and yet left pending since Feb'07 !

    • "Consecutive mode" sounds like it does the trick though, it determines how many rows you are inserting and makes room for them so other insertions can be made at the same time can be inserted early and be marked as row # after the mass insert. This essentially allows multiple inserts at the same time because it only has to make space for the insert via adding 1 to the increment for the next insert. As long as the slaves support the same feature everything will be fine as the results are deterministic. Only

  • by Anonymous Coward

    Does anyone know of anyone whom MySQL has forced to pay them for their database?

    • by headLITE (171240)

      How would that work, seeing as it is explicitly allowed to use the free version for any purpose?

    • by Jellybob (597204)

      No one who's been forced to, but we have a support contract for our primary DB server.

      That gets us binaries compiled with Intel's compiler (about a 20% performance boost I think), and a shoulder to cry on should anything go wrong.

  • by level4 (1002199) on Wednesday July 16, 2008 @11:26PM (#24223905)

    I would like to use MySQL instead of Postgres - it's easier for me to install, maintain, and just plain understand. I don't like how PG does things a lot of the time and find it needlessly complex. But because MySQL lacks the seemingly basic ability to store a timestamp with better than second accuracy, I can't, because I have to store log events which are often more than one a second - much more - and I need to know exactly when. Milliseconds would be fine, microseconds would be great.

    MySQL currently recommends some ridiculous hack where you strip the sub-second information from the time you send it and store it in another column, then write some kind of view which combines them back. What? I am not doing that to implement what I consider to be basic functionality! Do you remember how my motivation for switching is because I want things to be simple? Writing weird multi-column time recombination hacks is not my idea of simple.

    Replication improvements, XML parsing, great features all - but please just give us timestamps with accuracy better than a second? A lot can and does happen in less than a second and I need to be able to log it with accuracy!

    • If you're going to switch databases over the issue, you might as well consider other options, like Firebird: it's also free, I do believe the timestamps have better-than-second precision (at the very least it insists on showing me 4 extra digits I never use for anything), and it's certainly easier to install, setup, and admin than PostgreSQL (IMO). It has limitations, of course, and you should be careful to read the fine print, as you would with any product selection. I would worry that you're using some particularly esoteric features of PostgreSQL that won't translate well to Firebird, but if MySQL is even an option for you, that's highly unlikely.

      Slashdot declined to carry the story I posted on it (yeah, yeah, grousing...), but Firebird 2.1 (release) came out three months ago, with some really nifty features like on-commit triggers that let you enforce constraints no other database will help you enforce (that I've seen -- Oracle certainly won't.) It rocks.

      Your mileage WILL vary, but I'd recommend at least checking it out. Either http://www.ibphoenix.com/ [ibphoenix.com] or http://www.firebirdsql.org/ [firebirdsql.org].

      • Re: (Score:2, Informative)

        by level4 (1002199)

        I have checked out Firebird in the past and it looks great - but there's a huge chicken and egg problem. Basically, to adopt a DB requires that it be supported in the languages I use - and for scripting this kind of thing I use Ruby. I can't find any firebird support, native or otherwise, for Ruby, let alone support in the more high-level libraries like ActiveRecord or DataMapper.

        I'm not trying to put down the project - it looks great. But I can't possibly afford the time or resources to develop all my own

      • features like on-commit triggers that let you enforce constraints no other database will help you enforce (that I've seen -- Oracle certainly won't.)

        Oracle has deferrable constraints, that are checked on commit, and you can do almost everything you would do with an on commit trigger with deferrable constraints.
        Postgresql has deferrable triggers, that trigger on commit, and that's probably the same feature you call "on commit trigger" in firebird.

      • by jadavis (473492)

        nifty features like on-commit triggers that let you enforce constraints no other database will help you enforce

        Can you be more specific? PostgreSQL offers something called "constraint triggers", which can be deferred until commit time (using the same semantics as deferred FK checks).

        http://www.postgresql.org/docs/8.3/static/sql-createconstraint.html [postgresql.org]

        • Thanks for the link, I hadn't seen those. (I did specify "that I know of", right?)

          Looking at the docs, yes, they could be used for some of the same things as on-commit triggers, in the case of enforcing constraints. You could probably hack them into doing what you needed for on-commit triggers in the general sense, so long as you can find a table somewhere that will always be touched during the session. (Firebird has an on-start-transaction trigger as well, which could help with that, but we're talking abou

          • by jadavis (473492)

            "every master row must have between 5 and 7 child rows"

            You realize that you can't allow concurrent inserts if you have a check like that, right?

            "unless maybe a constraint-trigger is also allowed to modify data"

            Yeah, they can. They can't modify the rows before they are inserted, or anything like that, but you can, e.g., execute a separate INSERT statement.

            I think that both databases offer a lot of tools to developers. I don't know much about FirebirdSQL, but it seems like a pretty good RDBMS to me.

            • You realize that you can't allow concurrent inserts if you have a check like that, right?
              Sadly, very much so, yes. Currently the only constraints that can be verified across transaction boundaries (that is, the constraint can "see" more than the transaction itself can) are primary, unique, and foreign key constraints, and that's mostly thanks to voodoo in the indexing components of the database system. At the risk of being proven wrong again-ish (and this would be a good thing!) I know of no RDBMS that allo

              • by jadavis (473492)

                "Currently the only constraints that can be verified across transaction boundaries ... are primary, unique, and foreign key constraints, and that's mostly thanks to voodoo in the indexing components of the database system."

                It's not that they can see other data, it's that other transactions take out locks. Transactions aren't isolated from the locks of other transactions, obviously. UNIQUE is the only constraint in PostgreSQL that uses "index voodoo", that is, the locks required for a UNIQUE constraint don't

    • "Official" one from Feb 2005:

      http://bugs.mysql.com/bug.php?id=8523 [mysql.com]

      And here's another one going back to Nov 2003, which was strangely marked as a dupe of the above:

      http://bugs.mysql.com/bug.php?id=1764 [mysql.com]

      Should have put those in the original comment; apologies for my laziness.

    • by TheLink (130905)
      Actually if you want to have a better idea of the order events happened, maybe you should use another column where the number keeps incrementing. This is true no matter what DB you use.

      Then you do select * from logs where ... order by time_column, seq

      You say milliseconds would be fine. But maybe 5 years from now when you have a faster system they won't be good enough.

      Whereas having a separate column to keep track of the order might be fine, as long as you and the database does it right, and you only have on
  • Triggers (Score:5, Interesting)

    by iron-kurton (891451) on Thursday July 17, 2008 @12:52AM (#24224385)

    The only thing that I look forward to in 5.1 is the addition of triggers for non-root users. I've fought many a battles with hosting providers wanting to charge me upwards of $120/hr to put my triggers in place as root because MySQL didn't allow regular users to run it.

    Now, finding a hosting service willing to upgrade to 5.1 within a year after it's released is going to be a new bat

    • by gbjbaanb (229885)

      If you're that much of a "power user", you might just get yourself a virtual or dedicated server and do whatever you want without the hassle.

      • by Jellybob (597204)

        He could, but then he's also got to manage that server, instead of doing his real job.

        A managed server is probably the best bet, but they cost big money for anything useful, and you'll probably get some salesman trying to sell you an entire "solution", instead of just what you wanted.

      • Since when does using a fricken trigger make one a "power user."

        Honestly, if I were running a hosting provider, I wouldn't allow triggers just because they slow everything down so much.

        I mean, I use them often, but it's very easy to murder server performance by trying to do too much in a trigger (or doing it poorly).

        IIRC, the MySQL docs say that the most simple trigger adds a 10% overhead.

        • by gbjbaanb (229885)

          a power user is defined as someone who wants to achieve more than the general system readily allows for. So, in this case the OP is a power user - a normal user would accept that root-user triggers are not available, as he wants that power, he's a power user. (in a shared hosting environment that is, I wouldn't call him that if he wanted to add triggers to his own Oracle DB!)

  • by Aggrav8d (683620) on Thursday July 17, 2008 @01:11AM (#24224481) Homepage
    Bah! I misread as "for a final RC for the MySQL 5.1 server, Monty Widenius" and thought it was the latest version name, like Hardy Heron or Fallacious Ferret or Mr. Ed or something.

The typical page layout program is nothing more than an electronic light table for cutting and pasting documents.

Working...