You can't add constraints referencing indices that don't exist. Create your indecies first.
Ian Hartas wrote:
Description:
I have a database schema .sql file which is accepted when run with MyISAM tables. If I change the tables to be type = InnoDB, then it fails with the error message:
mysql -u ts -p hbs <schema_mysql.sql ERROR 1005 at line 54: Can't create table './hbs/#sql-34ef_f.frm' (errno: 150)
How-To-Repeat:
Save this text as schema_mysql.sql and run it as noted in the description line. database called 'hbs', user called 'ts'
/* ** MySQL schema for the Holiay Booking System within FJS. ** ** by Ian Hartas, Kid01. ** */
drop table if exists ts_proj_team; drop table if exists ts_resource_mgrs; drop table if exists ts_entry; drop table if exists ts_user_details; drop table if exists ts_project_code_details; drop table if exists ts_project_details; drop table if exists ts_team; drop table if exists ts_programme; drop table if exists ts_location_entry; drop table if exists ts_default_location;
create table ts_team ( TEAM_ID integer(8) not null primary key ,TEAM_NAME varchar(30) not null ,team_admin_email varchar(100) ) type = InnoDB;
create table ts_programme ( PROGRAMME_ID integer(8) not null primary key ,PROGRAMME_NAME varchar(30) not null ) type = InnoDB;
create table ts_user_details ( USER_ID varchar(8) not null primary key ,TEAM_ID integer(8) ,STANDARD_HOURS float(30,2) ,USER_NAME varchar(40) not null ,PASSWORD varchar(20) not null ,DEFAULT_LOCATION_ID integer(8) ,ACTIVE CHAR(1) -- check(ACTIVE in ('Y','N')) ,DESCRIPTION varchar(255) ,EMAIL_ADDRESS varchar(100) ,HOURLY_RATE decimal(9,2) ,ACCESS_LEVEL integer(8) default 0 ,charge_rate varchar(20) ,start_date datetime ,holiday_entitlement decimal(9,2) ,holiday_taken decimal(9,2) ,holiday_carry_over decimal(9,2) ,holiday_bought_sold decimal(9,2) ,holiday_hours varchar(200) ) type = InnoDB;
alter table ts_user_details add constraint u_t foreign key (team_id) references ts_team(team_id);
create table ts_project_details ( PROJECT_ID integer(8) not null primary key ,PROJECT_NAME varchar(60) not null ,DESCRIPTION varchar(255) ,ACTIVE CHAR(1) -- check(ACTIVE in ('Y','N')) ) type = InnoDB;
create table ts_project_code_details ( PROJECT_CODE_ID integer(8) not null primary key ,PROJECT_ID integer(8) not null ,PROJECT_CODE varchar(10) not null ,PROJECT_CODE_NAME varchar(60) not null ,DESCRIPTION varchar(255) ,UTIL_IND CHAR(1) ,ACTIVE CHAR(1) -- check(ACTIVE in ('Y','N')) ) type = InnoDB;
alter table ts_project_code_details add constraint pc_t foreign key (project_id) references ts_project_details(project_id);
create table ts_proj_team ( TEAM_ID integer(8) not null ,PROJECT_ID integer(8) not null ) type = InnoDB;
alter table ts_proj_team add constraint pt_p foreign key (project_id) references ts_project_details(project_id); alter table ts_proj_team add constraint pt_t foreign key (team_id) references ts_team(team_id);
create table ts_resource_mgrs ( TEAM_ID integer(8) not null ,USER_ID varchar(8) not null ) type = InnoDB;
alter table ts_resource_mgrs add constraint rm_p foreign key(team_id) references ts_team(team_id); alter table ts_resource_mgrs add constraint rm_u foreign key(user_id) references ts_user_details(user_id);
create table ts_entry ( ID integer(38) not null primary key ,USER_ID varchar(8) not null ,PROJECT_ID integer(8) ,PROJECT_CODE_ID integer(8) ,TEAM_ID integer(8) ,HOURS FLOAT(30,2) ,EDATE datetime ,CDATE datetime ,MDATE datetime ,LOCKED CHAR(1) -- check(LOCKED in ('Y','N','D')) ,UPDATE_HOURS FLOAT(30,2) ,UPDATE_PROJECT_ID integer(8) ,UPDATE_PROJECT_CODE_ID integer(8) ,DESCRIPTION varchar(255) ,nbhours decimal(9,2) ,update_nbhours decimal(9,2) ) type = InnoDB;
alter table ts_entry add constraint e_p foreign key (project_id) references ts_project_details(project_id);
alter table ts_entry add constraint ec_p foreign key (project_code_id) references ts_project_code_details(project_code_id);
alter table ts_entry add constraint e_u foreign key (user_id) references ts_user_details(user_id);
/* ** Staff movement tracking */
create table ts_location_entry ( USER_ID varchar(8) not null ,LDATE datetime ,AMPM CHAR(2) ,LOCATION varchar(40) ) type = InnoDB;
create table ts_default_location ( DEFAULT_LOCATION_ID integer(8) not null primary key ,DEFAULT_LOCATION varchar(40) ,location_country char(3) ) type = InnoDB;
create index ts_proj_code_id_idx on ts_project_code_details (project_id);
create index ts_proj_team_idx on ts_proj_team (team_id, project_id);
create index ts_entry_index on ts_entry (user_id, edate) ;
create index loc_ind on ts_location_entry (user_id,ldate,ampm) ;
# create sequence next_default_location_id increment by 1 start with 1000; create table next_default_location_id (id INT NOT NULL); insert into next_default_location_id values (999);
# create sequence next_team_id increment by 1 start with 1000; create table next_team_id (id INT NOT NULL); insert into next_team_id values (999);
# create sequence next_programme_id increment by 1 start with 1000; create table next_programme_id (id INT NOT NULL); insert into next_programme_id values (999);
# create sequence next_project_id increment by 1 start with 1000; drop table if exists next_project_id; create table next_project_id (id INT NOT NULL); insert into next_project_id values (999);
# create sequence next_project_code_id increment by 1 start with 1000; drop table if exists next_project_code_id; create table next_project_code_id (id INT NOT NULL); insert into next_project_code_id values (999);
# create sequence next_entry_id increment by 1 start with 1000; drop table if exists next_entry_id; create table next_entry_id (id INT NOT NULL); insert into next_entry_id values (999);
# End of schema
Fix:None known, other than to remove the "alter table" directives.
Submitter-Id: Ian Hartas
Originator: root
Organization:
Fujitsu Services Westfields House, West Avenue, Kidsgrove, Staffordshire, UK.
MySQL support: none
Synopsis: Syntax accepted with MyISAM is reject by InnoDB - Fails on Linux and Solaris
Severity: non-critical
Priority: low
Category: mysql
Class: sw-bug
Release: mysql-4.1.0-alpha (Source distribution)
Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.1.0-alpha, for pc-linux on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.0-alpha-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 5 days 21 hours 41 min 18 sec
Threads: 1 Questions: 110 Slow queries: 0 Opens: 99 Flush tables: 1 Open tables: 10 Queries per second avg: 0.000
C compiler: gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
C++ compiler: g++ (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
Environment:
Linux RedHat 8 and also Solaris 2.6 System: Linux lifeboat 2.4.18-24.8.0smp #1 SMP Fri Jan 31 06:03:47 EST 2003 i686 i686 i386 GNU/Linux Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking -- host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 14 Jan 29 14:54 /lib/libc.so.6 -> libc-2.2.93.so -rwxr-xr-x 1 root root 1235468 Sep 6 2002 /lib/libc-2.2.93.so -rw-r--r-- 1 root root 2233342 Sep 5 2002 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Sep 5 2002 /usr/lib/libc.so Configure command: ./configure
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
