Philip, > ----- Forwarded message from Philip Molter <[EMAIL PROTECTED]> ----- > > Date: Mon, 10 Mar 2003 08:30:22 -0600 > From: Philip Molter <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: InnoDB Assertion Error > > THE SITUATION: > We're running MySQL 3.23.51. We have a table which has a primary-keyed > field 'sid'. We have four tables what have foreign key references > on that field. We deleted all rows from those four tables that > had values in that foreign key index and then tried to delete the > row in the main table. The main table delete failed with a parent > row reference error.
please tell what SHOW CREATE TABLE says for these tables. What SELECT statements prove that there are no child rows? Have you run CHECK TABLE on the tables? Note also that if you have a self-referencing row, you have to use SET FOREIGN_KEY_CHECKS=0; <do the delete> SET FOREIGN_KEY_CHECKS=1; to delete the row. InnoDB is not smart enough to notice that the DELETE will remove also the 'child' row. > Fine, MySQL/InnoDB have gone through a few revisions and perhaps > this bug is fixed. Certainly, running this old version isn't going > to help us. > > THE PROBLEM: > After downloading and compiling MySQL 3.23.55 (we use the compile > flags from MySQL's web site, but we compiled our own), we switched > our config over to the new compilation (same data, same config, > etc. just a new binary) and started up. On a table scan (SHOW > TABLE STATUS, `mysql` without -A, etc.), MySQL crashes with an > assertion: The below assertion probably means that there is not a suitable index in a referenced table. I do not see how that could happen once you have succeeded to create the foreign key constraints. I tested the below with .51 and .56, but got no error. Please send me the whole schema which takes part in this. All referencing or referenced tables. mysqldump -d databasename dumps table defs. Do you remember what kind of ALTER TABLE did you use? Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL sql query > 030307 21:08:02 mysqld restarted > 030307 21:08:03 InnoDB: Started > /usr/local/mysql-3.23.55/libexec/mysqld: ready for connections > 030307 21:08:21 InnoDB: Assertion failure in thread 15 in file dict0load.c line 677 > 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=33550336 > record_buffer=131072 > sort_buffer=16777208 > max_used_connections=3 > max_connections=500 > threads_connected=4 > It is possible that mysqld could use up to > key_buffer_size + (record_buffer + sort_buffer)*max_connections = 4094456 K > bytes of memory > Hope that's ok, if not, decrease some variables in the equation > > 030307 21:08:21 mysqld restarted > > In the MySQL client it looks like this: > > mysql> use tx; > Reading table information for completion of table and column names > You can turn off this feature to get a quicker startup with -A > > Didn't find any fields in table 'task' > Didn't find any fields in table 'taxbase' > Didn't find any fields in table 'terms' > Didn't find any fields in table 'ticket' > Didn't find any fields in table 'ticket_master' > Didn't find any fields in table 'ticket_perm_bits' > Didn't find any fields in table 'traffic_service_history' > Didn't find any fields in table 'uid' > Database changed > > If it's reading the tables in order, it's having a problem with > one of the following tables: > > CREATE TABLE sync_times ( > system varchar(48) NOT NULL default '', > host varchar(255) NOT NULL default '', > tstamp datetime default NULL, > PRIMARY KEY (system,host) > ) TYPE=InnoDB; > > CREATE TABLE task ( > task int(11) NOT NULL auto_increment, > flags varchar(255) default NULL, > depends int(11) default NULL, > type varchar(40) default NULL, > name varchar(100) default NULL, > description text, > department varchar(20) default NULL, > owner varchar(20) default NULL, > customer int(11) default NULL, > contact int(11) default NULL, > origin varchar(255) default NULL, > status varchar(20) default NULL, > assigned varchar(20) default NULL, > entry_time datetime default NULL, > due_time datetime default NULL, > finish_time datetime default NULL, > tstamp timestamp(14) NOT NULL, > PRIMARY KEY (task), > KEY _task_ (task), > KEY _customer_ (customer), > KEY _contact_ (contact), > KEY _depends_ (depends), > KEY _name_ (name), > FOREIGN KEY (`depends`) REFERENCES `tx.task` (`task`), > FOREIGN KEY (`customer`) REFERENCES `tx.customer` (`customer`), > FOREIGN KEY (`contact`) REFERENCES `tx.contact` (`contact_id`) > ) TYPE=InnoDB; > > Backing out to the old version works fine. The only glaringly > obvious thing I see there is a table putting a foreign key onto > itself, but that should be allowed, no? > > Thanks for any assistance. > Philip > > * Philip Molter > * Texas.net Internet > * http://www.texas.net/ > * [EMAIL PROTECTED] > > --------------------------------------------------------------------- > 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 > > ----- End forwarded message ----- --------------------------------------------------------------------- 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
