Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    11

    Wink Unanswered: innodb FK errors

    I have built a database with about 22 tables, most innoDB, and 4 tables which require BLOBS, are built as myISAM types.

    The tables were built successfully, but on a number of tables, when I attempt to ALter Table Add Constraint' I get the following error

    mysql> ALTER TABLE Inventory ADD CONSTRAINT FK_Inventory FOREIGN KEY (cat_2_id) REFERENCES Misc_Category_2 (cat_2_id) ON DELETE CASCADE ON UPDATE CASCADE;
    ERROR 1005: Can't create table '.\bos\#sql-2c8_8.frm' (errno: 150)
    mysql>

    both tables Inventory and Misc_Category_2 have been successfully built, with all of their index's.

    Is this an innodb anomaly? I have been careful to drop tables first, before dropping the database when there were errors in building it.

    Thanks

    Simon

  2. #2
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320

    Re: innodb FK errors

    Originally posted by simetz
    I have built a database with about 22 tables, most innoDB, and 4 tables which require BLOBS, are built as myISAM types.

    The tables were built successfully, but on a number of tables, when I attempt to ALter Table Add Constraint' I get the following error

    mysql> ALTER TABLE Inventory ADD CONSTRAINT FK_Inventory FOREIGN KEY (cat_2_id) REFERENCES Misc_Category_2 (cat_2_id) ON DELETE CASCADE ON UPDATE CASCADE;
    ERROR 1005: Can't create table '.\bos\#sql-2c8_8.frm' (errno: 150)
    mysql>

    both tables Inventory and Misc_Category_2 have been successfully built, with all of their index's.

    Is this an innodb anomaly? I have been careful to drop tables first, before dropping the database when there were errors in building it.

    Thanks

    Simon
    Are you sure that Inventory and Misc_Category_2 tables are innodb tables cause frm is myisam file type
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  3. #3
    Join Date
    Mar 2003
    Posts
    11

    Re: innodb FK errors

    Originally posted by omiossec
    Are you sure that Inventory and Misc_Category_2 tables are innodb tables cause frm is myisam file type
    Yes I am quite sure that they are innodb. From command line I have run "show table status ", and all the tables (except the two which were specified to contain BLOBs and were built as myISAM) show Type =innoDB.

    Most of the tables show row format=dynamic, but there are two which show row type = fixed.

    In my data file all the tables have a .frm file, and the 2 myISAMs each have in addition the .MYD and .MYI files.

    There are only two tables which the Referential Integrity constraints (FK's and Cascading) that were in my sql batch file)

    Do you have any idea what has happened or how I might correct it? I have tried rebuilding after running delete table, and the same issues re-occur.

    Thanks

    Simon

  4. #4
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80

    Re: innodb FK errors

    Originally posted by simetz
    mysql> ALTER TABLE Inventory ADD CONSTRAINT FK_Inventory FOREIGN KEY (cat_2_id) REFERENCES Misc_Category_2 (cat_2_id) ON DELETE CASCADE ON UPDATE CASCADE;
    ERROR 1005: Can't create table '.\bos\#sql-2c8_8.frm' (errno: 150)
    mysql>
    Is there already data in the tables? If you have rows in Inventory that point to a non-existent row in Misc_Category, the ALTER TABLE statement will fail.

    Bradley
    Bradley

  5. #5
    Join Date
    Mar 2003
    Posts
    11

    Re: innodb FK errors

    Originally posted by bcrockett
    Is there already data in the tables? If you have rows in Inventory that point to a non-existent row in Misc_Category, the ALTER TABLE statement will fail.

    Bradley
    No, there is no data at all in the tables yet. But I get the same error every time I build it.

    Here is the contents of the my.ini file, in case this helps.

    [WinMySQLAdmin]
    Server=C:/mysql/bin/mysqld-max-nt.exe

    [mysqld]
    innodb_data_home_dir = c:/mysql/data
    innodb_data_file_path=data:200M:autoextend

    Thanks

  6. #6
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    Could you post your table creation script or a dump?

    Bradley
    Bradley

  7. #7
    Join Date
    Mar 2003
    Posts
    11
    Originally posted by bcrockett
    Could you post your table creation script or a dump?

    Bradley
    sure the whole file is very large but is is a sampling of the affected tables.

    CREATE TABLE Misc_Category_2 (
    cat_2_id INTEGER NOT NULL AUTO_INCREMENT,
    cat_2_name VARCHAR ( 50 ) NOT NULL,
    cat_2_discount DOUBLE PRECISION,
    CONSTRAINT PK_misc_category_261 PRIMARY KEY (cat_2_id)
    )
    Type = InnoDB;
    CREATE TABLE Inventory (
    item_id INTEGER NOT NULL AUTO_INCREMENT,
    item_num INTEGER,
    item_name VARCHAR ( 50 ) NOT NULL,
    SKU INTEGER NOT NULL,
    vendor_id INTEGER NOT NULL,
    manufacturer_name VARCHAR ( 50 ) NOT NULL,
    item_introduction_date VARCHAR ( 20 ),
    dept_id INTEGER NOT NULL,
    cat_1_id INTEGER NOT NULL,
    cat_2_id INTEGER NOT NULL,
    similar_item_1 INTEGER,
    similar_item_2 INTEGER,
    wholesale_price DOUBLE PRECISION NOT NULL,
    retail_price DOUBLE PRECISION NOT NULL,
    quantity_on_hand INTEGER NOT NULL,
    quantity_on_order INTEGER,
    reorder_level INTEGER,
    inventory_discount DOUBLE PRECISION,
    misc1 INTEGER,
    misc2 DOUBLE PRECISION,
    misc3 VARCHAR ( 50 ),
    CONSTRAINT PK_Inventory8 PRIMARY KEY (item_id)
    )
    Type = InnoDB;
    CREATE INDEX inventory_index ON Inventory (item_id, SKU, cat_1_id, cat_2_id, dept_id, vendor_id, manufacturer_name);
    CREATE INDEX misc_category_index ON Misc_Category_2 (cat_2_id, cat_2_name);
    ALTER TABLE Inventory ADD CONSTRAINT FK_Inventory81 FOREIGN KEY (cat_2_id) REFERENCES Misc_Category_2 (cat_2_id) ON DELETE CASCADE ON UPDATE CASCADE;
    ALTER TABLE Inventory ADD CONSTRAINT FK_Inventory80 FOREIGN KEY (cat_1_id) REFERENCES Misc_Category_1 (cat_1_id) ON DELETE CASCADE ON UPDATE CASCADE;
    ALTER TABLE Inventory ADD CONSTRAINT FK_Inventory78 FOREIGN KEY (dept_id) REFERENCES Dept (dept_id) ON DELETE CASCADE ON UPDATE CASCADE;
    ALTER TABLE Inventory ADD CONSTRAINT FK_Inventory76 FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id) ON DELETE CASCADE ON UPDATE CASCADE;


    Thanks again,

    Simon

  8. #8
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    InnoDB tables require you to manually add an index on the foreign key column of the referencing table. You need to add an index on the cat_2_id column of the Inventory table.

    Take a look at http://www.mysql.com/doc/en/SEC465.html.

    Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the FIRST columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly.
    Add the following line to the Inventory table creation statement:

    CREATE INDEX inventory_cat_2_id_index ON Inventory (cat_2_id);

    That oughta' do it!
    Bradley

Posting Permissions

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