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)

Thursday March 29, 2007
05:44 AM

Inconsistant metadata: Yet Another Reason to Hate MySQL

[ #32832 ]

Compare and contrast:

-- schema A
CREATE TABLE products (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    vendor_id   INT UNSIGNED REFERENCES vendors (id),
    name        VARCHAR(255),
    UNIQUE      ( name ),
    INDEX       ( vendor_id )
) TYPE = InnoDB;

-- schema B
CREATE TABLE products (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    vendor_id   INT UNSIGNED,
    name        VARCHAR(255),
    UNIQUE      ( name ),
    INDEX       ( vendor_id ),
    FOREIGN KEY ( vendor_id ) REFERENCES vendors ( id )
) TYPE = InnoDB;

Schema B sets up the meta-data that Rose::DB::Object::Loader uses to figure out the relationships between tables automatically. Schema A does not.

Six.... fardling... hours.... wasted.

Many thanks to John Siracusa for pointing me to the solution.

You can almost see the bit of code that needs to be refactored.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Yeah, I've already seen the annoying bit about inline FK references being parsed and discarded. Further, I was pretty sure it was silently discarded, so I ran your query:

    CREATE TABLE products (
        id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        vendor_id   INT UNSIGNED REFERENCES vendors (id),
        name        VARCHAR(255),
        UNIQUE      ( name ),
        INDEX   

    • Maybe it can be a can of tuna with a dolphin on it and the label says "MySQL Sucks!".

      I have seen enough just on these blogs that I would have to agree.
    • Does the incorrect syntax generate an error if you run MySQL in strict mode?
      • Our version of MySQL is old enough that strict mode is not available. I guess that makes my post a wee bit unfair :)

        • Quoth Aaron Crane on hates-software:

          Ah, yes, the “version N+1” problem: everything you think you might want out of MySQL is supported (somehow, even if brokenly and hatefully) in the version after the one that’s available on your production servers. (Actually, sometimes MySQL makes it version N+k, to give you some light relief.) I believe the “version N+1” phrasing is due to Smylers (the original poster in this thread), circa 2000. It’s still true. Seven years later. St

  • Are you using an old version of MySQL or is this still a problem with the information schema in version 5?