Just saw this popup on Twitter



  •  I had to create a forum account just to post this. I have some search columns in TweetDeck for things like #mysql, #ruby, #nagios. Well this gem just popped up in my feed:

     

    from @nwkr:

    "Make use of foreign keys and cascading delete in #MySQL. I didn't. A buyer had more products in an order than he ordered. And got it 4 free."

     

    I really want to reply and point out that MySQL isn't the problem here.

     

    sigh



  •  See if you can mesage him: "Less twittering, more learning to code"



  • Twits are always twits



  • If he thinks that's the problem, he's probably never heard of SQL injection. Where did you say this website was?



  • @lolwtf said:

    If he thinks that's the problem, he's probably never heard of SQL injection. Where did you say this website was?
     

     Wish I knew. Depending on what they sell, might be a new career in my future ;)



  • I can imagine a possible scenario of what could have happened:

    # "Buyer 2" buys some products:

    <font face="courier new,courier">    Buyers                       Products
    |  id |   name  |    | buyer_id | product_id | quantity |
    |  1  | Buyer 1 |    |     2    |     42     |     5    |
    |  2  | Buyer 2 |    |     2    |     69     |     3    |
    |  3  | Buyer 3 |</font>
     
    # "Buyer 2" gets deleted from the database

    <font face="courier new,courier">    Buyers                       Products
    |  id |   name  |    | buyer_id | product_id | quantity |
    |  1  | Buyer 1 |    |     2    |     42     |     5    |
    |  3  | Buyer 3 |    |     2    |     69     |     3    |</font>

    # "Buyer 4" is added to the database, and id = 2 is reused

    <font face="courier new,courier">    Buyers                       Products
    |  id |   name  |    | buyer_id | product_id | quantity |
    |  1  | Buyer 1 |    |     2    |     42     |     5    |
    |  3  | Buyer 3 |    |     2    |     69     |     3    |
    |  2  | Buyer 4 |
    </font>
    # "Buyer 4" already has more products than really bought


    Of course, this doesn't really explain why the buyer got them for free. But I'd bet that Products table was actually a ProductsToShip table.



  • @Zecc said:


    # "Buyer 4" is added to the database, and id = 2 is reused

    You'd have to be completely WTF'd to reuse an ID:

    1) Column for id should be an identity.  This will be unique (unless you purposely reset your identity to some value less than the max in the table).

    2) Selecting Max(id) for self assigned id's is so much easier than looking for a missing number.  I can't imagine a clueless person spending the time to do that.

    It is much more likely that it is a race condition where they insert in the buyer's table, then before adding products doing a Select Max(id) from Buyers to find out what the just inserted id was....This is a much more likely failure mode for a clueless coder to make. 



  •  I actually got a followup tweet from the guy:

     

    " I didn't delete children (from many-side) when deleting a parent. So children remained. Using ON DELETE CASCADE is the simplest way."

     

    I think he's missing the point of my WTF. The database still isn't the problem. It's more systemic than that. I can understand in GENERAL (regardless of application) where not deleting children can happen. 



  • @Auction_God said:

    2) Selecting Max(id) for self assigned id's is so much easier than looking for a missing number.  I can't imagine a clueless person spending the time to do that.

    But it's sooooo easy...

    [code] i = 1;
    while("SELECT COUNT(*) FROM table WHERE id = i" > 0) { i++; }
    "INSERT INTO table (id) VALUES(i)";
    [/code]
    See?


  • Selecting max(ID)+1 for your next ID will rightfully get you on thedailywtf.com. Only the hopelessly naive do that, because it doesn't scale for multiple transactions.

    But then, we're talking MySQL, where transactions until recently were either (a) very slow or (b) not used (see a).

    Friends don't let friends start new projects with MySQL, now that PostgreSQL is mature.



  • @realmerlyn said:

    Selecting max(ID)+1 for your next ID will rightfully get you on thedailywtf.com. Only the hopelessly naive do that, because it doesn't scale for multiple transactions.

    But then, we're talking MySQL, where transactions until recently were either (a) very slow or (b) not used (see a).

    Friends don't let friends start new projects with MySQL, now that PostgreSQL is mature.

    Right, who needs the speed, stability, simple maintenance and high-availability of MySQL when you can have Postgres!   Hope you like staring at an unresponsive console for a couple of hours while Postgres does its daily vacuum routine on a 100GB database.

     

    Postgres is a fine DB if you don't expect anyone to actually use your product.



  •  The database platform isn't the problem. I've dealt with LARGE databases on BOTH platforms for both OLTP and OLAP and the both have issues. Then again even the commercial dbs have issues too. Whether it's VACUUMing a huge DB on pgsql,  performing a reorg on DB2 or running an optimize on MySQL.

     Did you know indices are case sensitive on DB2? Did you know that innodb prefaces all indices with a subset of the PK value? Did you know that PGSQL can't (or at least couldn't) optimize queries with aggregate functions?

     

    All of these things are "gotchas" that have no bearing on the validity of DB choice. In the case of my OP, the fucktard probably would have had the same issue regardless of database ;)



  •  This makes me feel better about not being on twitter. I have no idea what @nwkr is trying to say.



  • @lusis said:

     The database platform isn't the problem. I've dealt with LARGE databases on BOTH platforms for both OLTP and OLAP and the both have issues. Then again even the commercial dbs have issues too. Whether it's VACUUMing a huge DB on pgsql,  performing a reorg on DB2 or running an optimize on MySQL.

     Did you know indices are case sensitive on DB2? Did you know that innodb prefaces all indices with a subset of the PK value? Did you know that PGSQL can't (or at least couldn't) optimize queries with aggregate functions?

     

    All of these things are "gotchas" that have no bearing on the validity of DB choice. In the case of my OP, the fucktard probably would have had the same issue regardless of database ;)

    Blah blah...  postgres vacuum fulls take way, way longer than optimize on similarly-sized MySQL.  Also, running optimize on MySQL speeds things up but isn't required.  Not running vacuum on postgres will eventually result in the DB ovewriting existing rows at near-random before taking dropping trow, taking a massive shit all down the back of its legs, curling up in a fetal position with its shit-smeared buttocks still exposed to the air, and dying.

     

    That's not even to mention the lack of good replication for postgres (fucking triggers?  you fucking kidding me?) and the complete non-existence of any features similar to ndb.  Postgres is also slow as fuck on queries, can't optimize queries for shit, spawns a process for every connect which takes for-fucking-ever (by web app standards, at least) and is prone to getting worker processes hung which ends up taking the whole DB down...  Also, I'm pretty sure the Postgres devs are ugly.



  • @morbiuswilters said:

    postgres vacuum fulls take way, way longer than optimize on similarly-sized MySQL
    Does the Visibility Map in 8.4 solve this?



  • @morbiuswilters said:

    Right, who needs the speed, stability, simple maintenance and high-availability of MySQL when you can have Postgres!  

    You have missed the *most* important attribute of a database system: Integrity.

    If your database has no data integrity then speed and stability and availability are irrelevant. Serving up bad data quickly and reliably is still bad.

    B



  • @havokk said:

    @morbiuswilters said:

    Right, who needs the speed, stability, simple maintenance and high-availability of MySQL when you can have Postgres!  

    You have missed the *most* important attribute of a database system: Integrity.

    If your database has no data integrity then speed and stability and availability are irrelevant. Serving up bad data quickly and reliably is still bad.

    B

    No, I didn't mention it because MySQL and Postgres are about equal when it comes to data integrity.  That's assuming Postgres doesn't end up tripping over itself because you didn't run a vacuum at the right time.  Either way, I've had no real problems with databases being corrupted in either, outside of hardware failures.  And failing hardware shouldn't be a problem if you're using high-availability features, although MySQL does clearly win there.



  • @morbiuswilters said:

    @havokk said:

    @morbiuswilters said:

    Right, who needs the speed, stability, simple maintenance and high-availability of MySQL when you can have Postgres!  

    You have missed the *most* important attribute of a database system: Integrity.

    If your database has no data integrity then speed and stability and availability are irrelevant. Serving up bad data quickly and reliably is still bad.

    B

    No, I didn't mention it because MySQL and Postgres are about equal when it comes to data integrity.  That's assuming Postgres doesn't end up tripping over itself because you didn't run a vacuum at the right time.  Either way, I've had no real problems with databases being corrupted in either, outside of hardware failures.  And failing hardware shouldn't be a problem if you're using high-availability features, although MySQL does clearly win there.

    All of this is based upon MySQL before the unholy alliance of Oracle and Sun manage to destroy it.  Give it a couple years...


  • @bstorer said:

    @morbiuswilters said:

    @havokk said:

    @morbiuswilters said:

    Right, who needs the speed, stability, simple maintenance and high-availability of MySQL when you can have Postgres!  

    You have missed the *most* important attribute of a database system: Integrity.

    If your database has no data integrity then speed and stability and availability are irrelevant. Serving up bad data quickly and reliably is still bad.

    B

    No, I didn't mention it because MySQL and Postgres are about equal when it comes to data integrity.  That's assuming Postgres doesn't end up tripping over itself because you didn't run a vacuum at the right time.  Either way, I've had no real problems with databases being corrupted in either, outside of hardware failures.  And failing hardware shouldn't be a problem if you're using high-availability features, although MySQL does clearly win there.

    All of this is based upon MySQL before the unholy alliance of Oracle and Sun manage to destroy it.  Give it a couple years...

    It will probably be forked by the community, I pray.  Sun hasn't managed to completely kill it yet, which is amazing considering they didn't want to kill it and Sun kills everything it touches.  And I suspect Oracle wants to kill it and since Oracle can't do anything right, either, there's a chance MySQL will outlive us all.



  • @morbiuswilters said:

    It will probably be forked by the community, I pray.  Sun hasn't managed to completely kill it yet, which is amazing considering they didn't want to kill it and Sun kills everything it touches.  And I suspect Oracle wants to kill it and since Oracle can't do anything right, either, there's a chance MySQL will outlive us all.
     

    Oracle Board: Gentlemen, we need to destroy MySQL.  Let's do whatever we can to make it the exact opposite of our own database, so that nobody will want it.

    2 years later

    TV News Anchor: ...sources at NASA say that the comet was deflected just 100 miles from striking the Earth thanks to a MySQL database.  This marks the fourth time this year that a MySQL server has protected us from a disaster.




  • 2) Selecting Max(id) for self assigned id's is so much easier than looking for a missing number.  I can't imagine a clueless person spending the time to do that.

    Please, please tell me you meant selecting Max(id)+1 ... selecting Max(id)+0 isn't going to help you at all if the last used id in the table was just deleted, as it will get reused on the next insert.



  • @daveime said:


    2) Selecting Max(id) for self assigned id's is so much easier than looking for a missing number.  I can't imagine a clueless person spending the time to do that.

    Please, please tell me you meant selecting Max(id)+1 ... selecting Max(id)+0 isn't going to help you at all if the last used id in the table was just deleted, as it will get reused on the next insert.

    No - you would select max(id).  The +1 would be done in the application code.  Besides....you would NEVER, EVER -> repeat after me "NEVER" use this technique anyway, so why does it matter which I meant?



  • @Auction_God said:

    No - you would select max(id).  The +1 would be done in the application code.  Besides....you would NEVER, EVER -> repeat after me "NEVER" use this technique anyway, so why does it matter which I meant?
    Right, in MySQL you'd define it as an autoincrementing int and just leave it blank on inserts..  In Oracle you'd have a seqence and do sequence.NEXTVAL.  I don't know anything about SQL Server but I bet it's closer to the Oracle implementation, which I don't like all that much.



  • @belgariontheking said:

    @Auction_God said:

    No - you would select max(id).  The +1 would be done in the application code.  Besides....you would NEVER, EVER -> repeat after me "NEVER" use this technique anyway, so why does it matter which I meant?
    Right, in MySQL you'd define it as an autoincrementing int and just leave it blank on inserts..  In Oracle you'd have a seqence and do sequence.NEXTVAL.  I don't know anything about SQL Server but I bet it's closer to the Oracle implementation, which I don't like all that much.

    Actually, it works similarly to the mySql implementation.  The advantage of the Oracle one is that you *could* have more than one identity on a table... I can't think of a reason you'd need to do that, but it does provide the flexibility.  It also allows you to use the same sequence across multiple tables. 

    Interesting tidbit: Oracle sequences cache a number of them in memory (I believe the default is 10).  So if your computer crashes, the unused numbers in that sequence are gone forever.  I had one project where the business guy was real anal and wanted to make sure that no sequences were ever lost...So I had to turn that feature off.



  • @belgariontheking said:

    @Auction_God said:

    No - you would select max(id).  The +1 would be done in the application code.  Besides....you would NEVER, EVER -> repeat after me "NEVER" use this technique anyway, so why does it matter which I meant?
    Right, in MySQL you'd define it as an autoincrementing int and just leave it blank on inserts..  In Oracle you'd have a seqence and do sequence.NEXTVAL.  I don't know anything about SQL Server but I bet it's closer to the Oracle implementation, which I don't like all that much.

    Postgres is somewhere in the middle.  You have to define a sequence/serial (which is actually a special type of table with only one row containing an int that is atomically incremented whenever you read from it) and then set the default value for your ID column to be nextval(sequence_table) or something to that effect.  Then you just use the table like you would in MySQL: insert a row without the ID column and it uses nextval() on the sequence as default.  IIRC, they eventually added the ability to declare a column in the table in such a way that the whole sequence and default value business is done implicitly.  The real bitch was renaming or dropping tables since the sequence/serial wasn't automatically deleted which meant creating a new table with the same name as the old would fuck things up since the sequence table would exist.  Oh, and if you wanted to rename a table in production and stuff was being inserted into it the sequence wouldn't move "with" the table which meant newly inserted rows had null values.  Why would you do such a thing?  Well, if you have an old table you want to "archive" while replacing it with a new one it was easier and faster to rename the table and create a new, blank one in the same transaction.  Then you could dump the old table and then drop it.  Truncating the table was no good because Postgres still has a bunch of old pages for the table laying around on disk and it won't reclaim them (or their oids) until you vacuum and if you get oid wraparound you get corruption.  So often the best way to "clear" a table was to do a rename old->create new->drop old but that also broke a lot..

     

    Have I mentioned that Postgres is a flaming piece of shit-garbage?



  • @morbiuswilters said:

    @belgariontheking said:

    @Auction_God said:

    No - you would select max(id).  The +1 would be done in the application code.  Besides....you would NEVER, EVER -> repeat after me "NEVER" use this technique anyway, so why does it matter which I meant?
    Right, in MySQL you'd define it as an autoincrementing int and just leave it blank on inserts..  In Oracle you'd have a seqence and do sequence.NEXTVAL.  I don't know anything about SQL Server but I bet it's closer to the Oracle implementation, which I don't like all that much.

    Postgres is somewhere in the middle.  You have to define a sequence/serial (which is actually a special type of table with only one row containing an int that is atomically incremented whenever you read from it) and then set the default value for your ID column to be nextval(sequence_table) or something to that effect.  Then you just use the table like you would in MySQL: insert a row without the ID column and it uses nextval() on the sequence as default.  IIRC, they eventually added the ability to declare a column in the table in such a way that the whole sequence and default value business is done implicitly.  The real bitch was renaming or dropping tables since the sequence/serial wasn't automatically deleted which meant creating a new table with the same name as the old would fuck things up since the sequence table would exist.  Oh, and if you wanted to rename a table in production and stuff was being inserted into it the sequence wouldn't move "with" the table which meant newly inserted rows had null values.  Why would you do such a thing?  Well, if you have an old table you want to "archive" while replacing it with a new one it was easier and faster to rename the table and create a new, blank one in the same transaction.  Then you could dump the old table and then drop it.  Truncating the table was no good because Postgres still has a bunch of old pages for the table laying around on disk and it won't reclaim them (or their oids) until you vacuum and if you get oid wraparound you get corruption.  So often the best way to "clear" a table was to do a rename old->create new->drop old but that also broke a lot..

     

    Have I mentioned that Postgres is a flaming piece of shit-garbage?

     I do believe that I recall you mentioning something to that effect.



  • @morbiuswilters said:

    The real bitch was renaming or dropping tables since the sequence/serial wasn't automatically deleted which meant creating a new table with the same name as the old would fuck things up since the sequence table would exist.  Oh, and if you wanted to rename a table in production and stuff was being inserted into it the sequence wouldn't move "with" the table which meant newly inserted rows had null values.  Why would you do such a thing?  Well, if you have an old table you want to "archive" while replacing it with a new one it was easier and faster to rename the table and create a new, blank one in the same transaction.

    Don’t worry, MySQL can do even better. If you ever rename a PK column, at some indeterminate point in the future (a week, 3 months, long enough that you forgot the schema ever changed), your tables will corrupt themselves and crash the server any time you try to use them due to an inconsistency that occurs between the column data that MySQL and InnoDB each store separately for some who-the-fuck-knows reason. At that point, your only option is to drop the table and restore from backup, and in the meantime any time someone tries to select from the table the entire MySQL daemon crashes. (I’m not saying that there’s really ever a very good reason to rename a PK, but it’s a similar type of operation with much more devastating consequences than seeing some NULL inserts.)

    As a follow-up, did you ever figure out why the table didn’t keep the sequence reference or come up with a work-around like adding an rule or trigger?

     

    @morbiuswilters said:

    if you get oid wraparound you get corruption.
    How do other RDBMS deal with MVCC and avoid a similar problem? (You are talking about transaction ID wraparound that causes data loss, right, and not something else where someone was assuming oids are unique and don’t wrap around?)

     

     @morbiuswilters said:

    Have I mentioned that Postgres is a flaming piece of shit-garbage?
    All databases are flaming pieces of shit-garbage in one way or another, though, aren’t they? :) I have to admit I don’t have much large-scale implementation experience with Postgres, but I have with MySQL, and I really can’t believe that pgsql is worse having dealt with some of the things I have.



  • @Auction_God said:

    Interesting tidbit: Oracle sequences cache a number of them in memory (I believe the default is 10).  So if your computer crashes, the unused numbers in that sequence are gone forever.  I had one project where the business guy was real anal and wanted to make sure that no sequences were ever lost...So I had to turn that feature off.

    You do realise the every database can drop sequence numbers however they are implemented?  The alternative would be to wait for every transaction to finish before starting another, which would make it close to useless for multiple users.  I don't know what you did, but I can guarantee there will be gaps if you ever have a transaction fail or rollback (or your computer crashes).



  • @snover said:

    Don’t worry, MySQL can do even better. If you ever rename a PK column, at some indeterminate point in the future (a week, 3 months, long enough that you forgot the schema ever changed), your tables will corrupt themselves and crash the server any time you try to use them due to an inconsistency that occurs between the column data that MySQL and InnoDB each store separately for some who-the-fuck-knows reason. At that point, your only option is to drop the table and restore from backup, and in the meantime any time someone tries to select from the table the entire MySQL daemon crashes. (I’m not saying that there’s really ever a very good reason to rename a PK, but it’s a similar type of operation with much more devastating consequences than seeing some NULL inserts.)

    As a follow-up, did you ever figure out why the table didn’t keep the sequence reference or come up with a work-around like adding an rule or trigger?

     

    Never seen those problems on MySQL, maybe they've been patched?  As far as workarounds, it just requires renaming the sequence and updating the table's reference to it, but it's the unexpectedness of the default behavior which baffles me.

     

    @snover said:

    @morbiuswilters said:
    if you get oid wraparound you get corruption.
    How do other RDBMS deal with MVCC and avoid a similar problem? (You are talking about transaction ID wraparound that causes data loss, right, and not something else where someone was assuming oids are unique and don’t wrap around?)

    MySQL (and IIRC Oracle) use a "redo" log to handle rollbacks and then just update the actual rows.  Postgres uses the convoluted "row duplication" method which means slower commits, ballooning disk space and possible ID wraparound.  I've never seen MySQL have a problem like Postgres does.  It's especially egregious if you have a table that doesn't store many rows at once (say, less than a million) but which gets billions of transactions comitted a day.  You have to constantly vacuum to reclaim disk space and prevent ID wraparound.  Autovac doesn't fix this as the autovac daemon basically just waits until the DB needs to be vacced and then starts one which slows the DB to a crawl and ends up denying requests from clients.  I think they've made progress in moving away from this model in recent releases but it was enough of a WTF when I had to deal with it that it made Postgres my sworn blood enemy.

     

    @snover said:

    @morbiuswilters said:
    Have I mentioned that Postgres is a flaming piece of shit-garbage?
    All databases are flaming pieces of shit-garbage in one way or another, though, aren’t they? :) I have to admit I don’t have much large-scale implementation experience with Postgres, but I have with MySQL, and I really can’t believe that pgsql is worse having dealt with some of the things I have.

    I agree on your first point but MySQL is still so much better than anything else I've had to work with, it's stunning.  I've done large-scale implementations of both and Postgres is much worse.  The only real replication support is via use of a trigger on every table that copies the row to your "slave" databases.  As you can imagine, this fails easily, is slow as shit and makes failover and failback a joy.  MySQL replication is no walk in the park, especially multi-master, but it's got nothing on the pain Postgres inflicts.  Then there are just the performance and maintenance nightmares of Postgres.  Sometimes the DB just slows to a crawl for no obvious reason because the Postgres data files have grown large enough (even if actual data is much smaller, due to Postgres keeping old pages around until vacuumed) that Postgres starts fighting with the kernel and thrashing swap.  Vacuuming locks the tables which causes any client apps to queue (and will eventually bring down clients with large volumes of traffic if you're not careful) and the only way to work around this is implement some kind of flag on the DB in your app that lets the app know to throw up a maintenance message.  I've got a lot more I could bitch about, but I'm tired and have to get up early tomorrow.

     

    tl;dr  MySQL sucks but Postgres sucks much worse and compared to other RDBMSes MySQL is awesome.



  • @morbiuswilters said:

    Never seen those problems on MySQL, maybe they've been patched?
    Nah, still there, hiding in the shadows, waiting to pounce and trash your data. I created a table using CREATE TABLE LIKE other_table, renamed the primary key, forgot about it for a couple months, and then suddenly the server started crashing during the nightly backup. The table hadn’t been used for weeks—it was for a sweepstakes that had ended—so I have no idea how, after sitting dormant for that period of time, it would just suddenly become corrupt. The worst part about it is that from what I can tell from that bug report, there’s not anything wrong with the data at all, there’s just a problem with the auto_increment metadata, so you should still at least be able to read from the table—but you can’t.

    @morbiuswilters said:

    I think they've made progress in moving away from this model in recent releases but it was enough of a WTF when I had to deal with it that it made Postgres my sworn blood enemy.
    Oh, don’t worry. According to the 8.4 documentation, even though they now have the Visibility Map to improve vacuum speed, it still has to do a full table sweep to update all of the transaction IDs, so you can probably safely continue to feel bitter for a while longer. Frankly, I’m surprised they haven’t just changed transaction IDs to be 64-bit instead. It’s a hack, but it sure would help to greatly reduce the need to touch every row during a vacuum.

    At least to alleviate some of your woes (and mine eventually I’m sure), someone there finally realised that they needed proper replication support, so that’s coming in 8.5. Whenever that is. Sometime in the next decade, maybe.

    I am still interested to know if when Postgres wasn’t doing stupid shit vacuuming, were you able to ever see a performance improvement through the features it has that MySQL doesn’t, like partial indexing, indexing on expressions, descending order indexes, index combining, multicolumn indexes that can be used even when the query isn’t using the first column in the index, etc.? MySQL’s dumb indexes are one of the (many) things that drive me absolutely crazy about working with it.

    For what it’s worth, Postgres is at least still smarter than Apple, whom this whole talk of transaction IDs has reminded me of. Every file or directory in an AFP volume has to have a unique ID which is 32-bits, and the IDs must never be reused. So, after 2^32 files/folders have been written to an AFP volume, the ID pool
    is depleted and no new file can be written to the volume and you have to delete it and create a new one. GENIUS! (AFP also doesn’t support symlinks, despite the fact that Mac OS has for years. Frankly, with all of its filesystem WTFs, I am flabbergasted that Mac OS ever manages to save any files at all.)



  • @morbiuswilters said:

    Postgres is somewhere in the middle.  You have to define a sequence/serial (which is actually a special type of table with only one row containing an int that is atomically incremented whenever you read from it) and then set the default value for your ID column to be nextval(sequence_table) or something to that effect.
    Actually that sounds like the worst from both worlds.  You have to define a separate object (in MySQL you don't) and then you have to use that object when you define your table (in Oracle you don't).@morbiuswilters said:
    Have I mentioned that Postgres is a flaming piece of shit-garbage?
    Sounds like it.



  • @belgariontheking said:

    @morbiuswilters said:
    Postgres is somewhere in the middle.  You have to define a sequence/serial (which is actually a special type of table with only one row containing an int that is atomically incremented whenever you read from it) and then set the default value for your ID column to be nextval(sequence_table) or something to that effect.
    Actually that sounds like the worst from both worlds.  You have to define a separate object (in MySQL you don't) and then you have to use that object when you define your table (in Oracle you don't).
    You don't actually have to do any of that.  You can simply do [code]CREATE TABLE foo (bar SERIAL);[/code] and Postgres takes care of the details.



  • @morbiuswilters said:

    Blah blah...  postgres vacuum fulls take way, way longer than optimize on similarly-sized MySQL.  Also, running optimize on MySQL speeds things up but isn't required.  Not running vacuum on postgres will eventually result in the DB ovewriting existing rows at near-random before taking dropping trow, taking a massive shit all down the back of its legs, curling up in a fetal position with its shit-smeared buttocks still exposed to the air, and dying.

    The problem is, give MySQL users the ability to use MYISAM, and they will. Just look at half the open source crap out there that comes with a "InnoDB not supported" tag. Cerberus helpdesk for one.. doesn't work at all. Wordpress, probably the most popular MySQL app out there? "InnoDB might work, but we recommend against it".

    Then you can take all the "but we implemented transactions" arguements from five years ago and start flogging the no longer dead horse again.

    Once again, it's always an implementation, more than the product, that causes the issue. But MySQL need to do a lot more (syslog("you are using myisam. You will burn in hell")) to make users do the right thing.



  • @josh26 said:

    @morbiuswilters said:

    Blah blah...  postgres vacuum fulls take way, way longer than optimize on similarly-sized MySQL.  Also, running optimize on MySQL speeds things up but isn't required.  Not running vacuum on postgres will eventually result in the DB ovewriting existing rows at near-random before taking dropping trow, taking a massive shit all down the back of its legs, curling up in a fetal position with its shit-smeared buttocks still exposed to the air, and dying.

    The problem is, give MySQL users the ability to use MYISAM, and they will. Just look at half the open source crap out there that comes with a "InnoDB not supported" tag. Cerberus helpdesk for one.. doesn't work at all. Wordpress, probably the most popular MySQL app out there? "InnoDB might work, but we recommend against it".

    Then you can take all the "but we implemented transactions" arguements from five years ago and start flogging the no longer dead horse again.

    Once again, it's always an implementation, more than the product, that causes the issue. But MySQL need to do a lot more (syslog("you are using myisam. You will burn in hell")) to make users do the right thing.

    And what, precisely, is the problem with MyISAM?  If you need transactions, InnoDB is there.  Most apps don't need transactions, so MyISAM is fine in most cases (and even preferable in many).  MySQL defines storage engines per-table so you can even mix-and-match.  What's the problem with that?



  • @bstorer said:

    @belgariontheking said:

    @morbiuswilters said:
    Postgres is somewhere in the middle.  You have to define a sequence/serial (which is actually a special type of table with only one row containing an int that is atomically incremented whenever you read from it) and then set the default value for your ID column to be nextval(sequence_table) or something to that effect.
    Actually that sounds like the worst from both worlds.  You have to define a separate object (in MySQL you don't) and then you have to use that object when you define your table (in Oracle you don't).
    You don't actually have to do any of that.  You can simply do <font face="Lucida Console" size="2">CREATE TABLE foo (bar SERIAL);</font> and Postgres takes care of the details.

    Read my original post.  As I said, I was pretty sure they added a way to do the whole thing implicitly from the table definition.



  • @morbiuswilters said:

    @bstorer said:

    @belgariontheking said:

    @morbiuswilters said:
    Postgres is somewhere in the middle.  You have to define a sequence/serial (which is actually a special type of table with only one row containing an int that is atomically incremented whenever you read from it) and then set the default value for your ID column to be nextval(sequence_table) or something to that effect.
    Actually that sounds like the worst from both worlds.  You have to define a separate object (in MySQL you don't) and then you have to use that object when you define your table (in Oracle you don't).
    You don't actually have to do any of that.  You can simply do <font size="2" face="Lucida Console">CREATE TABLE foo (bar SERIAL);</font> and Postgres takes care of the details.

    Read my original post.  As I said, I was pretty sure they added a way to do the whole thing implicitly from the table definition.

    Tell that to btk, not me.



  • @morbiuswilters said:

     precisely, is the problem with MyISAM?  If you need transactions, InnoDB is there.  Most apps don't need transactions, so MyISAM is fine in most cases (and even preferable in many).  MySQL defines storage engines per-table so you can even mix-and-match.  What's the problem with that?

    Because the associated complete lack of data integrity is only preferable when users go "I don't know what this means, but it's default, so meh".



  • @bstorer said:

    @morbiuswilters said:

    @bstorer said:

    @belgariontheking said:

    @morbiuswilters said:
    Postgres is somewhere in the middle.  You have to define a sequence/serial (which is actually a special type of table with only one row containing an int that is atomically incremented whenever you read from it) and then set the default value for your ID column to be nextval(sequence_table) or something to that effect.
    Actually that sounds like the worst from both worlds.  You have to define a separate object (in MySQL you don't) and then you have to use that object when you define your table (in Oracle you don't).
    You don't actually have to do any of that.  You can simply do <font size="2" face="Lucida Console">CREATE TABLE foo (bar SERIAL);</font> and Postgres takes care of the details.

    Read my original post.  As I said, I was pretty sure they added a way to do the whole thing implicitly from the table definition.

    Tell that to btk, not me.
    I didn't read the whole thing ... got bored pretty quickly.  I still haven't read the whole thing, but now I've read one more sentence, the sentence containing the word implicitly.



  • @snover said:

    At least to alleviate some of your woes (and mine eventually I’m sure), someone there finally realised that they needed proper replication support, so that’s coming in 8.5. Whenever that is. Sometime in the next decade, maybe.
    Well, yes, I suppose 2010 is technically the next decade, but this was in my email this morning:

    == PostgreSQL Weekly News - August 30 2009 ==

    8.5alpha1, the first ever PostgreSQL alpha release, is available for download.



  • @josh26 said:

    @morbiuswilters said:

     precisely, is the problem with MyISAM?  If you need transactions, InnoDB is there.  Most apps don't need transactions, so MyISAM is fine in most cases (and even preferable in many).  MySQL defines storage engines per-table so you can even mix-and-match.  What's the problem with that?

    Because the associated complete lack of data integrity is only preferable when users go "I don't know what this means, but it's default, so meh".

    If a user doesn't know that MyISAM doesn't support key constraints and transactions, they're not going to be using them in the first place.  If they do want to use them, they will use InnoDB tables.  Simple.  For a lot software, transactions and referential integrity aren't necessary.  Of course, you can always use them, so what the fuck are you bitching about?  That you have an extra feature?



  • @Sir Twist said:

    == PostgreSQL Weekly News - August 30 2009 ==

    8.5alpha1, the first ever PostgreSQL alpha release that's actually being called "alpha" and not "release", is available for download.
    FTFY



  • @morbiuswilters said:

    If a user doesn't know that MyISAM doesn't support key constraints and transactions, they're not going to be using them in the first place.  If they do want to use them, they will use InnoDB tables.  Simple.  For a lot software, transactions and referential integrity aren't necessary.  Of course, you can always use them, so what the fuck are you bitching about?  That you have an extra feature?
    Josh is just being a pedant, but there is something to bitch about: It's perfectly legal to use transactions and foreign key statements in MyISAM databases. They "work", in the sense that they don't give an error. Furthermore, although the statements are used, they do nothing. So, it's quite possible that people will think that transactions are working perfectly, and that their referential integrity is not compromized... until things explode.



  • @lolwtf said:

    If he thinks that's the problem, he's probably never heard of SQL injection. Where did you say this website was?
     

    Sometimes you don't even need SQL injection. Once (and I'm not sure how I noticed this) I saw that the online store of a MAJOR high-street chain that sells computer Games posted the details of what you ordered to the basket page. Fair enough, except that these details included the price. It took less than a minute to order various things for 1p each (0p seemed to trigger some kind of error handling and didn't work). I never actually finalised the order in case they chased after me for the full cost, but it was soooo tempting. I tried this again recently and it seems they fixed it.

    Actually, if you know particular things to Google for, it'll pick them out of the page source and you can find a lot of sites that have this same flaw. They tend to be very small one-man/woman operations selling homemade things though, and they're far more likely to notice than some big corporation.



  • I miss the days when I was ignorant enough to think of databases as just being magical black boxes that stored all my data in an efficient and convenient to retrieve format.



  • @Dudehole said:

    I miss the days when I was ignorant enough to think of databases as just being magical black boxes that stored all my data in an efficient and convenient to retrieve format.

    Ah yes.

    Growing up: it does little more than exposing more of the world's shit-filled innards.

    :D :D :D



  • @Dudehole said:

    I miss the days when I was ignorant enough to think of databases as just being magical black boxes that stored all my data in an efficient and convenient to retrieve format.

    Wait, they aren't?!  This shatters my entire world view!  Next you'll be telling me the Easter Bunny doesn't exist.  What?  Why are you looking at me like that?



  • @Dudehole said:

    I miss the days when I was ignorant enough to think of databases as just being magical black boxes that stored all my data in an efficient and convenient to retrieve format.

    Databases aren't like trucks you can just dump a bunch of data on, they're more like a hose that has data crammed into it.  Sometimes you have to tweak and massage and beat the hose to get the data to shoot out with appropriate velocity.



  • @morbiuswilters said:

    @Dudehole said:

    I miss the days when I was ignorant enough to think of databases as just being magical black boxes that stored all my data in an efficient and convenient to retrieve format.

    Databases aren't like trucks you can just dump a bunch of data on, they're more like a hose that has data crammed into it.  Sometimes you have to tweak and massage and beat the hose to get the data to shoot out with appropriate velocity.

     

    Just be careful, though; if your application interacts too vigorously with the database, it might shoot out data too soon, well before your program is ready for it.  You have to be gentle with your database if you don't want to make a mess of your program.



  • @Justice said:

    Filed under: GUI applications<input name="ctl00$ctl00$bcr$bcr$ctl00$PostList$ctl49$ctl23$ctl01" id="ctl00_ctl00_bcr_bcr_ctl00_PostList_ctl49_ctl23_ctl01_State" value="value:Filed%20under%3A%20%3Ca%20href%3D%22%2Ftags%2FGUI%2Bapplications%2Fdefault.aspx%22%20rel%3D%22tag%22%3EGUI%20applications%3C%2Fa%3E" type="hidden">


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.