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
