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 wrote:
> 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
>

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

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

On Feb 21, 10:31 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> laredotorn...@zipmail.com wrote:
> > 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
>
> After the failure, what does "show innodb status" show?
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

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/In...nstraints.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" <laredotornado@zipmail.com> wrote in
news:1172099457.912399.327270@v33g2000cwv.googlegr oups.com:

> 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?

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


--
felix

"laredotornado@zipmail.com" <laredotornado@zipmail.com> wrote:
>>
>> > CREATE TABLE IF NOT EXISTS `PROD_PRODUCTS` (
>> > `PRODUCT_ID` VARCHAR(255) NOT NULL,
<cut>
>> > PRIMARY KEY (`PRODUCT_ID`),
>> > KEY `PROD_PRODUCTS_IDX1` (`SUB_CATEGORY_ID`)
>> > ) TYPE=InnoDB;
>>
>> > CREATE TABLE PROD_PRODUCT_CUSTOM_PRICES (
>> > PRODUCT_ID VARCHAR(255) NOT NULL,
<cut>
>> > 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)

<cut>

> 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.

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/...db-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/

laredotornado@zipmail.com wrote:
> On Feb 21, 10:31 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> laredotorn...@zipmail.com wrote:
>>> 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
>> After the failure, what does "show innodb status" show?
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> 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/In...nstraints.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
>

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 documents, images, etc.).
I'll keep them separate from the table which has the document
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.
jstucklex@attglobal.net
==================