Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Adrian (66)

Adrian
  adrianh@quietstars.com
http://www.quietstars.com/
The hats I wear at work include: accessibility consultant; information architect; software developer (of the agile/XP/TDD breed - mostly in Perl); usability consultant; web site designer. I'm very dull:-) . [technorati.com]

Journal of Adrian (66)

Friday March 09, 2007
07:17 AM

0000-00-00 vs NULL: Yet Another Reason to Hate MySQL

[ #32627 ]

Just to join Ovid in his recent rants...

My co-worker and I have just spent the last forty five minutes with a bug that boils down to this "interesting" behaviour...

create temporary table the_dates (d date not null default '0000-00-00');
insert into the_dates values ('0000-00-00');

So far so normal.... but:

mysql> select count(*) from the_dates where d is null;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from the_dates where d is not null;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

.... sigh....


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

0000-00-00 vs NULL: Yet Another Reason to Hate MySQL 15 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.

  • Why, why, why do people insist upon using this piece of crap database? The arguments I get into typically go like this:

    Them: "But MySQL is faster."
    Ovid: "Do you have benchmarks?"
    Them: "No, but MySQL is still faster than other databases."
    Ovid: "Have you benchmarked your app with other databases?"
    Them: "No, but others swear up and down that it's faster."
    Ovid: "So you feel that fast is more important that correct answers?"
    Them: "But I already know MySQL."
    Ovid: "That's a fair point. The Postg

    • I like the elephant moniker more than the dolphin one. Does that count?
    • /me quotes the old Quiznos commercials

      "I fear change. I shall keep my bushes."
    • I blame phpMyAdmin for the insidious spread of this crappy database.

      I chose PostgreSQL for my own project, and I haven't regretted it one bit. Plus, you can claim it's "enterprise software" to keep the PHB's happy now that it ships with Solaris 10 by default. I think you can even get a service contract with Sun for it if you want (but I'd have to double check).

      • I blame:

        1. PHP

        2. Having a company pushing it

        3. A few years there where Pg wasn't moving much

        4. Much easier to manage accounts, just connect remotely as root and start creating accounts.
    • I don't know Postgres at all, but I can imagine it's not just db "features" and semantics that differ and need to be accounted for, but also the rest of the tool chain, e.g. clients, data loaders, backup procedures, etc. that you need to re-learn.

      All databases suck, but in different ways. MySQL, however, seems special.

    • Once, I switched an entire 28-node MySQL cluster to a single PostgreSQL instance by making clever use of table inheritance and a one-liner PL/Pgsql script written in Python 3000! PG RULEZ!!

  • ...the Heisendate. I can't remember how many times I was bitten by this kind of MySQL's DWSGSWM (Do What Some Guy SomeWhen Meant).

    --
    Ordinary morality is for ordinary people. -- Aleister Crowley
  • Now that I use mysql at $work these mysql behaviour s worry me a lot more but at least this is one mysqlism I'm not likely to fall into. I do not expect to be counting nulls in a not null table.
    • Plus, in what way is '0000-00-00' a valid date? I can only see it as a (badly) stringified version of a NULL date. Thus, it should have been disallowed as an entry in this table. IMO it should be disallowed anywhere.

      • '0000-00-00' is a valid "zero value" date in MySQL - see the docs [mysql.com] for the evil details.

        Needless to say - not my schema.

    • We had some generic SQL that applied to several tables in similar ways.

      ... and goddamn it - it's just wrong to say a table is null and not null at the same time :-)

      • Now that I work with mysql I plan to read
        http://sql-info.de/mysql/gotchas.html [sql-info.de] properly at some stage.

        At least this feature is documented in the mysql
        docs (but who ever reads the docs)

        "For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:

        SELECT * FROM tbl_name WHERE date_column IS NULL

        This is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value."
  • It's even faster than Mysql. In my tests, queries in MyISAM tables are roughly twice as fast as the same queries in InnoDB tables, in the same MySQL server.

    Thus... what is Mysql good for? Mainly for "change once, read many many times" databases, IMO. Such as underlying CMS systems on websites.

    • Problem with "[Mysql is good for] ... CMS systems on websites" argurment is whoever uses MySQL would (try to) push|use|propogate elsewhere for any other db related work.

      (A rant.) Even more amazing thing is even when one knows that version 4.x is horrible (compared to 5.1, which is actually available), there would be no motivation to upgrade for applications might break. Version 4.x would keep on laughing & living due to installation of new applications (which would be deined the chance of using a bet

      --
      - parv
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.