Slashdot is powered by your submissions, so send in your scoop

 


Forgot your password?
Close
typodupeerror
×
Databases Programming Software IT

MySQL 5 Production in November 286

Posted by Zonk from the new-way-to-play-with-old-toys dept.
thatoneguyfromphoeni writes "CIO.com is reporting that MySQL AB is eyeing Nov. for the production release of MySQL 5. 'The company is calling version 5 its most significant upgrade yet. It adds a handful of features considered important for enterprises that have long been available from market leaders Oracle Corp., IBM Corp. and Microsoft Corp. Chief among them are triggers, views and stored procedures.'"
This discussion has been archived. No new comments can be posted.

MySQL 5 Production in November More

MySQL 5 Production in November

Comments Filter:
  • http://developers.slashdot.org/article.pl?sid=05/1 0/05/2041240&from=rss [slashdot.org]

    Different link, but same idea anyway.

  • MySQL has finally caught up (Score:5, Insightful)

    by Michalson ( 638911 ) on Thursday October 13, 2005 @01:16PM (#13783358)
    MySQL has finally caught up to the state of the enterprise relational database industry...as it was in 1999. Points for effort, but everyone else is still ahead with core features like integrity, leaving them free to build on new and better features. Bundling with PHP will only get you so far.
    • Yup, and meanwhile PostgreSQL is prepping an 8.1 release with shared row locking, table partitioning, and better SMP support. Draft press release is here [pgfoundry.org].

      Anecdotally, RubyForge got 240K hits yesterday on a GForge site backed by a PostgreSQL 8 database with no problems; good times. PostgreSQL is good enough that our problem is bandwidth, not server load [blogs.com].
    • I thought there was supposed to be an endlessly moving target so that you could continue bashing mysql because it's fashionable and PC.

      It doesn't matter what feature set actually mysql has, it's just hip to bash it just like it's leet to bash linux when you're a (free|open|net)bsd fanatic.

    • No, it hasn't (Score:2, Insightful)

      by Anonymous Coward
      1. MySQL still requires you to choose a particular (non-default!!) table type in order to have ACID
      2. MySQL still has non-standard syntax and semantics
      3. MySQL still has a non-standard and broken user/permission system
      4. MySQL Still doesn't have nested queries

      I was using all of these in PostgreSQL in 1999.

      • MySQL still has non-standard syntax and semantics

        What 'non-standard' syntax are you talking about? Something like postgres' allowing LIMIT support in SQL statements? What 'standard' does that follow?
        • Its handling of NULL, for starters.

        • Re:No, it hasn't (Score:4, Interesting)

          by Michalson ( 638911 ) on Thursday October 13, 2005 @03:48PM (#13785073)
          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)

        • Re:No, it hasn't (Score:3, Interesting)

          by drdink ( 77 ) *
          Try reading this [arvin.dk] to understand just how broken MySQL (and others) is.

        • Re:No, it hasn't (Score:3, Interesting)

          by jadavis ( 473492 )
          Silently truncating strings inserted into a varchar(x) field.

          Allowing dates like Feb 31st.

          Truncating numbers silently.

          Strange NULL handling.

          A lot more.

      • Re:No, it hasn't (Score:3, Informative)

        by prockcore ( 543967 )
        MySQL Still doesn't have nested queries

        Yes it does. It has for 2 years now.

      • Re:No, it hasn't (Score:5, Interesting)

        by jadavis ( 473492 ) on Thursday October 13, 2005 @04:55PM (#13785802)
        MySQL still requires you to choose a particular (non-default!!) table type in order to have ACID

        And with Oracle's purchase of InnoBase, maker of InnoDB, there is a reasonable chance that MySQL will end up dropping the InnoDB storage engine. After all, since InnoDB is GPL, MySQL AB only has a right to distribute InnoDB under a commercial license because of a contract with InnoBase. Here are the possible outcomes:

        (1) Oracle renews the contract, and nobody worries until next time it comes up for renewal.

        Unlikely. Why do you think Oracle bought InnoBase? Not to be nice, that's for sure.

        (2) Oracle doesn't renew the contract, MySQL continues development, and drops it from their commercial version.

        Approximately 0% chance of happening. What's in it for MySQL aside from the bad PR of having a commercial version worse than the GPL version?

        (3) Oracle doesn't renew the contract, MySQL drops support for InnoDB, and the MySQL community forks, and MySQL develops another storage engine.

        Could happen. I have my doubts that would go very smoothly in the timeframe allowed. Most likely some functionality would change, breaking compatibility with existing InnoDB applications.

        (4) Oracle doesn't renew the contract, MySQL drops support for InnoDB, and the MySQL community forks, and MySQL doesn't develop another storage engine.

        Seems likely, although they lose ACID and foreign keys, which are the most important "Enterprise" features of all.

        So, it seems pretty much like #3 or #4, neither of which is good for the MySQL users. Expect MySQL AB to start preparing by warming up to BerkeleyDB to see if they can make that storage layer work. Expect MySQL AB to start spreading propoganda about how foreign keys and ACID really aren't necessary and only slow the database down (they can just un-rewrite some history and the propoganda is already there!). And expect them to try to make something out of SAP DB / MAX DB in a hurry. They'll try to get MySQL 5 out ASAP so that the impending problems with InnoDB don't take steam away from their release.

    • only so far, indeed (Score:3, Interesting)

      by vena ( 318873 )
      I mean, I never see a host offering MySQL. all they offer is PostgreSQL!

    • MySQL has finally caught up to the state of the enterprise relational database industry

      One of the things that is missing in most if not all databases (AFAIK) is an effective mechanism to deal with data dependencies. A data dependency exists whenever two or more applications of components share read/write access to the same records in a database. We need a simple to use mechanism that will notify relevant applications of changes in a shared record. Resolving dependencies in a timely manner is extremely impor
    • everyone else is still ahead with core features like integrity

      I've heard a lot of complaints regarding MySQL integrity. For the record proper transactions have been around for ages, and 5.0 has largely corrected the remaining integrity problems with triggers and the Server Mode variable [mysql.com] (options to prevent the insertion of 'bad' data). IMHO, it's the most important new feature. Why they haven't made a bigger deal about it is beyond me...

      And we need to stop saying "everyone is ahead of MySQL" when th
      • To anyone who thinks Postgres is better across the board, I say 'vaccum'.

        AutoVacuum

      • For the record proper transactions have been around for ages, and 5.0 has largely corrected the remaining integrity problems with triggers and the Server Mode variable (options to prevent the insertion of 'bad' data).

        Why oh why are not many of these new modes not on by default? For example:

        • STRICT_TRANS_TABLES: Why would you ever want to insert bad data? If you try to insert bad data, your insert should fail. (where "bad" is defined in several ways, such as "violates a foreign key, check, or unique
    • MySQL has finally caught up to the state of the enterprise relational database industry...as it was in 1999.

      1999 is a bit late for these features to be state of the art -- more like mid 80s I'd say. But it doesn't matter. Phrases like "Ready for the Enterprise" is a slogan, not a measurable property. There are many small enterprises (or parts of large enterprises) that muddle along with capabilities that are primitive by 1999 standards. Primitive 1999 MSQL stanards: I'm talking all those unforutnates w
    • Quite.

      I've had the dubious privilege of attempting to convert a web app that was originally designed to use Microsoft Access (don't laugh, it's a surprisingly capable performer for web applications provided you use the OLEDB driver) to both postgreSQL and MySQL. On the whole, the postgreSQL port was relatively painless (apart from the fact that the ODBC driver sometimes returns spurious null rows) and allowed some of the application logic to be simplified by making use of triggers and stored procedures. The
    • So what--the current version of SQL Server is vintage 2000.
    • Not quite yet. It is missing point in time recovery, robust replication, replication of subsets, failover and clustering (with on disk table types), multi version concurrency, domains, and a dozen other features. I haven't checked to see if it has nested transactions yet either.

      Don't get me wrong, I think it's great they are adding features and catching up to firebird, postgresql, ingres and other open source databases. They really should tackle replication next though, right now only ingres has comprehensi
    • The thing I would really like to see is a way to in a fool proof way, move databases from MySQL to Postgres and have all of our PHP scripts (not custom, some commercial, some custom, and none with time for us to go and find every little change required) just work.

      That's one thing I LOVED about Tango (now WiTango). It was a RAD web app development system that for the most part was 100% database agnostic. I know many others are too, but PHP is my favorite as a language. And I know if all the scripts we used w

  • Just in time... (Score:5, Interesting)

    by Martin Blank ( 154261 ) on Thursday October 13, 2005 @01:17PM (#13783361) Homepage Journal
    Sneaking in just in time before MS SQL 2005 can get out the door (or perhaps just after) is good for this.

    I recently showed the latest rev to the SQL devs here, and they were most impressed. Most of the complaints about it were gone; the new GUI is miles beyond what they had before, and the new features (views, stored procedures, better VARCHAR support) have people thinking that for smaller projects, MySQL will work out just as well as MS-SQL, and at a fraction of the cost, if any cost at all.
    • Suppose I create a table:

      create table my_table(
      id int autoincriment,
      fk1 int references other_table (id)
      ) type=innodb;

      Hmmm... Foreign keys are not enforced (with no warning issued by MySQL as to this behavior) but if I do

      create table my_table(
      id int autoincriment,
      fk1 int,
      foreign key fk1 references other_table (id)
      ) type=innodb;

      Now they are enforced. Why the difference?

      If I do a lot of inserts/updates/deletes on an innodb table, how do I recover space?

      If you want a real RDBMS at a fraction of the cost or no

  • Still Not an Enterprise Solution (Score:3, Insightful)

    by WhoDey ( 629879 ) on Thursday October 13, 2005 @01:29PM (#13783490) Homepage
    As much as I love (and use) mySQL, it's still not, nor will it likely ever be (never say never) an enterprise solution. Prove to me that mySQL is robust enough to be the backend service for a major bank's mortgage application, for example. It's simply not. As a previous poster already mentioned, mySQL has finally caught up to the base set of features that all major DBMS's had years ago. Now, after that rant, I will say this. mySQL is great at what it's designed to do. I use mySQL as the backend for personal websites and applications. It's (relatively) lightweight, simple, easy to administer, and, best of all, free as in beer (not withstanding products purchased from mySQL AB). So before you get all huffy about what I said in the first paragraph, just remember that mySQL is great at what it's made for, it's just not made to be an enterprise solution.

    • Re:Still Not an Enterprise Solution (Score:5, Insightful)

      by gtoomey ( 528943 ) on Thursday October 13, 2005 @01:46PM (#13783662)
      You can't compare Oracle (a Boeing 747) to MySQL (a Cessna). But there are a lot more Cesnas sold than Boeings.

      There's a very big niche for smaller DBMSs. Plus MySQL tends to be fast and the feature set is growing.

      • "You can't compare oracle (a Boeing 747) to MySQL (a Cessna)."

        I think a better comparison would be:

        Oracle compares to PostgreSQL as

        MS Access compares to MySQL.

        Although grant it..with the new things they're adding...MySQL is now steps above MS Access.

      • You're right, and it's fine for MySQL to be a Cessna.

        But for many years, MySQL proponents have, in blatant defiance of the facts, claimed that their Cessna could seat 275 passengers and cross the Pacific in one hop. If MySQL lacked a feature, proponents claimed the feature was unnecessary fluff. As soon as the feature was added, they announced that "MySQL is ready to run with the big dogs." When the features in question were utterly fundamental to effectively exploiting the power of the relational databa
    • Yes, mostly mainframes, but I've no doubt that some industries were running
      "enterprise" apps 5 years ago on platforms that aren't as robust as MySQL5 is now. Yes, software has become more demanding in the past few years, but the fundamentals haven't changed. If you could run 'enterprise' solutions on SQL Server 6.5 (and I saw companies doing it - and gosh, they didn't even have row level locking!) surely some "enterprise" industries can use MySQL5 today.
      • Row level locking isn't mandatory for scalability, performance and even less necessary for reliability. Stop reading the Oracle marketing :)

        As for 'enterprise' apps - believe me when I say many industries are running their serious enterprise apps on platforms that *are* much older than 5 years old (remember the y2k 'bug', think of all those COBOL programs that are running... 30 years old and still going. I'd say something that runs for 35 years is the very definition of robustness).

        The fundamentals are the
        • I completely understand that row-level locking isn't a mandatory item - I'm not reading the Oracle marketing either! :)

          What struck me about this was that I worked at a company in '98 using SQL Server 6.5 for most work. I was asking if we could use MySQL for some projects - we were doing unix/perl and NT/ASP. The NT devs all got to use SQL 6.5, and the Perl guys were only allowed to use flat files, no SSI on Apache, etc. Gave the NT devs an upper hand in being able to be more productive.

          When I'd asked abo
        • Actually, if you're going to use read-locking for ACIDity then row-level locking pretty much is mandatory unless you're completely read-only or single user.

          Note that this has nothing to do with the row-level shared locks that PostgreSQL added.

          Personally, I think MVCC is a far superior approach than read locking.

      • Re:What were banks using years ago... (Score:4, Informative)

        by kpharmer ( 452893 ) on Thursday October 13, 2005 @03:14PM (#13784646)
        > Yes, mostly mainframes, but I've no doubt that some industries were running
        > "enterprise" apps 5 years ago on platforms that aren't as robust as MySQL5
        > is now.

        Ah, good question. Here's how to look at this:

        1. mysql is just now in v5 putting in pieces that most commercial products had 10-20 years ago:
                - views (been around since something like 1981 in db2 & oracle)
                - triggers (been around since around 1995)
                - subselects (been around in db2/oracle since 1981 or so, in mysql for what? 1 year?)
                - transactions (been around in db2/oracle since around 1981, in mysql via innodb for 2 years)
                - online backups (been around 10 years? mysql still requires a separate product)
                - stored procs (been around 10 years? mysql just getting to it)

        2. data quality - mysql has:
                - silent errors
                - silent data truncations & conversions

        3. standardization - mysql has:
                - quite a few deviations from ansi sql - everything from comments to weird create statements
                - historically the lack of views, transactions, stored procs, triggers, and poor join performance meant that many queries had to be completely rewritten for mysql

        4. performance
                - mysql's performance reputation was built upon easily-cached data that could be easily looked up using simple indexes on mysql. Its performance of large queries (select many/most rows) stank, and its write performance was horrible - since required table locking.
                - mysql's performance on innodb was better for mixed environments, but innodb has a bloat problem that can get serious.
                - no support for query parallelism, partitioning, etc - isn't 1/40th the speed of a commercial product for many queries.
                - mysql's optimizer is trivial - and can't be relied upon for complex queries (> 5 tables)

        No, you can live without row locking - as long as you've got at least page-level locking. It's the accumulation of all the other stuff that makes you want to run from it.
        • 1) Inconsistancy in how create statements are handled. For example:
          create table table2 (
          id int autoincriment,
          fk int references table1 (id)
          ) type=innodb;

          does not enforce the foreign key even in 5.0 while:
          create table table2(
          id int autoincriment,
          fk int,
          foreign key (fk) references table1 (id)
          ) type=innodb;

          does enforce them.

          2) Even with strict mode, any application can turn it off, allowing it to add bad data (try adding Feb 31, 20005 as a date in MySQL with strict mode off). This is a violation of Date's Ce
    • Ahhmm.

      Most likely, your bank is running its mortgage application on a seven year old database. Many of these apps are actually running on much older hardware/software combinations. I don't think mortgages have changed that much in the last 10 years or so.

      Newer != Better, New Features != Required Features.

  • Playing catchup... (Score:2, Insightful)

    by slashname3 ( 739398 )
    So they finally add features that postgresql has. And don't forget the more difficult to use licensing they have imposed compared to postgresql.

    • Re:Playing catchup... (Score:2, Insightful)

      by bani ( 467531 )
      Yeah, because all the world is a nail and postgresql is the hammer.

      It's always easier when you view the entire universe in black and white.
      • Please give me an example where mySQL is a better choice for an application as opposed to PostgreSQL. I can only think of two, neither of which point to mySQL being a superior product.

  • MySQL's biggest problem (Score:4, Funny)

    by ShatteredDream ( 636520 ) on Thursday October 13, 2005 @01:41PM (#13783608) Homepage
    It makes no sense why they didn't buy out Innobase a while ago. Now that Oracle owns Innobase, they are ultimately at Oracle's mercy for much of their development since MySQL uses Innobase's code for a lot of their work. They really should have bought them out a while ago and integrated them into MySQL as a company before Oracle could get their hands on them.
    • I'm hoping, because they didn't need to.

      From what I understand there are several developers at MySQL who understand the innodb codebase extremely well. The first smell of trouble from Oracle and they fork the last GPL version and take it from there.

      I personally believe that Oracle bought Innobase to get their hands on the developers hoping to starve development on innodb. I hope that that isn't the case, or even possible.

      Innodb, imho, is a seriously nice transaction engine. It's very very Oracle in it's des
      • The first smell of trouble from Oracle and they fork the last GPL version and take it from there.

        And can't use it in their commercial product.

        If they can't get their license for InnoDB renewed, their commercial sales are in big trouble.
        • I know you are allowed to sell GPL code. Would their use of GPL InnoDB code in a commercial version of MySQL violate the 'linking' clause of the GPL? If so, could they get around it by modularizing or otherwise seperating the InnoDB code from the rest of MySQL in a GPL friendly fashion?
  • Does MySQL5 make their default table type ACIDic, or do you still have to use InnoDB if you want that?

    It seems silly (well, dangerous) to have triggers, etc. without transactions, so I'm inclined to think they finally went ACID. But if that's the case, who cares if Oracle bought Inno, so I'm inclined to think they didn't.

    Anyone actually know?
  • Performance wise, how does this new release of MySQL compare with other open source databases like PostgreSQL, Firebird and perhaps even SQLite?

    • Re:Performance wise.. (Score:5, Informative)

      by commanderfoxtrot ( 115784 ) on Thursday October 13, 2005 @04:26PM (#13785473) Homepage
      Very few (recent) comparisons around. From my experience, however, if you're running a simple web site with many SELECTs over a single table then MySQL may well suffice. If you're doing serious stuff with multiple table/view joins then you should move up a gear and use PostgreSQL.

      I've moved completely to PostgreSQL (works beautifully on core Drupal too) and have found complex queries complete in a fraction of the time. I had a complicated application which had multiple threads inserting, updating and reading all at the same time- complete run-time was reduced to a tenth by using PostgreSQL.

      It works for me- just make sure you use ADODB in PHP [sourceforge.net] or Perl/DBI [perl.org] to make switching easy when you hit the MySQL limits.

      One more thing: I work with serious mainframe DB2 during the day. MySQL just doesn't compare. Postgres feels closer.

  • Still no "device"/single file support (Score:4, Insightful)

    by Twillerror ( 536681 ) on Thursday October 13, 2005 @02:34PM (#13784127) Homepage Journal
    One of the biggest advantages of enterprise level dbs is that they are not file based. That is there is one or more files that can store many table/indexes/etc.

    Postgres recently added tablespaces, which allow you to specify a sub folder for these files. This is better then before, but not nearly where it needs to be.

    There are advantages to having single files versus a larger file, but most are administrative in nature. It can also lessen the effect of corruption. A sinlge table might fail and would not effect others. If a big database file corrupts it can damage a lot of data.

    Having a single files allows quite a few things.

    First it greatly reduces File system level fragmentation. The file grows once and the sectors are right next to each other. When you have 10+ gigs of data this is a real concern.

    Second it create a unified caching mechanism. The big file is broken into pages, generally 8k, which in turn store data rows. The data is not only user data, but indexes, system information. Other pages are used to store stats about other pages, and have header information about the file itself. Why is this important to caching, because you simply have a cache table, everytime a page is loaded it gets cached. Writing to page happens in memory and then written to disk. Enterprise dbs have huge caches. This is why 64 bit is so important for dbs, so we can have larger then 4 gig caches.

    Third is backing up. Some might say the file backup is easier, I beg to differ. Especailly when it gets big. When you go to backup you backup each page in the file. You mark each one as being backedup. At the end of the backup you backup the write ahead log. This allows you to restore to exactly the time the backup finished. Lastly, a diff backup simple looks at each page that has changed since the backup and backs only those pages up. Diffs can be very fast and faster to restore then a write ahead log.

    Also, single files on different drives arrays to increase performance. This is also capable with tablespaces. The good part is that the database knows only the file id that tables go on, and then file id corresponds to a file name on any path. With the right tool you can move the files around easily.

    Replication is also easier because writes are to a file id and page id. The replica database can have files place on any drive at any map point as long as the data.

    Both mysql and postgres got a way to go, but they are very nice products and one day can easily compete with the big boys. Although it will be a while before they are able to run high end clustered box with shared storage and super high speed interconnects, but if you need that kind of power, you've probably got the money...actually you absolutly do if you can afford the hardware.

  • The negative comments have gone from... (Score:4, Insightful)

    by puppetman ( 131489 ) on Thursday October 13, 2005 @03:29PM (#13784868) Homepage
    "MySQL doesn't have triggers or stored procedures and views" to "Even if it does have triggers, stored procedures and views, it's still not a real database like Postgres/Oracle/SQLServer".

    We have two websites, Boats.com [boats.com] and Yachtworld.com [yachtworld.com] - Boats has an Oracle backend databsae, and YW has MySQL using the InnoDB engine.

    The uptime is about the same for the two. We've had some issues on the Yachtworld database box due to 3ware drivers in Linux - they were corrupting pages in the database. Guess what? Innodb recovered without any lost data. Twice. This was a driver/hardware/linux issue, not a MySQL issue. We now appear to have a stable set of drivers, and I expect the MySQL database to hit 100% uptime pretty much every month.

    Yachtworld gets several million distict page views per day, whereas Boats.com gets half a million.

    Our MySQL database runs on a dual-opteron server, with 8 gig of RAM, with 6 gig of it allocated to the innodb block buffer pool (it caches row and index data so you don't have to go to disk).

    Try doing that in Oracle 10g on Linux. The SGA (Shared Global Area) can't get larger than 1.7 gig unless you,

    1) Use memory as a temporary file system so that Oracle can cache a bit more, and you also get the benefit of dicking around for several days, trying to configure your machine to try to take advantage of it (if it even can - we were never successful).
    2) Remap all the shared libraries so that they load in a lower memory address, to squeeze another few hundred meg of memory.

    Postgres (last I checked) preferred to let the OS do the data-caching. Thanks, but no thanks. And no 64-bit version (though I've read a few people have managed to compile one, I wouldn't trust it unless Postgres gave it the thumbs up).

    MySQL with InnoDB is straighforward (it's use of tablespaces, replication, tuning, and even compiling from source - someone with mediocre Linux skills like myself can do it without issue every time).

    MySQL with InnoDB is very fast, very reliable, and has awesome support via the MySQL mailing lists.

    MySQL is very well documented, with lots of great third party books that don't cost an arm and a leg (unlike an Oracle library).

    MySQL does not have stored procedures, triggers, and views in the current production version.

    Here's what I think of that:

    1) Triggers are hidden application logic that are very hard to debug, and are easily overlooked or forgotten by developers. Business logic (other than defensive logic like unique indexes, primary keys, foreign keys, not-null columns) does not belong in the database. They belong in the middle tier. They also make it much more difficult to move to another database.

    2) Stored procedures are like PERL - it's very easy to make a mess unless you are very careful. They are also hidden logic, and very difficult to debug. And again, keep that logic out of the database. They also make it much more difficult to move to another database.

    3) Views are a nice feature, but most often used to support business and reporting. I don't like managers connecting to the database to run queries (SELECT * FROM very_large_table_1, very_large_table_2; and suddenly you have cartesian join that results in tens of millions of rows coming back, bogging everything down). To do reports, views aren't necessary.

    If you think MySQL is not a "real" database, it is, and has been since 4.0. As an Oracle (and now MySQL DBA), I can honestly say that I can't wait to dump Oracle and get the Boats.com website over to MySQL.

    And for the few people who made comments like, "Do you really want your bank running on MySQL?": many banks run on old, legacy hardware and systems. Transactions are written out in many places (with geographic diversity) to ensure that a hardware or software crash is recoverable. There is no reason why you couldn't put MySQL in a situation like that, so long as the same precautions are taken.
    • 1) Triggers are hidden application logic that are very hard to debug, and are easily overlooked or forgotten by developers. Business logic (other than defensive logic like unique indexes, primary keys, foreign keys, not-null columns) does not belong in the database. They belong in the middle tier. They also make it much more difficult to move to another database.

      I used to think as you write here, but have modified my thinking some in the past few years. I agree with your view on the 'hidden application log

    • Re:The negative comments have gone from... (Score:4, Informative)

      by farnsworth ( 558449 ) on Thursday October 13, 2005 @04:20PM (#13785419)
      Triggers are hidden application logic that are very hard to debug

      Triggers are hidden *data* logic, and they should be hidden. They have the added benefit of being asyncronous if you choose, so if you need to write data fast, you can still lay it out in another format, or do something else arbitrary to it.

      Stored procedures are like PERL

      Agreed. But when you need them, you need them. They also go hand-in-hand with triggers frequently.

      Views are a nice feature, but most often used to support business and reporting.

      Views are an abstracted view of data. You can have a table called subscribers with lots of columns that tell you the status of the subscriber, and a view called current_subscribers that encapsulates all that logic.

      (psuedo sql)
      create table subscribers (id, start_date, end_date, cancelled, payment_is_late, is_overdue);

      create view current_subscribers as select id from subscribers where start_date now() and cancelled = 'N' and is_overdue = 'N';

      You could argue that this logic belongs in you DAO, but that only works if you have one DAO runtime, which is not true for a lot of application environments.

    • 3) Views are a nice feature, but most often used to support business and reporting. I don't like managers connecting to the database to run queries (SELECT * FROM very_large_table_1, very_large_table_2; and suddenly you have cartesian join that results in tens of millions of rows coming back, bogging everything down). To do reports, views aren't necessary.

      I'd say they're more often used to implement security than for reporting these days. If you've got a table which you only want certain rows or columns to

    • Views are a nice feature, but most often used to support business and reporting. I don't like managers connecting to the database to run queries.

      Yeah, what kind of crazy person would use a database engine to support managers in making business decisions. Wild, I tell you, just far out!

      The truth is that since MySQL doesn't allow you to define constraints on how long queries launched by a specific user can run, you've concluded that allowing ad hoc queries is a bad practice. That's tunnel vision, not

    • Re:The negative comments have gone from... (Score:5, Insightful)

      by kpharmer ( 452893 ) on Thursday October 13, 2005 @05:19PM (#13785992)
      Regarding memory:
      - I haven't tuned oracle memory in forever, but it sounds like you're trying to use 8 gbytes of memory
      on a 32-bit cpu. In that case oracle and db2 are both limited to 1-3.2 gbytes of memory depending
      on the os. There are ways of getting around that limitation but they are os-specific. On 64-bit
      CPUs, everything is very simple.
      - btw, putting most of your memory into a single buffer pool is seldom the best way to manage your
      memory: ideally you would create a few sets of buffer pools for different types of tables. That's
      the best way to increase your cache hits: indexes and all small tables pretty much just live in
      memory at that point.

      Regarding Innodb:
      - very fast? compared to what? writing to myisam? well, sure, but that's about it.
      - keep in mind that at a few million rows and several million queries, you shouldn't have
      a problem with this data in any database. This is small. Unless of course your queries
      are complex, are frequently reading 50,000 rows then aggregating that data into trends,
      counts, etc. Of course, if you are - then oracle's parallel query and partitioning will
      deliver great performance - possibly *dozens* of times faster than what mysql can do.

      Triggers:
      - as long as you keep it simple they are wonderful and are still easy to port. You can with
      triggers:
      - auto-populate some columns that the application doesn't require, but might be useful in
      partitioning the data (assuming your database supports partitioning).
      - populate denormalized tables strictly for reporting or searching
      - populate history tables with changes to all data in various important tables
      - capture changes in order to copy data to another database
      - etc
      - yes, you could do many of these things from within the application. but it'll be harder. Why
      make life hard?
      - and sometimes you *can't* do these things from within the app - it's closed source, but luckily
      for you it's on a database that supports triggers.

      Stored Procedures
      - again, keep it simple and they are very useful and not at all difficult to port between databases
      - when validation is performed here it allows you to *easily* enable multiple applications
      to write to the database. I'm working on a project right now in which I've got to allow another
      department to create a portal to one of our databases. The folks in this department can barely
      write SQL, and I'm not interested in them training on our dime. We're giving them stored
      procedures that they won't be able to screw up. Much, much safer this way!
      - also gives your dba the ability to change the physical database (adjust for changes in business,
      performance, security, etc) without having to change all the applications: the change can be
      encapsulated.
      - and sometimes you *can't* do these things from within the app - it's closed source, but luckily
      for you it's on a database that supports triggers.

      Views
      - again, this gives you a ton of flexibility
      - for example: in most of my databases I don't give de

    • Postgres (last I checked) preferred to let the OS do the data-caching. Thanks, but no thanks.

      Well, Postgres does do its own caching in userspace (see the shared_buffers configuration parameter and related documentation). It just does that caching in addition to (or rather, on top of) the caching and I/O scheduling done by the kernel. Why do you consider this to be a problem?

      (Yes, letting the kernel do most of the caching does result in a minor performance hit, but I think that the amount of work required to

  • Whenever MySQL vs PostgreSQL comparison comes up, there are always people who claim that MySQL still has its own niche of the "not ACID but fast" database for basic things. Is it alone there, though? How well does it compare with e.g. SQlite?

  • InnoBase and Oracle acquisition (Score:3, Interesting)

    by kbahey ( 102895 ) on Thursday October 13, 2005 @08:58PM (#13787437) Homepage

    What worries me is that new acquisition of InnoBase by Oracle a few days ago.

    InnoBase is the maker of InnoDB, which is the full featured dual licensed storage engine with transactions, referential integrity, hot backups and more.

    The GPL version of MySQL will not be affected should Oracle decide to misbehave.

    What may get affected is the commerical version of MySQL. Oracle can demand a hefty price for relicensing InnoDB, when the contract is up for renewal hence choking MySQL AB financially, by depriving it from the revenue stream of commerical licensing MySQL with InnoDB.

    This may in turn cause long term trouble for the community by depriving it from contributions by MySQL.

    I hope Oracle does not do that, but still, they are a corporation with no open source culture, and may have the mentality of choking the competition, using the very rules of open source dual licensing.

    Or, they may be softening MySQL to buy them cheap in the near future ....?

Slashdot Top Deals

"Pull the trigger and you're garbage." -- Lady Blue

Close