Richard,
----- Original Message -----
From: ""Richard Harms"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Monday, April 22, 2002 2:32 AM
Subject: InnoDB Error 150, MySQL 4.0.1 alpha & 3.23.49
> Hello,
>
> When attempting to create some tables using the MySQL 4.0.1 alpha and
> 3.23.49, I'm getting a generic error message, "General error: Can't
> create table './industria/ORDEREDITEMS.frm' (errno: 150)." The other 6
> tables, all using similar features, are created without a problem.
> perror just says, "Error code 150: Unknown error 150" which isn't
> terribly helpful.
>
> Some assistance with this would be appreciated. :-)
>
> -rh
>
> CREATE TABLE VENDORS ( V_VENDORID BIGINT NOT NULL, V_VENDORNAME
> VARCHAR(20) NOT NULL, V_DESCRIPTION VARCHAR(80) NOT NULL, V_LOCKCNT
> BIGINT NOT NULL DEFAULT '0', V_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
> (V_VENDORID), UNIQUE KEY VENDORS_VENDORNAME_IDX (V_VENDORNAME) )
> TYPE=InnoDB;
>
> CREATE TABLE ITEMS ( I_ITEMID BIGINT NOT NULL, I_V_VENDORID BIGINT NOT
> NULL, I_ITEMNAME VARCHAR(20) NOT NULL, I_DESCRIPTION VARCHAR(80) NOT
> NULL, I_ACTIVE ENUM('T','F') NOT NULL, I_PRICE DECIMAL(10, 2) NOT NULL,
> I_LOCKCNT BIGINT NOT NULL DEFAULT '0', I_TIMESTAMP TIMESTAMP NOT NULL,
> PRIMARY KEY (I_ITEMID), UNIQUE KEY ITEMS_VENDORITEM_IDX (I_V_VENDORID,
> I_ITEMNAME), FOREIGN KEY (I_V_VENDORID) REFERENCES VENDORS(V_VENDORID)
> ON DELETE CASCADE ) TYPE=InnoDB;
>
> CREATE TABLE CATALOGS ( C_CATALOGID BIGINT NOT NULL, C_CATALOGNAME
> VARCHAR(20) NOT NULL, C_DESCRIPTION VARCHAR(80) NOT NULL, C_LOCKCNT
> BIGINT NOT NULL DEFAULT '0', C_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
> (C_CATALOGID), UNIQUE KEY CATALOGS_CATALOGNAME_IDX (C_CATALOGNAME) )
> TYPE=InnoDB;
>
> CREATE TABLE CATEGORIES ( CG_CATEGORYID BIGINT NOT NULL, CG_C_CATALOGID
> BIGINT NOT NULL, CG_CATEGORYNAME VARCHAR(20) NOT NULL, CG_CG_CATEGORYID
> BIGINT NOT NULL, CG_DESCRIPTION VARCHAR(80) NOT NULL, CG_LOCKCNT BIGINT
> NOT NULL DEFAULT '0', CG_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
> (CG_CATEGORYID), UNIQUE KEY CATEGORIES_CATALOGCATEGORY_IDX
> (CG_C_CATALOGID, CG_CATEGORYNAME), KEY CATEGORIES_CATALOGID_IDX
> (CG_C_CATALOGID), KEY CATEGORIES_PARENTCATEGORYID_IDX
> (CG_CG_CATEGORYID), FOREIGN KEY (CG_C_CATALOGID) REFERENCES
> CATALOGS(C_CATALOGID) ON DELETE CASCADE, FOREIGN KEY (CG_CG_CATEGORYID)
> REFERENCES CATEGORIES(CG_CATEGORYID) ON DELETE CASCADE ) TYPE=InnoDB;
>
> CREATE TABLE CATAGORYITEMS ( CI_CG_CATEGORYID BIGINT NOT NULL,
> CI_I_ITEMID BIGINT NOT NULL, CI_LOCKCNT BIGINT NOT NULL DEFAULT '0',
> CI_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY (CI_CG_CATEGORYID,
> CI_I_ITEMID), KEY CATAGORYITEMS_ITEMID_IDX (CI_I_ITEMID), FOREIGN KEY
> (CI_CG_CATEGORYID) REFERENCES CATEGORIES(CG_CATEGORYID) ON DELETE
> CASCADE, FOREIGN KEY (CI_I_ITEMID) REFERENCES ITEMS(I_ITEMID) ON DELETE
> CASCADE ) TYPE=InnoDB;
>
> CREATE TABLE ORDERS ( O_ORDERID BIGINT NOT NULL, O_ORDEREDITEMSEQUENCE
> INT NOT NULL, O_LOCKCNT BIGINT NOT NULL DEFAULT '0', O_TIMESTAMP
> TIMESTAMP NOT NULL, PRIMARY KEY (O_ORDERID) ) TYPE=InnoDB;
>
> CREATE TABLE ORDEREDITEMS ( OI_O_ORDERID BIGINT NOT NULL,
> OI_ORDEREDITEMSEQUENCE INT NOT NULL, OI_I_ITEMID BIGINT NOT NULL,
> OI_QUANTITY INT(4) NOT NULL, OI_LOCKCNT BIGINT NOT NULL DEFAULT '0',
> OI_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY (OI_O_ORDERID,
> OI_ORDEREDITEMSEQUENCE), FOREIGN KEY (OI_O_ORDERID) REFERENCES
> ORDERS(O_ORDERID) ON DELETE CASCADE, FOREIGN KEY (OI_I_ITEMID)
> REFERENCES ITEMS(I_ITEMID) ON DELETE CASCADE ) TYPE=InnoDB;
there is no index on OI_I_ITEMID. InnoDB requires that a suitable index is
explicitly defined for a foreign key. Also note that ON DELETE CASCADE only
works from 3.23.50 up. When you migrate to 3.23.50, you must recreate your
tables so that InnoDB is aware of ON DELETE CASCADE.
java.sql.SQLException: General error: Can't create table
> './industria/ORDEREDITEMS.frm' (errno: 150)
> at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
> at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
> at org.gjt.mm.mysql.MysqlIO.sqlQuery(Unknown Source)
> at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
> at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
> at org.gjt.mm.mysql.Statement.executeQuery(Unknown Source)
> at org.gjt.mm.mysql.jdbc2.Statement.executeQuery(Unknown Source)
> at
> com.darkrealms.entities.DatabaseUtilities.createMissingTable(DatabaseUti
> lities.java:432)
> at
> com.darkrealms.entities.DatabaseUtilities.checkTables(DatabaseUtilities.
> java:464)
> at
> com.darkrealms.entities.DatabaseUtilities.openDatabaseConnection(Databas
> eUtilities.java:38)
> at
> com.darkrealms.entities.DatabaseUtilitiesForMySQL.<init>(DatabaseUtiliti
> esForMySQL.java:19)
> at
> com.darkrealms.entities.DatabaseFromXML.main(DatabaseFromXML.java:17)
> Exception in thread "main"
Best regards,
Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
