>Description:
When using MySQL with InnoDB table type, and turning on the
table case-insensitivity flag in the config file
(set-variable = lower_case_table_names=1), MySQL/InnoDB does not
correctly handle the case-insensitivity of the target table name in
the REFERENCES part of the FOREIGN KEY clause. This is true for
both the CREATE TABLE and ALTER TABLE variations. Note the
column names and the referer table name (in the case of the ALTER
TABLE clause) are correctly treated as case insensitive.
This problem may need to be posted to InnoDB, but I couldn't
locate any method of notifying them of bug reports.
Example script which shows the cases that work and the one that
doesn't is attached in the How-To-Repeat.
<precise description of the problem (multiple lines)>
>How-To-Repeat:
drop table if exists foo1;
create table foo1(
pk1 integer NOT NULL,
primary key(pk1)
) type = innodb;
##
## This will work fine
##
drop table if exists foo2;
create table foo2(
pk1 integer NOT NULL,
fk1 integer,
foreign key (fk1) references foo1 (pk1),
index ix1 (fk1)
) type = innodb;
##
## As will this
##
drop table if exists foo2;
create table foo2(
pk1 integer NOT NULL,
fk1 integer,
foreign key (FK1) references foo1 (PK1),
index ix1 (fk1)
) type = innodb;
##
## But this won't
## In my case, the specific error was:
## ERROR 1005: Can't create table './dbname/foo2.frm' (errno: 150)
##
drop table if exists foo2;
create table foo2(
pk1 integer NOT NULL,
fk1 integer,
foreign key (fk1) references FOO1 (pk1),
index ix1 (fk1)
) type = innodb;
>Fix:
<how to correct or work around the problem, if known (multiple lines)>
>Submitter-Id: <submitter ID>
>Originator: Eugene Bekker
>Organization:
<organization of PR author (multiple lines)>
>MySQL support: none
>Synopsis: case-sensitivity broken in FK clause for MySQL+InnoDB
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.50 (Official MySQL RPM)
>Environment:
RedHat 7.1
Using pre-packaged 3.23.50 (Max) from www.mysql.com
<machine, os, target, libraries (multiple lines)>
System: Linux betelgeuse.powervision.com 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686
unknown
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/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-81)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mcpu=pentiumpro'
CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mcpu=pentiumpro' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Feb 2 07:17 /lib/libc.so.6 -> libc-2.2.2.so
-rwxr-xr-x 1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so
-rw-r--r-- 1 root root 26350254 Apr 6 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 6 2001 /usr/lib/libc.so
Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc
--without-berkeley-db --without-innodb --enable-assembler --enable-local-infile
--with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
--with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin
--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql
--infodir=/usr/info --includedir=/usr/include --mandir=/usr/man
'--with-comment=Official MySQL RPM'
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
