MySQL Forums
Forum List  »  InnoDB

sql works under 5.0.51a but not under 5.1
Posted by: landon kelsey
Date: December 28, 2008 12:54AM

sql works under 5.0.51a (Fedora 9) but not under 5.1 (Windows XP Prof SP3)

should I use InnoDB?

C:\>mysql -ulandon -p
Enter password: ***********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.30-community MySQL Community Server (GPL) ???????

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

fails in the alter statements at the end

ERROR 1005 (HY000): Can't create table 'groff_weinberg.#sql-674_4' (errno: 150)
ERROR 1005 (HY000): Can't create table 'groff_weinberg.#sql-674_4' (errno: 150)
ERROR 1005 (HY000): Can't create table 'groff_weinberg.#sql-674_4' (errno: 150)
ERROR 1005 (HY000): Can't create table 'groff_weinberg.#sql-674_4' (errno: 150)
ERROR 1005 (HY000): Can't create table 'groff_weinberg.#sql-674_4' (errno: 150)
ERROR 1005 (HY000): Can't create table 'groff_weinberg.#sql-674_4' (errno: 150)
ERROR 1005 (HY000): Can't create table 'groff_weinberg.#sql-674_4' (errno: 150)

sql:

create table salesreps
(empl_num int not null,
name varchar(15) not null,
age int,
rep_office int,
title varchar(10),
hire_date datetime not null,
manager int,
quota float,
sales float not null,
primary key (empl_num ));

create table offices
(office int not null,
city varchar(15) not null,
region varchar(10) not null,
mgr int,
target int,
sales int not null,
primary key (office));

create table customers
(cust_num int not null,
company varchar(20) not null,
cust_rep int,
credit_limit float,
primary key (cust_num));

create table products
(mfr_id char(3) not null,
product_id char(5) not null,
description varchar(20) not null,
price float not null,
qty_on_hand int not null,
primary key (mfr_id, product_id));

create table orders
(order_num int not null,
order_datetime datetime not null,
cust int not null,
rep int,
mfr char(3) not null,
product char(5) not null,
qty int not null,
amount float not null,
primary key (order_num));
insert into salesreps values (109,'Mary Jones',31,11,'Sales Rep',STR_TO_DATE('12-10-89', '%d-%m-%Y'),106,300000.00,392725.00);
insert into salesreps values (102,'Sue Smith',48,21,'Sales Rep',STR_TO_DATE('10-12-86', '%d-%m-%Y'),108,350000.00,474050.00);
insert into salesreps values (106,'Sam Clark',52,11,'VP Sales',STR_TO_DATE('14-06-88', '%d-%m-%Y'),NULL,275000.00,299912.00);
insert into salesreps values (104,'Bob Smith',33,12,'Sales Mgr',STR_TO_DATE('19-05-87', '%d-%m-%Y'),106,200000.00,142594.00);
insert into salesreps values (101,'Dan Roberts',45,12,'Sales Rep',STR_TO_DATE('20-10-86', '%d-%m-%Y'),104,300000.00,305673.00);
insert into salesreps values (110,'Tom Snyder',41,NULL,'Sales Rep',STR_TO_DATE('13-01-90', '%d-%m-%Y'),101,NULL,75985.00);
insert into salesreps values (108,'Larry Fitch',62,21,'Sales Mgr',STR_TO_DATE('12-10-89', '%d-%m-%Y'),106,350000.00,361865.00);
insert into salesreps values (103,'Paul Cruz',29,12,'Sales Rep',STR_TO_DATE('01-03-87', '%d-%m-%Y'),104,275000.00,286775.00);
insert into salesreps values (107,'Nancy Angelli',49,22,'Sales Rep',STR_TO_DATE('14-11-88', '%d-%m-%Y'),108,300000.00,186042.00);
insert into salesreps values (105,'Bill Adams',37,13,'Sales Rep',STR_TO_DATE('12-02-88', '%d-%m-%Y'),104,350000.00,367911.00);

