Professional Web Applications Themes

The infamous errorno:150 - MySQL

Hi, I'm using MySQL 5 on Fedora Core 5 Linux. I'm trying to create a couple of tables and am getting errors when creating the second. Any ideas? DROP TABLE IF EXISTS `PROD_PRODUCTS`; CREATE TABLE IF NOT EXISTS `PROD_PRODUCTS` ( `PRODUCT_ID` VARCHAR(255) NOT NULL, `SKU` varchar(16) default NULL, `SUB_CATEGORY_ID` VARCHAR(255) NOT NULL default '0', `NAME` varchar(255) NOT NULL default '', `DESCRIPTION` text, `PRICE` float unsigned default NULL, `QUANTITY` int(10) unsigned NOT NULL default '1', MODEL_NUM VARCHAR(32), COLOR VARCHAR(32), EXAMPLE_IMG_PATH VARCHAR(255), `LOW_QUANTITY` int(11) default NULL, PRIMARY KEY (`PRODUCT_ID`), KEY `PROD_PRODUCTS_IDX1` (`SUB_CATEGORY_ID`) ) TYPE=InnoDB; ALTER TABLE PROD_PRODUCTS ADD COLUMN RETAIL_PRICE FLOAT UNSIGNED; ...

  1. #1

    Default The infamous errorno:150

    Hi,

    I'm using MySQL 5 on Fedora Core 5 Linux. I'm trying to create a
    couple of tables and am getting errors when creating the second. Any
    ideas?

    DROP TABLE IF EXISTS `PROD_PRODUCTS`;
    CREATE TABLE IF NOT EXISTS `PROD_PRODUCTS` (
    `PRODUCT_ID` VARCHAR(255) NOT NULL,
    `SKU` varchar(16) default NULL,
    `SUB_CATEGORY_ID` VARCHAR(255) NOT NULL default '0',
    `NAME` varchar(255) NOT NULL default '',
    `DESCRIPTION` text,
    `PRICE` float unsigned default NULL,
    `QUANTITY` int(10) unsigned NOT NULL default '1',
    MODEL_NUM VARCHAR(32),
    COLOR VARCHAR(32),
    EXAMPLE_IMG_PATH VARCHAR(255),
    `LOW_QUANTITY` int(11) default NULL,
    PRIMARY KEY (`PRODUCT_ID`),
    KEY `PROD_PRODUCTS_IDX1` (`SUB_CATEGORY_ID`)
    ) TYPE=InnoDB;
    ALTER TABLE PROD_PRODUCTS ADD COLUMN RETAIL_PRICE FLOAT UNSIGNED;
    ALTER TABLE PROD_PRODUCTS ADD COLUMN MAX_YIELD INTEGER UNSIGNED;
    ALTER TABLE PROD_PRODUCTS ADD COLUMN MANUALLY_CREATED TINYINT DEFAULT
    0;

    DROP TABLE IF EXISTS PROD_PRODUCT_CUSTOM_PRICES;
    CREATE TABLE PROD_PRODUCT_CUSTOM_PRICES (
    PRODUCT_ID VARCHAR(255) NOT NULL,
    `SKU` varchar(16) default NULL,
    PRICE FLOAT UNSIGNED DEFAULT NULL,
    RETAIL_PRICE FLOAT UNSIGNED DEFAULT NULL,
    `DESCRIPTION` text,
    PRIMARY KEY (PRODUCT_ID),
    CONSTRAINT FK1_PROD_PRODUCT_CUSTOM_PRICES FOREIGN KEY
    (PRODUCT_ID) REFERENCES PROD_PRODUCTS(PRODUCT_ID) ON DELETE CASCADE
    ) TYPE = INNODB;

    ERROR 1005 (HY000): Can't create table './REFILLSTATION/
    PROD_PRODUCT_CUSTOM_PRICES.frm' (errno: 150)


    Thanks, - Dave

    laredotornado@zipmail.com Guest

  2. #2

    Default Re: The infamous errorno:150

    com wrote: 

    After the failure, what does "show innodb status" show?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: The infamous errorno:150

    On Feb 21, 10:31 pm, Jerry Stuckle <net> wrote: 





    >
    > After the failure, what does "show innodb status" show?
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    There's a lot there, but here's probably what you meant

    LATEST FOREIGN KEY ERROR
    ------------------------
    070221 23:28:30 Error in foreign key constraint of table REFILLSTATION/
    PROD_PRODUCT_CUSTOM_PRICES:
    FOREIGN KEY
    (PRODUCT_ID) REFERENCES PROD_PRODUCTS(PRODUCT_ID) ON DELETE CASCADE
    ) TYPE = INNODB:
    Cannot find an index in the referenced table where the
    referenced columns appear as the first columns, or column types
    in the table and the referenced table do not match for constraint.
    Note that the internal storage type of ENUM and SET changed in
    tables created with >= InnoDB-4.1.12, and such columns in old tables
    cannot be referenced by such columns in new tables.
    See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
    for correct foreign key definition.

    But the columns, PRODUCT_ID, are both defined the same way and there
    are indexes on both in both tables. So what else coudl it be?

    Thanks, - Dave

    laredotornado@zipmail.com Guest

  4. #4

    Default Re: The infamous errorno:150

    "com" <com> wrote in
    news:googlegroups.com:
     

    Cutting-and-pasting your code into my MySQL 5.0.27 on RHEL3 shows no
    problems whatsoever.


    --
    felix
    Felix Guest

  5. #5

    Default Re: The infamous errorno:150

    "com" <com> wrote: [/ref][/ref]
    <cut> 
    >> [/ref][/ref]
    <cut> 
    >> [/ref][/ref]

    <cut>
     

    Interesting. This error message is apparently bogus. However there are
    two things to note:

    1. A foreign key constraint between PRIMARY KEYS is quite uncommon.
    Why not just put the fields from the referencing table in the
    referenced table and allow them to be NULL?

    2. Using a VARCHAR(255) as PRIMARY KEY in an InnoDB table is a bad
    choice. Even more if you intend to use it for referencing.
    First point on this list:
    http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  6. #6

    Default Re: The infamous errorno:150

    com wrote: 
    >> After the failure, what does "show innodb status" show?
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================[/ref]
    >
    > There's a lot there, but here's probably what you meant
    >
    > LATEST FOREIGN KEY ERROR
    > ------------------------
    > 070221 23:28:30 Error in foreign key constraint of table REFILLSTATION/
    > PROD_PRODUCT_CUSTOM_PRICES:
    > FOREIGN KEY
    > (PRODUCT_ID) REFERENCES PROD_PRODUCTS(PRODUCT_ID) ON DELETE CASCADE
    > ) TYPE = INNODB:
    > Cannot find an index in the referenced table where the
    > referenced columns appear as the first columns, or column types
    > in the table and the referenced table do not match for constraint.
    > Note that the internal storage type of ENUM and SET changed in
    > tables created with >= InnoDB-4.1.12, and such columns in old tables
    > cannot be referenced by such columns in new tables.
    > See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
    > for correct foreign key definition.
    >
    > But the columns, PRODUCT_ID, are both defined the same way and there
    > are indexes on both in both tables. So what else coudl it be?
    >
    > Thanks, - Dave
    >[/ref]

    Hi, Dave,

    I agree with Axel on several accounts. It works fine here, also, on a
    couple of different versions (5.0.27 on Windows and 4.1.11 on Debian).

    I also agree you shouldn't use the varchar for an index. It will slow
    things down immensely when you're joining tables. Much better to have
    an integer for it.

    Also, there are reasons for having two primary keys in your constraint.
    However, those are few and far between. For instance, I'll do this
    when I have a large BLOB column (i.e. scanned doents, images, etc.).
    I'll keep them separate from the table which has the doent
    description, etc., because the latter table is generally used more often.

    But this isn't one of those cases; your data are relatively small, and I
    also think you would be better off with everything in one table.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. CFusion MX and the infamous "JRUN closed connection'
    By Dennis P. Smith in forum Coldfusion Server Administration
    Replies: 0
    Last Post: May 25th, 06:36 PM
  2. MXa and the infamous grouped objects 'bug'
    By Head webforumsuser@macromedia.com in forum Macromedia Freehand
    Replies: 6
    Last Post: October 23rd, 01:31 PM
  3. Replies: 0
    Last Post: October 19th, 01:37 AM
  4. Passing optional errorno/errormsg by reference
    By BKDotCom in forum PHP Development
    Replies: 9
    Last Post: October 3rd, 10:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139