> -----Ursprüngliche Nachricht-----
> Von: Ian Hartas [mailto:[EMAIL PROTECTED]
> Gesendet: Donnerstag, 3. April 2003 11:40
> An: [EMAIL PROTECTED]
> Betreff: Schema definition fails when converted to InnoDB
>
>
> >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)
This sounds like you are trying to create foreign keys but don't have an index on
these fields.
You may check this.
This would also explain why it works on MyISAM -> this format has no foreign key
feature and ignores these settings, but not with InnoDB.
Bye
Thorsten
> >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/mysql?> [EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
