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