Trouble creating a table

Hi, This statement works fine on my MySQL 5.0 dev machine, but on my
hosting environment, this statement

CREATE TABLE SCHOOL_LIST (
ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
FIRST_NAME VARCHAR(255),
LAST_NAME VARCHAR(255),
STUDENT_ID VARCHAR(255),
PRIMARY KEY (ID)
) TYPE = INNODB;
INSERT INTO SCHOOL_LIST (FIRST_NAME, LAST_NAME, STUDENT_ID) VALUES
('Dave', 'Zzzzz', '3'), ('Jim', 'Bruno', '1'), ('Beau', 'Clark', '2'),
('Peter', 'Leighton', '10'), ('Tony', 'Lucki', '5'), ('Greg', 'Metz',
'4'), ('Barry', 'O''Callaghan', '6'), ('Fiona', 'O''Carroll', '9'),
('Steve', 'Richards', '11'), ('Annette', 'Royal', '7'), ('Clarence',
'Slacker', '8');
CREATE TABLE CLASS_LIST (
ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
SCHOOL_LIST_ID INTEGER UNSIGNED NOT NULL,
CONSTRAINT FK_CLASS_LIST FOREIGN KEY (SCHOOL_LIST_ID)
REFERENCES SCHOOL_LIST(ID),
PRIMARY KEY (ID)
) TYPE = INNODB;

gives this error

MySQL said: Documentation
#1005 - Can't create table './hmco/CLASS_LIST.frm' (errno: 150)

How can I rewrite the CLASS_LIST table creation statement to make this
work?

Thanks, - Dave

laredotornado@zipmail.com wrote:
> Hi, This statement works fine on my MySQL 5.0 dev machine, but on my
> hosting environment, this statement
>
> CREATE TABLE SCHOOL_LIST (
> ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
> FIRST_NAME VARCHAR(255),
> LAST_NAME VARCHAR(255),
> STUDENT_ID VARCHAR(255),
> PRIMARY KEY (ID)
> ) TYPE = INNODB;
> INSERT INTO SCHOOL_LIST (FIRST_NAME, LAST_NAME, STUDENT_ID) VALUES
> ('Dave', 'Zzzzz', '3'), ('Jim', 'Bruno', '1'), ('Beau', 'Clark', '2'),
> ('Peter', 'Leighton', '10'), ('Tony', 'Lucki', '5'), ('Greg', 'Metz',
> '4'), ('Barry', 'O''Callaghan', '6'), ('Fiona', 'O''Carroll', '9'),
> ('Steve', 'Richards', '11'), ('Annette', 'Royal', '7'), ('Clarence',
> 'Slacker', '8');
> CREATE TABLE CLASS_LIST (
> ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
> SCHOOL_LIST_ID INTEGER UNSIGNED NOT NULL,
> CONSTRAINT FK_CLASS_LIST FOREIGN KEY (SCHOOL_LIST_ID)
> REFERENCES SCHOOL_LIST(ID),
> PRIMARY KEY (ID)
> ) TYPE = INNODB;
>
> gives this error
>
> MySQL said: Documentation
> #1005 - Can't create table './hmco/CLASS_LIST.frm' (errno: 150)
>
> How can I rewrite the CLASS_LIST table creation statement to make this
> work?
>
> Thanks, - Dave
>

Dave,

What does SHOW INNODB STATUS tell you after this error?

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

