>Description:
I have a mysql server running with ansi option.
When I run my database create script I get:
ERROR 1005: Can't create table './pinball/B.frm' (errno: 150)
This seems to indicate that that:
150 = Foreign key constraint is incorrectly formed
I checked everything about the foreign key constraint but could find
no errors in the syntax. I seems that it has something to do with the
double-quotes (") when using InnoDB tables.
>How-To-Repeat:
Running the following script reproduces the problem:
CREATE TABLE "A"
(
"A_ID" integer NOT NULL,
PRIMARY KEY ("A_ID")
) TYPE=INNODB;
CREATE TABLE "B"
(
"A_ID" integer,
INDEX("A_ID"),
FOREIGN KEY ("A_ID") REFERENCES "A" ("A_ID")
) TYPE=INNODB;
I get no errors when running it like:
CREATE TABLE A
(
A_ID integer NOT NULL,
PRIMARY KEY (A_ID)
) TYPE=INNODB;
CREATE TABLE B
(
A_ID integer,
INDEX(A_ID),
FOREIGN KEY (A_ID) REFERENCES A (A_ID)
) TYPE=INNODB;
or:
CREATE TABLE "A"
(
"A_ID" integer NOT NULL,
PRIMARY KEY ("A_ID")
);
CREATE TABLE "B"
(
"A_ID" integer,
INDEX("A_ID"),
FOREIGN KEY ("A_ID") REFERENCES "A" ("A_ID")
);
>Fix:
Unknown
>Submitter-Id: Marijn Dee
>Originator:
>Organization:
Marijn Dee Data Distilleries B.V.
e-mail: [EMAIL PROTECTED] Kruislaan 402
1098 SM Amsterdam
The Netherlands
>
>MySQL support: none
>Synopsis: Creating foreign key on InnoDB table with ansi option fails
>Severity: serious
>Priority:
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.13 (Gentoo Linux mysql-4.0.13-r4)
>C compiler: gcc (GCC) 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r1, propolice)
>C++ compiler: g++ (GCC) 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r1, propolice)
>Environment:
System: Linux massicot 2.6.0-test5-mm4 #1 Tue Sep 23 14:49:07 CEST 2003 i686 Pentium
III (Coppermine) GenuineIntel 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/i686-pc-linux-gnu/3.2.3/specs
Configured with: /tmp/portage/gcc-3.2.3-r1/work/gcc-3.2.3/configure --prefix=/usr
--bindir=/usr/i686-pc-linux-gnu/gcc-bin/3.2
--includedir=/usr/lib/gcc-lib/i686-pc-linux-gnu/3.2.3/include
--datadir=/usr/share/gcc-data/i686-pc-linux-gnu/3.2
--mandir=/usr/share/gcc-data/i686-pc-linux-gnu/3.2/man
--infodir=/usr/share/gcc-data/i686-pc-linux-gnu/3.2/info --enable-shared
--host=i686-pc-linux-gnu --target=i686-pc-linux-gnu --with-system-zlib
--enable-languages=c,c++,ada,f77,objc,java --enable-threads=posix --enable-long-long
--disable-checking --enable-cstdio=stdio --enable-clocale=generic
--enable-__cxa_atexit --enable-version-specific-runtime-libs
--with-gxx-include-dir=/usr/lib/gcc-lib/i686-pc-linux-gnu/3.2.3/include/g++-v3
--with-local-prefix=/usr/local --enable-shared --enable-nls --without-included-gettext
Thread model: posix
gcc version 3.2.3 20030422 (Gentoo Linux 1.4 3.2.3-r1, propolice)
Compilation info: CC='gcc' CFLAGS='-march=pentium3 -O3 -pipe -mfpmath=sse -msse -mmmx
-fomit-frame-pointer -DHAVE_ERRNO_AS_DEFINE=1 -DUSE_OLD_FUNCTIONS' CXX='g++'
CXXFLAGS='-march=pentium3 -O3 -pipe -mfpmath=sse -msse -mmmx -fomit-frame-pointer
-felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jul 17 12:30 /lib/libc.so.6 -> libc-2.3.2.so
-rwxr-xr-x 1 root root 1466302 Jul 17 12:30 /lib/libc-2.3.2.so
-rw-r--r-- 1 root root 2663206 Jul 17 12:30 /usr/lib/libc.a
-rwxr-xr-x 1 root root 204 Jul 17 12:30 /usr/lib/libc.so
-rwxr-xr-x 1 root root 580404 Sep 9 10:41 /usr/lib/libc.so.5
Configure command: ./configure '--prefix=/usr' '--host=i686-pc-linux-gnu'
'--mandir=/usr/share/man' '--infodir=/usr/share/info' '--datadir=/usr/share'
'--sysconfdir=/etc' '--localstatedir=/var/lib' '--libexecdir=/usr/sbin'
'--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--with-raid'
'--with-low-memory' '--enable-assembler' '--with-charset=latin1'
'--enable-local-infile' '--with-mysqld-user=mysql' '--with-extra-charsets=all'
'--enable-thread-safe-client' '--with-client-ldflags=-lstdc++' '--with-comment=Gentoo
Linux mysql-4.0.13-r4' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock'
'--with-embedded-server' '--with-berkeley-db=./bdb' '--without-readline'
'--enable-shared' '--enable-static' '--with-libwrap' '--with-innodb' '--with-vio'
'--with-openssl' '--without-debug' 'CC=gcc' 'CFLAGS=-march=pentium3 -O3 -pipe
-mfpmath=sse -msse -mmmx -fomit-frame-pointer -DHAVE_ERRNO_AS_DEFINE=1
-DUSE_OLD_FUNCTIONS' 'CXXFLAGS=-march=pentium3 -O3 -pipe -mfpmath=sse -msse -mmmx
-fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' 'CXX=g++'
'host_alias=i686-pc-linux-gnu'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
