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
---------------------------------------------------------------------
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
