Harald,
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.
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'?
What is your my.cnf like?
Regards,
Heikki
heikki@donna:~/mysql-4.0.1-alpha-pc-linux-gnu-i686/bin> mysqld
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file ./ibdata1 size to 64 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
020208 16:31:36 InnoDB: Started
020208 16:31:36 mysqld: Table 'mysql.func' doesn't exist
020208 16:31:36 Can't open mysql/func table
mysqld: ready for connections
....................
heikki@donna:~/mysql-4.0.1-alpha-pc-linux-gnu-i686/bin> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.1-alpha
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql>
mysql> CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id) ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE t2 (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT
NUL
L,
-> PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES
t1(
id)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> 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;
Query OK, 0 rows affected (0.01 sec)
mysql>
..........................
===========================================
020208 16:38:29 INNODB TABLE MONITOR OUTPUT
===========================================
--------------------------------------
TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 2
COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; FOR_NAME:
DATA_VARCHAR DA
TA_ENGLISH len 0 prec 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0;
N_COL
S: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0;
DB_TRX_I
D: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR
len 7
prec 0;
INDEX: name ID_IND, table name SYS_FOREIGN, id 0 11, fields 1/6, type 3
root page 46, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
INDEX: name FOR_IND, table name SYS_FOREIGN, id 0 12, fields 1/2, type 0
root page 47, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: FOR_NAME ID
INDEX: name REF_IND, table name SYS_FOREIGN, id 0 13, fields 1/2, type 0
root page 48, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: REF_NAME ID
--------------------------------------
TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 2
COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; POS: DATA_INT len 4
prec
0; FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_COL_NAME:
DATA_VARC
HAR DATA_ENGLISH len 0 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0;
DB_
TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS
DATA_ROLL_PTR l
en 7 prec 0;
INDEX: name ID_IND, table name SYS_FOREIGN_COLS, id 0 14, fields 2/6, type
3
root page 49, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
--------------------------------------
TABLE: name test/innodb_table_monitor, id 0 18, columns 5, indexes 1,
appr.rows
0
COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6
prec
0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS
DATA_ROLL
_PTR len 7 prec 0;
INDEX: name GEN_CLUST_INDEX, table name test/innodb_table_monitor, id 0
20, fi
elds 0/4, type 1
root page 55, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a
--------------------------------------
TABLE: name test/t1, id 0 13, columns 5, indexes 1, appr.rows 0
COLUMNS: id: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6
prec
0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS
DATA_ROL
L_PTR len 7 prec 0;
INDEX: name PRIMARY, table name test/t1, id 0 15, fields 1/3, type 3
root page 50, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: id DB_TRX_ID DB_ROLL_PTR
FOREIGN KEY CONSTRAINT 0_15: test/t2 ( t1id )
REFERENCES test/t1 ( id )
--------------------------------------
TABLE: name test/t2, id 0 14, columns 6, indexes 2, appr.rows 0
COLUMNS: id: DATA_INT len 4 prec 0; t1id: DATA_INT len 4 prec 0;
DB_ROW_ID: DA
TA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec
0; D
B_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
INDEX: name PRIMARY, table name test/t2, id 0 16, fields 1/4, type 3
root page 51, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: id DB_TRX_ID DB_ROLL_PTR t1id
INDEX: name t1id, table name test/t2, id 0 17, fields 1/2, type 0
root page 52, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: t1id id
FOREIGN KEY CONSTRAINT 0_15: test/t2 ( t1id )
REFERENCES test/t1 ( id )
FOREIGN KEY CONSTRAINT 0_17: test/t3 ( t2id )
REFERENCES test/t2 ( id )
--------------------------------------
TABLE: name test/t3, id 0 16, columns 6, indexes 2, appr.rows 0
COLUMNS: id: DATA_INT len 4 prec 0; t2id: DATA_INT len 4 prec 0;
DB_ROW_ID: DA
TA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec
0; D
B_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
INDEX: name PRIMARY, table name test/t3, id 0 18, fields 1/4, type 3
root page 53, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: id DB_TRX_ID DB_ROLL_PTR t2id
INDEX: name t2id, table name test/t3, id 0 19, fields 1/2, type 0
root page 54, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: t2id id
FOREIGN KEY CONSTRAINT 0_17: test/t3 ( t2id )
REFERENCES test/t2 ( id )
-----------------------------------
END OF INNODB TABLE MONITOR OUTPUT
==================================
-----Original Message-----
From: Harald Fuchs <[EMAIL PROTECTED]>
To: Heikki Tuuri <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Friday, February 08, 2002 3:50 PM
Subject: Re: InnoDB foreign keys crash MySQL
>In article <003b01c1b0a4$351665f0$540ec5c2@omnibook>,
>"Heikki Tuuri" <[EMAIL PROTECTED]> writes:
>
>> Harald,
>> I tested the below with 3.23.48, and it worked ok.
>
>> Could it be that you have not used DROP TABLE or DROP DATABASE to remove
>> InnoDB tables? Then the internal data dictionary may be out-of-sync from
the
>> .frm files of your tables.
>
>I just tried it again on a fresh MySQL/InnoDB installation ("a new
>database to be created" etc) and it still crashes.
>
>> Please use innodb_table_monitor (section 9.1 in
>> http://www.innodb.com/ibman.html ) to print the contents of the internal
>> data dictionary, and compare it to the .frm files you have for the
tables.
>> Please send the output to the mailing list.
>
>Before the crash:
>
>>> ===========================================
>>> 020208 14:42:39 INNODB TABLE MONITOR OUTPUT
>>> ===========================================
>>> --------------------------------------
>>> TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 0
>>> COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; FOR_NAME:
DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH
len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID
len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR:
DATA_SYS DATA_ROLL_PTR len 7 prec 0;
>>> INDEX: name ID_IND, table name SYS_FOREIGN, id 0 11, fields 1/6, type
3
>>> root page 46, appr.key vals 0, leaf pages 1, size pages 1
>>> FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
>>> INDEX: name FOR_IND, table name SYS_FOREIGN, id 0 12, fields 1/2, type
0
>>> root page 47, appr.key vals 0, leaf pages 1, size pages 1
>>> FIELDS: FOR_NAME ID
>>> INDEX: name REF_IND, table name SYS_FOREIGN, id 0 13, fields 1/2, type
0
>>> root page 48, appr.key vals 0, leaf pages 1, size pages 1
>>> FIELDS: REF_NAME ID
>>> --------------------------------------
>>> TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 0
>>> COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; POS: DATA_INT len
4 prec 0; FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0;
REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; DB_ROW_ID: DATA_SYS
DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0;
DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
>>> INDEX: name ID_IND, table name SYS_FOREIGN_COLS, id 0 14, fields 2/6,
type 3
>>> root page 49, appr.key vals 0, leaf pages 1, size pages 1
>>> FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
>>> --------------------------------------
>>> TABLE: name test/innodb_table_monitor, id 0 18, columns 5, indexes 1,
appr.rows 0
>>> COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len
6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR:
DATA_SYS DATA_ROLL_PTR len 7 prec 0;
>>> INDEX: name GEN_CLUST_INDEX, table name test/innodb_table_monitor, id
0 20, fields 0/4, type 1
>>> root page 51, appr.key vals 0, leaf pages 1, size pages 1
>>> FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a
>>> -----------------------------------
>>> END OF INNODB TABLE MONITOR OUTPUT
>>> ==================================
>
>No tables whatsoever, as you can see.
>
>During the crash:
>
>>> InnoDB: foreign constraint creation failed;
>>> InnoDB: internal error number 17
>>> InnoDB: Assertion failure in thread 53260 in file dict0crea.c line 1241
>>> InnoDB: We intentionally generate a memory trap.
>>> InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
>>> mysqld got signal 11;
>>> This could be because you hit a bug. It is also possible that this
binary
>>> or one of the libraries it was linked against is corrupt, improperly
built,
>>> or misconfigured. This error can also be caused by malfunctioning
hardware.
>>> We will try our best to scrape up some info that will hopefully help
diagnose
>>> the problem, but since we have already crashed, something is definitely
wrong
>>> and this may fail.
>>>
>>> key_buffer_size=67104768
>>> record_buffer=258048
>>> sort_buffer=2097144
>>> max_used_connections=1
>>> max_connections=100
>>> threads_connected=2
>>> It is possible that mysqld could use up to
>>> key_buffer_size + (record_buffer + sort_buffer)*max_connections = 295531
K
>>> bytes of memory
>>> Hope that's ok; if not, decrease some variables in the equation.
>>>
>>> InnoDB: Thread 20486 stopped in file os0sync.c line 374
>>> Attempting backtrace. You can use the following information to find out
>>> where mysqld died. If you see no messages after this, something went
>>> terribly wrong...
>>> Stack range sanity check OK, backtrace follows:
>>> 0x807db8f
>>> 0x82bc35a
>>> 0x817c93b
>>> 0x817fd18
>>> 0x81a2725
>>> 0x80d99c3
>>> 0x80cd6ed
>>> 0x80c1f46
>>> 0x80de9c2
>>> 0x808662a
>>> 0x808a522
>>> 0x8085117
>>> 0x808a954
>>> 0x8084556
>>> Stack trace seems successful - bottom reached
>>> Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
>>> stack trace is much more helpful in diagnosing the problem, so please do
>>> resolve it
>>> Trying to get some variables.
>>> Some pointers may be invalid and cause the dump to abort...
>>> thd->query at 0x8569f98 = 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
>>> thd->thread_id=4
>>>
>>> Successfully dumped variables, if you ran with --log, take a look at the
>>> details of what thread 4 did to cause the crash. In some cases of
really
>>> bad corruption, the values shown above may be invalid.
>>>
>>> The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
>>> information that should help you find out what is causing the crash.
>>>
>>> Number of processes running now: 0
>>> 020208 14:43:59 mysqld restarted
>>> InnoDB: Database was not shut down normally.
>>> InnoDB: Starting recovery from log files...
>>> InnoDB: Starting log scan based on checkpoint at
>>> InnoDB: log sequence number 0 56356
>>> InnoDB: Doing recovery: scanned up to log sequence number 0 60855
>>> InnoDB: Starting an apply batch of log records to the database...
>>> InnoDB: Apply batch completed
>>> 020208 14:44:03 InnoDB: Started
>>> /usr/sbin/mysqld: ready for connections
>
>After the crash:
>
>>> ===========================================
>>> 020208 14:46:03 INNODB TABLE MONITOR OUTPUT
>>> ===========================================
>>> --------------------------------------
>>> TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 1
>>> COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; FOR_NAME:
DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH
len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID
len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR:
DATA_SYS DATA_ROLL_PTR len 7 prec 0;
>>> INDEX: name ID_IND, table name SYS_FOREIGN, id 0 11, fields 1/6, type
3
>>> root page 46, appr.key vals 1, leaf pages 1, size pages 1
>>> FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
>>> INDEX: name FOR_IND, table name SYS_FOREIGN, id 0 12, fields 1/2, type
0
>>> root page 47, appr.key vals 1, leaf pages 1, size pages 1
>>> FIELDS: FOR_NAME ID
>>> INDEX: name REF_IND, table name SYS_FOREIGN, id 0 13, fields 1/2, type
0
>>> root page 48, appr.key vals 1, leaf pages 1, size pages 1
>>> FIELDS: REF_NAME ID
>>> --------------------------------------
>>> TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 1
>>> COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; POS: DATA_INT len
4 prec 0; FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0;
REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; DB_ROW_ID: DATA_SYS
DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0;
DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
>>> INDEX: name ID_IND, table name SYS_FOREIGN_COLS, id 0 14, fields 2/6,
type 3
>>> root page 49, appr.key vals 1, leaf pages 1, size pages 1
>>> FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
>>> --------------------------------------
>>> TABLE: name test/innodb_table_monitor, id 0 22, columns 5, indexes 1,
appr.rows 0
>>> COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len
6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR:
DATA_SYS DATA_ROLL_PTR len 7 prec 0;
>>> INDEX: name GEN_CLUST_INDEX, table name test/innodb_table_monitor, id
0 24, fields 0/4, type 1
>>> root page 51, appr.key vals 0, leaf pages 1, size pages 1
>>> FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a
>>> --------------------------------------
>>> TABLE: name test/t1, id 0 19, columns 5, indexes 1, appr.rows 0
>>> COLUMNS: id: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID
len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR:
DATA_SYS DATA_ROLL_PTR len 7 prec 0;
>>> INDEX: name PRIMARY, table name test/t1, id 0 21, fields 1/3, type 3
>>> root page 52, appr.key vals 0, leaf pages 1, size pages 1
>>> FIELDS: id DB_TRX_ID DB_ROLL_PTR
>>> FOREIGN KEY CONSTRAINT 0_21: test/t2 ( t1id )
>>> REFERENCES test/t1 ( id )
>>> --------------------------------------
>>> TABLE: name test/t2, id 0 20, columns 6, indexes 2, appr.rows 0
>>> COLUMNS: id: DATA_INT len 4 prec 0; t1id: DATA_INT len 4 prec 0;
DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS
DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0;
>>> INDEX: name PRIMARY, table name test/t2, id 0 22, fields 1/4, type 3
>>> root page 53, appr.key vals 0, leaf pages 1, size pages 1
>>> FIELDS: id DB_TRX_ID DB_ROLL_PTR t1id
>>> INDEX: name t1id, table name test/t2, id 0 23, fields 1/2, type 0
>>> root page 54, appr.key vals 0, leaf pages 1, size pages 1
>>> FIELDS: t1id id
>>> FOREIGN KEY CONSTRAINT 0_21: test/t2 ( t1id )
>>> REFERENCES test/t1 ( id )
>>> -----------------------------------
>>> END OF INNODB TABLE MONITOR OUTPUT
>>> ==================================
>
>Apparently t1 and t2 were created correctly, but for t3 there's only a
>.frm file without its InnoDB counterpart.
>
>Maybe the InnoDB version of your MySQL 3.23.48 is newer than that of
>my MySQL 4.0.1 and the bug got fixed in the meantime?
---------------------------------------------------------------------
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