On Apr 13, 3:13 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> laredotorn...@zipmail.com wrote:
> > Hi, This statement works fine on my MySQL 5.0 dev machine, but on my
> > hosting environment, this statement
>
> > CREATE TABLE SCHOOL_LIST (
> > ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
> > FIRST_NAME VARCHAR(255),
> > LAST_NAME VARCHAR(255),
> > STUDENT_ID VARCHAR(255),
> > PRIMARY KEY (ID)
> > ) TYPE = INNODB;
> > INSERT INTO SCHOOL_LIST (FIRST_NAME, LAST_NAME, STUDENT_ID) VALUES
> > ('Dave', 'Zzzzz', '3'), ('Jim', 'Bruno', '1'), ('Beau', 'Clark', '2'),
> > ('Peter', 'Leighton', '10'), ('Tony', 'Lucki', '5'), ('Greg', 'Metz',
> > '4'), ('Barry', 'O''Callaghan', '6'), ('Fiona', 'O''Carroll', '9'),
> > ('Steve', 'Richards', '11'), ('Annette', 'Royal', '7'), ('Clarence',
> > 'Slacker', '8');
> > CREATE TABLE CLASS_LIST (
> > ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
> > SCHOOL_LIST_ID INTEGER UNSIGNED NOT NULL,
> > CONSTRAINT FK_CLASS_LIST FOREIGN KEY (SCHOOL_LIST_ID)
> > REFERENCES SCHOOL_LIST(ID),
> > PRIMARY KEY (ID)
> > ) TYPE = INNODB;
>
> > gives this error
>
> > MySQL said: Documentation
> > #1005 - Can't create table './hmco/CLASS_LIST.frm' (errno: 150)
>
> > How can I rewrite the CLASS_LIST table creation statement to make this
> > work?
>
> > Thanks, - Dave
>
> Dave,
>
> What does SHOW INNODB STATUS tell you after this error?
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================


My hosting company denies me this privilege:

#1227 - Access denied. You need the SUPER privilege for this
operation

- Dave

laredotornado@zipmail.com wrote:
> On Apr 13, 3:13 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> laredotorn...@zipmail.com wrote:
>>> Hi, This statement works fine on my MySQL 5.0 dev machine, but on my
>>> hosting environment, this statement
>>> CREATE TABLE SCHOOL_LIST (
>>> ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
>>> FIRST_NAME VARCHAR(255),
>>> LAST_NAME VARCHAR(255),
>>> STUDENT_ID VARCHAR(255),
>>> PRIMARY KEY (ID)
>>> ) TYPE = INNODB;
>>> INSERT INTO SCHOOL_LIST (FIRST_NAME, LAST_NAME, STUDENT_ID) VALUES
>>> ('Dave', 'Zzzzz', '3'), ('Jim', 'Bruno', '1'), ('Beau', 'Clark', '2'),
>>> ('Peter', 'Leighton', '10'), ('Tony', 'Lucki', '5'), ('Greg', 'Metz',
>>> '4'), ('Barry', 'O''Callaghan', '6'), ('Fiona', 'O''Carroll', '9'),
>>> ('Steve', 'Richards', '11'), ('Annette', 'Royal', '7'), ('Clarence',
>>> 'Slacker', '8');
>>> CREATE TABLE CLASS_LIST (
>>> ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
>>> SCHOOL_LIST_ID INTEGER UNSIGNED NOT NULL,
>>> CONSTRAINT FK_CLASS_LIST FOREIGN KEY (SCHOOL_LIST_ID)
>>> REFERENCES SCHOOL_LIST(ID),
>>> PRIMARY KEY (ID)
>>> ) TYPE = INNODB;
>>> gives this error
>>> MySQL said: Documentation
>>> #1005 - Can't create table './hmco/CLASS_LIST.frm' (errno: 150)
>>> How can I rewrite the CLASS_LIST table creation statement to make this
>>> work?
>>> Thanks, - Dave
>> Dave,
>>
>> What does SHOW INNODB STATUS tell you after this error?
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
>
> My hosting company denies me this privilege:
>
> #1227 - Access denied. You need the SUPER privilege for this
> operation
>
> - Dave
>

Dave,

It's pretty much of a guess, then. Without the ability to see what
INNODB is complaining about there's little way to tell what the problem
is, especially since it's working on your other system.

Maybe a version difference? Don't know.

But I do wonder why you would have a table with only an id and a foreign
key column...
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================