M. Dee, yes, this is a known bug. I already fixed it in spring, but then inadvertently wiped out the changes :(. Let us try to keep this bug in mind so that it gets fixed soon.
Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL ----- Original Message ----- From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, October 02, 2003 5:39 PM Subject: Creating InnoDB tables with ansi option fails > >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=/u sr/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 --man dir=/usr/share/gcc-data/i686-pc-linux-gnu/3.2/man --infodir=/usr/share/gcc-d ata/i686-pc-linux-gnu/3.2/info --enable-shared --host=i686-pc-linux-gnu --ta rget=i686-pc-linux-gnu --with-system-zlib --enable-languages=c,c++,ada,f77,o bjc,java --enable-threads=posix --enable-long-long --disable-checking --enab le-cstdio=stdio --enable-clocale=generic --enable-__cxa_atexit --enable-vers ion-specific-runtime-libs --with-gxx-include-dir=/usr/lib/gcc-lib/i686-pc-li nux-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-poin ter -DHAVE_ERRNO_AS_DEFINE=1 -DUSE_OLD_FUNCTIONS' CXX='g++' CXXFLAGS='-march=pentium3 -O3 -pipe -mfpmath=sse -msse -mmmx -fomit-frame-po inter -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-poin ter -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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]