Harald, I was now able to repeat the error and fixed it. If one defines a non-latin1 character set as the default (german1 in your case), then the sorting order of the InnoDB internal datatype DATA_VARCHAR was undefined, and foreign key system tables did not work, because they contain DATA_VARCHAR columns.
The fix probably makes it to upcoming MySQL-4.0.2, if Monty has not yet frozen the 4.0.2 codebase. It will also be in 3.23.49. Best regards, Heikki Innobase Oy -----Original Message----- From: Harald Fuchs <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]> Date: Saturday, February 09, 2002 3:59 PM Subject: Re: InnoDB foreign keys crash MySQL >> Harald, > >Hi, Heikki! > ... > >> I tested now with the official Linux binary of 4.0.1 (not -max) and it >> worked ok on our dual Xeon Linux-2.4.16-SMP-64GB. I did not define any >> InnoDB startup options in my.cnf. > >I just downloaded mysql-4.0.1-alpha-pc-linux-gnu-i686.tar.gz (now also >not -max) from the German mirror and tried it. I still get the crash. > >My system is a 550MHz AMD K6-2 running kernel 2.2.18, if this should >matter. I don't think this is a hardware bug: I tried the same script >on another Linux box (dual 450MHz Pentium II, 2.2.18-SMP) running >4.0.1-max and also get the crash. > >> When you recreated the InnoDB data files, did you remember to remove the >> .frm files of the InnoDB tables t1, t2, t3 in the database directory 'test'? > >Yes. Actually, I did "rm -rf test/* ib*" before starting MySQL. > >> What is your my.cnf like? > >After removing all InnoDB directives, there's not much left: > > [client] > port = 3306 > > [mysqld] > port = 3306 > user = mysql > skip-locking > set-variable = join_buffer_size=512k > set-variable = key_buffer_size=64M > set-variable = record_buffer=256k > set-variable = sort_buffer=2M > set-variable = table_cache=16 > set-variable = tmp_table_size=32M > log-slow-queries > > default-character-set=german1 > >The SQL command sequence causing the crash is as follows: > > DROP TABLE IF EXISTS t1; > DROP TABLE IF EXISTS t2; > DROP TABLE IF EXISTS t3; > > CREATE TABLE t1 ( > id INT UNSIGNED NOT NULL AUTO_INCREMENT, > PRIMARY KEY (id) > ) TYPE=InnoDB; > > CREATE TABLE t2 ( > id INT UNSIGNED NOT NULL AUTO_INCREMENT, > t1id INT UNSIGNED NOT NULL, > PRIMARY KEY (id), > KEY (t1id), > FOREIGN KEY (t1id) REFERENCES t1(id) > ) TYPE=InnoDB; > > CREATE TABLE t3 ( > id INT UNSIGNED NOT NULL AUTO_INCREMENT, > t2id INT UNSIGNED NOT NULL, > PRIMARY KEY (id), > KEY (t2id), > FOREIGN KEY (t2id) REFERENCES t2(id) > ) TYPE=InnoDB; > > DROP TABLE IF EXISTS t3; > DROP TABLE IF EXISTS t2; > DROP TABLE IF EXISTS t1; > >Any other information I could supply? --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
