On 12/1/05, Nathan L. Walls <[EMAIL PROTECTED]> wrote: > Yes, I'm cherry picking, but some points I wanted to touch on or ask > about.
Fair enough, I did the same thing :) > On Dec 1, 2005, at 2:40 PM, Cees Hek wrote: > > > There are <tonnes> of other annoyances with MySQL but I'll only list a > > couple that 'really' annoy me: > > - the first timestamp field in any table is always updated on every > > UPDATE > > - 0000-00-00 is a valid date in MySQL > > - 2005-02-30 is a valid date in MySQL > > - insert NULL into a NOT NULL column and MySQL will give it a > > default value > > - overflowing data is truncated instead of returning an error > > Some of this sort of thing bit us when we started using MySQL over > PostgreSQL for newer applications. I have been made aware, however, > of a new configuration option in Version 5.0 that removes some of > this "helpfulness" > > http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html But that just goes the route that PHP takes. If you don't like the way it works, change it in the config file. The result of that is a mish mash of code in the application to figure out how MySQL is actually configured (have you seen some of the PHP code that tries to figure out if mysql quoting is turned on, or if safe mode is enabled, and try to do the 'right thing' depending on the settings?). For an inhouse application that will be fine, but for an app that needs to run all over the place, you can never be sure what you get. The forum that I mentioned in my last post actually has a configuration setting that asks if you are using InnoDB or MyISAM tables. So the application becomes massively more complex because it needs to cater to the configuration of the database. Personally I would just put a requirement of InnoDB in my app, but not everyone can or wants to do that appearantly). > >> 2. Reliable > > Reliable in that MySQL rarely crashes. But not reliable when it comes > > to data integrity. The fact that MySQL has some of the best crash > > recovery tools available should be a sign. I still get table > > corruption happening regularly on heavily used tables (Although almost > > all of the time MySQL is able to recover those tables with the caveat > > that I can never be sure that nothing was deleted in the cleanup). > > If you got into trouble with data corruption and an RDBMS and you > didn't have recovery tools, I'd see being upset. But to "punish" > someone for having good tools? I think that's unfair. My point is that a good database should never have data corruption. Only a physical disk failure should ever kill the data (and you should be using RAID and good backups to solve that problem). The fact that MySQL has a ton of really good crash recovery tools (mostly for MyISAM tables) is an indicator to me that they are a necesary evil. However, I am very thankful that they are there, because I get good use out of them.. > > On the other hand, I still suffer from table corruption with MySQL. > > It still munges my data at a whim just so it doesn't have to throw an > > exception. I still have to do things with temporary tables that > > should be done with a sub-select (we don't run mysql 5 yet, since it > > is too new for me). > > Data corruption? In InnoDB tables? Are there general circumstances > when that occurs. I'm genuinely curious on that one. Sorry, I should have been clear on that. The large database I was talking about dates back about 5 years (but is still active and in production), but it uses MyISAM tables. I have considered converting, but haven't got around to it. As I mentioned, it is quite heavily used (mostly writes, as the data is not accessed by humans too often). The system will queue transactions to file when the database is down, and it takes about a half day to catch up after the tables are cleaned and put back in place. Management don't want to spend any money on it for more development (and I would rather not do it anyway :) ). They accept the occational downtime (every 3 - 4 months) and leave me to clean up the mess... So the problem is annoying, but managable. Anyway, my appologies for not specifying the table type, as it was an important piece on info in this case. However, on the topic of InnoDB tables, using them takes away the biggest claim of MySQL - that it is scorching fast! If I have to take a performance hit by using InnoDB tables, then I might as well be using PostgreSQL... Cheers, Cees --------------------------------------------------------------------- Web Archive: http://www.mail-archive.com/[email protected]/ http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2 To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