insert into products values ('REI','2A45C','Ratchet Link',79.00,210);
insert into products values ('ACI','4100Y','Widget Remover',2750.00,25);
insert into products values ('QSA','XK47','Reducer',355.00,38);
insert into products values ('BIC','41672','Plate',180.00,0);
insert into products values ('IMM','779C','900-lb Brace',1875.00,9);
insert into products values ('ACI','41003','Size 3 Widget',107.00,207);
insert into products values ('ACI','41004','Size 4 Widget',117.00,139);
insert into products values ('BIC','41003','Handle',652.00,3);
insert into products values ('IMM','887P','Brace Pin',250.00,24);
insert into products values ('QSA','XK48','Reducer',134.00,203);
insert into products values ('REI','2A44L','Left Hinge',4500.00,12);
insert into products values ('FEA','112','Housing',148.00,115);
insert into products values ('IMM','887H','Brace Holder',54.00,223);
insert into products values ('BIC','41089','Retainer',225.00,78);
insert into products values ('ACI','41001','Size 1 Widget',55.00,277);
insert into products values ('IMM','775C','500-lb Brace',1425.00,5);
insert into products values ('ACI','4100Z','Widget Installer',2500.00,28);
insert into products values ('QSA','XK48A','Reducer',177.00,37);
insert into products values ('ACI','41002','Size 2 Widget',76.00,167);
insert into products values ('REI','2A44R','Right Hinge',4500.00,12);
insert into products values ('IMM','773C','300-lb Brace',975.00,28);
insert into products values ('ACI','4100X','Widget Adjuster',25.00,37);
insert into products values ('FEA','114','Motor Mount',243.00,15);
insert into products values ('IMM','887X','Brace Retainer',475.00,32);
insert into products values ('REI','2A44G','Hinge Pin',350.00,14);

insert into orders values (112961,STR_TO_DATE('17-12-89', '%d-%m-%Y'),2117,106,'REI','2A44L',7,31500.00);
insert into orders values (113012,STR_TO_DATE('11-01-90', '%d-%m-%Y'),2111,105,'ACI','41003',35,3745.00);
insert into orders values (112989,STR_TO_DATE('03-01-90', '%d-%m-%Y'),2101,106,'FEA','114',6,1458.00);
insert into orders values (113051,STR_TO_DATE('10-02-90', '%d-%m-%Y'),2118,108,'QSA','XK47',4,1420.00);
insert into orders values (112968,STR_TO_DATE('12-10-89', '%d-%m-%Y'),2102,101,'ACI','41004',34,3978.00);
insert into orders values (113036,STR_TO_DATE('30-01-90', '%d-%m-%Y'),2107,110,'ACI','4100Z',9,22500.00);
insert into orders values (113045,STR_TO_DATE('02-02-90', '%d-%m-%Y'),2112,108,'REI','2A44R',10,45000.00);
insert into orders values (112963,STR_TO_DATE('17-12-89', '%d-%m-%Y'),2103,105,'ACI','41004',28,3276.00);
insert into orders values (113013,STR_TO_DATE('14-01-90', '%d-%m-%Y'),2118,108,'BIC','41003',1,652.00);
insert into orders values (113058,STR_TO_DATE('23-02-90', '%d-%m-%Y'),2108,109,'FEA','112',10,1480.00);
insert into orders values (112997,STR_TO_DATE('08-01-90', '%d-%m-%Y'),2124,107,'BIC','41003',1,652.00);
insert into orders values (112983,STR_TO_DATE('27-12-89', '%d-%m-%Y'),2103,105,'ACI','41004',6,702.00);
insert into orders values (113024,STR_TO_DATE('20-01-90', '%d-%m-%Y'),2114,108,'QSA','XK47',20,7100.00);
insert into orders values (113062,STR_TO_DATE('24-02-90', '%d-%m-%Y'),2124,107,'FEA','114',10,2430.00);
insert into orders values (112979,STR_TO_DATE('12-10-89', '%d-%m-%Y'),2114,102,'ACI','4100Z',6,15000.00);
insert into orders values (113027,STR_TO_DATE('22-01-90', '%d-%m-%Y'),2103,105,'ACI','41002',54,4104.00);
insert into orders values (113007,STR_TO_DATE('08-01-90', '%d-%m-%Y'),2112,108,'IMM','773C',3,2925.00);
insert into orders values (113069,STR_TO_DATE('02-03-90', '%d-%m-%Y'),2109,107,'IMM','775C',22,31350.00);
insert into orders values (113034,STR_TO_DATE('29-01-90', '%d-%m-%Y'),2107,110,'REI','2A45C',8,632.00);
insert into orders values (112992,STR_TO_DATE('04-11-89', '%d-%m-%Y'),2118,108,'ACI','41002',10,760.00);
insert into orders values (112975,STR_TO_DATE('12-10-89', '%d-%m-%Y'),2111,103,'REI','2A44G',6,2100.00);
insert into orders values (113055,STR_TO_DATE('15-02-90', '%d-%m-%Y'),2108,101,'ACI','4100X',6,150.00);
insert into orders values (113048,STR_TO_DATE('10-02-90', '%d-%m-%Y'),2120,102,'IMM','779C',2,3750.00);
insert into orders values (112993,STR_TO_DATE('04-01-89', '%d-%m-%Y'),2106,102,'REI','2A45C',24,1896.00);
insert into orders values (113065,STR_TO_DATE('27-02-90', '%d-%m-%Y'),2106,102,'QSA','XK47',6,2130.00);
insert into orders values (113003,STR_TO_DATE('25-01-90', '%d-%m-%Y'),2108,109,'IMM','779C',3,5625.00);
insert into orders values (113049,STR_TO_DATE('10-02-90', '%d-%m-%Y'),2118,108,'QSA','XK47',2,776.00);
insert into orders values (112987,STR_TO_DATE('31-12-89', '%d-%m-%Y'),2103,105,'ACI','4100Y',11,27500.00);
insert into orders values (113057,STR_TO_DATE('18-02-90', '%d-%m-%Y'),2111,103,'ACI','4100X',24,600.00);
insert into orders values (113042,STR_TO_DATE('02-02-90', '%d-%m-%Y'),2113,101,'REI','2A44R',5,22500.00);

insert into offices values (22,'Denver','Western',108,300000.00,186042.00);
insert into offices values (11,'New York','Eastern',106,575000.00,692637.00);
insert into offices values (12,'Chicago','Eastern',104,800000.00,735042.00);
insert into offices values (13,'Atlanta','Eastern',105,350000.00,367911.00);
insert into offices values (21,'Los Angeles','Western',108,725000.00,835915.00);
insert into offices values (45,'Dallas','Western',108,725000.00,835915.00);
insert into offices values (41,'Houston','Western',108,725000.00,835915.00);
insert into offices values (62,'Galveston','Western',108,725000.00,835915.00);
insert into offices values (29,'San Antonio','Western',108,725000.00,835915.00);
insert into offices values (49,'Austin','Western',108,725000.00,835915.00);


insert into customers values (2111,'JCP Inc.',103,50000.00);
insert into customers values (2102,'First Corp.',101,65000.00);
insert into customers values (2103,'Acme Mfg.',105,50000.00);
insert into customers values (2123,'Carter and Sons',102,40000.00);
insert into customers values (2107,'Ace International',110,35000.00);
insert into customers values (2115,'Smithson Corp.',101,20000.00);
insert into customers values (2101,'Jones Mfg.',106,65000.00);
insert into customers values (2112,'Zetacorp',108,50000.00);
insert into customers values (2121,'QMA Assoc.',103,45000.00);
insert into customers values (2114,'Orion Corp.',102,20000.00);
insert into customers values (2124,'Peter Brothers',107,40000.00);
insert into customers values (2108,'Holm Landis',109,55000.00);
insert into customers values (2117,'J.P. Sinclair',106,35000.00);
insert into customers values (2122,'Three-Way Lines',105,30000.00);
insert into customers values (2120,'Rico Enterprises',102,50000.00);
insert into customers values (2106,'Fred Lewis Corp.',102,65000.00);
insert into customers values (2119,'Solomon Inc.',109,25000.00);
insert into customers values (2118,'Midwest Systems',108,60000.00);
insert into customers values (2113,'Ian and Schmidt',104,20000.00);
insert into customers values (2109,'Chen Associates',103,25000.00);
insert into customers values (2105,'AAA Investments',101,45000.00);

alter table customers
add constraint hasrep
foreign key (cust_rep)
references salesreps
on delete no action;

alter table offices
add constraint hasmgr
foreign key (mgr)
references salesreps
on delete no action;

alter table orders
add constraint placedby
foreign key (cust)
references customers
on delete no action;

alter table orders
add constraint takenby
foreign key (rep)
references salesreps
on delete no action;

alter table orders
add constraint isfor
foreign key (mfr,product)
references products;

alter table salesreps
add constraint dummy
foreign key (manager)
references salesreps
on delete no action;

alter table salesreps
add constraint worksin
foreign key (rep_office)
references offices
on delete no action;

Options: ReplyQuote


Subject
Views
Written By
Posted
sql works under 5.0.51a but not under 5.1
4142
December 28, 2008 12:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.